084605计算机财务管理实验指导书

1、山东工商学院 上上 机机 实实 验验 指指 导导 书书 课程编号： 084605 课程名称：计算机财务管理 院（部）会计学院 教研室会计信息化 日 期2011 年 9 月 一、实验课时安排 课时分配(学时) 章 节 名 称课堂 授课 实 验 上 机 讨 论 课 外 备 注 实验一 复习 excel 基本操作 4 实验二 excel 函数、公式、数据管理和分析 4 实验三 财务分析模型的设计和建立 4 实验四 筹资决策模型的设计和建立 4 实验五 项目投资决策模型的设计和建立 4 实验六 excel 最优化问题模型的建立 4 实验七 财务预测模型的设计和建立 4 实验八 财务管理系统的建立 4

2、合 计32 总 课 时32 二、实验参考书目 1刘兰娟等经济管理中的计算机应用excel 数据分析、统计预测和决策 模拟北京：清华大学出版社，2006 2韩良智excel 在财务管理中的应用北京：清华大学出版社，2009 3黄操军excel 在财务分析与投资管理中的应用北京：中国水利水电出版 社,2008 4刘捷萍等excel 在财务管理中的应用北京：高等教育出版社，2008 5恒盛杰资讯excel 会计与财务管理经典 108 例北京：中国青年出版社， 2007 6excel homeexcel 图表实战技巧精粹北京：人民邮电出版社，2008 目 录 实验一 复习 excel 基本操作.1 一

3、、实验目的.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 实验七 财务预测模型的设

4、计和建立.36 一、实验目的.36 二、实验内容.36 三、实验步骤.37 实验八 财务管理系统的建立.42 一、实验目的.42 二、实验内容.42 三、实验步骤.42 0 实验一实验一 复习复习 excelexcel 基基本操作本操作 一、实验目的 1掌握 excel 的基本操作。熟练掌握各种启动退出 excel 的有关操作； 熟悉 excel 的窗口及初始屏幕各部分的功能；掌握 excel 联机帮助功能的使用方 法。 2掌握工作表的建立方法。熟练掌握工作表的建立方法和操作技巧，如建 立一个新的工作薄、向工作表中输入各种类型的数据、设置数据的有效性、数 据的编辑和修改、格式的编排、查找和替换

5、单元格中的数据以及各种引用的操 作；保存工作薄、工作薄密码的设置。 3掌握工作表的编辑方法。熟练掌握工作表的各种编辑操作，包括移动、 复制、删除、更名、隐藏等，熟练掌握工作表的管理方法，包括打开、查看多 个工作薄等。 4掌握窗口的切换、隐藏、拆分、冻结等操作。 5掌握打印操作，包括页面设置、分页设置与调整、打印预览等。 6学会用图表的形式表示数据间的复杂关系，直观地分析统计数据。包括 图标的建立步骤、图表的编辑等。 7了解 excel 的网络功能。包括创建超级链接、获取或发布数据等。 二、实验内容 1在磁盘上建立一个文件名为“学生成绩表”的工作薄，在工作表 sheet1 中创建一个如表 1-1

6、 所示的工作表。 表 1-1 2011 级学生成绩表 姓名性别高数线代英语物理语文会计学财务管理总成绩平均分 张三男92807372809086 梁齐男86767576828367 王文女75617671868481 吕梁男68806980797986 王青女59738069657672 岳韩男71697167746890 戴卫女82816585816274 杜平男73787064836592 实验要求如下。 （1）练习添加记录，并用公式和函数求出每位同学的总成绩和平均分。 （2）将工作表重新命名为“学生成绩表” 。 （3）将“2011 级学生成绩表”这一标题设置格式为黑体 14 号字，居中显

7、 示，颜色为红色。表格中的文字型数据居中显示。 （4）将“学生成绩表”调整到适当的列宽和行高。 （5）将“学生成绩表”复制到同一工作簿的另一工作表中，命名为“学生 1 成绩表 2” 。 （6）在“学生成绩表”中显示平均分大于等于 75 且小于 80 的记录，然后 恢复原状。 （7）在“2011 级学生成绩表”数据区域使用自动套用格式。 （8）在“学生成绩表 2”数据区的最后添加一行，利用公式计算出各科成 绩和全班同学总成绩的平均分。 （9）将“学生成绩表 2”的标题设为楷体，22 号字，水平居中显示，并且 将标题单元格所在的行高设为 24。 （10）在）在“学生成绩表学生成绩表2”中只显示姓王

8、的成绩记录。中只显示姓王的成绩记录。 （11）为“学生成绩表”工作簿设置文件打开密码 123456。 （12）在“学生成绩表 2”中，根据每位同学的总成绩建立柱形图，并在 该图中添加数据标志。 （13）为“学生成绩表”设置页边距页眉和页脚，并练习设置打印区 域设置打印标题及其他打印选项，最后进行打印预览。 2某部门职工工资表如下表 1-2 所示。 表 1-2 某部门职工工资表 编号科室姓名 性 别 基本 工资 职务 工资 奖 金 应发 工资 公积金 扣款 其他 扣款 实发 工资 1 教务处张建兰女 89630030050 2 总务处杨审评男 75620020080 3 学生处石为国男 1250

9、40015045 4 财务处杨芳女 95015024035 5 财务处柳丝从女 64553030025 6 学生处黄连素女 100020020084 7 教务处王小郑男 45631028025 8 学生处华丽伟男 78925027030 9 教务处李铁牛男 35434045035 10 学生处廖晨女 114542340045 11 总务处赵学军男 75032130028 12 总务处杨家辉男 45030032034 13 学生处张德华男 56026045265 14 总务处李楠女 78025332010 实验要求如下： （1）输入上述职工工资表（“性别”列除外） ，将工作表命名为“工资表” ，

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

11、18（24 像素） ， “编号”列宽度设为 4.5（41 像素） 。 （9）根据（5）计算的各工资项目总额，分别计算基本工资总额、职务工 资总额和奖金总额占应发工资总额的比例，建立可分离的饼状图，标明具体数 值。 （10）按科室分类汇总实发工资、应发工资。 （11）将“职工工资表”设置密码保护，整个表格的“实发工资”列以及 表外的各行各列不允许鼠标到达，其它各列用户可以输入数据。 （12）将“职工工资表”中的首行冻结起来，使得向下按垂直滚动条时， 首行保持不动。 （13）再添加若干行的数据，进行页面设置，使得浏览（或打印）时每页 都显示首行标题，页脚显示页码。 3从上海证券交易所（http:/

12、） 、中国上市公司咨询网 （http:/）或某上市公司的网页中，找到相关的数据资料，在 excel 中创建 web 查询来获取相关数据。 三、实验步骤 1 启动 excel，在 sheet1 工作表中输入表 1-1 数据，保存工作簿，将其命名为 “学生成绩表” ，如下图所示。 （1）在 j3 单元格中输入公式“=sum(c3:i3)” ，利用填充柄向下填充至 j10；在 k3 单元格中输入公式“=average(c3:i3)” ，利用填充柄向下填充至 3 k10。 （2）双击 sheet1 工作表标签，输入“学生成绩表” 。 （3）选择 a1:k1 单元格区域，单击格式工具栏相应按钮，设置字体

13、、字 号、颜色及居中。 （4）略 （5）鼠标单击“学生成绩表”标签，按住“ctrl”键，拖动鼠标到标签的 其他位置，释放鼠标；再同（2）将其名称改为“学生成绩表 2” 。 （6）选择 a2:k10 单元格区域，选择“数据/筛选/自动筛选” ，鼠标单击 “平均分”右侧的下三角按钮，选择“自定义” ，在【自定义自动筛选方式】 ” 对话框中设置相应的条件后，按【确定】按钮，显示符合条件的记录。若想恢 复原状，再用鼠标单击“平均分”右侧的下三角按钮，选择“全部”即可。 （7）选择“2011 级学生成绩表”数据所在单元格区域，单击【格式】 /【自动套用格式】 ，出现对话框，选择相应项格式即可。 （8）选

14、定“学生成绩表 2” ，选择 c11 单元格，输入公式 “=average(c3:c10)” ，在利用填充柄填充至 j11 即可。 （9）略 （10）选择 a2:k11 单元格区域，选择“数据/筛选/自动筛选” ，鼠标单击 “姓名”右侧的下三角按钮，选择“自定义” ，在【自定义自动筛选方式】 ”对 话框中的“姓名”行中输入“王*” ，按【确定】按钮，显示符合条件的记录。 （11）单击【格式】/【选项】 ，选择“安全性”选项卡，在“打开权限密 码”看重输入“123456” ，按【确定】按钮。 （12）选定“学生成绩表 2” ，选择 a3:a10，j3;j10 单元格区域，单击常用 工具栏上的【图

15、表向导】按钮，选择“柱形图” ，单击“分类轴标志”框右边的 “折叠”按钮，选择 a3:a10；单击【下一步】按钮，在“数据标志”选项卡中 选择“值” ；接下来可以进行其他设置，单击【完成】按钮即可。 （13）略 2 启动 excel，在 sheet1 工作表中输入表 1-2 数据，保存工作簿，将其命名为 “某部门职工工资表” （也可以在上题的工作簿中的另一工作表中输入相应数据） ，如下图所示。 4 （1）将工作表命名为“工资表” （略） ；选择 h3，输入公式“=sum(e3:j3)” ，并填充至 h16。 （2）在 v2、v3 单元格中分别输入“男”和“女” ，将 v 列隐藏（也可以 部隐藏

16、） ，选择 d3，单击【数据】菜单下的【有效性】命令，在“允许”框中 选择“序列” ，单击“来源”框右侧的“折叠”按钮，选择 v2:v3，单击【确定】 按钮，再利用填充柄将 d3 填充至 d16。 （3）选择 i3 单元格，输入公式“=round(if(h33000) 公式：=sumif(a3:a10，a1，b3:b10) 公式：=sumif(a3:a10，*a*，b3:b10) 注意：*a*是使用*（星号）模糊求和，星号代表任意多个字符 公式：=sumif(a3:a10，?a2?，b3:b10) 注意：?a2?：利用通配符“？”（问号）实现占位功能。问号在字符中 是强行占用一个字符的位置。

17、（3） 公式：=countif(e2:e7，2500) 公式：=countif(a2:a7，财务部) （4）略 （5） 10 公式：=daverage(a3:g9,总工资,a1:g2) （6）-（9）略 3-6 略 实验三实验三 财务分析模型的设计财务分析模型的设计和建立和建立 一、实验目的 掌握获取数据的方法并能设计财务分析模型。 二、实验内容 1仿照课本将“pz.txt”和“pz.cvs”文本文件的数据引入 excel 中； 2建立数据库查询，将所给的 access 数据库（文件名为 zzbb.mdb，内容 参见第 3 章）中的资产负债表和利润表数据引入到 excel 工作表中； 3建立

18、web 查询，地址栏中输入 “http:/ ，将华能国际 近三年年的资产负债表、利润表数据导入到 excel 中。 4对华能国际当年的财务报表进行分析（包括比率分析和杜邦分析） 。 三、实验步骤 1略 2 （1）启动 microsoft query。单击“数据”菜单上的“导入外部数据”子 菜单，然后单击“新建数据库查询”命令，出现选择数据源对话框，如图 3-1 所示。 （2）在指定的数据库文件中选择欲导入的数据。使用 microsoft query 可以检索多种类型数据库中的数据，包括 microsoft access、microsoft sql server 和 olap services

19、等等。我们在这里选择数据库中的“ms access database*” ，按“确定”按钮，出现选择数据库对话框，如图 3-2 所示。 图 3-1 选择数据源对话框 11 （3）选择 zzbb.mdb 数据库，按“确定”按钮，出现选择数据表对话框。 选择资产负债表，按照向导进行操作。 （4）数据返还给 excel 工作表。在查询向导即将完成查询作业时，选定数 据结果需返回的工作表：本工作表、新建工作表和数据透视表。如果选择 “将 数据返回到 microsoft excel” ，然后再选择放置外部数据的位置，如图 3-3 所示。 （5）同理，将利润表引入 excel 中即可。 3略 4略 图 3

20、-3 确定数据返还位置对话框 图 3-2 选择数据库对话框 12 实验四实验四 筹资决策模型的设计筹资决策模型的设计和建立和建立 一、实验目的 练习相关的财务函数，并能设计和建立相关的筹资决策模型。 二、实验内容 1按图 5-4 所示的模型结构，进行长期借款筹资决策动态模型扩展设计。 要求至少完成除还款方式选择、还款期间选择和还款时点选择以外的所有设计 内容。这 3 个选项在你设计的模型中分别固定为等额本息、月付和期末还款。 2根据 5.2.3 节所述，按照净利租赁现值法设计借款购入设备或融资租入 设备的决策模型。 3根据 5.2.3 节所述，按照损益平衡租金法设计借款购入设备或融资租入 设备

21、的决策模型。 4根据 5.3.2 节所述，参考图 5-11 中优先股资本成本计算分析模型，按股 利折现模型设计普通股资本成本计算分析模型。 5根据图 5-11 模型原理，参照公司普通股（外部权益）资本成本计算模 型，设计留存收益（内部权益）资本成本计算分析模型。 6按图 5-20 所示的模型结构，利用双变量模拟运算表，设计一个针对贷 款金额和贷款年限的长期借款分析模型。要求：年利率、每年还款次数和贷款 年限用控件工具箱中的数值调节钮或滚动条使其成为可变参数。 7某公司发行面额为 500 万元的 10 年期债券，票面利率 12%，发行费率 为 5%，所得税税率为 33%，设计一个计算该债券资本成

22、本的模型，并在此基 础上利用窗体控件使该模型具有一定的通用性。 8某公司目前的资本来源包括每股面值 1 元的普通股 800 万股和平均利 率 10%的 3000 万元债券。现有一项目需投资 4000 万元，预计投产后每年可增 加营业利润（息税前利润）400 万元。该项目备选的筹资方案有三个：按 11%的 利率发行债券；按面值发行股利率为 12%的优先股；按每股 20 元的价格增 发普通股。公司目前的息税前利润为 1600 万元；公司所得税率为 25%；证券发 图 5-20 长期借款模型结构 13 行费可忽略不计。要求设计一模型完成以下辅助决策功能：计算按不同方案 筹资后的普通股每股收益；计算增

23、发普通股和债券筹资的每股收益无差别点， 以及增发普通股和优先股筹资的每股收益无差别点；计算筹资前的财务杠杆 和按三个方案筹资后的财务杠杆；根据计算结果，该模型能给出辅助决策结 果，即应选择哪一种筹资方案及其理由。 9按图 5-21 所示的结构设计一个购房贷款分析模型，模型右半部分是对 总还款额和总利息额在等额本息和等额本金还款方式下的分析比较图。 要求如下： （1）用户只需输入房价、房屋面积以及等额本金还款分析中的还款月份； 用滚动条调节首付百分比、贷款利率和贷款年限，其他单元格内容由模型根据 公式自动计算。 （2）数据有效性规则：房价、房屋面积为大于等于 0 的数值；还款月份 为大于等于 1

24、 但小于等于总还款月数的整数；首付百分比为 5%80%，无小数； 贷款利率为 1.00%10.00%，保留两位小数；贷款年限为 530 的整数。 （3）根据等额本息和等额本金还款方式下的总还款额、总还款利息绘制 柱形图，以便更加直观地对两种还款方式进行对比分析。 （4）将房价、房屋面积、还款月份以及滚动条链接单元格以外的整个工 作表保护，以防误录数据。 10某公司的普通股发行价格为每股 20 元，上年股利率为每股 1 元，新 发行普通股的筹资费率为 9.62%，股利预计增长率为 17.88%。要求设计一模型 计算该公司的普通股和留存收益的资本成本率，并具有一定的通用性。 三、实验步骤 1 模型

25、总体结构布局如教材图 5-4 所示。 参数调节按钮主要属性设置如下表所示。 linkedcellmaxminsmallchange 贷款总额(元) j310000001000010000 图 5-21 购房贷款分析模型 可调节参数 属性 14 贷款总期限(年) j41511 年利率 l71000022501 贷款期次(月) l1218011 起始期次 l1918011 结束期次 l2018011 主要单元格公式设置如下表所示。 单元格公式备注 a3=j12 a4=a3+1 复制到 a5a27 b3=if(a3=j$6,-ppmt($j$9,$a3,$j$6,$j$3),0) 复制到 b4-b2

26、7 c3=-cumprinc(j9,j6,j3,1,j12,0) c4=if(a4=j$6,c3+b4,0) 复制到 c5-c27 d3=if(a3=j$6,j$3-c3,0) 复制到 d4d27 e3=if(a3=j$6,-ipmt($j$9,$a3,$j$6,$j$3),0) 复制到 e4e27 f3=-cumipmt(j9,j6,j3,1,j12,0) f4=if(a4j6,j6,l12) j13=-pmt($j$9,$j$6,$j$3) j14=-ppmt($j$9,$j$12,$j$6,$j$3) j15=-cumprinc(j9,j6,j3,1,j12,0) j16=j3-j15

27、j17=-ipmt($j$9,$j$12,$j$6,$j$3) j18=-cumipmt(j9,j6,j3,1,j12,0) j19=if(l19j20,j20,l19) j20=if(l20j6,j6,l20) j21=-cumprinc(j9,j6,j3,j19,j20,0) j22=-cumipmt(j9,j6,j3,j19,j20,0) 2 15 模型总体结构布局如下图所示。 控件及参数调节按钮主要属性设置如下表所示。 数据源区域 linkedcell 单元格链接 max 最大值 min 最小值 smallchange 步长 租赁设备名称 $f$3:$f$11$a$2 租赁年限 $a$

28、5711 租金支付时点 $d$9:$d$10$a$6 每年付款期数 $d$3:$d$8$a$7 租金年利率 $a$920002251 所得税税率 $a$1033101 基准收益率 k $a$1118008001 借款比例 $f$121001001 借款年利率 $k$1230002251 主要单元格公式设置如下表所示。 单元格公式备注 c3 =index(生产厂家,a2) c4 =租赁年限*index(年租金,a2) c5 =if(a5a15,每期应付租金*每年付款 期数,0),if(and(a150,租赁年限=a15),每期应付租金*每年付款期 数,0) c15 =b15*所得税税率 d15

29、=(b15-c15)/(1+基准收益率 k)a15 e15 =if(and(租金支付时点=先付),if(租赁年限a15,借款金额*借款年 利率,0),if(and(a150,租赁年限=a15),借款金额*借款年利率,0) f15 =if(租金支付时点=先付,if(租赁年限a15,sln(index(设备价值, a$2),index(设备残值,a$2),index(使用年限,a$2),0), if(and(a150,租赁年限=a15),sln(index(设备价值,a$2),index(设 备残值,a$2),index(使用年限,a$2),0) g15 =(e15+f15)*所得税税率 h15

30、=g15/(1+借款年利率)a15 i15 =if(租金支付时点=先付,if(租赁年限a15,index(每年维修费, $a$2),0),if(and(a150,租赁年限=a15),index(每年维修费,$a$2), 0) j15 =i15*基准收益率 k k15 =(i15-j15)/(1+基准收益率 k)a15 选中 b15: k15 区域 拖动 该区 域右 下角 的复 制柄 复制 到 b16: k22 区域 b23=sum(b15:b22) 选中 b23 并将其复制到 c23:k23 区域 c24 =index(设备价款,a2)+k23对应于公式 5-1 的项 f24 =d23+h23

31、+index(设备残值,a2)/(1+基准收益率 k)租赁年限 对应于公式 5-1 的项 g24=if(c24f24,nal0,if(c24f24,nalf24,融资租入设备,借款购入设备) 3 模型总体结构布局如下图所示。 控件及参数调节按钮主要属性设置和主要单元格公式设置与上机实验 2 类 似。不同之处在于：h24 =c24-f24；利用单变量求解工具求得 nal=0 时 的年租金，具体过程为：选定某一租赁设备，点击菜单“工具” 、 “单变量求解” ， 目标单元格 h24，目标值 0，可变单元格为该设备的“nal=0 年租金” ，确定后， 可得到相应的“nal=0 年租金”和辅助决策方案。

32、 17 4 模型总体结构布局如下图所示。 控件及参数调节按钮主要属性设置如下表所示。 租赁或借款决策模型-损益租金平衡法(nal=0) 普通股股利折现模型 18 数据源区域 linkedcell 单元格链接 max 最大值 min 最小值 smallchange 步长 普通股 $b$213000100 1 年股利率 $c$3170001001 筹资费率 e $c$4130001001 股利固定年增 长率 $g$4130001001 主要单元格公式设置如下表所示。 单元格公式备注 b3=c3/10000 b4=c4/10000 b5 =普通股*年股利率 f2 =年固定股利/(普通股*(1-筹资费

33、率 e) f3 =年固定股利 f4=g4/10000 f5 =第一年股利/(普通股*(1-筹资费率 e)+股利固定年增长率 a9 =年固定股利/(普通股*(1-筹资费率 e) a10 =筹资费率 e a11 =a10+0.0001,并将其拖动复制到 a12:a14 区域. b9 =年股利率 c9 =b9+0.0001,并将其拖动复制到 d9:g9 区域. 选中 a9:g14 区域， 对其调用模拟运算表， 引用行的单元格为 b3，引用列的单元格 为 b4，确定即可。 a18 =第一年股利/(普通股*(1-筹资费率 e)+股利固定年增长率 a19 =筹资费率 e a20 =a19+0.0001,并

34、将其拖动复制到 a21:a23 区域. b18 =股利固定年增长率 c18 =b18+0.0001,并将其拖动复制到 d18:g18 区域. 选中 a18:g23 区域， 对其调用模拟运算表， 引用行的单元格为 f4，引用列的单元格 为 b4，确定即可。 5 模型总体结构布局如下图所示。 可调节参数 属性 留存收益资本成本计算分析模型 19 控件及参数调节按钮主要属性设置如下表所示。 数据源区域 linkedcell 单元格链接 max 最大值 min 最小值 smallchange 步长 普通股(万元) $c$213000100 1 年股利率 $d$3130001001 筹资费率 d $d$

35、4130001001 股利固定年增长率 $d$8130001001 无风险报酬率 $d$10130001001 市场组合预期报酬率 $d$11150001001 股票贝塔系数 $d$1221001001 债券投资收益率 $d$1480001001 普通股风险溢价 $d$1595001001 年股利额(元/股) $g$23000101 普通股每股市价 $g$33000050010 主要单元格公式设置如下表所示。 单元格公式备注 c3=d3/10000 c4=d4/10000 c5 =普通股_万元*年股利率 c6 =年固定股利/(普通股_万元*(1-筹资费率 d) c7 =年固定股利 c8=d8/

36、10000 c9 =第一年股利/(普通股_万元*(1-筹资费率 d)+固定股利年增长率 c10=d10/10000 c11=d11/10000 c12=d12/100 c13 =无风险报酬率+股票贝塔系数*(市场组合预期报酬率-无风险报酬率) c14=d14/10000 c15=d15/10000 c16 =债券投资收益率+普通股风险溢价 f2=g2/100 f3=g3/1000 f6 =年股利额/普通股每股市价 f7 =年股利额*(1+普通股利年增长率) f8 =固定股利年增长率 f9 =预期年股利额/普通股每股市价+普通股利年增长率 f10 =无风险报酬率 f11 =市场组合预期报酬率 f

37、12 =股票贝塔系数 f13 =无风险报酬率+股票贝塔系数*(市场组合预期报酬率-无风险报酬率) f14 =债券投资收益率 可调节参数 属性 20 f15 =普通股风险溢价 f16 =债券投资收益率+普通股风险溢价 6 如教材图 5-20 所示。 数据源区域 linkedcell 单元格链接 max 最大值 min 最小值 smallchange 步长 年利率 $e$31000100 1 每年还款次数 $g$31211 贷款年限 $i$32011 主要单元格公式设置如下表所示。 单元格公式备注 a5 =pmt(年利率/每年还款次数,每年还款次数*贷款年限,-贷款金额,0) a6 =贷款金额 a

38、7 =a6+50000,并将其拖动复制到 a8:a14 区域. b5 =贷款年限 c5 =b5+1,并将其拖动复制到 d5:i5 区域. 选中 a5:i14 区域， 对其调用模拟运算 表，引用行的单元 格为 i3，引用列的 单元格为 b3，确定 即可。 7 模型总体结构布局如下图所示。 可调节参数 属性 21 控件及参数调节按钮主要属性设置如下表所示。 数据源区域 linkedcell 单元格链接 max 最大值 min 最小值 smallchange 步长 借款金额 $b$3100010 1 借款年利率 $c$412002251 借款期限_年 $b$51511 每年付息次数 a $b$612

39、11 筹资费率 a $c$71000501 所得税税率 $c$840171 债券发行总额 $e$3100001001 债券总面值 $e$320001001 票面利率 $f$512003001 债券期限_年 $e$62011 每年付息次数 b $e$71211 筹资费率 b $f$81200501 主要单元格公式设置如下表所示。 单元格公式备注 b10 =借款年利率*(1-所得税税率)/(1-筹资费率 a) b11 =每年付息次数 a*rate(借款期限_年*每年付息次数 a,-(借 款金额*借款年利率/每年付息次数 a)*(1-所得税税率),借 款金额*(1-筹资费率 a),-借款金额) e1

40、0 =债券总面值*票面利率*(1-所得税税率)/(债券发行总额 *(1-筹资费率 b) e11 =每年付息次数 b*rate(债券期限_年*每年付息次数 b,-(债 券总面值*票面利率/每年付息次数 b)*(1-所得税税率),债 券发行总额*(1-筹资费率 b),-债券总面值) a15=b11 选中 a23:f28 区域，对其调用模拟运算表，引用 行的单元格为 b5，引用列的单元格为 b4，确定即 可。 a23=e11 选中 a15:f20 区域，对其调用模拟运算表，引用 行的单元格为 e3，引用列的单元格为 e8，确定即 可。 8 模型总体结构布局如下图所示。 可调节参数 属性 22 控件及

41、参数调节按钮主要属性设置如下表所示。 数据源区域 linkedcell 单元格链接 max 最大值 min 最小值 smallchange 步长 普通股股数 $b$310001 1 每股面值 $b$410011 债务资本 $b$6100001001 债务利率 $b$71211 所得税税率 $c$1240171 债券利率 $f$4100001001 股利率 $f$8100001001 普通股股价 $e$1130011 主要单元格公式设置如下表所示。 单元格公式备注 b5 =普通股股数*每股面值 b7=c7/10000 b11 =目前的 ebit+新增 ebit b12=c12/10000 e5

42、=发行债券*债券利率 e8=f8/10000 e10 =普通股股价*增发普通股股数 e14 =(筹资后的 ebit-债务资本*债务利率-年利息额)*(1-所得税税率) -优先股*股利率)/普通股股数 e15 =(筹资后的 ebit-债务资本*债务利率)*(1-所得税税率)-优先股* 股利率)/普通股股数 e16 =(筹资后的 ebit-债务资本*债务利率)*(1-所得税税率)-优先股* 可调节参数 属性 每股收益分析法筹资决策模型 23 股利率)/(普通股股数+增发普通股股数) f14 =筹资后的 ebit/(筹资后的 ebit-债务资本*债务利率-年利息额) f15 =筹资后的 ebit/(

43、筹资后的 ebit-债务资本*债务利率-优先股*股利 率/(1-所得税税率) f16 =筹资后的 ebit/(筹资后的 ebit-债务资本*债务利率) f17 =目前的 ebit/(目前的 ebit-债务资本*债务利率) c14 =(与每股收益无差异点的 ebit-债务资本*债务利率-年利息额)*(1-所得税税率)-优先 股*股利率)/普通股股数-(与每股收益无差异点的 ebit-债务资本*债务利率)*(1-所得 税税率)-优先股*股利率)/(普通股股数+增发普通股股数) c15 调用单变量求解工具，目标单元格 c14，目标值 0，可变单元格 c15，确定即可。 c16 =(与每股收益无差异点

44、的 ebit-债务资本*债务利率)*(1-所得税税率)-优先股*股利率) /普通股股数-(与每股收益无差异点的 ebit-债务资本*债务利率)*(1-所得税税率)-优 先股*股利率)/(普通股股数+增发普通股股数)，利用单变量求解工具求得 c17 之值。 c17 调用单变量求解工具，目标单元格 c16，目标值 0，可变单元格 c17，确定即可。 9 模型总体结构布局如下图所示。 控件及参数调节按钮主要属性设置如下表所示。 数据源区域 linkedcell 单元格链接 max 最大值 min 最小值 smallchange 步长 贷款年限 $d$3201 1 贷款利率 $e$430001001

45、首付比例 $e$7100101 还款月份 $f$924011 主要单元格公式设置如下表所示。 单元格公式备注 b5 =房价*房屋面积 b6 =房价总额-首付款 b7 =房价总额*首付比例 可调节参数 属性 购房贷款分析模型 24 d5 =贷款利率/12 d6 =贷款年限*12 d7=e7/100 d9 =if(f9贷款年限*12,f9,贷款年限*12) b9=d9 b10 =pmt(贷款利率/12,贷款年限*12,-贷款总额,0) b11 =ppmt(贷款利率/12,b9,贷款年限*12,-贷款总额) b12 =ipmt(贷款利率/12,b9,贷款年限*12,-贷款总额) b13 =-cump

46、rinc(贷款月利率,贷款总期数,贷款总额,1,贷款总期数,0)+b14 b14 =-cumipmt(贷款月利率,贷款总期数,贷款总额,1,贷款总期数,0) d4=e4/10000 d5 贷款利率/12 d6 =贷款年限*12 d7=e7/100 d10=d11+d12 d11 =贷款总额/贷款总期数 d12=index(d16:d256,d9) d13 =贷款总额+d14 d14=index(e16:e256,d6) b17 =if(a17=贷款总期数,d$11*a17,0) c17 =if(a17=贷款总期数,贷款总额-b17,0) 选中 b17:c17，并将其公式拖动 复制到 b18:c

47、256 区域。 d17 =贷款总额*贷款月利率 e17=d17 d18 =if(a18=贷款总期数,c17*贷款月利率,0) e18 =if(a18e10,项目 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%时

48、是 6.8，利用插值法公式： “=c17+(d18-d17)/(d19-d17)*(c19-c17)”计算出的贴现率为 12.04%。 4略 30 实验六实验六 excelexcel 最优化问题模型的建立最优化问题模型的建立 一、实验目的 1练习掌握 excel 规划求解工具的使用。 2掌握与财务管理相关的最优化求解问题。 二、实验内容 1 “猛牛”奶制品厂用牛奶为原料生产 a 和 b 两种奶制品。一桶牛奶可以 在 1 号生产线上用 12 小时加工成 3 公斤 a 产品，或者在 2 号生产线上用 8 小 时加工成 4 公斤 b 产品。由于市场对 a、b 两种奶制品的需求非常旺盛，因此 每天所生

49、产的 a、b 两种奶制品都能全部出售。根据目前的市场情况，销售每 公斤 a 产品可获利 24 元，销售每公斤 b 产品可获利 16 元。 “猛牛”奶制品厂 每天能得到 50 桶牛奶的供应，每天工人的总劳动时间为 480 小时，并且 1 号 生产线每天至多能加工 100 公斤 a 产品，2 号生产线的加工能力没有限制。请 为该厂制定一个生产计划，如何安排 a、b 两种奶制品的生产，能使每天获利 最大。 2nutri-jenny 是一个体重控制中心。它为客户提供各种各样的冷冻主餐。 这些主餐受到了严格的营养成分监控，保证客户膳食平衡。一种新的主餐称为 “牛腰间肉大餐” 。是由牛腰间肉和肉汁，加上一

50、些豌豆、胡罗卜和面包卷制 成的。nutri-jenny 需要确定主餐中各种配料的数量以满足营养需求，同时使 成本尽可能地低。每一种配料的营养成分和成本如表 9-4 所示。 表表 9 9- -4 4 每每种种配配料料的的营营养养成成分分和和成成本本表表 配料 卡路里 （每盎司） 脂肪所含卡路里 （每盎司） 维生素 a （国际单位 每盎司） 维生素 c （毫克每盎 司） 蛋白质 （克每盎 司） 成本 （每盎司） 牛腰间肉 54198 40 美分 肉汁 20151 35 美分 豌豆 151531 15 美分 胡萝卜 835011 18 美分 面包卷 40101 10 美分 主餐的营养要求如下：必须含

51、有 280 到 320 之间的卡路里；脂肪所含 卡路里不能超过总卡路里含量的 30%；必须至少含有 600 国际单位的维生素 a、10 毫克的维生素 c、30 克的蛋白质，而且由于实际原因，至少必须包含 2 盎司的牛腰间肉，每盎司牛腰间肉至少配有半盎司的肉汁。 要求：建立线性规划模型并求解，生成规划求解报告。 3某企业有三个工厂，生产的产品将运到 5 个仓库，三个工厂的生产能 力分别为 310、260 和 280，每个仓库的需求量为 180、80、200、160 和 220。 从工厂运到各仓库的运费如表 9-5 所示。问该公司怎样安排，所花费的总运费 最小？ 31 表表 9 9- -5 5 从

52、从工工厂厂到到仓仓库库的的运运费费表表 运费仓库 1仓库 2仓库 3仓库 4仓库 5 工厂 a 108654 工厂 b 65436 工厂 c 34559 4现有一笔资金，准备购买甲、乙、丙三个公司的股票，各公司在过去 12 年的收益率如表 9-6 所示。 表 9-6 各公司过去 12 年的收益率 年份甲乙丙 111.20%8.00%10.90% 210.80%9.20%22.00% 311.60%6.60%37.90% 4-1.60%18.50%-11.80% 5-4.10%7.40%12.90% 68.60%13.00%-7.50% 76.80%22.00%9.30% 811.90%14.0

53、0%48.70% 912.00%20.50%-1.90% 108.30%14.00%19.10% 116.00%19.00%-3.40% 1210.20%9.00%43.00% 要求：在保证收益率不低于 12%的前提下，怎样组合可以使风险最小？在 方差不大于 0.01 的前提下，怎样组合可以使收益率最大？若风险厌恶度等于 0.5，则最优证券组合是什么？此时的收益率和方差各为多少？ 5求解方程组。 三、实验步骤 1 步骤 1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条 件函数。 本生产计划是要解决如何安排 a、b 两种奶制品生产数量，才能获利最大， 因此目标变量为总利润，设为 y。

54、决策变量为 a、b 两种奶制品生产数量，设 6640128 80221830 100162520 zyx zyx zyx 32 x1 和 x2 分别为 a、b 两种奶制品的产量（公斤） ，显然，x1 和 x2 必须大于等 于 0.因为每公斤 a 产品可获利 24 元，每公斤 b 产品可获利 16 元，那么目标 函数即为：y=24x1+16x2。由于 1 号生产线可用 12 小时加工成 3 公斤 a 产品， 因此生产每公斤 a 产品所需要的时间为 4 小时；同理，2 号生产线生产每公斤 b 产品所需要的时间为 2 小时，生产两种产品的总时间不能超过 480 小时，这 样工时的约束条件为：4x1+

55、2x2480。因为一桶牛奶可加工成 3 公斤 a 产品或 4 公斤 b 产品，每天只能得到 50 桶牛奶，所以原材料的约束条件为： x1/3+x2/450。又因为 1 号生产线每天只能加工 100 公斤 a 产品，2 号生产 线的加工能力没有限制，所以生产能力的约束条件为 x1100.根据上述分析， 可列出以下目标函数和约束条件： 0 x,x 100 x 50 4 x 3 x 4802x4x:st 16x24xy:max 21 1 21 21 21 步骤 2：根据目标函数和约束条件，在 excel 中建立参考模型。如下图所 示。 步骤 3：检查模型中各单元格的计算公式。 c9 单元格中输入公式

56、“=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$80 和$e$8:$e$10$f$8:$f

57、$10 为约束条件。 33 步骤 5：分析求解结果，仔细阅读“运算结果报告” 、 “敏感性报告”与 “极限值报告” ，并理解报告中的各项内容。 求解后发现：当 1 号生产线安排 60 公斤的生产量且 2 号生长线上安排 120 公斤的生产量时， “猛牛”公司的获利最大，达到 3360 元。 2 步骤 1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条 件函数。此步略。 步骤 2：根据目标函数和约束条件，在 excel 中建立参考模型。如下图所 示。 步骤 3：检查模型中各单元格的计算公式。 g14 单元格中输入公式 “=b14*$b$22+c14*$c$22+d14*$d$22+e1

58、4*$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$220；$c$22=$h$20；$g$14$i$14；$g$14$h $14；$g$15$i$15；$g$16:$g$18$=$h$1

59、6:$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:

60、$g$10 为决策变量， $c$11:$g$11=$c$12:$g$12；$c$8:$g$100；$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 甲企业