《经济软件分析》实验指导书12

《经济软件分析》实验指导书刘建功候国庆编著

实验一 Excel图表分析

实验目的：

运用EXCEL进行图表分析

实验要求：

1、个人单独完成

2、按实验内容规定步骤操作

3、完成实验习题，实现实验习题参照结果

4、撰写并提交实验报告

5、实验过程中的其它要求

实验内容及操作步骤：

1、打开需要插入图表的工作簿，选择工作表及数据区域。注意：要包括字段名。如图1-1所示。

图1-1

2、单击常用工具栏中的“图表向导”按钮，出现“图表向导-图表类型”对话框。

3、在“图表向导-图表类型”对话框的“图表类型”列表框中，选择一种图表类型，如图1-2所示。

图1-2

4、单击“图表向导-图表类型”对话框中的“下一步”按钮出现“图表向导-图表源数据”对话框，如图1-3所示。因为在步骤1中选择了创建图表所需要的数据区域，因此在对话框中的“数据区域”编辑栏中已经显示有步骤1中选择的数据区域范围（如未执行步骤1，则需要在步骤4中设定数据区域）

图1-3

5、单击“图表向导-图表源数据”对话框中的“下一步”按钮，出现“图表向导-图表选项”对话框。

6、在“图表向导-图表选项”对话框中的“图表标题”栏中输入标题，如图1-4 所示。

图1-4

7、单击“图表向导-图表选项”对话框中“下一步”按钮，出现“图表向导-图表位置“对话框，如图1-5所示。

图1-5

8、单击“图表向导-图表位置”对话框中的“作为其中的对象插入”下拉列表框的下拉按钮，可以选择需要插入的工作表。

9、单击“图表向导-图表位置”对话框中的“完成”按钮，即在工作表中插入了所选图表，在对图表进行修饰美化后如图1-6所示。

图

1-6

10、单击“图表”工具栏的“关闭”按钮，可以关闭“图表”工具栏。

11、分析：可能有季节性趁势，这种趁势在一季度比较明显；三地相较，合肥总部销售业绩始终保持第一，无锡销售处销售业绩排名基本垫底；苏州销售处销售业绩有稳步上升的趋势。

实验习题及参照结果：

1、根据下表，绘制如参照结果的图表并作简要分析。

2、根据下表，绘制如参照结果的图表并作简要分析。

参照结果：

3、根据下表及图，绘制如参照结果的图表并作简要分析。表:

参照结果1：

每月开支

房租￡250.00

食物

￡200.00

水

￡15.00

煤气

￡25.00

电

￡30.00

旅游

￡45.00

存款

￡30.00

娱乐

￡185.00

参照结果2：

￡200.00￡25.00￡45.00￡185.00

实验二数据管理与经营综合分析

实验目的：

运用EXCEL进行数据管理与经营综合分析

实验要求：

1、个人单独完成

2、按实验内容规定步骤操作

3、完成实验习题，实现实验习题参照结果

4、撰写并提交实验报告

5、实验过程中的其它要求

实验内容及操作步骤：

(一)数据排序、筛选、分类汇总

1、阅读数据资料

3、菜单中“数据/排序”命令，打开“排序”对话框。

4、单击“排序”对话框的“主要关键字”下拉列表框的下拉按钮，出现其下拉列表，选择一个排序关键字（如类型）。

5、选择排序方式（“升序”或“降序”）。

6、单击“排序”对话框中的“确定”按钮，即完成排序操作。

7、单击子菜单中的“自动筛选”后，在数据清单行字段的右端出现一个箭头。

8、单击某一字段的箭头，在下拉列表中显示出(全部)、(前10个…)、(自定义…)3项。

9、经排序后，可单击数据清单中任一单元格，在“数据”菜单中选择“分类汇总”。

10、在对话框中，列有“分类字段”、“汇总方式”和“选定汇总项”3个框。单击“分

类字段”右端的箭头，列出数据清单各字段名。可从中选择“类别”（本例选“类型”）作为分类的依据。在“汇总方式”框中常设为“求和”，如果单击右端的箭头。可以列出“求和”、“均值”等数据库函数，单击“选定汇总项”右端的箭头，在下拉列表中选择“金额”进行汇总(即求和)。选定以后，单击“确定”按钮，即可得出下图所示的结果。

提花长袖衫汇总167,760.00

总计259,730.00

11、深入实验：按成份分类汇总。

（二）经济指标分析

1收集企业经营数据。

2

3、输入计算公式：选定F4单元格，输入计算比值的公式“＝D4/E4”，计算作图数据。

4、向下填充公式：填充计算公式：选定F4单元格，将鼠标指向当前单元格的右下角填充柄。当鼠标指针变为黑色十字形状时，按住鼠标左键拖到F7放开。

5、计算平均值：选定F3单元格，单击粘贴函数按钮，选定常用或统计分类中的AVERAGE函数。或是输入等号后，单击编辑栏左侧的函数列表框下拉箭头，从中选择AVERAGE函数。然后在AVERAGE函数对话框中的Number1框中输入“F4:F7”，或是直接用鼠标选定F4:F7单元格区域。建立计算平均值的公式“＝AVERAGE(F4:F7)”。按照类似的方法，计算流动性、安全性等其它比值和平均值。

6、选定制作雷达图的数据源。选定A3: A27单元格区域，然后按住[Ctrl]键，再选定F3:F27单元格区域。前者用来标识坐标轴信息，后者是实际作图的数据源。

7、单击图表向导工具按钮，将出现图表向导－4步骤之1－图表类型对话框。。在图表类型框中选雷达图，并在子表图类型中选定左边的图形，单击确定。

8、这时将出现图表向导－4步骤之2－图表源数据对话框。确认数据区域中的单元格区域地址无误后，单击确定。

9、这时将出现图表向导－4步骤之3－图表选项对话框。该对话框有多个选项卡。在标题选项卡中，可以输入雷达图的标题。在坐标轴选项卡和网格线选项卡中可以选择是否显示坐标轴和网格线，一般按默认值设置。在图例选项卡中可以选择是否显示图例以及显示图例的位置，这里选择不显示图例。数据标志选项卡可以选择是否显示数据标志以及按值还是按百分比显示，这里选择无。每当选择不同的选项，对话框中的预览图都会显示相应的图形示例。图表选项都选定后，单击确定。

10、最后出现图表向导－4步骤之4－图表位置对话框。选择是将图表作为图表工作表插入到工作簿中，还是将图表作为对象插入在指定的工作表中。这里选择将图表作为新工作表插入，并将新工作表命名为经营分析图。最后单击完成。

11、分析：该企业的安全性、成长性和生产性比率指标均位于标准区，但是流动性、收益性比率指标却低于同行业平均水平，这属于积极扩大型。以增加数量为主要手段扩大经营的企业通常出现这种情况。这时应注意改进利润计划，根据市场

需求尽量投产高利润产品，同时还应注意节约经费。争取出现积极扩大型——稳定理想型趋势，避免出现积极扩大型——活动型——均衡缩小型。

实验习题

任找一家上市公司年报，并收集其行业数据，据此对其进行经营分析。

实验三财务与决策分析

实验目的：

运用EXCEL进行财务与决策分析

实验要求：

1、个人单独完成

2、按实验内容规定步骤操作

3、完成实验习题，实现实验习题参照结果

4、撰写并提交实验报告

5、实验过程中的其它要求

实验内容及操作步骤：

（一）财务函数分析

1、分析题目：已知要开办一家服装商店，预计投资为￥110,000，并预期为今后五年的净收益为：￥15,000、￥21,000、￥28,000、￥36,000和￥45,000。试求此项投资四年、五年、二年后的内部收益率

2、输入数据：在工作表的B1：B6输入数据，如下表所示。

3、输入计算公式：内部收益率，IRR（v,g）。计算此项投资四年后的内部收益率IRR（B1：B5）为-3.27%；计算此项投资五年后的内部收益率IRR（B1：B6）为8.35%；计算两年后的内部收益率时必须在函数中包含g，即IRR（B1：B3，-10%）为-48.96%。注：如果函数IRR返回错误值#NUM！，或结果没有靠近期望值，可以给g换一个值再试。

4、观察分析结果

（二）理财实例：个人住房贷款分析

1、分析思路：列出不同付款期数的贷款利率，根据贷款总额计算出每期应支付的贷款本息，然后再根据自己的经济承受能力选择一种分期付款方式。（采用等额本息还款方式）

2、定义变量

选择要定义为变量的数据范围(如选择B3单元格)；

打开菜单中“插入/名称/定义”命令，出现“定义名称”对话框；

单击“定义名称”对话框中的“添加”按钮，即定义变量“300000”的名称为“贷款总额”并加入到“在当前工作簿中的名称”列表中；

单击“定义名称”对话框中的“关闭”按钮；

选择其他要定义为变量的数据(即选择选择A6至A17单元格)；

打开菜单中“插入/名称/定义”命令，出现“定义名称”对话框；

单击“定义名称”对话框中的“添加”按钮，即将变量“年利率”加入到“在当前工作簿中的名称”列表中；

单击“定义名称”对话框中的“关闭”按钮；

同理选择数据B6:B17区域设定名称为“贷款期数”。

3、输入计算公式

单击工作表的C6单元格；

单击常用工具栏中的“粘贴函数”命令，出现“粘贴函数”对话框；

单击“粘贴函数”对话框中的“或选择类别”下拉列表框的下拉按钮，选择其列表中“财务”类；

在“粘贴函数”对活框的“选择函数”列表中选择“PMT”函数；

单击“粘贴函数”对话框中的“确定”按钮，出现“函数参数”对话框；

在“函数参数”对话框的“Rate”文本框中输入：年利率/100/12，在“Nper”文本框中输入：“贷款期数”，在“Pv”文本框中输入“贷款总额”；

单击“函数参数”对话框中的“确定”按钮，注意：由于是偿还贷款，所以C6单元格中的数值是负值，并且用红色标示；

使用C6单元格右下角填充控点向下拖动到C9单元格，完成公式复制。

4、分析

通常根据自己家庭的月总收人来确定贷款方式。另外，要考虑到增加工资、物价的上涨以及货币的自然贬值的因素。

1列出可能方案并给出其成功的相应概率；

2运用sumproduct函数求出各方案的期望值；

3运用

选择方案:方案3

=LOOKUP(Max(E3:E5),E3:E5,A3:A5)

实验习题

1、财务函数练习（见实验3习题1）。

2、计算“理财实例：个人住房贷款分析”中的利息。

3、用等额本金还款方式计算“理财实例：个人住房贷款分析”中的每月还款额与利息。

实验四模拟运算与透视分析

实验目的：

运用EXCEL进行模拟运算与透视分析

实验要求：

1、个人单独完成

2、按实验内容规定步骤操作

3、完成实验习题，实现实验习题参照结果

4、撰写并提交实验报告

5、实验过程中的其它要求

实验内容及操作步骤：

（一）贷款分析：双变量模拟运算表的操作

1、构造运算列表，输入数据

选择某个单元格区域作为模拟运算表存放区域，在该区域的最左列输入假设的利率变化范围数据；在该区域的第一行输入可能的贷款年限数据。在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式。

贷款分析

贷款额10,000,000

年利率5%

年限10

月偿还额-￥106,066

表对话框的输入引用行的单元格框中输入“$B$3”；在输入引用列的单元格框中输入“$B$4”单击确定。

贷款分析

贷款额10,000,000

年利率5%

年限10

月偿还额-￥106,066

“{=表(B3,B4)}”，表示其是一个以B3为行变量，B4为列变量的模拟运算表。

（二）方案分析

1分析并构建分析列表

前景分析

由于现在还没有任何方案，所以方案管理器对话框中间显示“未定义方案”的信息。根据提示，单击添加按钮。出现添加方案对话框；

3、在方案名框中键入方案的名称，这里键入“新产品”。然后指定投资（万元）、产品所在的单元格为可变单元格，单击确定。出现方案变量值对话框，框中显示原来的数据。在相应的框中键入模拟数值：300。单击确定。“新产品”方案创建完毕，相应的方案自动添加到方案管理器的方案列表中。按照上述步骤再建立“现有产品”方案（模拟值为100）。

4、浏览、编辑方案

方案创建好以后，可以根据需要查看每个方案对投资项目净现值数据的影响。其具体操作方法是：在方案管理器对话框的方案列表中，选定要查看的方案。单击方案管理器对话框的显示按钮，再单击确定。这时工作表中将显示该模拟方案的计算结果。

5、方案总结

单击工具菜单中的方案命令，将弹出方案管理器对话框。单击方案管理器对话框中的总结按钮，将弹出方案总结对话框，根据需要在方案总结对话框中选择适当的结果类型，一般情况下可选择方案总结，如果需要对报告进一步分析，可选方案数据透视表。在结果单元格框中指定净现值所在的单元格B16。单击确定按钮。

注释：“当前值”这一列表示的是在

建立方案汇总时，可变单元格的值。

每组方案的可变单元格均以灰色底纹突出显示。

在方案总结中，“当前值”列显示的是在建立方案汇总时，可变单元格原来的数值。每组方案的可变单元格均以灰色底纹突出显示。根据各方案的模拟数据计算出的目标值也同时显示在总结中，便于管理人员比较分析。比较两个方案的结果单元格“净现值”的数值，可以看出“扩建厂”方案效果最好。

（三）透视分析

1、实验数据见“实验4内容3：数据透视1”；

2、打开“数据”菜单，选择其中的“数据透视表和数据透视图”命令，出现“数据透视表和数据透视图向导——3步骤之1”对话框；

3、选择“请指定待分析数据的数据类型”中“Microsoft Office Excel”数剧列表或数据库；选择“所需创建的报表类型”中“数据透视表”，如图9-2所示；

4、单击“数据透视表和数据透视图向导——步骤之1”对话框中的“下一步”按钮，出现“数据透视表和数据透视图向导——步骤之2”对话框；

5、适当移动“数据透视表和数据透视图向导一一3步骤之2”对话框的位置，并

选择需要透视的数据区域(注意要包括标题在内)；

6、单击“数据透视表和数据透视图向导——3步骤之2”对话框中的“下一步”按钮，出现“数据透视表和数据透视图向导——3步骤之3”对话框；系统默认的选择是在“现有工作表”内显示透视表。可以选择“新建工作表”来显示透视表以免影响原工作表的数据；

7、选择“数据透视表和数据透视图向导——3步骤之3”对话框中的“新建工作表”按钮。

8、单击“数据透视表和数据透视图向导——步骤之3”对话框中的“完成”按钮，即出现数据透视表。

注意：上述操作产生的表格并不是真正的数据透视表，它仅仅是一个数据透视表的框架。有3个部分：

一是“数据透视表”框架，

二是“数据透视表字段”窗格，

三是“数据透视表”工具栏。

9、使用数据透视表框架创建数据透视表的操作步骤如下：

⑴将“数据透视表字段列表”中“区域”字段拖到数据透视表框架中的“请将列字段拖至此处”栏；

⑵将“数据透视表字段列表”中的“产品类别”和“产品编号”字段拖到数据透视表框架中的“请将行字段拖至此处”栏；

⑶将“数据透视表字段列表”中的“金额”和“数量”字段拖到数据透视表框架中的“请将数据项拖至此处”栏，结果即显示了所有产品的销售数量与销售金额汇总。