电子表格决策分析实验报告

决策支持系统实验报告实验名称：电子表格决策分析实验姓名：学号：专业：信息管理与信息系统班级：指导教师：实验成绩：批阅教师签字：实验二电子表格决策分析实验（3个学时）【实验目的】1.了解决策支持系统模型的基础知识。

2.掌握EXCEL提供的函数的使用方法。

3.掌握在EXCEL中进行模型开发与决策分析的方法。

【实验内容】1.理解模型的概念和类别。

2.练习使用EXCEL的投资决策函数：NPV、XNPV、IRR、XIRR 和 MIRR。

3.根据要求构建投资指标决策分析模型，并用以进行决策分析。

【实验步骤】1.理解模型的概念和类别步骤：（1）查阅EXCEL帮助，对照教材分析EXCEL对模型实现的支持方式（2）总结EXCEL提供了哪些类型的DSS模型2. 掌握EXCEL提供的函数的使用方法（1）完成实验教材“6.3.3使用EXCEL的投资决策函数”节的适用于进行现金流分析的Excel 函数示例实验：NPV、XNPV、IRR、XIRR 和 MIRR。

（2）对数据分析结果进行讨论。

3. 根据要求构建投资指标决策分析模型，并用以进行决策分析（1）完成实验教程“6.3.4构建投资指标决策分析模型”节建模试验（2）对模型进行方案试错与优化（3）通过模型对投资方案进行灵敏度分析（4）总结最佳方案及其灵敏度（5）根据以上步骤完成实验教程第6章练习题4.写实验报告，字数>800字【实验过程及结果分析】1.理解模型的概念和类别步骤：（1）查阅EXCEL帮助，对照教材分析EXCEL对模型实现的支持方式。

Excel对模型实现的支持方式体现在建立模型与模型分析、数据访问与管理以及用户界面与可视建模三个方面。

1) 建立模型与模型分析。

Excel具有灵活的建模能力和对所建模型进行What-if分析的功能，利用公式、大量的内建财务统计函数、以及ＶＢＡ语言建立案例数值模型，并利用模拟运算表、方案管理器等功能模型对“如果决策变量如此变化的话，目标变量将如何变化“进行分析。

Excel利用单变量求解功能求解“为了使目标变量达到某个特定的目标值，决策变量应该如何变化"的问题。

此外，Excel具有大量内建函数，操作者可以利用Excel提供的各种函数公式，通过确定参数的数值，按特定的顺序或结构自动执行复杂计算，大大简化了计算模型建立的过程，并有助于提高模型的准确性，特别是Excel 的财务函数、统计函数等对于财务分析相关模型的建立有很大帮助。

2) 数据访问与管理。

Excel具有十分强大的数据分析功能，并且与各种数据库具有良好的接口。

Excel不仅可以创建OLAP多维数据集的数据源，然后使用该数据源进行分析，也可以借助于ODBC工具，直接访问各种由dBase、FoxPro、Access或者SQLServer 等生成的外部数据库。

因而Excel可以通过简单的操作从具有不同结构的数据库中获取所需要的数据，利用数据分析功能直接进行一些复杂的计算、统计工作等，给模型的实现提供了强大的数据支持。

3) 用户界面与可视建模。

Excel不仅具有良好的图形显示能力，而且允许用户利用Excel本身的菜单、工具条、对话框等手段方便地进行操作和建立个性化系统界面。

这些功能与其他功能相结合，决策者可以在决策模型的基础上经过简单操作制作高质量的动态图形，将抽象的决策模型与直观生动的图形完美结合，一定程度上实现可视建模与分析，从而使模型以最有效的方法帮助决策者进行各种必要的决策分析。

例如：利用Excel 的数据透视表功能，可以十分方便地从大量数据中提取有用的综合统计信息，（2）总结EXCEL提供了哪些类型的DSS模型excel其中比较常见的模型有以下几类：1)预测与决策模型，包括：项目投资决策、风险性决策、不确定性决策、多目标决策分析。

2)决策树模型。

3)数学规划模型，包括：线性规划模型和非线性规划模型。

Excel通过其规划求解功能将最优化算法的能力加入到对工作表数据进行操纵的能力之中，使得Excel特别适合建立线性规划、非线性规划等各种优化模型。

运用Excel规划求解的基本原理如下图所示：4)财务报表模型。

5)管理经济学模型。

如：工资成本核算模型、个人所得税计算模型、平均税率计算模型、员工工资管理模型等。

6)各类计划模型，比如生产计划。

7)计量经济模型。

2. 掌握EXCEL提供的函数的使用方法（1）完成实验教材“6.3.3使用EXCEL的投资决策函数”节的适用于进行现金流分析的Excel 函数示例实验：NPV、XNPV、IRR、XIRR 和 MIRR。

NPV：语法：NPV(rate,value1,value2, ...)a．单击【公式】|【函数】命令，在弹出的”插入函数”对话框（图2-1）中选择”财务”类别中的NPV函数，再单击【确定】后，弹出如图2-2所示对话框。

图2-1b．按图2-2所示输入各参数，可以看到即时计算的结果，再从公式编辑栏可以看出其计算公式，即该项目净现值为：NPV(10%,-10000,3000,4200,6800)= ￥1,188.44。

如图2-3所示。

图2-2图2-3c．采用公式编辑器或直接为对应表格单元输入函数的方法来计算。

如图2-4所示。

图2-4结果分析：利用NPV计算可知，该项目净现值为1188.44元。

XNP：语法：XNPV(rate,values,dates)a.在工作簿中构建如表2-5的计算净现值模型。

图2-5b.可以看到结果显示为（如图2-6）：图2-6结果分析：该项目的定期现金流的净现值为2086.65元。

ＩＲＲ（内含报酬收益率）：语法：IRR(values, guess)设某项目计划投资70000元，并预期随后5年的净收益为：12000、15000、18000、21000、26000(元)。

请分别计算该项目在2、4、5年后的内含报酬收益率。

图2-7b. 结果显示如图2-8：图2-8结果分析：从结果来看，随着投资时间的延长，内部收益率提高。

●XIRR()（不定期现金流内含报酬率）：语法：XIRR(values,dates,guess)a.在工作簿中构建如表2-9的计算内含报酬率模型。

图2-9b.结果显示如图2-10所示：图2-10结果分析：该项目的内含报酬率为0.373。

●MIRR（）（修正内含报酬率函数）：语法：MIRR(values,finance_rate,reinvest_rate)a.在工作簿中构建如表2-11的计算内含报酬率模型。

b.结果显示如图2-12所示。

图2-12结果分析：再投资收益率影响修正内含报酬率。

从本次结果来看，再投资收益率越高，修正内含报酬率越高。

3. 根据要求构建投资指标决策分析模型，并用以进行决策分析（1）完成实验教程“6.3.4构建投资指标决策分析模型”节建模试验。

实验结果及分析如图3-1所示：图3-1（2）对模型进行方案试错与优化。

使用What-if分析进行方案试错与优化。

从表3-2中可以看出，无论资本成本率和在投资收益年利率如何变化，方案Ａ的其他指标始终优于方案Ｂ和方案Ｃ。

但是，当其他条件不变，初始投资额减少相同的比例时，方案Ａ的各项指标都优于方案Ｂ和Ｃ。

进一步试错，发现当初始投资额增加7.9%时，A方案净现金值变成了负数，方案不可取。

如图3-3且当初始投资额增加17%时，A的内含报酬收益率和修正内含报酬率都小于方案B。

如图3-4所示。

图3-2图3-3图3-4因而，方案A的优化方向是在其他条件不变的情况下降低初始投资额。

如果有可能的话，尽可能的提高投资再回收年利率和降低资本成本率，是方案更优。

（3）通过模型对投资方案进行灵敏度分析。

1）分析指标：净现金值、内含报酬收益率、修正内含报酬率。

2）不确定因素：资本成本率、在投资收益的年利率、初期投资成本。

3）变化幅度：三个因素变化幅度分别取：-30%，-20%，-10%，10%，20%，30%。

当变动因素变动时，净现金值、内含报酬率、修正内含报酬率变动情况如图3-5所示。

图3-5结合图3-5进行灵敏度分析：（4）总结最佳方案及其灵敏度。

实际环境中，投资额过剩的情况不常出现，因而最佳方案是A方案。

A方案对初始投资额的变动最为敏感，其他条件不变，过度投资会带来很大损失。

4.第6章练习题(1)练习题的投资指标决策分析模型如实验结果如图3-6所示：图3-6结果分析：净现金值均为正值且大小依次是：D>A>B>C。

但要注意的是，D在0期投入的资金是A 的两倍。

内含报酬收益率大小依次是：A>D>B>C。

修正内含报酬率的大小依次是D>A>B>C，其中，D方案的修正内含报酬率仅是略大于A。

如果考虑净现金流量和修正内含报酬率来看，应该选择D方案。

但是如果考虑到投资成本与内含报酬收益率时，应该选择A方案。

所以，如果手上有充足的资金进行投资，可以选择D方案。

如果手上资金不足，可以选择A方案。

但总体来说，相比D方案，A方案投资成本小、内含报酬收益率高，因而推荐A方案。

(2)对模型进行方案试错与优化。

如图3-7所示：1）当初始投资额增加10%时，D方案的3个指标相比其余3个方案都是最好的，但是各个指标并不是最好的，例如净现值相比于初始投资额不增加时，明显下降。

进一步试错，发现当初始投资额投入增加(8%-25%)时，D方案最优。

（图3-8表示初始投资增加8%时的情况）。

所以D方案适用于投资过剩的情况。

2）当资本成本率变化时，D方案是最优方案。

3）当再投资收益年利率增加10%以上时，A的修正内含报酬率大于D方案，此时，A方案最优。

图3-7图3-84）从净现金流量入手对A方案进一步试错和优化。

以下优化是指其他条件不改变的情况下，改变方案中的某个条件进行优化：经计算，发现只要各期的净现金流量增加50及以上，即可使A方案的内含报酬收益率和修正内含报酬率均大于D方案。

A方案的变动范围很小。

只要保证每期的净现金流量增加50以上，即可使方案最优。

5）从净现金流量入手对A方案进一步试错和优化。

施行D方案时，可以尽量使净现金流量增加800元左右，达到最优。

(3)通过模型对投资方案进行灵敏度分析。

1)分析指标：净现金值、内含报酬收益率、修正内含报酬率。

2)不确定因素：资本成本率、在投资收益的年利率、初期投资成本。

3)变化幅度：三个因素变化幅度分别取：-30%，-20%，-10%，10%，20%，30%。

4)当变动因素变动时，净现金值、内含报酬率、修正内含报酬率变动情况如图3-9所示。

图3-95）灵敏度分析：(4)总结最佳方案及其灵敏度。

总体而言，A方案对于条件改变的灵敏度大于D方案，即A方案比D方案更易受环境改变影响，风险更大些。

所以，如果投资者有足够的资金，D方案是最佳选择，在投资运营的过程中，努力达到优化模型即使净现金流量增加800元左右，D方案即为最佳方案。