计算机财务管理实验指导

目 录

实验一 复习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

控件及参数调节按钮主要属性设置如下表所示。 可调节参数 属性 数据源区域 LinkedcellMax Min Smallchange单元格链接 最大值 最小值 步长 $C$2 $D$3 $D$4 $D$8 $D$10 $D$11 $D$12 $D$14 $D$15 $G$2 $G$3 13000 13000 13000 13000 13000 15000 2100 8000 9500 3000 30000 100 100 100 100 100 100 100 100 100 10 500 1 1 1 1 1 1 1 1 1 1 10 备注 普通股(万元) 年股利率 筹资费率D 股利固定年增长率 无风险报酬率 市场组合预期报酬率 股票贝塔系数 债券投资收益率 普通股风险溢价 年股利额(元/股) 普通股每股市价 单元格 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C13 C14 C15 C16 F2 F3 F6 F7 F8 F9 F10 F11 F12 F13 F14 公式 =D3/10000 =D4/10000 主要单元格公式设置如下表所示。 =普通股_万元*年股利率 =年固定股利/(普通股_万元*(1-筹资费率D)) =年固定股利 =D8/10000 =第一年股利/(普通股_万元*(1-筹资费率D))+固定股利年增长率 =D10/10000 =D11/10000 =D12/100 =无风险报酬率+股票贝塔系数*(市场组合预期报酬率-无风险报酬率) =D14/10000 =D15/10000 =债券投资收益率+普通股风险溢价 =G2/100 =G3/1000 =年股利额/普通股每股市价 =年股利额*(1+普通股利年增长率) =固定股利年增长率 =预期年股利额/普通股每股市价+普通股利年增长率 =无风险报酬率 =市场组合预期报酬率 =股票贝塔系数 =无风险报酬率+股票贝塔系数*(市场组合预期报酬率-无风险报酬率) =债券投资收益率 20

F15 F16 6．

=普通股风险溢价 =债券投资收益率+普通股风险溢价 如教材图5-20所示。 可调节参数 属性 数据源区域 LinkedcellMax Min Smallchange单元格链接 最大值 最小值 步长 $E$3 $G$3 $I$3 1000 12 20 100 1 1 1 1 1 备注 选中A5:I14区域，对其调用模拟运算表，引用行的单元格为I3，引用列的单元格为B3，确定即可。 年利率 每年还款次数 贷款年限 单元格 A5 A6 A7 B5 C5 7．

公式 主要单元格公式设置如下表所示。 =PMT(年利率/每年还款次数,每年还款次数*贷款年限,-贷款金额,0) =贷款金额 =A6+50000,并将其拖动复制到A8:A14区域. =贷款年限 =B5+1,并将其拖动复制到D5:I5区域. 模型总体结构布局如下图所示。

21

控件及参数调节按钮主要属性设置如下表所示。 可调节参数 属性 数据源区域 LinkedcellMax Min Smallchange单元格链接 最大值 最小值 步长 1000 1200 15 12 1000 40 10000 2000 1200 20 12 1200 10 225 1 1 50 17 100 100 300 1 1 50 1 1 1 1 1 1 1 1 1 1 1 1 备注 借款金额 借款年利率 借款期限_年 筹资费率A 所得税税率 债券发行总额 债券总面值 票面利率 债券期限_年 筹资费率B 单元格 B10 B11 公式 $B$3 $C$4 $B$5 $B$6 $C$7 $C$8 $E$3 $E$3 $F$5 $E$6 $E$7 $F$8 每年付息次数A 每年付息次数B 主要单元格公式设置如下表所示。 =借款年利率*(1-所得税税率)/(1-筹资费率A) =每年付息次数A*RATE(借款期限_年*每年付息次数A,-(借款金额*借款年利率/每年付息次数A)*(1-所得税税率),借款金额*(1-筹资费率A),-借款金额) =债券总面值*票面利率*(1-所得税税率)/(债券发行总额*(1-筹资费率B)) =每年付息次数B*RATE(债券期限_年*每年付息次数B,-(债券总面值*票面利率/每年付息次数B)*(1-所得税税率),债券发行总额*(1-筹资费率B),-债券总面值) =B11 =E11 8．

E10 E11 A15 A23 选中A23:F28区域，对其调用模拟运算表，引用行的单元格为B5，引用列的单元格为B4，确定即可。 选中A15:F20区域，对其调用模拟运算表，引用行的单元格为E3，引用列的单元格为E8，确定即可。 模型总体结构布局如下图所示。

22

每股收益分析法筹资决策模型

控件及参数调节按钮主要属性设置如下表所示。 可调节参数 属性 数据源区域 LinkedcellMax Min Smallchange单元格链接 最大值 最小值 步长 $B$3 $B$4 $B$6 $B$7 $C$12 $F$4 $F$8 $E$11 1000 100 10000 12 40 10000 10000 300 1 1 100 1 17 100 100 1 1 1 1 1 1 1 1 1 备注 普通股股数 每股面值 债务资本 债务利率 所得税税率 债券利率 股利率 普通股股价 单元格 B5 B7 B11 B12 E5 E8 E10 E14 E15 E16 F14 公式 主要单元格公式设置如下表所示。 =普通股股数*每股面值 =C7/10000 =目前的EBIT+新增EBIT =C12/10000 =发行债券*债券利率 =F8/10000 =普通股股价*增发普通股股数 =((筹资后的EBIT-债务资本*债务利率-年利息额)*(1-所得税税率)-优先股*股利率)/普通股股数 =((筹资后的EBIT-债务资本*债务利率)*(1-所得税税率)-优先股*股利率)/普通股股数 =((筹资后的EBIT-债务资本*债务利率)*(1-所得税税率)-优先股*股利率)/(普通股股数+增发普通股股数) =筹资后的EBIT/(筹资后的EBIT-债务资本*债务利率-年利息额) 23

F15 F16 F17 C14 =筹资后的EBIT/(筹资后的EBIT-债务资本*债务利率-优先股*股利率/(1-所得税税率)) =筹资后的EBIT/(筹资后的EBIT-债务资本*债务利率) =目前的EBIT/(目前的EBIT-债务资本*债务利率) =((①与③每股收益无差异点的EBIT-债务资本*债务利率-年利息额)*(1-所得税税率)-优先股*股利率)/普通股股数-((①与③每股收益无差异点的EBIT-债务资本*债务利率)*(1-所得税税率)-优先股*股利率)/(普通股股数+增发普通股股数) C15 C16 调用单变量求解工具，目标单元格C14，目标值0，可变单元格C15，确定即可。 =((②与③每股收益无差异点的EBIT-债务资本*债务利率)*(1-所得税税率)-优先股*股利率)/普通股股数-((②与③每股收益无差异点的EBIT-债务资本*债务利率)*(1-所得税税率)-优先股*股利率)/(普通股股数+增发普通股股数)，利用单变量求解工具求得C17之值。 C17 9．

调用单变量求解工具，目标单元格C16，目标值0，可变单元格C17，确定即可。 模型总体结构布局如下图所示。

购房贷款分析模型

控件及参数调节按钮主要属性设置如下表所示。 可调节参数 属性 数据源区域 LinkedcellMax Min Smallchange单元格链接 最大值 最小值 步长 $D$3 $E$4 $E$7 $F$9 20 3000 100 240 1 100 10 1 1 1 1 1 备注 贷款年限 贷款利率 首付比例 还款月份 单元格 B5 B6 B7 D5 D6 公式 主要单元格公式设置如下表所示。 =房价*房屋面积 =房价总额-首付款 =房价总额*首付比例 =贷款利率/12 =贷款年限*12 24

D7 D9 B9 B10 B11 B12 B13 B14 D4 D5 D6 D7 D10 D11 D12 D13 D14 B17 C17 D17 E17 D18 E18 10．

=E7/100 =IF(F9<贷款年限*12,F9,贷款年限*12) =D9 =PMT(贷款利率/12,贷款年限*12,-贷款总额,0) =PPMT(贷款利率/12,B9,贷款年限*12,-贷款总额) =IPMT(贷款利率/12,B9,贷款年限*12,-贷款总额) =-CUMPRINC(贷款月利率,贷款总期数,贷款总额,1,贷款总期数,0)+B14 选中B17:C17，并将其公式拖动复制到B18:C256区域。 =-CUMIPMT(贷款月利率,贷款总期数,贷款总额,1,贷款总期数,0) =E4/10000 贷款利率/12 =贷款年限*12 =E7/100 =D11+D12 =贷款总额/贷款总期数 =INDEX(D16:D256,D9) =贷款总额+D14 =INDEX(E16:E256,D6) =IF(A17<=贷款总期数,D$11*A17,0) =IF(A17<=贷款总期数,贷款总额-B17,0) =贷款总额*贷款月利率 =D17 =IF(A18<=贷款总期数,C17*贷款月利率,0) 选中D18:E18，并将其公式拖动复制到D19:E256区域。 =IF(A18<=贷款总期数,E17+D18,0) 模型总体结构布局如下图所示。

普通股和留存收益资本成本计算分析模型

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

25

可调节参数 属性 数据源区域 LinkedcellMax Min Smallchange单元格链接 最大值 最小值 步长 $C$2 $D$3 $D$4 $D$6 $D$8 $D$9 $D$10 $D$12 $D$13 100 1000 5000 6000 6000 6000 6000 6000 6000 1 10 10 100 100 100 100 100 100 1 1 1 1 1 1 1 1 1 备注 普通股发行价格 上年股利 筹资费率D 固定股利年增长率 无风险报酬率 股票贝塔系数 债券投资收益率 普通股风险溢价 单元格 C3 C4 C5 C6 C7 C8 C9 B9 C10 C11 C12 C13 C14 E5 E7 E11 E14

公式 =D3/100 =D4/10000 市场组合预期报酬率 主要单元格公式设置如下表所示。 =上年股利/(普通股发行价格*(1-筹资费率D)) =D6/10000 =上年股利/(普通股发行价格*(1-筹资费率D))+固定股利年增长率 =D8/10000 =D9/10000 =D9 =D10/100 =D12/10000 =D13/10000 =债券投资收益率+普通股风险溢价 =上年股利/普通股发行价格 =上年股利/普通股发行价格+固定股利年增长率 =债券投资收益率+普通股风险溢价 =无风险报酬率+股票贝塔系数*(市场组合预期报酬率-无风险报酬率) =无风险报酬率+股票贝塔系数*(市场组合预期报酬率-无风险报酬率) 26

实验五 项目投资决策模型的设计和建立

一、实验目的

1．掌握项目投资决策相关的财务函数。

2．练习基于净现值的项目投资决策模型的设计和建立。 二、实验内容

1．已知贴现率为10%，有三种投资机会，有关数据如表6-1所示。建立Excel模型，应用NPV、IRR函数分别计算三种方案的净现值、内含报酬率和现值指数。

表6-1 期间 0 1 2 3 合 计

A方案现金净流量 B方案现金净流量 C方案现金净流量 (20000) 12000 14000 6000 (10000) 1200 6000 6000 3200 (12000) 4600 4600 4600 1800 2．一个投资项目的持续期限为4年，其初始投资额330000元用来购置设备。该项目在4年内每年创造的现金流量为115000元，在2年未时设备需要进行一次大修，大修费用为40000元。公司使用的贴现率为12%。要求：

（1）在Excel工作表中构造一个模型以求出该投资项目的净现值。

（2）在上述工作表中生成必要的数据，然后制作一个表明当贴现率在5%~17%范围内变化时该项目净现值随贴现率的变化曲线图形。

（3）在图形中添加一个表示净现值曲线与横坐标轴交点的点（为此当然需要在本工作表中做必要的计算）与经过该点的垂直参考线。

3．某投资者有1000万元资金，现有两个投资项目，项目A是小户型公寓，项目B是社区商铺。项目A初始投入1000万元，根据预测每年可获得租金90万元的投资收益，10年后该物业的市场价值1200万元；项目B初始投入1000万元，根据预测该项目第1年可获得租金50万元的收益，以后每年的收益在上年基础上递增10%，10年后该物业的市场价值1500万元。假定贴现率为6%，要求：

（1）在Excel工作表中建立一个对两个项目进行比较的模型，在两个并列的单元格中分别求出每个投资项目的净现值，在一个单元格中利用IF（）函数给出“项目A较优”或“项目B较优”的结论。

（2）将上述模型加以扩充，在两个并列的单元格中分别求出各项目的内含报酬率。

27

（3）使用一个Excel内建函数求出使项目A和项目B的净现值相等的贴现率。

（4）利用模拟运算表生成自变量-函数对照表，用查表加内插的方法再次求出使A、B两个项目的净现值相等的贴现率。(贴现率的变化范围为1%-20%，间隔为1%。)

4．某人当前在一个银行账户中具有5000元余额，他在未来2年内每月月初向该账户补充存入1000元，银行存款年利率为4%，假定银行以复利方式计算利息。要求如下。

（1）在Excel工作表中建立一个包含“月份”、“月初余额”、“月初存款”、“利息”与“月末余额”等五列的模拟模型，通过对于24个月的模拟计算出此人2年内每月的月末余额。

（2）在单元格D8中利用Excel提供的财务函数FV（）来求出此人2年末在银行账户中的余额，此数值与用模拟模型求得的结果应该完全相同。 三、实验步骤 1．略

2．如下图所示

（1）E8公式：“=E4+PV(E7,E3,-E6)+PV(E7,2,0,E5)”

（2）先生成一个单变量模拟运算表，如图右半部分。选择I3:J10，按【图表向导】按钮，选择XY散点图，修改有关设置，生成图形。

（3）在F10中输入0，添加2个系列，系列1数据为D12:E14，目的是垂直线；系列2数据是E10:F10，目的是与X轴交点。 3．

（1）第1步，建立模型框架

28

将本实验有关数据输入到Excel工作表的单元格中，如下图所示

第2步，计算投资项目净现值

在单元格D10中输入公式“=NPV($D$3,D25:D34)+D5”，并将其复制到单元格E10中；

第3步，找出最优投资项目

在单元格D11中输入公式“=IF(D10>E10,\项目A较优\项目B较优\”.

（2）

将2个项目各年的数据输入（或采用公式计算）到Excel中，如下图所示。

29

在单元格D12中输入公式“=IRR(D24:D34)”，将单元格D12中的公式复制到单元格E12中。

（3）在单元格D13中输入公式“=IRR(D24:D34-E24:E34)”。

（4）建立贴现率为1%-20%的净现值单变量模拟运算表，如上图所示。发现12%时是-0.28，13%时是6.8，利用插值法公式：

“=C17+(D18-D17)/(D19-D17)*(C19-C17)”计算出的贴现率为12.04%。

4．略

30

实验六 Excel最优化问题模型的建立

一、实验目的

1．练习掌握Excel规划求解工具的使用。 2．掌握与财务管理相关的最优化求解问题。 二、实验内容

1．“猛牛”奶制品厂用牛奶为原料生产A和B两种奶制品。一桶牛奶可以在1号生产线上用12小时加工成3公斤A产品，或者在2号生产线上用8小时加工成4公斤B产品。由于市场对A、B两种奶制品的需求非常旺盛，因此每天所生产的A、B两种奶制品都能全部出售。根据目前的市场情况，销售每公斤A产品可获利24元，销售每公斤B产品可获利16元。“猛牛”奶制品厂每天能得到50桶牛奶的供应，每天工人的总劳动时间为480小时，并且1号生产线每天至多能加工100公斤A产品，2号生产线的加工能力没有限制。请为该厂制定一个生产计划，如何安排A、B两种奶制品的生产，能使每天获利最大。

2．Nutri-Jenny是一个体重控制中心。它为客户提供各种各样的冷冻主餐。这些主餐受到了严格的营养成分监控，保证客户膳食平衡。一种新的主餐称为“牛腰间肉大餐”。是由牛腰间肉和肉汁，加上一些豌豆、胡罗卜和面包卷制成的。Nutri-Jenny需要确定主餐中各种配料的数量以满足营养需求，同时使成本尽可能地低。每一种配料的营养成分和成本如表9-4所示。

表9-4 每种配料的营养成分和成本表

配料 牛腰间肉 肉汁 豌豆 胡萝卜 面包卷 维生素A 维生素C 卡路里 脂肪所含卡路里 （国际单位（毫克每盎（每盎司） （每盎司） 每盎司） 司） 54 20 15 8 40 19 15 10 15 350 1 3 1 蛋白质 成本 （克每盎（每盎司） 司） 8 1 1 1 40美分 35美分 15美分 18美分 10美分 主餐的营养要求如下：①必须含有280到320之间的卡路里；②脂肪所含卡路里不能超过总卡路里含量的30%；③必须至少含有600国际单位的维生素A、10毫克的维生素C、30克的蛋白质，而且由于实际原因，至少必须包含2盎司的牛腰间肉，每盎司牛腰间肉至少配有半盎司的肉汁。

要求：建立线性规划模型并求解，生成规划求解报告。

3．某企业有三个工厂，生产的产品将运到5个仓库，三个工厂的生产能力分别为310、260和280，每个仓库的需求量为180、80、200、160和220。从工厂运到各仓库的运费如表9-5所示。问该公司怎样安排，所花费的总运费最小？

31

表9-5 从工厂到仓库的运费表

运费 工厂A 工厂B 工厂C

仓库1 10 6 3 仓库2 8 5 4 仓库3 6 4 5 仓库4 5 3 5 仓库5 4 6 9 4．现有一笔资金，准备购买甲、乙、丙三个公司的股票，各公司在过去12年的收益率如表9-6所示。

表9-6 各公司过去12年的收益率 年份 1 2 3 4 5 6 7 8 9 10 11 12

甲 11.20% 10.80% 11.60% -1.60% -4.10% 8.60% 6.80% 11.90% 12.00% 8.30% 6.00% 10.20% 乙 8.00% 9.20% 6.60% 18.50% 7.40% 13.00% 22.00% 14.00% 20.50% 14.00% 19.00% 9.00% 丙 10.90% 22.00% 37.90% -11.80% 12.90% -7.50% 9.30% 48.70% -1.90% 19.10% -3.40% 43.00% 要求：在保证收益率不低于12%的前提下，怎样组合可以使风险最小？在方差不大于0.01的前提下，怎样组合可以使收益率最大？若风险厌恶度等于0.5，则最优证券组合是什么？此时的收益率和方差各为多少？

5．求解方程组。 20x?25y?16z?100 30x?18y?22z?80 8x?12y?40z?66三、实验步骤

1．

步骤1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条件函数。

本生产计划是要解决如何安排A、B两种奶制品生产数量，才能获利最大，因此目标变量为总利润，设为y。决策变量为A、B两种奶制品生产数量，设x1和x2分别为A、B两种奶制品的产量（公斤），显然，x1和x2必须大于等于0.

32

因为每公斤A产品可获利24元，每公斤B产品可获利16元，那么目标函数即为：y=24x1+16x2。由于1号生产线可用12小时加工成3公斤A产品，因此生产每公斤A产品所需要的时间为4小时；同理，2号生产线生产每公斤B产品所需要的时间为2小时，生产两种产品的总时间不能超过480小时，这样工时的约束条件为：4x1+2x2≦480。因为一桶牛奶可加工成3公斤A产品或4公斤B产品，每天只能得到50桶牛奶，所以原材料的约束条件为：x1/3+x2/4≦50。又因为1号生产线每天只能加工100公斤A产品，2号生产线的加工能力没有限制，所以生产能力的约束条件为x1≦100.根据上述分析，可列出以下目标函数和约束条件：

max:y?24x1?16x2st:4x1?2x2?480x1x2??5034x1?100x1,x2?0步骤2：根据目标函数和约束条件，在Excel中建立参考模型。如下图所示。

步骤3：检查模型中各单元格的计算公式。 C9单元格中输入公式“=C8/C3*C4”，将其复制到D9中。 C10单元格中输入公式“=C8/C3”，将其复制到D10中。 C11单元格中输入公式“=C8*C5”，将其复制到D11中。

E8单元格中输入公式“=C8”，E9公式“=SUM(C9:D9)”，E10公式“=SUM(C10:D10)”，E11公式“=SUM(C11:D11)”

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

选择“工具”中的“规划求解”选项，设置求解此问题的规划求解的各项参数。其中$E$11为目标函数的因变量，$C$8:$D$8为决策变量，$C$8:$D$8＞＝0和$E$8:$E$10＜＝$F$8:$F$10为约束条件。

步骤5：分析求解结果，仔细阅读“运算结果报告”、“敏感性报告”与“极限值报告”，并理解报告中的各项内容。

33

求解后发现：当1号生产线安排60公斤的生产量且2号生长线上安排120公斤的生产量时，“猛牛”公司的获利最大，达到3360元。 2．

步骤1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条件函数。此步略。

步骤2：根据目标函数和约束条件，在Excel中建立参考模型。如下图所示。

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

“=B14*$B$22+C14*$C$22+D14*$D$22+E14*$E$22+F14*$F$22”，并利用填充柄复制到G18。

H20单元格中输入公式“=0.5*B22”，H22单元格中输入公式“=B10*B22+C10*C22+D10*D22+E10*E22+F10*F22”。

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

选择“工具”中的“规划求解”选项，设置求解此问题的规划求解的各项参数。其中$H$22为目标函数的因变量，$B$22:$F$22为决策变量，$B$22〉=$H$19；$B$22:$F$22＞＝0；$C$22＞=$H$20；$G$14＜＝$I$14；$G$14＞＝$H$14；$G$15＜＝$I$15；$G$16:$G$18$＞=$H$16:$H$18为约束条件。单击【确定】即可。

3．

同上，步骤1略。 步骤2：根据目标函数和约束条件，在Excel中建立参考模型。如下图所示。

34

步骤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

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