长沙理工大学excel上机实验报告

1、 10/10长沙理工大学excel上机实验报告 上机报告课程名称：EXCEL在财务管理中的应用 学生姓名： 学号： 班级： 专业：会计 指导教师：张勇 实验项目： EXCEL的高级应用 实验目的：掌握EXCEL的公式，函数，数据排序，筛选，分类汇总，模拟运算，规划求解等 实验平台：WINDOWS XP ，EXCEL2003 实验内容和要求：掌握EXCEL的公式，函数（MOD，ROUND，MID，INDEX,MATCH,VLOOKUP,LOOKUP,CHOOSE等主要函数），数据排序，筛选，分类汇总，模拟运算，规划求解等的使用技巧。 实验步骤和结果（空间不够可加页）： 1.公式运用 选中单元格B

2、5，输入公式“=B2B3B4”，鼠标拖拽单元格下脚标至G5 2.函数 1）MOD 2）ROUND 3)MID 4)INDEX 5)MATCH 6)VLOOKUP 7)LOOKUP 3.数据排序 单击表中任意单元格，单击【数据】惨淡，选择【排序】项，系统弹出【排序】对话框； 在【排序】对话框中选择主要关键字“部单击确定。 门”升序，次要关键字“序号”降序； 4.筛选 普通筛选 1)单击表中任一非空单元格； 2)单击【数据】菜单，选择【筛选】中【自动筛选】项。 高级筛选 1）建立条件区域 2）单击表单中任一非空单元格，单击【数据】【筛选】【高级筛选】 3）在高级筛选对话框中作如下选择，单击【确定】

3、，得出结果 1)按部门将序排列； 2)单击任一非空单元格，单击【数据】菜单，选择【分类汇总】项； 3)在【分类汇总】对话框中，做如下选择，单击确定； 6.模拟运算 假设某企业向银行贷款50000元，期限为5年，则可使用模拟运算表来测试不同的利率对月还款的影响。 1)设计模拟运算表结构，如下图: 2）选取包括公式和要进行模拟运算单元格区域在内的单元格区域A4:B14， 在【数据】菜单中单击【模拟运算表】命令，如图： 3）单击确定按钮，结果如图： 7.规划求解 某企业生产甲、乙两种产品及资源的限制条件如下表： 步骤：1）建立优化模型（设x和y分别表示甲乙产品的销量）约束条件如下： 9x+6y340

4、 4x+7y160 15x+18y520 x25 x0,y0,且为整数 2）设计计算表结构。将单元格B9和C9设为可变单元格，分别放甲乙的生产量，将单元格B10作为目标单元格并在其中输入公式： 3)在B11中输入公式：“=SUMPRODUCT(B4:C4,B9:C9)”，在B12中输入公式： “=SUMPRODUCT(B5:C5,B9:C9)”，在B13中输入公式：“=SUMPRODUCT(B6:C6,B9:C9)” 4）在【工具】菜单中单击【规划求解】，在弹出的对话框中输入约束条件，然后关闭【添加约束】对话框，则显示【规划求解参数】对话框，确认无误后单击【确定】，再次弹出对话框，单击【求解】

5、，系统将弹出【规划求解结果】对话框，单击【确定】后得到下图： 实验项目： EXCEL在财务分析中的应用 实验目的：掌握EXCEL在财务分析中的各种分析方法。 实验平台：WINDOWS XP， EXCEL2003 实验内容和要求：掌握EXCEL在财务分析中的各种分析方法的使用。 实验步骤和结果（空间不够可加页）： 1.终止函数2）年金终值 1）整收整付款项的复利终值（1）普通年金终值 （2）先付年金终止（3）递延年金终值 2.现值函数 1）整收整付款项的复利现值 2）年金现值 普通年金现值先付年金现值 3递延年金现值4永续年金 3）函数NPV() 3.年金、本金和利息函数 1）年金函数PMT()

6、 2)年金中的本金函数PPMT() 3)年金中的利息函数IPT() 4.期数函数NPER( ) 5.折现率函数 1）利率函数RATE( ) 2）内含报酬率函数IRR( ) 3）修正内涵报酬率函数MIRR（） 6.折旧计算函数 1）函数SLN（） 2)函数DB( ) 3）函数DDB（） 步骤：1输入B6公式“=DDB($B$1,$B$2,$B$3,B5)”，向右复制到C6：D6； 2输入E6，F6公式“=(B1-B6-C6-D6)/2”。 4）函数VDB（） 5）函数SYD（） 步骤：输入B6公式“=SYD($B$1,$B$2,$B$3,B5)”，向右复制到C6：F6。 7.资产负债表分析 7.

7、1资产负债表结构分析 输入C25单元格公式“=B25/B$25”并复制到B4：B21单元格区域 输入E25单元格公式“=E25/E$25”并复制到E4：E24单元格区域 设置C列和E列数字部分区域【单元格格式】中【数字】分类为【百分比】 7.2资产负债表趋势分析 在表页【0305资产负债表】中选择数据区域A3：D3、A15:D15、A21：D21、A28:D28，选择【插入】【图表】【柱形图】【簇状柱形图】，在源数据对话框中作如下选择，单击确定 输入B5单元格公式“=0305资产负债表!C5-0305资产负债表!B5”，输入C5单元格公式“=B5/0305资产负债表!B5”,输入D5单元格公式

8、“=0305资产负债表!D5-0305资产负债表!C5”，输入E5单元格公式“=D5/0305资产负债表 !C5”，利用填充柄完成全部数据区域的填充 按上述同样方法完成G、H、I、J列的操作 设置C、E、H、I列数字部分区域【单元格格式】中【数字】分类为【百分比】 8.利润表分析 8.1利润表结构分析 输入C4单元格公式“=B4/$B$4”，向下一直复制到C19单元格；输入E4单元格公式“=D4/$D$4”，向下一直复制到E19单元格 选中A4：A19和D4:D19单元格区域，选择【插入】【图表】【柱状图】，在弹出对话框中【系列产生在】选择列，单击【确定】 8.2利润表趋势分析 输入D3单元格

9、公式“=B3-C3”，输入E3单元格公式“=D3/C3”，利用填充柄完成全部数据区域的填充 9.现金流量表分析 9.1现金流量表结构分析 1）总体结构分析 输入B4单元格公式“=现金流量表!B5/(现金流量表!B5+现金流量表!B16+现金流量表!B25)”，输入B7单元格公式“=现金流量表!B16/(现金流量表!B5+现金流量表!B16+现金流量表!B25)”，输入B10单元格公式“=现金流量表!B25/(现金流量表!B5+现金流量表!B16+现金流量表!B25)”，输入B13单元格公式“=B4+B7+B10”，利用填充柄完成C4:D13单元格区域全部数据区域的填充 依据相同原理，填充其他单

10、元格 2）分类结构分析 输入B4单元格公式“=现金流量表!B3/现金流量表!$B$5”，并复制到B5:B6单元格区域 输入C7单元格公式“=现金流量表!B6/现金流量表!$B$10”，并复制到C8:C12单元格区域 9.2现金流量表趋势分析 输入B4单元格公式“=现金流量表!C3-现金流量表!D3”，输入C4单元格公式“=B4/现金流量表!D3”，输入D4单元格公式“=现金流量表!B3-现金流量表!C3”，输入E4单元格公式“=D4/现金流量表!C3” 利用利用填充柄完成全部数据区域的填充 设置C、D两列单元格【单元格格式】中【数字】分类为【百分比】 实验项目： EXCEL在财务预测中的应用

11、实验目的：掌握EXCEL在财务预测中的各种分析方法。 实验平台：WINDOWS XP，EXCEL2003 实验内容和要求：掌握EXCEL在财务预测中的各种分析方法的使用。 实验步骤和结果（空间不够可加页）： 设某公司甲产品2011年112月实际市场销售额（万元）如下所示： 1.移动平均法 1）建立表格如下： 2）选择【工具】【加载宏】，选择“分析 工具”，单击【确定】 3）选择【工具】【数据分析】【分析工具】【移动平均】，在【输入区域】框中输入“$B$3:$B$14”，【间隔】框中输入“4”，【输出区域】框中输入“$C$3”，选中【图标输出】，单击确定 可知，2012年1月份甲产品市场预测销售

12、额为1362.5万元。 2.指数平滑法 步骤： 1）建立表格如下： 2）选择【工具】【数据分析】【指数 平滑】，单击确定，打开【指数平滑】 对话框 2）在【输入区域】框中输入“$B$3:$B$14”，【阻尼系数】框中输入“0.8”，【输出区域】框中输入“$C$3”，选中【图标输出】，单击确定 可知，2012年1月份甲产品市场预测销售额为1283.48万元。 3.回归分析法 1）选择【工具】【数据分析】【回归】 3）单击确定，在弹出的【回归】对话框中，设置各项如下： 4）单击【确定】，结果如下： 5）根据回归结果构建一元回归方程，并求出产品销售额预测值： 由回归结果可知，本一元回归方程为Y =1

13、070.18+29.36X,说明销售额随时间序列呈正向变动关系。对这些数据进行检验分析可知相关系数为0.68，说明因变量与自变量间的相关性高，其他统计检验也已经达到相应的标准。则可得2011年1月份儿的产品销售额为:Y=1070.18+29.36*13=1451.86 实验项目： EXCEL在项目投资决策中的应用 实验目的：掌握EXCEL在项目投资决策中的应用。 实验平台：WINDOWS XP，EXCEL2003 实验内容和要求：掌握EXCEL在项目投资决策中的应用。 实验步骤和结果（空间不够可加页）： 1.投资决策分析的一般方法 1.1非贴现法 设某公司三项投资方案资料如下，资金成本率为8%

14、 步骤：1）计算各方案各年累计金额，输入B10单元格公式“=C5”，输入B11单元格公式“=C5+C6”，输入B1,2单元格公式“=C5+C6+C7”，选择B10：B12单元格区域，单击【复制】，选择D10：D12单元格区域，单击【粘贴】，选择F10：F12单元格区域，单击【粘贴】 2）计算各方案各年未收金额，输入C10单元格公式“=C9-B10”，输入C11单元格公式“=C9-B11”，输入C12单元格公式“=C9-B12”，将上述公式分别复制到E10：E12与G10：G12单元格区域 3）计算投资年，输入C13单元格公式“=COUNTIF(C10：C12,=0)”，将公式复制到E13与G1

15、3单元格中 1）计算投资修正值，输入C14单元格公式“=C10/(B11-B10)”，将公式复制到E14与G14单元格中 2）计算整个方案投资回收期，输入C15单元格公式“=SUM(C13：C14)”，将公式复制到E15与G15单元格中 3）计算各方案的会计收益率，输入C16单元格公式“=AVERAGE(B5：B7)/C4”，将公式复制到E16与G16单元格中 结论：从计算结果可以看出，A方案投资回收期最短，如果该企业的资金成本是8%，则A、B两方案的会计收益率大于资金成本，因此A、B两方案可以接受。而C方案应放弃。 1.2贴现法 设某企业A、B两个互斥的投资方案资料如下，资金成本率为10%：

16、 1）净现值法 步骤：建立表格如下： 计算各年现金净流量，输入D3单元格公式“=B3-C3”，将公式复制到 D4：D11和H3：H11单元格区域 计算净现值，输入E3单元格公式“=NPV(0,D3)”，输入E4单元格公式“=NPV(10%,D4)”，输入E5单元格公式“=SUM(E3:E4)”，输入E11单元格公式“=NPV(10%,0,D6:D10)”，输入E11单元格公式“=E5+E11”，将公式复制到I列相应单元格区域 A项目净现值6131.56元，B项目净现值为8168.45元，单就投资项目净现值分析，两个项目净现值都大于哦，都可行，且B项目优于A项目。 2）现值指数法 重复净现值法步

17、骤，输入E13单元格公式“=ABS(E11/E5)”，并将公式复制到I13单元格 A项目现值指数为1.4215，B项目现值指数为1.6030，都大于1，说明都是可取的，且B项目优于A项目。 3）内含报酬率法 设某公司A、B两个投资项目各年现金流量如下： 步骤： 1立如下表格：2输入B14单元格公式 “=IRR(B$4:B6,-10%)” 输入B15单元格公式“=IRR(B$4:B7)”，拖动单元格B15右下角填充柄，将公式复制到单元格区域B16：B19 将单元格区B14：B19公式复制到单元格区域C14：C19 从内含报酬率指标看，B项目优于A项目。 4）净年值法 步骤：建立表格如下： 输入B

18、5单元格公式“=(PV($D$2,B4,-B3)-B2)/(PV($D$2,B4,-1)”并将其复制到C5单元格 通过比较可以看出，B方案净年值最大，所以B方案为最优方案。 2.固定资产更新决策 2.1固定资产折旧分析 设某公司有一台设备，原值60万元，预计净残值为10万元，预计使用5年。设计表格如下： 1）平均年限法 步骤：输入B7单元格公式“=SLN($B$2,$B$3,$B$4)”，并复制到B8： B11单元格区域 输入B12单元格公式“=SUM(B7:B11)” 2）双倍余额递减法 步骤：输入C7单元格公式“=DDB($B$2,$B$3,$B$4,A7)”，并复制到C8：C11单元格区

19、域 输入C12单元格公式“=SUM(C7:C11)” 3）年数总和法 步骤：输入D7单元格公式“=SYD($B$2,$B$3,$B$4,A7)”，并复制到D8：D11单元格区域 输入D12单元格公式“=SUM(D7:D11)” 4)固定资产折旧方法分析比较图 步骤： 选择【插入】【图表】，在弹出对话框中选择【标准类型】为【折线图】，【子图标类型】为【折线图】 单击【下一步】，在弹出的【源数据】对话框中，输入【数据区域】为“$A$6：$D$11”，选择【系列产生在】为“列” 单击【下一步】，在弹出的源数据对话框中，输入【图表标题】为“固定资产折旧方法比较分析图”，输入【分类（X）轴】为“年份”，

20、输入【分类（Y）轴】为“折旧额（万元）” 单击【下一步】，在弹出的源数据对话框中单击【完成】 2.2固定资产更新决策模型 设某公司有一个设备更新方案，新旧设备的原始资料如下（如购买新设备的话，现在处置旧设备科的净收益40000元）： 步骤：建立表格，输入B17单元格公式“=SLN($B$3,$B$8,$B$4)”并复制到C17：F17单元格区域；输入B27单元格公式 “=SYD($C$3,$C$8,$C$4,B24)”并复制到C27：F27单元格区域 输入B18单元格公式“=B15-B16-B17”并复制到C18：F18单元格区域；输入B28单元格公式“=B25-B26-B27”并复制到C28

21、：F28单元格区域 输入B19单元格公式“=B18*$B$11”并复制到C19：F19单元格区域；输入B29单元格公式“=B28*$B$11”并复制到C29：F29单元格区域 输入B20单元格公式“=B18-B19”并复制到C20：F20单元格区域；输入B30单元格公式“=B28-B29”并复制到C30：F30单元格区域 输入B21单元格公式“=B17+B20”并复制到C21：F21单元格区域；输入B31单元格公式“=B27+B30”并复制到C31：F31单元格区域 输入B22单元格公式“=NPV(B10,B21:F21)”；输入B32单元格公式“=NPV(B10,B31:F31)-C3” 新

22、旧设备的净现值差额=76166.40-434202.03+40000=71764.37（元） 根据上述结果，新旧设备的净现值差额明显大于0，即应当适用新设备。 3.投资风险分析 3.1投资风险的衡量 设某公司明年有A、B两个投资方案，资料如下： 步骤：输入B8单元格公式“=SUMPRODUCT(B4:D4,B3:D3)”；输入B9单元格公式“=SUMPRODUCT(B5:D5,B3:D3)” 输入C8单元格公式“=SUM(B3*(B4-B8)2,C3*(C4-B8)2,D3*(D4- B8)2)”；输入C9单元格公式“=SUM(B3*(B5-B9)2,C3*(C5-B9)2,D3*(D5-B9

23、)2)” 输入D8单元格公式“=SQRT(C8)”；输入D9单元格公式“=SQRT(C9)” 输入E8单元格公式“=D8/B8”；输入E9单元格公式“=D9/B9” 以上计算结果说明B方案的投资风险小于A方案，应当选择B方案做为最优方案。 3.2投资风险决策分析 1）风险调整折现率法 某公司现有甲、乙两个投资方案可供选择，有关资料如下： 步骤：输入B17单元格公式“=SUMPRODUCT(B4:B6,C4:C6)”； 输入B18单元格公式“=SUMPRODUCT(B7:B9,C7:C9)”； 输入B19单元格公式“=SUMPRODUCT(B10:B12,C10:C12)”； 讲上述公式复制到单

24、元格区域D17：D19 输入C17单元格公式“=SQRT(SUMPRODUCT(B4:B6-B17)2,C4:C6)”； 输入C18单元格公式“=SQRT(SUMPRODUCT(B7:B9-B18)2,C7:C9)”； 输入C19单元格公式“=SQRT(SUMPRODUCT(B10:B12-B19)2,C10:C12)”； 将上述公式复制到单元格区域E17：E19 输入C21单元格公式“=NPV($B$13,B17:B19)”, 输入C22单元格公式 “=SQRT(C172/(1+$B$13)(2*$A$17)+C182/(1+$B$13)(2*$A$18)+C192/(1+$B$13) (2

25、*$A$19)”,输入C23单元格公式“=C22/C21”,输入C24单元格公式 “=$B$13+$E$13*C23”,输入C25单元格公式“=NPV(C24,B17:B19)+B3”, 将上述公式复制到单元格区域E21：E25 根据上述结果，公司财务人员可以选择净现值最大的乙方案。 2）风险调整现金流法 步骤：输入B18单元格公式“=SUMPRODUCT(C7:C9,B7:B9)”； 输入B19单元格公式“=SUMPRODUCT(C10:C12,B10:B12)”； 输入B20单元格公式“=SUMPRODUCT(C13:C15,B13:B15)”； 输入B23单元格公式“=SUMPRODUC

26、T(E7:E9,D7:D9)”； 输入B24单元格公式“=SUMPRODUCT(E10:E12,D10:D12)”； 输入B25单元格公式“=SUMPRODUCT(E13:E15,D13:D15)”。 输入C18单元格公式“=SQRT(SUMPRODUCT(C7:C9-B18)2,B7:B9)”； 输入C19单元格公式“=SQRT(SUMPRODUCT(C10:C12-B19)2,B10:B12)”；输入C20单元格公式“=SQRT(SUMPRODUCT(C13:C15-B20)2,B13:B15)”；输入C23单元格公式“=SQRT(SUMPRODUCT(E7:E9-B23)2,D7:D9)

27、”； 输入C24单元格公式“=SQRT(SUMPRODUCT(E10:E12-B24)2,D10:D12)”；输入C25单元格公式“=SQRT(SUMPRODUCT(E13:E15-B25)2,D13:D15)” 输入D18单元格公式“=C18/B18”，复制到D19：D20和D23：D25单元格区域 输入E18单元格公式“=IF(D18$D$2,$E$3,IF(D18$C$2,$D$3,IF (D18$B$2,$C$3,$B$3)”，复制到E19：E20和E23：E25单元格区域 输入F18单元格公式“=B18*E18”，复制到F19：F20和F23：F25单元格区域 输入G18单元格公式“

28、=NPV(B1,F18:F20)+C6”,输入G23单元格公式 “=NPV(B1,F23:F25)+E6” 由计算结果看出，由于A方案各年的现金流的变差系数比较高，其肯定当量的现金流就比较低，最终导致其净现值为负数，A方案不可行。 实验项目： EXCEL在筹资决策中的应用 实验目的：掌握EXCEL在筹资决策中的应用。 实验平台：WINDOWS XP，EXCEL2003 实验内容和要求：掌握EXCEL在筹资决策中的应用 实验步骤和结果（空间不够可加页）： 1.资金需求量的预测分析 1.1销售百分比分析法 输入C2单元格公式“=B2/$C$9”，并复制到B3：B4和E2:E4单元格区域，输入B8单

29、元格公式“=SUM(B2:B6)”，输入E8单元格公式“=SUM(E2:E7)”，输入C8单元格公式“=SUM(C2:C4)”并复制到F8单元格 输入E10单元格公式“=C9*(1+C10)”，输入E11单元格公式“=E10*C12”，输入C13单元格公式“=(E10-C9)*(C8-F8)”，输入C14单元格公式“=E11*(1-C11)”，输入C15单元格公式“=C13-C14” 1.2资金习性预测法 1）高低点法 输入B7单元格公式“=(MAX(C2:C6)-MIN(C2:C6)/(MAX(B2:B6)-MIN(B2:B6)”，输入B8单元格公式“=MAX(C2:C6)-B7*MAX(B

30、2:B6)”，输入B10单元格公式“=B8+B7*B9” 2）散点图法 以B2:C6委会图数据区域，绘制散点图，并作直线与Y轴相交，由图可知交点为60万左右，即为a值 输入B7单元格公式“=(C6-60)/B6”，输入B9单元格公式“=60+B7*B8”3）回归分析法 输入B4单元格公式“=FORECAST(7,B3:G3,B2:G2)” 2.长期借款筹资分析 2.1长期借款基本模型&长期借款双变量分析模型 选择【视图】【工具栏】【窗体】 在B6、B7单元格分别插入【组合框控件】，并分别鼠标右键单击组合框控件，选择【设置控件属性】选项，设置B6单元格组合框控件如下（1），设置B7单元格组合框控件如下（2） （1） （2） 输入B8单元格公式“=IF(B7=1,B5*1,IF(B7=2,B5*2,IF(B7=3,B5*4,B5*12)”，输入B9单元格公式“=IF(INDEX(D3:D4,B6)=期末,PMT(B4/(B8/B5),B8,- B3,0),PMT(B4/(B8/B5),B8,-B3,