计算机财务管理实验指导

1、目 录实验一 复习 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实验七 财务预测模型的设

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

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

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

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

6、学生成绩表 2”中，根据每位同学的总成绩建立柱形图，并在该图中添加数据标志。（13）为“学生成绩表”设置页边距页眉和页脚，并练习设置打印区域设置打印标题及其他打印选项，最后进行打印预览。2某部门职工工资表如下表 1-2 所示。表 1-2 某部门职工工资表编号科室姓名性别基本工资职务工资奖金应发工资公积金扣款其他扣款实发工资1教务处张建兰女896300300502总务处杨审评男756200200803学生处石为国男1250400150454财务处杨芳女950150240355财务处柳丝从女645530300256学生处黄连素女1000200200847教务处王小郑男456310280258学生处

7、华丽伟男789250270309教务处李铁牛男3543404503510学生处廖晨女11454234004511总务处赵学军男7503213002812总务处杨家辉男4503003203413学生处张德华男5602604526514总务处李楠女78025332010实验要求如下：（1）输入上述职工工资表（“性别”列除外） ，将工作表命名为“工资表” ，并计算应发工资=基本工资+职务工资+奖金。（2）利用数据的有效性，在“性别”列中只允许用户选择男或女输入每个人的性别。（3）公积金扣款计算公式为当应发工资小于 1300 元时，扣款为应发工资的 2%，否则为 5%（用 IF 函数） ，格式保留 2

8、 位小数。（4）计算实发工资应发工资公积金扣款其他扣款。3（5）在现有表格的下一行计算基本工资、职务工资、奖金、应发工资、公积金扣款、其他扣款和实发工资的总额。（6）将该表标题设定为“职工工资表” ，合并居中， “姓名” 、 “性别”列水平垂直均分散对齐。（7）给表格加边框，外粗内细，外红内黑。（8）将表格内每行的高度设置为 18（24 像素） ， “编号”列宽度设为4.5（41 像素） 。（9）根据（5）计算的各工资项目总额，分别计算基本工资总额、职务工资总额和奖金总额占应发工资总额的比例，建立可分离的饼状图，标明具体数值。（10）按科室分类汇总实发工资、应发工资。（11）将“职工工资表”设

9、置密码保护，整个表格的“实发工资”列以及表外的各行各列不允许鼠标到达，其它各列用户可以输入数据。（12）将“职工工资表”中的首行冻结起来，使得向下按垂直滚动条时，首行保持不动。（13）再添加若干行的数据，进行页面设置，使得浏览（或打印）时每页都显示首行标题，页脚显示页码。3从上海证券交易所（http:/） 、中国上市公司咨询网（http:/）或某上市公司的网页中，找到相关的数据资料，在Excel 中创建 Web 查询来获取相关数据。三、实验步骤1启动 Excel，在 sheet1 工作表中输入表 1-1 数据，保存工作簿，将其命名为“学生成绩表” ，如下图所示。（1）在 J3 单元格中输入公式

10、“=sum(C3:I3)” ，利用填充柄向下填充至J10；在 K3 单元格中输入公式“=Average(C3:I3)” ，利用填充柄向下填充至4K10。（2）双击 Sheet1 工作表标签，输入“学生成绩表” 。（3）选择 A1:K1 单元格区域，单击格式工具栏相应按钮，设置字体、字号、颜色及居中。（4）略（5）鼠标单击“学生成绩表”标签，按住“Ctrl”键，拖动鼠标到标签的其他位置，释放鼠标；再同（2）将其名称改为“学生成绩表 2” 。（6）选择 A2:K10 单元格区域，选择“数据/筛选/自动筛选” ，鼠标单击“平均分”右侧的下三角按钮，选择“自定义” ，在【自定义自动筛选方式】 ”对话框

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

12、显示符合条件的记录。（11）单击【格式】/【选项】 ，选择“安全性”选项卡，在“打开权限密码”看重输入“123456” ，按【确定】按钮。（12）选定“学生成绩表 2” ，选择 A3:A10，J3;J10 单元格区域，单击常用工具栏上的【图表向导】按钮，选择“柱形图” ，单击“分类轴标志”框右边的“折叠”按钮，选择 A3:A10；单击【下一步】按钮，在“数据标志”选项卡中选择“值” ；接下来可以进行其他设置，单击【完成】按钮即可。（13）略2启动 Excel，在 sheet1 工作表中输入表 1-2 数据，保存工作簿，将其命名为“某部门职工工资表” （也可以在上题的工作簿中的另一工作表中输入相

13、应数据），如下图所示。5（1）将工作表命名为“工资表” （略） ；选择 H3，输入公式“=sum(E3:J3)”，并填充至 H16。（2）在 V2、V3 单元格中分别输入“男”和“女” ，将 V 列隐藏（也可以部隐藏） ，选择 D3，单击【数据】菜单下的【有效性】命令，在“允许”框中选择“序列” ，单击“来源”框右侧的“折叠”按钮，选择 V2:V3，单击【确定】按钮，再利用填充柄将 D3 填充至 D16。（3）选择 I3 单元格，输入公式“=round(IF(H33000)公式：=SUMIF(A3:A10，A1，B3:B10)公式：=SUMIF(A3:A10，*A*，B3:B10) 注意：*A

14、*是使用*（星号）模糊求和，星号代表任意多个字符公式：=SUMIF(A3:A10，?A2?，B3:B10) 注意：?A2?：利用通配符“？”（问号）实现占位功能。问号在字符中是强行占用一个字符的位置。（3）公式：=COUNTIF(E2:E7，2500)公式：=COUNTIF(A2:A7，财务部)（4）略（5）11公式：=DAVERAGE(A3:G9,总工资,A1:G2)（6）-（9）略3-6 略实验三实验三 财务分析模型的设计财务分析模型的设计和建立和建立一、实验目的掌握获取数据的方法并能设计财务分析模型。二、实验内容1仿照课本将“pz.txt”和“pz.cvs”文本文件的数据引入 Excel

15、 中；2建立数据库查询，将所给的 Access 数据库（文件名为 zzbb.mdb，内容参见第 3 章）中的资产负债表和利润表数据引入到 Excel 工作表中；3建立 WEB 查询，地址栏中输入“http:/ ，将华能国际近三年年的资产负债表、利润表数据导入到 EXCEL 中。4对华能国际当年的财务报表进行分析（包括比率分析和杜邦分析） 。三、实验步骤1略2（1）启动 Microsoft Query。单击“数据”菜单上的“导入外部数据”子菜单，然后单击“新建数据库查询”命令，出现选择数据源对话框，如图 3-1所示。（2）在指定的数据库文件中选择欲导入的数据。使用 Microsoft Query

16、可以检索多种类型数据库中的数据，包括 Microsoft Access、Microsoft SQL Server 和 OLAP Services 等等。我们在这里选择数据库中的“MS Access Database*” ，按“确定”按钮，出现选择数据库对话框，如图 3-2 所示。图 3-1 选择数据源对话框12（3）选择 ZZBB.MDB 数据库，按“确定”按钮，出现选择数据表对话框。选择资产负债表，按照向导进行操作。（4）数据返还给 Excel 工作表。在查询向导即将完成查询作业时，选定数据结果需返回的工作表：本工作表、新建工作表和数据透视表。如果选择 “将数据返回到 Microsoft E

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

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

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

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

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

22、实验步骤1模型总体结构布局如教材图 5-4 所示。参数调节按钮主要属性设置如下表所示。 linkedcellMaxminsmallchange贷款总额(元)J310000001000010000图 5-21 购房贷款分析模型可调节参数属性15贷款总期限(年)J41511年利率L71000022501贷款期次(月)L1218011起始期次L1918011结束期次L2018011主要单元格公式设置如下表所示。单元格公式备注A3=J12A4=A3+1复制到 A5A27B3=IF(A3=J$6,-PPMT($J$9,$A3,$J$6,$J$3),0)复制到 B4-B27C3=-CUMPRINC(J9,

23、J6,J3,1,J12,0)C4=IF(A4=J$6,C3+B4,0)复制到 C5-C27D3=IF(A3=J$6,J$3-C3,0)复制到 D4D27E3=IF(A3=J$6,-IPMT($J$9,$A3,$J$6,$J$3),0)复制到 E4E27F3=-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-J15J17=-IPMT($J$9,$J$12,$J$6,$J$3)

24、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)216模型总体结构布局如下图所示。控件及参数调节按钮主要属性设置如下表所示。 数据源区域Linkedcell单元格链接Max最大值Min最小值Smallchange步长租赁设备名称$F$3:$F$11$A$2 租赁年限$A$5711租金支付时点$D$9:$D$10$A$6每年付款期数$D$3:$D$8$A$7租金年利率$A$

25、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=(B15-C15)/(1+基准收益率 k)A15E15=IF(AND(租金支付时点=先付),IF(租赁年限A15,借款金额*借款年利率,0),IF(AND(

26、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=G15/(1+借款年利率)A15I15=IF(租金支付时点=先付,IF(租赁年限A15,INDEX(每年维修费,$A$2),0),IF(AND(A150,租赁年限=A15),IND

27、EX(每年维修费,$A$2),0)J15=I15*基准收益率 kK15=(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+INDEX(设备残值,A2)/(1+基准收益率k)租赁年限对应于公式 5-1 的项G24=IF(C24F24,NAL0,IF(C24F24,NALF24,融资租入设备,借款购入设备)3模型总体结构布局如下图所示。控件及参数调节按钮主要属性

28、设置和主要单元格公式设置与上机实验 2 类似。不同之处在于：H24 =C24-F24；利用单变量求解工具求得 NAL=0 时的年租金，具体过程为：选定某一租赁设备，点击菜单“工具” 、 “单变量求解” ，目标单元格 H24，目标值 0，可变单元格为该设备的“NAL=0 年租金” ，确定后，可得到相应的“NAL=0 年租金”和辅助决策方案。184模型总体结构布局如下图所示。控件及参数调节按钮主要属性设置如下表所示。租赁或借款决策模型-损益租金平衡法(NAL=0)普通股股利折现模型19 数据源区域Linkedcell单元格链接Max最大值Min最小值Smallchange步长普通股$B$21300

29、0100 1年股利率$C$3170001001筹资费率 E$C$4130001001股利固定年增长率$G$4130001001主要单元格公式设置如下表所示。单元格公式备注B3=C3/10000B4=C4/10000B5=普通股*年股利率F2=年固定股利/(普通股*(1-筹资费率 E)F3=年固定股利F4=G4/10000F5=第一年股利/(普通股*(1-筹资费率 E)+股利固定年增长率A9=年固定股利/(普通股*(1-筹资费率 E)A10=筹资费率 EA11=A10+0.0001,并将其拖动复制到 A12:A14 区域.B9=年股利率C9=B9+0.0001,并将其拖动复制到 D9:G9 区域

30、.选中 A9:G14 区域，对其调用模拟运算表，引用行的单元格为B3，引用列的单元格为 B4，确定即可。A18=第一年股利/(普通股*(1-筹资费率 E)+股利固定年增长率A19=筹资费率 EA20=A19+0.0001,并将其拖动复制到 A21:A23 区域.B18=股利固定年增长率C18=B18+0.0001,并将其拖动复制到 D18:G18 区域.选中 A18:G23 区域，对其调用模拟运算表，引用行的单元格为F4，引用列的单元格为 B4，确定即可。5模型总体结构布局如下图所示。可调节参数属性留存收益资本成本计算分析模型20控件及参数调节按钮主要属性设置如下表所示。 数据源区域Linke

31、dcell单元格链接Max最大值Min最小值Smallchange步长普通股(万元)$C$213000100 1年股利率$D$3130001001筹资费率 D$D$4130001001股利固定年增长率$D$8130001001无风险报酬率$D$10130001001市场组合预期报酬率$D$11150001001股票贝塔系数$D$1221001001债券投资收益率$D$1480001001普通股风险溢价$D$1595001001年股利额(元/股)$G$23000101普通股每股市价$G$33000050010主要单元格公式设置如下表所示。单元格公式备注C3=D3/10000C4=D4/10000

32、C5=普通股_万元*年股利率C6=年固定股利/(普通股_万元*(1-筹资费率 D)C7=年固定股利C8=D8/10000C9=第一年股利/(普通股_万元*(1-筹资费率 D)+固定股利年增长率C10=D10/10000C11=D11/10000C12=D12/100C13=无风险报酬率+股票贝塔系数*(市场组合预期报酬率-无风险报酬率)C14=D14/10000C15=D15/10000C16=债券投资收益率+普通股风险溢价F2=G2/100F3=G3/1000F6=年股利额/普通股每股市价F7=年股利额*(1+普通股利年增长率)F8=固定股利年增长率F9=预期年股利额/普通股每股市价+普通股

33、利年增长率F10=无风险报酬率F11=市场组合预期报酬率F12=股票贝塔系数F13=无风险报酬率+股票贝塔系数*(市场组合预期报酬率-无风险报酬率)F14=债券投资收益率可调节参数属性21F15=普通股风险溢价F16=债券投资收益率+普通股风险溢价6如教材图 5-20 所示。 数据源区域Linkedcell单元格链接Max最大值Min最小值Smallchange步长年利率$E$31000100 1每年还款次数$G$31211贷款年限$I$32011主要单元格公式设置如下表所示。单元格公式备注A5=PMT(年利率/每年还款次数,每年还款次数*贷款年限,-贷款金额,0)A6=贷款金额A7=A6+5

34、0000,并将其拖动复制到 A8:A14 区域.B5=贷款年限C5=B5+1,并将其拖动复制到 D5:I5 区域.选中 A5:I14 区域，对其调用模拟运算表，引用行的单元格为 I3，引用列的单元格为 B3，确定即可。7模型总体结构布局如下图所示。可调节参数属性22控件及参数调节按钮主要属性设置如下表所示。 数据源区域Linkedcell单元格链接Max最大值Min最小值Smallchange步长借款金额$B$3100010 1借款年利率$C$412002251借款期限_年$B$51511每年付息次数 A$B$61211筹资费率 A$C$71000501所得税税率$C$840171债券发行总额

35、$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),-借款金额)E10=债券总面值*票面利率*(1-所得税税率)/(债券发行总额*(1-筹资费率 B)E11=每年付息次数 B*RATE(债券期限_

36、年*每年付息次数 B,-(债券总面值*票面利率/每年付息次数 B)*(1-所得税税率),债券发行总额*(1-筹资费率 B),-债券总面值)A15=B11选中 A23:F28 区域，对其调用模拟运算表，引用行的单元格为 B5，引用列的单元格为 B4，确定即可。A23=E11选中 A15:F20 区域，对其调用模拟运算表，引用行的单元格为 E3，引用列的单元格为 E8，确定即可。8模型总体结构布局如下图所示。可调节参数属性23控件及参数调节按钮主要属性设置如下表所示。 数据源区域Linkedcell单元格链接Max最大值Min最小值Smallchange步长普通股股数$B$310001 1每股面值

37、$B$410011债务资本$B$6100001001债务利率$B$71211所得税税率$C$1240171债券利率$F$4100001001股利率$F$8100001001普通股股价$E$1130011主要单元格公式设置如下表所示。单元格公式备注B5=普通股股数*每股面值B7=C7/10000B11=目前的 EBIT+新增 EBITB12=C12/10000E5=发行债券*债券利率E8=F8/10000E10=普通股股价*增发普通股股数E14=(筹资后的 EBIT-债务资本*债务利率-年利息额)*(1-所得税税率)-优先股*股利率)/普通股股数E15=(筹资后的 EBIT-债务资本*债务利率)

38、*(1-所得税税率)-优先股*股利率)/普通股股数E16=(筹资后的 EBIT-债务资本*债务利率)*(1-所得税税率)-优先股*可调节参数属性每股收益分析法筹资决策模型24股利率)/(普通股股数+增发普通股股数)F14=筹资后的 EBIT/(筹资后的 EBIT-债务资本*债务利率-年利息额)F15=筹资后的 EBIT/(筹资后的 EBIT-债务资本*债务利率-优先股*股利率/(1-所得税税率)F16=筹资后的 EBIT/(筹资后的 EBIT-债务资本*债务利率)F17=目前的 EBIT/(目前的 EBIT-债务资本*债务利率)C14=(与每股收益无差异点的 EBIT-债务资本*债务利率-年利

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

40、 0，可变单元格 C17，确定即可。9模型总体结构布局如下图所示。控件及参数调节按钮主要属性设置如下表所示。 数据源区域Linkedcell单元格链接Max最大值Min最小值Smallchange步长贷款年限$D$3201 1贷款利率$E$430001001首付比例$E$7100101还款月份$F$924011主要单元格公式设置如下表所示。单元格公式备注B5=房价*房屋面积B6=房价总额-首付款B7=房价总额*首付比例可调节参数属性 购房贷款分析模型25D5=贷款利率/12D6=贷款年限*12D7=E7/100D9=IF(F9贷款年限*12,F9,贷款年限*12)B9=D9B10=PMT(贷款

41、利率/12,贷款年限*12,-贷款总额,0)B11=PPMT(贷款利率/12,B9,贷款年限*12,-贷款总额)B12=IPMT(贷款利率/12,B9,贷款年限*12,-贷款总额)B13=-CUMPRINC(贷款月利率,贷款总期数,贷款总额,1,贷款总期数,0)+B14B14=-CUMIPMT(贷款月利率,贷款总期数,贷款总额,1,贷款总期数,0)D4=E4/10000D5贷款利率/12D6=贷款年限*12D7=E7/100D10=D11+D12D11=贷款总额/贷款总期数D12=INDEX(D16:D256,D9)D13=贷款总额+D14D14=INDEX(E16:E256,D6)B17=I

42、F(A17=贷款总期数,D$11*A17,0)C17=IF(A17=贷款总期数,贷款总额-B17,0)选中 B17:C17，并将其公式拖动复制到 B18:C256 区域。D17=贷款总额*贷款月利率E17=D17D18=IF(A18=贷款总期数,C17*贷款月利率,0)E18=IF(A18E10,项目 A 较优,项目 B 较优)”.（2）将 2 个项目各年的数据输入（或采用公式计算）到 Excel 中，如下图所示。30在单元格 D12 中输入公式“=IRR(D24:D34)” ，将单元格 D12 中的公式复制到单元格 E12 中。（3）在单元格 D13 中输入公式“=IRR(D24:D34-E

43、24:E34)” 。（4）建立贴现率为 1%-20%的净现值单变量模拟运算表，如上图所示。发现 12%时是-0.28，13%时是 6.8，利用插值法公式：“=C17+(D18-D17)/(D19-D17)*(C19-C17)”计算出的贴现率为 12.04%。4略31实验六实验六 ExcelExcel 最优化问题模型的建立最优化问题模型的建立一、实验目的1练习掌握 Excel 规划求解工具的使用。2掌握与财务管理相关的最优化求解问题。二、实验内容1 “猛牛”奶制品厂用牛奶为原料生产 A 和 B 两种奶制品。一桶牛奶可以在 1 号生产线上用 12 小时加工成 3 公斤 A 产品，或者在 2 号生产

44、线上用 8 小时加工成 4 公斤 B 产品。由于市场对 A、B 两种奶制品的需求非常旺盛，因此每天所生产的 A、B 两种奶制品都能全部出售。根据目前的市场情况，销售每公斤 A 产品可获利 24 元，销售每公斤 B 产品可获利 16 元。 “猛牛”奶制品厂每天能得到 50 桶牛奶的供应，每天工人的总劳动时间为 480 小时，并且 1 号生产线每天至多能加工 100 公斤 A 产品，2 号生产线的加工能力没有限制。请为该厂制定一个生产计划，如何安排 A、B 两种奶制品的生产，能使每天获利最大。2Nutri-Jenny 是一个体重控制中心。它为客户提供各种各样的冷冻主餐。这些主餐受到了严格的营养成分

45、监控，保证客户膳食平衡。一种新的主餐称为“牛腰间肉大餐” 。是由牛腰间肉和肉汁，加上一些豌豆、胡罗卜和面包卷制成的。Nutri-Jenny 需要确定主餐中各种配料的数量以满足营养需求，同时使成本尽可能地低。每一种配料的营养成分和成本如表 9-4 所示。表表 9 9- -4 4 每每种种配配料料的的营营养养成成分分和和成成本本表表配料卡路里（每盎司）脂肪所含卡路里（每盎司）维生素 A（国际单位每盎司）维生素 C（毫克每盎司）蛋白质（克每盎司）成本（每盎司）牛腰间肉5419840 美分肉汁2015135 美分豌豆15153115 美分胡萝卜83501118 美分面包卷4010110 美分主餐的营养

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

47、工厂厂到到仓仓库库的的运运费费表表运费仓库 1仓库 2仓库 3仓库 4仓库 5工厂 A108654工厂 B65436工厂 C345594现有一笔资金，准备购买甲、乙、丙三个公司的股票，各公司在过去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.00%48.70%912.00%20.50%

48、-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。决策变量为 A、B 两种奶制品生产数量，设66401288022183

49、0100162520zyxzyxzyx33x1 和 x2 分别为 A、B 两种奶制品的产量（公斤） ，显然，x1 和 x2 必须大于等于 0.因为每公斤 A 产品可获利 24 元，每公斤 B 产品可获利 16 元，那么目标函数即为：y=24x1+16x2。由于 1 号生产线可用 12 小时加工成 3 公斤 A 产品，因此生产每公斤 A 产品所需要的时间为 4 小时；同理，2 号生产线生产每公斤B 产品所需要的时间为 2 小时，生产两种产品的总时间不能超过 480 小时，这样工时的约束条件为：4x1+2x2480。因为一桶牛奶可加工成 3 公斤 A 产品或4 公斤 B 产品，每天只能得到 50

50、桶牛奶，所以原材料的约束条件为：x1/3+x2/450。又因为 1 号生产线每天只能加工 100 公斤 A 产品，2 号生产线的加工能力没有限制，所以生产能力的约束条件为 x1100.根据上述分析，可列出以下目标函数和约束条件：0 x,x100 x504x3x4802x4x:st16x24xy:max211212121步骤 2：根据目标函数和约束条件，在 Excel 中建立参考模型。如下图所示。步骤 3：检查模型中各单元格的计算公式。C9 单元格中输入公式“=C8/C3*C4” ，将其复制到 D9 中。C10 单元格中输入公式“=C8/C3” ，将其复制到 D10 中。C11 单元格中输入公式

51、“=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$10 为约束条件。34步骤 5：分析求解结果，仔细阅读“运算结果报告” 、 “敏感性报告”与“极限值报告” ，并理解报告中的各项内容。求解后发现：当 1

52、号生产线安排 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

53、*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$16:$H$18 为约束条件。单击【确定】即可。3同上，步骤 1 略。步骤 2：根据目标函数和约束条件，在 Excel 中建立参考模型。如下图所示。35步骤 3：检查模型中各单元格的计算公式。H8 单

54、元格中输入公式“=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$100；$H$8:$H$10$I$8:$I$10,单击【确定】即可。4.仿照课本例 9-6即可。5略36实验七实验七 财务预测模型的设计

55、和建立财务预测模型的设计和建立一、实验目的1练习掌握预测方法和预测函数，包括INTERCEPT、SLOPE、LINEST、FORCAST、TREND 等。2掌握利用数据分析工具进行财务预测。二、实验内容1甲企业只生产一种产品，在 2010 年 1 月至 2011 年 12 月生产的产品数量（单位：件）如表 10-10 所示。表 10-10 甲企业 2010-2011 年各月的产品产量年月数量年月数量年月数量2010 年 1月3162010 年 9 月3912011 年 5 月4422010 年 2月4282010 年 10 月4332011 年 6 月4012010 年 3月4842010 年

56、 11 月4282011 年 7 月3502010 年 4月4052010 年 12 月3952011 年 8 月3272010 年 5月3682011 年 1 月3352011 年 9 月3602010 年 6月3822011 年 2 月3202011 年 10 月4142010 年 7月3272011 年 3 月3692011 年 11 月4802010 年 8月4602011 年 4 月3982011 年 12 月388要求：（1）利用移动平均工具（间隔为 3）预测该企业在 2012 年 1 月生产的产品数量。（2）利用指数平滑工具（阻尼系数为 0.4）预测该企业在 2012 年 1 月

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

58、万元）员工薪酬总额（万元）销售额（万元）198.324.821.51098.8267.723.621826.337433.922.41003.3415127.724.71554.6590.845.523.211996102.342.624.31483.17115.64023.11407.1812545.829.11551.39137.851.724.61601.210175.667.227.52311.711155.26526.52126.712174.365.426.82256.53丙企业 2011 年各月冰箱销售额的有关数据如表 10-12 所示。表 10-12 丙企业 2011 年各月的

59、冰箱销售额月份123456789101112销售额（万元） 330300300320350380430480530630730830根据以往的经验，该企业的销售额随着时间的推移有可能呈线性变动趋势或指数变动趋势。要求利用有关函数建立一个带有选择销售额变动趋势组合框控件的预测 2012 年 1 月的冰箱销售额的模型。三、实验步骤1（1）操作步骤如下：启动 Excel 电子表，将本例的有关资料录入 Excel 电子表，并设计模型的结构。利用移动平均分析工具对各月生产的产品数量进行预测。方法如下：单击打开【工具】菜单后，找到【数据分析】选项，单击打开【数据分析】对话框，在对话框中选择“移动平均” ，

60、单击【确定】按钮，打开【移动平均】对话框，在对话框的“输入区域”一栏输入“$B$2:$B$25” ，在“间隔”栏输入“3” ，作为移动平均时间跨度，在“输出区域”一栏输入“$C$2” ，然后选中对话框下方的“图表输出”与“标准误差”两个选项，最后单击【确定】按钮，就得到了从 2009 年 3 月份开始出现的移动平均预测值和自 2009 年 5 月38份才有的标准误差。另外，还生成了生产数量的实际值和移动平均预测值对比的图表。在单元格 E6 输入“=C25” 。操作的结果如下图所示。（2）操作步骤如下：启动 Excel 电子表，将本例的有关资料录入 Excel 电子表，并设计模型的结构。利用指数