计算机财务管理实验指导 - 图文

目 录

实验一 复习EXCEL基本操作........................................... 1 一、实验目的 ...................................................... 1 二、实验内容 ...................................................... 1 三、实验步骤 ...................................................... 3 实验二 EXCEL函数、公式、数据管理和分析 ............................. 6 一、实验目的 ...................................................... 6 二、实验内容 ...................................................... 6 三、实验步骤 ..................................................... 10 实验三 财务分析模型的设计和建立.................................... 11 一、实验目的 ..................................................... 11 二、实验内容 ..................................................... 11 三、实验步骤 ..................................................... 11 实验四 筹资决策模型的设计和建立.................................... 13 一、实验目的 ..................................................... 13 二、实验内容 ..................................................... 13 三、实验步骤 ..................................................... 14 实验五 项目投资决策模型的设计和建立................................ 27 一、实验目的 ..................................................... 27 二、实验内容 ..................................................... 27 三、实验步骤 ..................................................... 28 实验六 EXCEL最优化问题模型的建立 .................................. 31 一、实验目的 ..................................................... 31 二、实验内容 ..................................................... 31 三、实验步骤 ..................................................... 32 实验七 财务预测模型的设计和建立.................................... 36 一、实验目的 ..................................................... 36 二、实验内容 ..................................................... 36 三、实验步骤 ..................................................... 37 实验八 财务管理系统的建立.......................................... 42 一、实验目的 ..................................................... 42 二、实验内容 ..................................................... 42 三、实验步骤 ......................................................................................................................... 42

实验一 复习Excel基本操作

一、实验目的

1．掌握Excel的基本操作。熟练掌握各种启动．退出Excel的有关操作；熟悉Excel的窗口及初始屏幕各部分的功能；掌握Excel联机帮助功能的使用方法。

2．掌握工作表的建立方法。熟练掌握工作表的建立方法和操作技巧，如建立一个新的工作薄、向工作表中输入各种类型的数据、设置数据的有效性、数据的编辑和修改、格式的编排、查找和替换单元格中的数据以及各种引用的操作；保存工作薄、工作薄密码的设置。

3．掌握工作表的编辑方法。熟练掌握工作表的各种编辑操作，包括移动、复制、删除、更名、隐藏等，熟练掌握工作表的管理方法，包括打开、查看多个工作薄等。

4．掌握窗口的切换、隐藏、拆分、冻结等操作。

5．掌握打印操作，包括页面设置、分页设置与调整、打印预览等。

6．学会用图表的形式表示数据间的复杂关系，直观地分析统计数据。包括图标的建立步骤、图表的编辑等。

7．了解Excel的网络功能。包括创建超级链接、获取或发布数据等。 二、实验内容

1．在磁盘上建立一个文件名为“学生成绩表”的工作薄，在工作表sheet1中创建一个如表1-1所示的工作表。

表1-1 2011级学生成绩表 姓名 张三 梁齐 王文 吕梁 王青 岳韩 戴卫 杜平 性别 男 男 女 男 女 男 女 男 高数 92 86 75 68 59 71 82 73 线代 80 76 61 80 73 69 81 78 英语 73 75 76 69 80 71 65 70 物理 72 76 71 80 69 67 85 64 语文 80 82 86 79 65 74 81 83 会计学 财务管理 90 83 84 79 76 68 62 65 总成绩 平均分 86 67 81 86 72 90 74 92 实验要求如下。

（1）练习添加记录，并用公式和函数求出每位同学的总成绩和平均分。 （2）将工作表重新命名为“学生成绩表”。

（3）将“2011级学生成绩表”这一标题设置格式为黑体14号字，居中显示，颜色为红色。表格中的文字型数据居中显示。

（4）将“学生成绩表”调整到适当的列宽和行高。

（5）将“学生成绩表”复制到同一工作簿的另一工作表中，命名为“学生成绩表2”。

1

（6）在“学生成绩表”中显示平均分大于等于75且小于80的记录，然后恢复原状。

（7）在“2011级学生成绩表”数据区域使用自动套用格式。

（8）在“学生成绩表2”数据区的最后添加一行，利用公式计算出各科成绩和全班同学总成绩的平均分。

（9）将“学生成绩表2”的标题设为楷体，22号字，水平居中显示，并且将标题单元格所在的行高设为24。

（10）在“学生成绩表2”中只显示姓王的成绩记录。

（11）为“学生成绩表”工作簿设置文件打开密码123456。

（12）在“学生成绩表2”中，根据每位同学的总成绩建立柱形图，并在该图中添加数据标志。

（13）为“学生成绩表”设置页边距．页眉和页脚，并练习设置打印区域．设置打印标题及其他打印选项，最后进行打印预览。

2．某部门职工工资表如下表1-2所示。

表1-2 某部门职工工资表

编号 1 2 3 4 5 6 7 8 9 10 11 12 13 14 科室 姓名 性基本 职务别 工资 工资 女 男 女 女 男 男 男 男 男 男 女 896 756 950 645 456 789 354 750 450 560 780 奖金 应发公积金工资 扣款 其他实发扣款 工资 50 80 45 35 25 84 25 30 35 45 28 34 65 10 教务处 张建兰 总务处 杨审评 学生处 石为国 财务处 杨芳 财务处 柳丝从 学生处 黄连素 教务处 王小郑 学生处 华丽伟 教务处 李铁牛 学生处 廖晨 总务处 赵学军 总务处 杨家辉 学生处 张德华 总务处 李楠 300 300 200 200 400 150 150 240 530 300 200 200 310 280 250 270 340 450 423 400 321 300 300 320 260 452 253 320 男 1250 女 1000 女 1145 实验要求如下：

（1）输入上述职工工资表（“性别”列除外），将工作表命名为“工资表”，并计算应发工资=基本工资+职务工资+奖金。

（2）利用数据的有效性，在“性别”列中只允许用户选择男或女输入每个人的性别。

（3）公积金扣款计算公式为当应发工资小于1300元时，扣款为应发工资的2%，否则为5%（用IF函数），格式保留2位小数。

（4）计算实发工资＝应发工资－公积金扣款－其他扣款。

（5）在现有表格的下一行计算基本工资、职务工资、奖金、应发工资、公

2

积金扣款、其他扣款和实发工资的总额。

（6）将该表标题设定为“职工工资表”，合并居中，“姓名”、“性别”列水平垂直均分散对齐。

（7）给表格加边框，外粗内细，外红内黑。 （8）将表格内每行的高度设置为18（24像素），“编号”列宽度设为4.5（41像素）。

（9）根据（5）计算的各工资项目总额，分别计算基本工资总额、职务工资总额和奖金总额占应发工资总额的比例，建立可分离的饼状图，标明具体数值。

（10）按科室分类汇总实发工资、应发工资。

（11）将“职工工资表”设置密码保护，整个表格的“实发工资”列以及表外的各行各列不允许鼠标到达，其它各列用户可以输入数据。

（12）将“职工工资表”中的首行冻结起来，使得向下按垂直滚动条时，首行保持不动。

（13）再添加若干行的数据，进行页面设置，使得浏览（或打印）时每页都显示首行标题，页脚显示页码。

3．从上海证券交易所（http://www.sse.com.cn）、中国上市公司咨询网（http://www.cnlist.com）或某上市公司的网页中，找到相关的数据资料，在Excel中创建Web查询来获取相关数据。 三、实验步骤

1．

启动Excel，在sheet1工作表中输入表1-1数据，保存工作簿，将其命名为“学生成绩表”，如下图所示。

（1）在J3单元格中输入公式“=sum(C3:I3)”，利用填充柄向下填充至J10；在K3单元格中输入公式“=Average(C3:I3)”，利用填充柄向下填充至K10。

（2）双击Sheet1工作表标签，输入“学生成绩表”。

（3）选择A1:K1单元格区域，单击格式工具栏相应按钮，设置字体、字号、

3

颜色及居中。

（4）略

（5）鼠标单击“学生成绩表”标签，按住“Ctrl”键，拖动鼠标到标签的其他位置，释放鼠标；再同（2）将其名称改为“学生成绩表2”。

（6）选择A2:K10单元格区域，选择“数据/筛选/自动筛选”，鼠标单击“平均分”右侧的下三角按钮，选择“自定义”，在【自定义自动筛选方式】”对话框中设置相应的条件后，按【确定】按钮，显示符合条件的记录。若想恢复原状，再用鼠标单击“平均分”右侧的下三角按钮，选择“全部”即可。

（7）选择“2011级学生成绩表”数据所在单元格区域，单击【格式】/【自动套用格式】，出现对话框，选择相应项格式即可。

（8）选定“学生成绩表2”，选择C11单元格，输入公式“=average(C3:C10)”，在利用填充柄填充至J11即可。 （9）略 （10）选择A2:K11单元格区域，选择“数据/筛选/自动筛选”，鼠标单击“姓名”右侧的下三角按钮，选择“自定义”，在【自定义自动筛选方式】”对话框中的“姓名”行中输入“王*”，按【确定】按钮，显示符合条件的记录。

（11）单击【格式】/【选项】，选择“安全性”选项卡，在“打开权限密码”看重输入“123456”，按【确定】按钮。

（12）选定“学生成绩表2”，选择A3:A10，J3;J10单元格区域，单击常用工具栏上的【图表向导】按钮，选择“柱形图”，单击“分类轴标志”框右边的“折叠”按钮，选择A3:A10；单击【下一步】按钮，在“数据标志”选项卡中选择“值”；接下来可以进行其他设置，单击【完成】按钮即可。

（13）略 2．

启动Excel，在sheet1工作表中输入表1-2数据，保存工作簿，将其命名为“某部门职工工资表”（也可以在上题的工作簿中的另一工作表中输入相应数据），如下图所示。

4

（1）将工作表命名为“工资表”（略）；选择H3，输入公式“=sum(E3:J3)”，并填充至H16。

（2）在V2、V3单元格中分别输入“男”和“女”，将V列隐藏（也可以部隐藏），选择D3，单击【数据】菜单下的【有效性】命令，在“允许”框中选择“序列”，单击“来源”框右侧的“折叠”按钮，选择V2:V3，单击【确定】按钮，再利用填充柄将D3填充至D16。

（3）选择I3单元格，输入公式“=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）略

（9）在E18单元格中输入公式“=E17/$H$17”，再利用填充柄填充至G17，并设置相应单元格的格式为“百分比”；选择E2:G2，E18:G18单元格区域，仿照上题中的（12）建立图形。

（10）先按“科室”排序，再单击【数据】/【分类汇总】，在弹出的【分类汇总】对话框中，选定汇总项为“实发工资”“应发工资”后，按【确定】按钮。

（11）重点是掌握方法。选择A:J列，右击鼠标，选择【设置单元格格式】命令，弹出对话框，选择“保护”选项卡，清除“锁定”；单击【工具】菜单下的【保护】子菜单中的【保护工作表】命令，弹出【保护工作表】对话框，清除“选定锁定单元格”复选框，输入密码，单击【确定】按钮。

（12）选择A3单元格，单击【窗口】菜单下的【冻结窗格】命令。 （13）略 3．略

5

实验二 Excel函数、公式、数据管理和分析

一、实验目的

1．熟练掌握Excel公式的使用。

2．熟练掌握Excel中常用函数的功能和语法格式。

3．掌握数据的排序、筛选、分类汇总、合并计算和数据透视表的建立步骤。 4．掌握数据分析工具的使用。 二、实验内容

1．在Excel默认状态下建立工作表如表2-3所示，按照如下步骤操作，请写出相应的结果。

表2-3 工作表

1 2 3 4 5 6 7 A 2 B 5 10 C 1 2 3 D （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、D3单元格中的数据分别为 、 、 。

（5）选中C5单元格，单击工具栏上的∑，然后按回车，则该单元格的值为 。

（6）先将A1:B2单元格区域命名为―求和‖，A7单元格中有公式―=SUM（求和）‖，此时A7单元格的值为 ；若重新将―求和‖指定为B1:C2单元格区域的名字，则A7单元格的值为 。

2.上机练习如下函数 （1）SUM函数

6

某企业往来单位余额对比表如表2-4所示，根据具体问题求和。

表2-4 某企业往来单位余额对比表

1 2 3 4 5 6 7 8 9 10 往来单位 单位-1 单位-2 单位-3 单位-4 单位-5 单位-6 单位-7 合计 A B 往来单位余额对比表 2011年 20000 30000 25000 96000 78000 100000 96000 2012年 12000 25000 14000 36000 85000 120000 85900 C ①计算2012年往来单位余额总和。

②计算―单位-1‖、―单位-2‖、―单位-5‖和―单位-7‖4个单位的2012年往来单位余额总和。

（2）SUMIF函数

某企业商品销售明细表如表2-5所示，根据要求求和。

表2-5 某企业商品销售明细表 1 2 3 4 5 6 7 8 9 10

A1 000A20 B1 B1 000A20 000A34E A1 000A1 A 商品销售明细表 商品名称 销售金额 1,200.00 2,500.00 1,400.00 10,000.00 2,000.00 1,000.00 3,000.00 100.00 B ①根据B列销售金额，对销售金额大于2000的数值进行求和。

②根据商品名称对商品名称为A1的商品相对应的销售金额进行求和。 ③计算商品名称包含―A‖的销售金额之和

④计算商品名称中第四五个字符为―A2‖且字符总长度为6的销售金额之和

7

（3）COUNTIF函数

某企业员工工资表如表2-6所示，根据要求计数。

表2-6某企业员工工资表

部门 财务部 财务部 销售部 人事部 销售部 财务部

姓名 张景生 杨会来 李琴 原林 吴凤莲 张飞 基本工资 1500 2000 3000 1200 2300 2100 提成工资 800 500 200 400 600 100 实发工资 2300 2500 3200 1600 2900 2200 ①统计实发工资大于2500的人数 ②统计财务部的人数

（4）平均函数——AVERAGE函数

某企业员工的工资表如表2-7所示，要求：求出所有员工总工资的平均值；

表2-7某企业员工的工资表

编号 2001 2002 2003 2004 2005 姓名 李碧华 林淑芬 王喜育 吕利萍 姚启明 性别 女 女 女 女 男 职务 行政 行政 财务 财务 业务 基本工资 1200 1200 1500 2400 1000 效绩工资 400 200 400 800 2200 总工资 1600 1400 1900 3200 3200

（5）DAVERAGE函数 资料如上题（表2-7），要求：求出职务是―财务‖的员工总工资的平均值。 （6）IF函数

某企业销售提成计算表如表2-8所示，C列显示的数据是根据B列的销售额进行判断的结果，如果―销售额‖大于30000，―提成额‖按―销售额*0.015‖计算，否则按―销售额*0.001‖计算。要求：用IF函数公式计算每个人的提成额

表2-8

姓名 张三 李四 王五 孙六 李七

销售额 25000 36000 30000 45000 21000 提成额 250 540 300 675 210 （7）VLOOKUP函数

某公司个人所得税计算表如表2-9所示，请求出每位员工应税所得额对应的税率、速算扣除数及交纳的税金。

8

表2-9个人所得税计算表

姓名 张雨 刘平 李正 王华 应税所得额 3700 2500 500 6500 对应对应速算税率 扣除数 10% 10% 3% 20% 105 105 0 555 税金 265 145 15 745 级数 1 2 3 4 5 6 7 应税所得 0 1500 4500 9000 35000 55000 80000 且不超过 1500 4500 9000 35000 55000 80000 税率 3% 10% 20% 25% 30% 35% 45% 速算扣除数 0 105 555 1005 2755 5505 13505 （8）MINVERSE和TRANSPOSE函数

?213???要求：求矩阵412的逆矩阵和转置矩阵。 ????223??（9）MMULT函数

?213??368?????要求：求矩阵412和5122的乘积。 ??????223????497??

3．函数F(X)=5X+8，利用模拟运算表的行引用和列引用两种方式分别计算X取值为-5、-4、-3、-2、-1、0、1、2、3、4、5时的F(X)的值。

4．利用模拟运算表编制三角形九九乘法口诀表。 5．某公司2011年7月份的销售数据如表2-10所示

表2-10某公司2011年7月份的销售数据

日期 2011-7-1 2011-7-3 2011-7-3 2011-7-8 2011-7-10 2011-7-13 2011-7-18 2011-7-18 2011-7-19 2011-7-20 2011-7-24 2011-7-24 2011-7-31 商品 彩电 空调 彩电 冰箱 电脑 电脑 冰箱 空调 空调 彩电 电脑 空调 冰箱 单价（元） 数量（台） 金额（元） 销售人员 4500 8500 1600 1700 6800 8600 2500 2300 2200 3800 8600 2600 220 2800 10 60 80 40 10 20 100 80 100 120 20 40 500 70 9

地区 济南 济南 济南 青岛 青岛 青岛 烟台 烟台 烟台 日照 北京 北京 北京 北京 45000 510000 128000 68000 68000 172000 250000 184000 220000 456000 172000 104000 110000 196000 张三 张三 张三 王五 王五 王五 赵六 赵六 赵六 周七 李四 李四 李四 李四 2011-7-28 电风扇

要求：根据表中的数据，建立数据透视表，显示每个销售人员、各种商品的销售数量和金额；根据建立的数据透视表建立数据透视图。

6．某企业在银行存入300000元，存期3年。为了能在第3年末从银行取出400000元，存款的年利率是多少？若为500000万元，年利率又为多少？ 三、实验步骤

1．

（1）选中A1单元格，按下Ctrl键的同时拖动A1单元格右下角的填充柄到A6，则A2、A3被填充的数据分别为3、4。

（2）选择B1:B2单元格区域，拖动B2单元格右下角的填充柄到B6，则B3、B4被填充的数据分别为15、20。

（3）选中C3单元格， 拖动C3单元格右下角的填充柄到C4，则C4被填充的数据为3。

（4）在D1单元格中输入公式―=$A1+C$2‖，然后按回车键，并将公式复制到D2、D3单元格中，则D1、D2、D3单元格中的数据分别为4、5、6。

（5）选中C5单元格，单击工具栏上的∑，然后按回车，则该单元格的值为9。

（6）先将A1:B2单元格区域命名为―求和‖，A7单元格有公式―=SUM（求和）‖，此时A7单元格的值为20；若重新将―求和‖指定为B1:C2单元格区域的名字，则A7单元格的值为18。

2． （1）略 （2）

①公式：=SUMIF(B3:B10，\

②公式：=SUMIF(A3:A10，\，B3:B10) ③公式：=SUMIF(A3:A10，\，B3:B10)

注意：*A*是使用*（星号）模糊求和，星号代表任意多个字符 ④公式：=SUMIF(A3:A10，\，B3:B10)

注意：\：利用通配符―？‖（问号）实现占位功能。问号在字符中是强行占用一个字符的位置。

（3）

①公式：=COUNTIF(E2:E7，\②公式：=COUNTIF(A2:A7，\财务部\（4）略

（5）

公式：=DAVERAGE(A3:G9,\总工资\（6）-（9）略 3-6略

10

实验三 财务分析模型的设计和建立

一、实验目的

掌握获取数据的方法并能设计财务分析模型。 二、实验内容

1．仿照课本将“pz.txt”和“pz.cvs”文本文件的数据引入Excel中； 2．建立数据库查询，将所给的Access数据库（文件名为zzbb.mdb，内容参见第3章）中的资产负债表和利润表数据引入到Excel工作表中；

3．建立WEB查询，地址栏中输入“http://quotes.money.163.com/corp/1034/code=600011.html”，将华能国际近三年年的资产负债表、利润表数据导入到EXCEL中。

4．对华能国际当年的财务报表进行分析（包括比率分析和杜邦分析）。 三、实验步骤

1．略 2．

（1）启动Microsoft Query。单击“数据”菜单上的“导入外部数据”子菜单，然后单击“新建数据库查询”命令，出现选择数据源对话框，如图3-1所示。

图3-1 选择数据源对话框 （2）在指定的数据库文件中选择欲导入的数据。使用Microsoft Query可以检索多种类型数据库中的数据，包括Microsoft Access、Microsoft SQL Server和OLAP Services等等。我们在这里选择数据库中的“MS Access Database*”，按“确定”按钮，出现选择数据库对话框，如图3-2所示。

11

图3-2 选择数据库对话框

（3）选择ZZBB.MDB数据库，按“确定”按钮，出现选择数据表对话框。选择资产负债表，按照向导进行操作。

（4）数据返还给Excel工作表。在查询向导即将完成查询作业时，选定数据结果需返回的工作表：本工作表、新建工作表和数据透视表。如果选择“将数据返回到Microsoft Excel”，然后再选择放置外部数据的位置，如图3-3所示。

图3-3 确定数据返还位置对话框

（5）同理，将利润表引入Excel中即可。 3．略 4．略

12

实验四 筹资决策模型的设计和建立

一、实验目的

练习相关的财务函数，并能设计和建立相关的筹资决策模型。 二、实验内容

1．按图5-4所示的模型结构，进行长期借款筹资决策动态模型扩展设计。要求至少完成除还款方式选择、还款期间选择和还款时点选择以外的所有设计内容。这3个选项在你设计的模型中分别固定为等额本息、月付和期末还款。

2．根据5.2.3节所述，按照净利租赁现值法设计借款购入设备或融资租入设备的决策模型。

3．根据5.2.3节所述，按照损益平衡租金法设计借款购入设备或融资租入设备的决策模型。

4．根据5.3.2节所述，参考图5-11中优先股资本成本计算分析模型，按股利折现模型设计普通股资本成本计算分析模型。

5．根据图5-11模型原理，参照公司普通股（外部权益）资本成本计算模型，设计留存收益（内部权益）资本成本计算分析模型。

6．按图5-20所示的模型结构，利用双变量模拟运算表，设计一个针对贷款金额和贷款年限的长期借款分析模型。要求：年利率、每年还款次数和贷款年限用控件工具箱中的数值调节钮或滚动条使其成为可变参数。

图5-20长期借款模型结构

7．某公司发行面额为500万元的10年期债券，票面利率12%，发行费率为5%，所得税税率为33%，设计一个计算该债券资本成本的模型，并在此基础上利用窗体控件使该模型具有一定的通用性。

8．某公司目前的资本来源包括每股面值1元的普通股800万股和平均利率10%的3000万元债券。现有一项目需投资4000万元，预计投产后每年可增加营业利润（息税前利润）400万元。该项目备选的筹资方案有三个：①按11%的利率发行债券；②按面值发行股利率为12%的优先股；③按每股20元的价格增发普通股。公司目前的息税前利润为1600万元；公司所得税率为25%；证券发行

13

费可忽略不计。要求设计一模型完成以下辅助决策功能：①计算按不同方案筹资后的普通股每股收益；②计算增发普通股和债券筹资的每股收益无差别点，以及增发普通股和优先股筹资的每股收益无差别点；③计算筹资前的财务杠杆和按三个方案筹资后的财务杠杆；④根据计算结果，该模型能给出辅助决策结果，即应选择哪一种筹资方案及其理由。

9．按图5-21所示的结构设计一个购房贷款分析模型，模型右半部分是对总还款额和总利息额在等额本息和等额本金还款方式下的分析比较图。

图5-21购房贷款分析模型

要求如下：

（1）用户只需输入房价、房屋面积以及等额本金还款分析中的还款月份；用滚动条调节首付百分比、贷款利率和贷款年限，其他单元格内容由模型根据公式自动计算。

（2）数据有效性规则：房价、房屋面积为大于等于0的数值；还款月份为大于等于1但小于等于总还款月数的整数；首付百分比为5%~80%，无小数；贷款利率为1.00%~10.00%，保留两位小数；贷款年限为5~30的整数。

（3）根据等额本息和等额本金还款方式下的总还款额、总还款利息绘制柱形图，以便更加直观地对两种还款方式进行对比分析。

（4）将房价、房屋面积、还款月份以及滚动条链接单元格以外的整个工作表保护，以防误录数据。

10．某公司的普通股发行价格为每股20元，上年股利率为每股1元，新发行普通股的筹资费率为9.62%，股利预计增长率为17.88%。要求设计一模型计算该公司的普通股和留存收益的资本成本率，并具有一定的通用性。 三、实验步骤

1．

模型总体结构布局如教材图5-4所示。 参数调节按钮主要属性设置如下表所示。 可调节参数 属性 linkedcell J3 Max 1000000 14

min 10000 smallchange 10000 贷款总额(元)

贷款总期限(年) J4 年利率 贷款期次(月) 起始期次 结束期次 单元格 A3 A4 B3 C3 C4 D3 E3 F3 F4 J5 J6 J7 J8 J9 J12 J13 J14 J15 J16 J17 J18 J19 J20 J21 J22

公式 =J12 =A3+1 L7 L12 L19 L20 15 10000 180 180 180 1 2250 1 1 1 备注 1 1 1 1 1 主要单元格公式设置如下表所示。 复制到A5—A27 复制到B4--B27 复制到C5--C27 复制到D4—D27 复制到E4—E27 复制到F5—F27 =IF(A3<=J$6,-PPMT($J$9,$A3,$J$6,$J$3),0) =-CUMPRINC(J9,J6,J3,1,J12,0) =IF(A4<=J$6,C3+B4,0) =IF(A3<=J$6,J$3-C3,0) =IF(A3<=J$6,-IPMT($J$9,$A3,$J$6,$J$3),0) =-CUMIPMT(J9,J6,J3,1,J12,0) =IF(A4<=J$6,F3+E4,0) =J4*4 =J4*12 =L7/100000 =J7/4 =J7/12 =IF(L12>J6,J6,L12) =-PMT($J$9,$J$6,$J$3) =-PPMT($J$9,$J$12,$J$6,$J$3) =-CUMPRINC(J9,J6,J3,1,J12,0) =J3-J15 =-IPMT($J$9,$J$12,$J$6,$J$3) =-CUMIPMT(J9,J6,J3,1,J12,0) =IF(L19>J20,J20,L19) =IF(L20>J6,J6,L20) =-CUMPRINC(J9,J6,J3,J19,J20,0) =-CUMIPMT(J9,J6,J3,J19,J20,0) 2．

15

模型总体结构布局如下图所示。

租赁或借款决策模型--净利租赁现值法(NAL)

控件及参数调节按钮主要属性设置如下表所示。 可调节参数 属性 数据源区域 $F$3:$F$11 $D$9:$D$10 $D$3:$D$8 LinkedcellMax Min Smallchange单元格链接 最大值 最小值 步长 $A$2 $A$5 $A$6 $A$7 $A$9 $A$10 $A$11 $F$12 $K$12 7 2000 33 1800 100 3000 1 225 10 800 100 225 1 1 1 1 1 1 备注 租赁设备名称 租赁年限 租金支付时点 每年付款期数 租金年利率 所得税税率 基准收益率k 借款比例 借款年利率 单元格 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 公式 主要单元格公式设置如下表所示。 =INDEX(生产厂家,A2) =租赁年限*INDEX(年租金,A2) =IF(A5<=INDEX(使用年限,A2),A5,INDEX(使用年限,A2)) =IF(A6=1,\先付\后付\=租赁年限*每年付款期数 =A9/10000 =A10/100 =A11/10000 =IF(租金支付时点=\先付\租金年利率/每年付款期数,总付款期数1,-租金总额,0,1),PMT(租金年利率/每年付款期数,总付款期数1,-16

=IF(A7=1,1,IF(A7=2,2,IF(A7=3,4,IF(A7=4,12,IF(A7=5,24,365))))) 租金总额)) E12 H12 B15 =F12/100 =INDEX(设备价款,A2)*借款比例 =IF(租金支付时点=\先付\租赁年限>A15,每期应付租金*每年付款期数,0),IF(AND(A15<>0,租赁年限>=A15),每期应付租金*每年付款期数,0)) =B15*所得税税率 =(B15-C15)/(1+基准收益率k)^A15 =IF(AND(租金支付时点=\先付\租赁年限>A15,借款金额*借款年利率,0),IF(AND(A15<>0,租赁年限>=A15),借款金额*借款年利率,0)) =IF(租金支付时点=\先付\租赁年限>A15,SLN(INDEX(设备价值,A$2),INDEX(设备残值,A$2),INDEX(使用年限,A$2)),0),IF(AND(A15<>0,租赁年限>=A15),SLN(INDEX(设备价值,A$2),INDEX(设备残值,A$2),INDEX(使用年限,A$2)),0)) =(E15+F15)*所得税税率 =G15/(1+借款年利率)^A15 =IF(租金支付时点=\先付\租赁年限>A15,INDEX(每年维修费,$A$2),0),IF(AND(A15<>0,租赁年限>=A15),INDEX(每年维修费,$A$2),0)) =I15*基准收益率k =(I15-J15)/(1+基准收益率k)^A15 =SUM(B15:B22) =INDEX(设备价款,A2)+K23 选中B23并将其复制到C23:K23区域 对应于公式5-1的①②项 C15 D15 E15 F15 G15 H15 I15 J15 K15 B23 C24 F24 G24 J24

选中B15:K15区域拖动该区域右下角的复制柄复制到B16:K22区域 =D23+H23+INDEX(设备残值,A2)/(1+基准收益率对应于公式5-1的③④⑤项 k)^租赁年限 =IF(C24>F24,\=IF(C24>F24,\融资租入设备\借款购入设备\ 3．

模型总体结构布局如下图所示。

控件及参数调节按钮主要属性设置和主要单元格公式设置与上机实验2类似。不同之处在于：①H24 =C24-F24；②利用单变量求解工具求得NAL=0时的年租金，具体过程为：选定某一租赁设备，点击菜单“工具”、“单变量求解”，目标单元格H24，目标值0，可变单元格为该设备的“NAL=0年租金”，确定后，可得到相应的“NAL=0年租金”和辅助决策方案。

17

租赁或借款决策模型--损益租金平衡法(NAL=0)

4．

模型总体结构布局如下图所示。

普通股股利折现模型

控件及参数调节按钮主要属性设置如下表所示。

18

可调节参数 属性 数据源区域 LinkedcellMax Min Smallchange单元格链接 最大值 最小值 步长 $B$2 $C$3 $C$4 $G$4 13000 17000 13000 13000 100 100 100 100 1 1 1 1 普通股 年股利率 筹资费率E 股利固定年增长率 单元格 B3 B4 B5 F2 F3 F4 F5 A9 A10 A11 B9 C9 A18 A19 A20 B18 C18 公式 =C3/10000 =C4/10000 主要单元格公式设置如下表所示。 备注 选中A9:G14区域，对其调用模拟运算表，引用行的单元格为B3，引用列的单元格为B4，确定即可。 选中A18:G23区域，对其调用模拟运算表，引用行的单元格为F4，引用列的单元格为B4，确定即可。 =普通股*年股利率 =年固定股利/(普通股*(1-筹资费率E)) =年固定股利 =G4/10000 =第一年股利/(普通股*(1-筹资费率E))+股利固定年增长率 =年固定股利/(普通股*(1-筹资费率E)) =筹资费率E =A10+0.0001,并将其拖动复制到A12:A14区域. =年股利率 =B9+0.0001,并将其拖动复制到D9:G9区域. =第一年股利/(普通股*(1-筹资费率E))+股利固定年增长率 =筹资费率E =A19+0.0001,并将其拖动复制到A21:A23区域. =股利固定年增长率 =B18+0.0001,并将其拖动复制到D18:G18区域. 5．

模型总体结构布局如下图所示。

留存收益资本成本计算分析模型

19

步骤3：检查模型中各单元格的计算公式。

H8单元格中输入公式“=SUM(C8:G8)，并利用填充柄复制到H10。 C11单元格中输入公式“=SUM(C8:C10)”，并利用填充柄复制到G11。 C13单元格中输入公式“{=SUM(C8:G10*C3:G5)}”。

步骤4：设置规划求解的各项参数并求解。

选择“工具”中的“规划求解”选项，设置求解此问题的规划求解的各项参数。其中$C$13为目标函数的因变量，$C$8:$G$10为决策变量，$C$11:$G$11=$C$12:$G$12；$C$8:$G$10＞＝0；$H$8:$H$10＜＝$I$8:$I$10,单击【确定】即可。

4.仿照课本[例9-6]即可。 5．略

35

实验七 财务预测模型的设计和建立

一、实验目的

1．练习掌握预测方法和预测函数，包括INTERCEPT、SLOPE、LINEST、FORCAST、TREND等。

2．掌握利用数据分析工具进行财务预测。 二、实验内容

1．甲企业只生产一种产品，在2010年1月至2011年12月生产的产品数量（单位：件）如表10-10所示。

表10-10 甲企业2010-2011年各月的产品产量

年月 2010年1月 2010年2月 2010年3月 2010年4月 2010年5月 2010年6月 2010年7月 2010年8月 数量 316 428 484 405 368 382 327 460 年月 2010年9月 2010年10月 2010年11月 2010年12月 2011年1月 2011年2月 2011年3月 2011年4月 数量 391 433 428 395 335 320 369 398 年月 2011年5月 2011年6月 2011年7月 2011年8月 2011年9月 2011年10月 2011年11月 2011年12月 数量 442 401 350 327 360 414 480 388 要求：

（1）利用移动平均工具（间隔为3）预测该企业在2012年1月生产的产品数量。

（2）利用指数平滑工具（阻尼系数为0.4）预测该企业在2012年1月生产的产品数量。

2．乙企业将其2011年1月至2011年12月的库存资金占用情况、广告投入的费用、员工薪酬以及销售额等方面的数据做了一个汇总，如表10-11所示。乙企业的管理人员试图根据这些数据找到销售额与其他3个变量之间的关系，以便进行销售额预测并为未来的预算工作提供参考。试利用回归分析工具建立一个合适的回归方程，据此预测乙企业2012年1月的销售额（假设2012年1月乙企业的库存资金额为150万元，广告投入预算为45万元，员工薪酬总额为27万元）。

表10-11 乙企业2011年各月的销售额与影响因素表 月份 1 2 3 库存资金额 （万元） 98.3 67.7 74 广告投入 （万元） 24.8 23.6 33.9 员工薪酬总额 （万元） 21.5 21 22.4 销售额 （万元） 1098.8 826.3 1003.3 36

4 5 6 7 8 9 10 11 12

151 90.8 102.3 115.6 125 137.8 175.6 155.2 174.3 27.7 45.5 42.6 40 45.8 51.7 67.2 65 65.4 24.7 23.2 24.3 23.1 29.1 24.6 27.5 26.5 26.8 1554.6 1199 1483.1 1407.1 1551.3 1601.2 2311.7 2126.7 2256.5 3．丙企业2011年各月冰箱销售额的有关数据如表10-12所示。

表10-12 丙企业2011年各月的冰箱销售额 月份 1 2 3 4 5 6 7 8 9 10 11 12 销售额（万元） 330 300 300 320 350 380 430 480 530 630 730 830 根据以往的经验，该企业的销售额随着时间的推移有可能呈线性变动趋势或指数变动趋势。要求利用有关函数建立一个带有选择销售额变动趋势组合框控件的预测2012年1月的冰箱销售额的模型。 三、实验步骤

1．

（1）操作步骤如下：

①启动Excel电子表，将本例的有关资料录入Excel电子表，并设计模型的结构。

②利用移动平均分析工具对各月生产的产品数量进行预测。方法如下： ●单击打开【工具】菜单后，找到【数据分析】选项，单击打开【数据分析】对话框，在对话框中选择“移动平均”，单击【确定】按钮，打开【移动平均】对话框，在对话框的“输入区域”一栏输入“$B$2:$B$25”，在“间隔”栏输入“3”，作为移动平均时间跨度，在“输出区域”一栏输入“$C$2”，然后选中对话框下方的“图表输出”与“标准误差”两个选项，最后单击【确定】按钮，就得到了从2009年3月份开始出现的移动平均预测值和自2009年5月份才有的标准误差。另外，还生成了生产数量的实际值和移动平均预测值对比的图表。

③在单元格E6输入“=C25”。 操作的结果如下图所示。

37

利用移动平均分析工具预测的结果

（2）操作步骤如下：

①启动Excel电子表，将本例的有关资料录入Excel电子表，并设计模型的结构。

②利用指数平滑分析工具对各月生产的产品数量进行预测。方法如下： ● 单击打开【工具】菜单后，找到【数据分析】选项，单击打开【数据分析】对话框，在对话框中选择“指数平滑”，单击【确定】按钮，打开【指数平滑】对话框，在对话框的“输入区域”一栏输入“$B$2:$B$25”，在“阻尼系数”栏输入“0.4”，在“输出区域”一栏输入“$C$2”，然后选中对话框下方的“图表输出”选项，最后单击【确定】按钮，就得到了自2009年2月份开始出现的指数平滑预测值。另外，还生成了生产数量实际值和指数平滑预测值对比的图表。

③复制单元格C25，并向下粘贴到单元格C26中，就得到了2011年1月产品数量的预测值。操作的结果如图10-26所示。

利用指数平滑分析工具预测的结果

38

2．

操作步骤如下：

（1）启动Excel电子表，将本例的有关资料录入Excel电子表，设计模型的结构，利用录入的数据分别求得库存资金额与销售额、广告投入与销售额、员工薪酬总额与销售额的回归分析判定系数R2的值。

运用回归分析工具生成员工薪酬总额对销售额影响的回归分析报告，以获取R2的值。方法如下：

● 单击打开【工具】菜单后，找到【数据分析】选项，单击打开【数据分析】对话框，在对话框中选择“回归”，单击【确定】按钮，打开【回归】对话框，在对话框的“Y值输入区域”一栏输入“$E$3:$E$14”，在“X值输入区域”一栏输入“$D$3:$D$14”，选中“置信度”复选框并保持系统默认的95%的置信度不变，在“输出区域”一栏输入“$A$16”，最后单击【确定】按钮，这时Excel会自动生成一个回归分析报告，得到的R2的值为0.6543。

采用同样的方法，分别生成广告投入对销售额影响的回归分析报告和库存资金额对销售额影响的回归分析报告，得到R2的值分别为0.8013和0.9032，由此可见，应该选择判定系数最大的回归方程，即自变量为库存资金额、因变量为销售额的方程。

（2）利用生成回归分析报告的方法求得二元回归方程的调整后判定系数。 如果二元方程的自变量是库存资金额和广告投入，方法如下：

● 单击打开【工具】菜单后，找到【数据分析】选项，单击打开【数据分析】对话框，在对话框中选择“回归”，单击【确定】按钮，打开【回归】对话框，在对话框的“Y值输入区域”一栏输入“$E$3:$E$14”，在“X值输入区域”一栏输入“$B$3:$C$14”，选中“置信度”复选框并保持系统默认的95%的置信度不变，在“输出区域”一栏输入“$A$76”，最后单击【确定】按钮，生成回归分析报告，得到调整后判定系数R2的值为0.973。

采用同样的方法，当自变量为库存资金额和员工薪酬总额时，生成回归分析报告，得到调整后判定系数R2的值为0.8992。

（3）利用生成回归分析报告的方法求得三元回归方程的调整后判定系数，方法同上。根据生成的回归分析报告，得知调整后判定系数R2的值为0.9698。

通过对判定系数和调整后判定系数的比较，选取系数最大的回归方程，即自变量为库存资金额和广告投入、因变量为销售额的二元回归方程。

（4）由回归分析报告中给出的回归系数值，可建立起销售额预测模型： Y = 8.37X1 + 12.92X2 – 62.65

（5）将2011年1月的库存资金额150万元、广告投入45万元代入方程，即可求得销售额的预测值为1774.15万元。

操作的结果如下图所示。

39

利用回归分析工具预测的结果

3．

操作步骤如下：

（1）启动Excel电子表，将本例的有关资料录入Excel电子表，并设计模型的结构。

（2）建立一个可以选择销售额变动趋势的组合框控件，方法如下： ● 单击【视图】菜单后，找到【工具栏】子菜单，单击【窗体】，打开【窗体工具栏】，选中【组合框】控件并向B4单元格里添加一个组合框。在该组合框的位置上双击鼠标左键打开【设置控件格式】对话框，单击对话框中的“控制”选项卡，在“数据源区域”栏输入“$A$5:$A$6”，在“单元格链接”栏输入“$B$4”，在“下拉显示项数”栏输入“2”，并选中“三维阴影”复选框，最后单击【确定】按钮。

（3）利用函数计算系数a、系数b和判定系数R2，方法如下： ● 在单元格D6中输入：

“=IF(B4=1,INDEX(LINEST($B$3:$M$3,$B$2:$M$2,TRUE,TRUE),1,2),INDEX(LOGEST($B$3:$M$3,$B$2:$M$2,TRUE,TRUE),1,2))”，得到系数a的计算结果。

● 在单元格E6中输入：

“=IF(B4=1,INDEX(LINEST($B$3:$M$3,$B$2:$M$2,TRUE,TRUE),1,1),INDEX(LOGEST($B$3:$M$3,$B$2:$M$2,TRUE,TRUE),1,1))”，得到系数b的计算结果。

● 在单元格F6中输入：

“=IF(B4=1,INDEX(LINEST($B$3:$M$3,$B$2:$M$2,TRUE,TRUE),3,1),INDEX(LOGEST($B$3:$M$3,$B$2:$M$2,TRUE,TRUE),3,1))”，得到判定系数R2的计算结果。

（4）绘制XY散点图并据此判断2010年各月的销售额呈何种变动趋势，方法如下：

40

● 选取单元格区域B2:M3，单击打开【插入】菜单后，找到【图表】子菜单，单击打开【图表向导-4步骤之1-图表类型】对话框，在对话框的“图表类型”中选择“XY散点图”，在“子图表类型”中选择“散点图”，单击【下一步】按钮，打开【图表向导-4步骤之2-图表源数据】对话框，单击该对话框的【系列】选项卡，在“X值”一栏中输入“=Sheet1!$B$2:$M$2”，在“Y值”一栏中输入“=Sheet1!$B$3:$M$3‖，单击【下一步】按钮，打开【图表向导-4步骤之3-图表选项】对话框，在对话框的“图表标题”栏输入“2010年度各月的销售额”，在“数值（X）轴”栏输入“月份”，在“数值（Y）轴”栏输入“销售额”，然后单击【完成】按钮，得到生成的图表，根据图表判断可知：2010年各月的销售额呈指数变动趋势，因此，应该在组合框中选择“指数趋势”。

（5）预测2011年1月份的冰箱销售额.，方法如下： ● 选中单元格区域H6，输入“=IF(B4=1,D6+E6*H5,D6*E6^H5)”，这样就得到2011年1月份的预测销售额。

操作的结果如下图所示。

销售额变动趋势预测模型

41

实验八 财务管理系统的建立

一、实验目的

1．练习系统界面的设计，了解编写部分宏代码。 2．掌握系统菜单的制作、宏程序的运行。 二、实验内容

1．根据本章所学的知识，结合前几章上机实验的内容，设计一个完整的Excel财务管理系统。要求：

（1）设计直观易于操作的系统界面。

（2）新增完整的系统菜单。

（3）编写进入各个模块的宏程序。

（4）为界面中的各个模块图形指定宏，为系统菜单指定宏，使得用户点击模块名称或选择系统菜单时，可以进入相应的模块。

（5）设计自动运行宏程序，使得在用户界面所在Excel工作簿打开时，系统提示“欢迎进入财务管理系统”，在用户界面所在Excel工作簿关闭时，系统提示“感谢使用财务管理系统”。 三、实验步骤

略

42

本文来源：https://www.bwwdw.com/article/2o75.html