Excel在财务管理中的应用实验报告.docx

1、Excel在财务管理中的应用实验报告实验1：Excel 基础知识一、实验目的和实验内容表中是“2011级学生成绩表”，实验要求如下：（1）用公式和函数求出每位同学的总成绩和平均分。（2）将工作表重新命名为“学生成绩表”。（3）将“2011级学生成绩表”这一标题设置格式为黑体14号字，居中显示，颜色为红色。表格中的文字型数据居中显示。（4）将“学生成绩表”调整到适当的列宽和行高。（5）将“学生成绩表”复制到同一工作簿的另一工作表中，命名为“学生成绩表2”。（6）在“学生成绩表”中显示平均分大于等于75且小于80的记录，然后恢复原状。（7）在“2011级学生成绩表”数据区域使用自动套用格式。（8）

2、在“学生成绩表2”数据区的最后添加一行，利用公式计算出各科成绩和全班同学总成绩的平均分。（9）将“学生成绩表2”的标题设为楷体，22号字，水平居中显示，并且将标题单元格所在的行高设为24。（10）在“学生成绩表2”中只显示姓王的成绩记录，并恢复原状。（11）为“学生成绩表”工作簿设置文件打开密码123456。（12）在“学生成绩表2”中，根据每位同学的总成绩建立柱形图，并在该图中添加数据标志。（13）为“学生成绩表”设置页边距页眉和页脚，并练习设置打印区域设置打印标题及其他打印选项，最后进行打印预览。实验要求如下：（1）输入上述职工工资表（“性别”列除外），将工作表命名为“工资表”，并计算应发

3、工资=基本工资+职务工资+奖金。（2）利用数据的有效性，在“性别”列中只允许用户选择男或女输入每个人的性别。（3）公积金扣款计算公式为当应发工资小于1300元时，扣款为应发工资的2%，否则为5%（用IF函数），格式保留2位小数。（4）计算实发工资应发工资公积金扣款其他扣款。（5）在现有表格的下一行计算基本工资、职务工资、奖金、应发工资、公积金扣款、其他扣款和实发工资的总额。（6）将该表标题设定为“职工工资表”，合并居中，“姓名”、“性别”列水平垂直均分散对齐。（7）给表格加边框，外粗内细，外红内黑。（8）将表格内每行的高度设置为18（24像素），“编号”列宽度设为4.5（41像素）。（9）根据

4、（5）计算的各工资项目总额，分别计算基本工资总额、职务工资总额和奖金总额占应发工资总额的比例，建立可分离的饼状图，标明具体数值。（10）按科室分类汇总实发工资、应发工资。（11）将“职工工资表”设置密码保护，整个表格的“实发工资”列以及表外的各行各列不允许鼠标到达，其它各列用户可以输入数据。（12）将“职工工资表”中的首行冻结起来，使得向下按垂直滚动条时，首行保持不动。（13）删除“职工工资表”密码保护，再添加若干行的数据，进行页面设置，使得浏览（或打印）时每页都显示首行标题，页脚显示页码。实验一、Excel基础知识一在Excel默认状态下建立工作表如左表，按照如下步骤操作，请写出相应的结果。

5、（1）Excel表中，选中A1单元格，按下Ctrl键的同时拖动A1单元格右下角的填充柄到A6，看看A2、A3被填充的数据分别为多少？（2）选择B1:B2单元格区域，拖动B2单元格右下角的填充柄到B6 ，则B3、B4被填充的数据分别为多少？（3）选中C3单元格， 拖动C3单元格右下角的填充柄到C4， 则C4被填充的数据为多少？（4）在D1单元格中输入公式“=$A1+C$2”，然后按回车键，并将公式复制到D2、D3单元格中，则D1、D2、B3单元格中的数据分别为多少？（5）选中C5单元格，单击工具栏上的，然后按回车，则该单元格的值为多少？（6）现将A1:B2单元格区域命名为“求和”，A7单元格有公

6、式“=SUM（求和）”，此时A7单元格的值为多少？若重新将“求和”指定为B1:C2单元格区域的名字，则A7单元格的值为？思考：1、绝对引用、相对引用和混合引用在何种情况下使用？表中是“2011级学生成绩表”，实验要求如下：（1）用公式和函数求出每位同学的总成绩和平均分。（2）将工作表重新命名为“学生成绩表”。（3）将“2011级学生成绩表”这一标题设置格式为黑体14号字，居中显示，颜色为红色。表格中的文字型数据居中显示。（4）将“学生成绩表”调整到适当的列宽和行高。（5）将“学生成绩表”复制到同一工作簿的另一工作表中，命名为“学生成绩表2”。（6）在“学生成绩表”中显示平均分大于等于75且小于

7、80的记录，然后恢复原状。（7）在“2011级学生成绩表”数据区域使用自动套用格式。（8）在“学生成绩表2”数据区的最后添加一行，利用公式计算出各科成绩和全班同学总成绩的平均分。（9）将“学生成绩表2”的标题设为楷体，22号字，水平居中显示，并且将标题单元格所在的行高设为24。（10）在“学生成绩表2”中只显示姓王的成绩记录，并恢复原状。（11）为“学生成绩表”工作簿设置文件打开密码123456。（12）在“学生成绩表2”中，根据每位同学的总成绩建立柱形图，并在该图中添加数据标志。（13）为“学生成绩表”设置页边距页眉和页脚，并练习设置打印区域设置打印标题及其他打印选项，最后进行打印预览。二、

8、实验步骤1启动Excel，在sheet1工作表中输入表1-1数据，保存工作簿，将其命名为“学生成绩表”，如下图所示。（1）在J3单元格中输入公式“=sum(C3:I3)”，利用填充柄向下填充至J10；在K3单元格中输入公式“=Average(C3:I3)”，利用填充柄向下填充至K10。（2）双击Sheet1工作表标签，输入“学生成绩表”。（3）选择A1:K1单元格区域，单击格式工具栏相应按钮，设置字体、字号、颜色及居中。（4）选中表格，鼠标单击【格式】，选择【行】，选择【最适合的行高】。（5）鼠标单击“学生成绩表”标签，按住“Ctrl”键，拖动鼠标到标签的其他位置，释放鼠标；再同（2）将其名称

9、改为“学生成绩表2”。（6）选择A2:K10单元格区域，选择“数据/筛选/自动筛选”，鼠标单击“平均分”右侧的下三角按钮，选择“自定义”，在【自定义自动筛选方式】”对话框中设置相应的条件后，按【确定】按钮，显示符合条件的记录。若想恢复原状，再用鼠标单击“平均分”右侧的下三角按钮，选择“全部”。（7）选择“2011级学生成绩表”数据所在单元格区域，单击【格式】/【自动套用格式】，出现对话框，选择相应项格式即可。（8）选定“学生成绩表2”，选择C11单元格，输入公式“=average(C3:C10)”，在利用填充柄填充至J11即可。（9）选中“学生成绩表2”整行，设置格式，合并居中。（10）选择A

10、2:K11单元格区域，选择“数据/筛选/自动筛选”，鼠标单击“姓名”右侧的下三角按钮，选择“自定义”，在【自定义自动筛选方式】”对话框中的“姓名”行中输入“王*”，按【确定】按钮，显示符合条件的记录。（11）单击【格式】/【选项】，选择“安全性”选项卡，在“打开权限密码”看重输入“123456”，按【确定】按钮。（12）选定“学生成绩表2”，选择A3:A10，J3;J10单元格区域，单击常用工具栏上的【图表向导】按钮，选择“柱形图”，单击“分类轴标志”框右边的“折叠”按钮，选择A3:A10；单击【下一步】按钮，在“数据标志”选项卡中选择“值”；接下来可以进行其他设置，单击【完成】按钮即可。（1

11、3）鼠标单击【文件】，选择【页面设置】，进行相关设置。2启动Excel，在sheet1工作表中输入表1-2数据，保存工作簿，将其命名为“某部门职工工资表”（也可以在上题的工作簿中的另一工作表中输入相应数据），如下图所示。（1）将工作表命名为“工资表”（略）；选择H3，输入公式“=sum(E3:J3)”，并填充至H16。（2）在V2、V3单元格中分别输入“男”和“女”，将V列隐藏（也可以部隐藏），选择D3，单击【数据】菜单下的【有效性】命令，在“允许”框中选择“序列”，单击“来源”框右侧的“折叠”按钮，选择V2:V3，单击【确定】按钮，再利用填充柄将D3填充至D16。（3）选择I3单元格，输入公

12、式“=round(IF(H3=1300,0.02*H3,0.05*H3),2)”，再利用填充柄填充至I16。（4）选择K3单元格，输入公式“=H3-I3-J3”，再利用填充柄填充至K16。（5）选择E17单元格，输入公式“=SUM(E3:E16)”，再利用填充柄填充至K17。（6）设置该表标题为“职工工资表”，并选择该行，合并居中；选择姓名、性别列，右击选择【设置单元格格式】，选择对齐,对话框中选择“分散对齐”。 （7）选择整个表格，右击选择【设置单元格格式】，选择【边框】，先选择线条颜色，再选择样式，最后选择边框，点击【确定】。 （8）选择表格所有行，选择【格式】，选择【行高】，输入18；再

13、选择“编号”列，同上，设置列宽4.5。（9）在E18单元格中输入公式“=E17/$H$17”，再利用填充柄填充至G17，并设置相应单元格的格式为“百分比”；选择E2:G2，E18:G18单元格区域，仿照上题中的（12）建立图形。（10）先按“科室”排序，再单击【数据】/【分类汇总】，在弹出的【分类汇总】对话框中，选定汇总项为“实发工资”“应发工资”后，按【确定】按钮。（11）重点是掌握方法。选择A:J列，右击鼠标，选择【设置单元格格式】命令，弹出对话框，选择“保护”选项卡，清除“锁定”；单击【工具】菜单下的【保护】子菜单中的【保护工作表】命令，弹出【保护工作表】对话框，清除“选定锁定单元格”复

14、选框，输入密码，单击【确定】按钮。（12）选择A3单元格，单击【窗口】菜单下的【冻结窗格】命令。（13）选择【工具】中的【保护】，选择“撤销保护工作表”，即可；选择【文件】中的【页面设置】进行设置，选择【工作表】，在“顶端标题行”中选择首行标题，点击【确定】。3 鼠标点击【插入】，选择【超链接（I）】，打开对话框，选择从网上找到的要链接进来的内容，点击【确认】。三、实验中碰到了哪些问题？你是如何解决的？还有哪些问题没有解决？ 在实验过程中，对于利用数据的有效性，在“性别”列中只允许用户选择男或女输入每个人的性别以及将“职工工资表”设置密码保护，整个表格的“实发工资”列以及表外的各行各列不允许鼠

15、标到达，其它各列用户可以输入数据，这两道题没有正确掌握方法，导致做题时出现了问题，而且由于长时间没有练习Excel的缘故，有些快捷键的应用记的模糊不清，另外Excel的某些功能也忘记了，很多东西忘记在哪个菜单的那个目录下面了。 就存在的问题，先是咨询了一下同学，并且从课本上找到相应的知识点，按照书上写的，把操作步骤正确的掌握了下来，对于有些问题也会从XX上搜索，找到答案。 经过自己的练习，基本把本次实验遇到的问题都解决了，如果其他同学还遇到别的问题，我们都不能解决的，就直接向老师请教。四、实验感想、意见和建议 通过本次实验，发现excel功能的强大，以前学的很多知识太肤浅了，而且好多功能不经常

16、使用容易遗忘。Excel中我完成同一种结果可以有许多种不同的方式，可以根据自己的习惯选择不同的方式。要熟练运用Excel平时还要多加练习。excel能把很多复杂的问题简单化，进行数据处理更加便利。实验2：Excel数据基础一、实验目的和实验内容1在空单元格中分别输入以下函数，回车后看其结果（1）round(3.45673,2)（2）=round(3.4657,0)（3）=round(5673.45673,-2)（4）=int(85.45)（5）=int(-41.48)（6）=int(54.98)（7）=today()（8） =now() （9）=left(“abcdefg”,4) （10）=l

17、eft(“上机指导”,2)（11）=right(“abcdefg”,4)（12）=right(“上机指导”,2)2. 在表2-1中，计算：（1）计算每人平均分和单科平均分（2）计算参加考试的学生总人数。（3）统计出专业成绩在80分以上的人数。（4）计算一班高数成绩的和。（5）计算二班所有同学的平均分。思考：（1）将学号为06082101学生的“平均分”用公式“AVERAGE（C2：E2）”换成“AVERAGE(C$2:E$2)”，再复制到以下行的其它单元格时，其他学生的平均值还对吗？换成“AVERAGE($C2:$E2)”时，复制到该列以下行的其他单元格，结果还对吗？换成“AVERAGE($C

18、$2:$E$2)”还对吗？为什么？（2）二班所有同学的平均分你是如何计算的？可以直接用函数吗？如表2-2所示，在商品销售明细表中，根据要求求和。（1）根据B列销售金额求和，要求对销售金额大于2000的数值进行求和。（2）根据商品名称求和，要求对商品名称为A1的商品相对应的销售金额进行求和。（3）求商品名称包含“A”的销售金额之和（4）商品名称第四五个字符为“A2”且字符总长度为6个字符的的销售金额之和如表2-3所示的员工工资表，根据要求计数。（1）统计实发工资大于2500的人数（2）统计财务部的人数某公司的工资表如表2-4所示，要求：求出职务是“财务”的员工的平均工资；思考：（1）你考虑用什么

19、方法？用DAVERAGE函数可以吗？某企业销售提成计算表所示，“提成额”列显示的数据是根据“销售额”列的销售额进行判断的结果，如果“销售额”大于30000，“提成额”按“销售额*0.015”计算，否则按“销售额*0.01”计算。要求：用IF函数公式计算每个人的提成额某公司个人所得税计算表如上表左侧所示（右侧为个人所得税七级累进税率），要求：在Excel中，用Vlookup函数求出左侧每位员工应税所得额对应的税率、速算扣除数及交纳的税金。要求：根据表中的数据，建立数据透视表，显示每个销售人员、各种商品的销售数量和金额；根据建立的数据透视表建立数据透视图。1. 函数F(X)=5X+8，利用模拟运算

20、表的行引用和列引用两种方式分别计算X取值为-5、-4、-3、-2、-1、0、1、2、3、4、5时的F(X)的值。2某企业在银行存入300000元，存期3年。为了能在第3年末从银行取出400000元，存款的年利率是多少？若为500000万元，年利率又为多少？（提示：注意收入和支出正负号）二、实验步骤1在空单元格中分别输入以下函数，回车后观察结果。（1）round(3.45673,2) 结果:3.46（2）=round(3.4657,0) 结果：3（3）=round(5673.45673,-2) 结果：5700（4）=int(85.45) 结果：85（5）=int(-41.48) 结果：-42（6

21、）=int(54.98) 结果：54（7）=today() 结果：2013-3-21（8） =now() 结果：2013-3-21 8：23（9）=left(“abcdefg”,4) 结果：abcd（10）=left(“上机指导”,2) 结果：上机（11）=right(“abcdefg”,4) 结果：defg（12）=right(“上机指导”,2) 结果：指导三、实验中碰到了哪些问题？你是如何解决的？还有哪些问题没有解决？对于不懂的问题，我会及时向同学请教，或是翻阅一下课本，找到相应的知识点，进行巩固，按照书上写的进行操作，也有的时候会参考一下老师给的答案，按照步骤操作。四、实验感想、意见和建

22、议通过本次实验，了解了Excel函数功能的强大，许多财务方面的数据，excel都能迎刃而解。由于不经常使用对许多函数还都很陌生，所以今后要加强应用，熟练掌握Excel公式和Excel中常用函数的功能和语法格式。运用Excel的相关知识可以使复杂的问题简单化，处理复杂繁琐的财务数据更加便捷也更加准确，减少的差错的可能性。实验4：Excel在筹资决策中应用 一、实验目的和实验内容实验三 财务分析1.将“pz.txt”和“pz.csv”文本文件的数据引入Excel中；2.建立数据库查询，将所给的Access数据库中的资产负债表和利润表数据引入到Excel工作表中；3.建立WEB查询，地址栏中输入“二

23、、实验步骤（1）【数据】菜单上【导入外部数据】子菜单中的【导入数据】命令，弹出相应的对话框，在文件类型中选择文本文件，点击打开pz.txt文件，单击【下一步】，选择“分隔符号”中的空格，【下一步】-【完成】（2）与第一步相同，只是在“分隔符号”中选择“逗号”即可。三、实验中碰到了哪些问题？你是如何解决的？还有哪些问题没有解决？在实验过程中，利用Excel获取数据时，没有把应当掌握的基本方法掌握下来，而且在对华能国际当年的财务报表进行分析时，例如运用比率分析和杜邦分析时，因为不熟悉流程，所以做起来不太熟练。对于遇到的不明白的问题，及时翻看课本相关知识，把相应的知识点掌握下来以后，然后进行操作，或

24、是根据老师给的参考答案，进行练习。遇到的其他问题，通过请教同学或是利用XX搜索引擎，把不懂的地方都弄明白，按时完成本次实验内容。四、实验感想、意见和建议通过本次实验，了解到Excel丰富的函数及图表等功能，可以根据需要对公司的财务数据进行综合分析，发现其在财务分析中的应用很强大，尤其对我们学习会计的人来说，学好Excel对我们今后的工作有极大的帮助，做了这几次实验，我更加深刻的体会到Excel一定多加练习，熟能生巧。一些公式的运用，常用函数的功能等等，要学会灵活运用。把所学的知识真正运用到实践中来，才能真正把理论运用于实际。实验5：长期负债筹资决策模型 一、实验目的和实验内容1假设你要购买一项

25、保险，该保险可以在今后20年内每月末汇报500元。此项保险的购买成本为60000元，假定投资回报率为8%，请问是否值得购买？2假设需要为一年后的某个项目预筹资金，现在讲1000元以年利率6%，按月计息存入银行账户，并在以后12个月内每月末在存入100元，则一年后银行存款账户余额是多少？3某客户欲购买一套价值50万元的房子，首付20万元，余下30万元向银行申请商业贷款，期限10年，年利率5%，试采用等额本息还款和等本金还款两种方式计算每月的还款额。（补充：等本金还款，若期初偿还本金，期末偿还利息，利息可用ISPMT函数计算）4现取得30万元贷款，列表计算利率为5%、6%15%，期限分别为1、2、

26、3、4、510年的到期还款额。5.某企业需要一台价值100万人民币的设备，使用6年。如果要租赁该设备，年租金为24.32万元人民币，每年年末支付一次，残值归租赁公司所有；如果要购买该设备，需要向银行贷款100万元，银行利率为8.59%，偿还方式为每年年末等额偿还。该企业采用年限平均法计提折旧，6年后残值为15万元，所得税税率为25%，现金贴现率为10%。计算年租金为24.32万元时的内含利息率，并设计制作模型，判断是贷款购买还是租赁更合适？内含利息率在1%-15%之间变动时，年租金也会发生变化，判断是贷款购买还是租赁更合适？二、实验步骤1=PV(8%/12,20*12,500) 解得：-￥59

27、,777.15这个解表示当初需要支付-￥59,777.15，就可以在今后20年内每月末汇报500元，所以对于这项投资并不合算。2 =FV(6%/12,12,-100,-1000) 答案：￥2,295.233 =$H$3/($H$4*12)=ISPMT($H$5/12,A19,$H$4*12,-$H$3)可以根据这个公式以此类推。建立租赁筹资模型1租赁分析2建立租赁筹资模型的一般步骤（1）建立租赁筹资分析模型。（2）建立图形控制项按钮。打开“视图”菜单，选择“工具栏/窗体”命令，即可显示“窗体”工具栏。 建立“设备名称”项目下的下拉控制项。 建立“每年付款次数”微调控制项。 设置“租金年利率”滚

28、动条。 设置“租赁期限”微调控制项。 设置模型中其他单元格的公式。三、实验中碰到了哪些问题？你是如何解决的？还有哪些问题没有解决？在使用【数值调节钮】控件设置参数调节按钮方面出现了一些问题，但通过看书，问同学，XX搜索引擎已经解决。四、实验感想、意见和建议通过第五章实验，我深深感觉到只是通过理论课堂的学习根本就记不住那么多东西，所以在上实验课之前要及时巩固。实验6：基于净现值的项目投资决策模型一、实验目的和实验内容1. 在互斥方案中选择最优并用图形展示，例如垂直参考线2. 熟练掌握建立投资决策模型的方法3. 熟练掌握一些基本控件的选择与设置4. 掌握投资决策指标及其函数的应用，例如：NPV（）

29、IRR（）现值指数等等二、实验步骤1 已知贴现率为10%，有三种投资机会，有关数据如表6-1所示。建立Excel模型，应用NPV、IRR函数分别计算三种方案的净现值、内含报酬率和现值指数。根据题目的要求在指定的单元格中写出A项目、B项目净现值、现值指数、内含报酬率等项目在A项目下方计算NPV=NPV(10%,C4:C6)-C3，之后拖动鼠标即可得到B、C项目的值在A项目下方计算现值指数=E14/(-C3)，之后拖动鼠标即可得到B、C项目的值在A项目下方计算内含报酬率=IRR(C3:C6)，之后拖动鼠标即可得到B、C项目的值2一个投资项目的持续期限为4年，其初始投资额330000元用来购置设备。

30、该项目在4年内每年创造的现金流量为115000元，在2年未时设备需要进行一次大修，大修费用为40000元。公司使用的贴现率为12%。要求：（1）在Excel工作表中构造一个模型以求出该投资项目的净现值。根据题意输入数据分别输入第一年到第四年的数据利用该公式F20=NPV(G15,F16:F19)+F15（2）在上述工作表中生成必要的数据，然后制作一个表明当贴现率在5%17%范围内变化时该项目净现值随贴现率的变化曲线图形。根据题目的叙述输入每年的现金流量，之后视图工具栏窗体选中滚动条到J14右击设置控件格式最大值最小值以及步长单元格连接到$P$14单元格令K14 =P14/100N22输入NPV

31、 =NPV($K$14,$J$23:$M$23)+$I$23图标向导柱形图数据区域N22和N23确定就完成了可以随$P$14单元格变化的柱形图了；在图形中分别添加文本框以及微调按钮令滚动条单元格链接为$P$14在文本框编辑区输入=K14选中图形文本框和滚动条进行多次的组合完成（3）在图形中添加一个表示净现值曲线与横坐标轴交点的点（为此当然需要在本工作表中做必要的计算）与经过该点的垂直参考线。 利用NPV()分别求出在折现率为5%17%范围内变化时的不同的净现值选中数据确定求出IRR=10%O23,O24=10%P23,P24分别为-6000和6000 右击图形原数据添加系列选中O23,O24，P23,P2