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

决策支持系统实验报告实验名称：电子表格决策分析实验姓名：学号：专业：信息管理与信息系统班级：指导教师：实验成绩：批阅教师签字：实验二 电子表格决策分析实验（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。l 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元。l XNP：语法：XNPV(rate,values,dates)a. 在工作簿中构建如表2-5的计算净现值模型。 图2-5b. 可以看到结果显示为（如图2-6）： 图2-6结果分析：该项目的定期现金流的净现值为2086.65元。l （内含报酬收益率）：语法：IRR(values, guess)设某项目计划投资70000元，并预期随后5年的净收益为：12000、15000、18000、21000、26000(元)。请分别计算该项目在2、4、5年后的内含报酬收益率。在工作簿中构建如表2-7的计算内含报酬收益率模型。-70,000某项业务的初期成本费用12,000第一年的净收入15,000第二年的净收入18,000第三年的净收入21,000第四年的净收入26,000第五年的净收入公式说明（结果）=IRR(A1:A5)投资四年后的内部收益率 =IRR(A1:A6)五年后的内部收益率=IRR(A1:A3,-10%)计算2年后的内含报酬收益率时必须包含guess参数，否则函数 IRR 返回错误值 #NUM!。图2-7b. 结果显示如图2-8：图2-8结果分析：从结果来看，随着投资时间的延长，内部收益率提高。l XIRR()（不定期现金流内含报酬率）：语法：XIRR(values,dates,guess)a. 在工作簿中构建如表2-9的计算内含报酬率模型。图2-9b. 结果显示如图2-10所示：图2-10结果分析：该项目的内含报酬率为0.373。l MIRR（）（修正内含报酬率函数）：语法：MIRR(values,finance_rate,reinvest_rate)a. 在工作簿中构建如表2-11的计算内含报酬率模型。数据说明-120,000资产原值39,000第一年的收益30,000第二年的收益21,000第三年的收益37,000第四年的收益46,000第五年的收益10.00%120,000 贷款额的年利率12.00%再投资收益的年利率公式说明（结果）=MIRR(A2:A7, A8, A9)五年后投资的修正收益率=MIRR(A2:A5, A8, A9)三年后的修正收益率 =MIRR(A2:A7, A8, 14%)五年的修正收益率（基于 14% 的再投资收益率）表2-11b. 结果显示如图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进行灵敏度分析：变动因素灵敏度分析初始投资额净现值、内含报酬率和修正内含报酬率对初始投资额的变化很敏感。当其他条件不变，投资额增加17%时，方案B优于方案A，但此时所有的方案的净现值都为负数，所有方案都不可取。资本成本率灵敏度为0。在其他条件不变的情况下，资本成本率的变动不会影响内含报酬率和修正内含报酬率，且在变动范围中，无论资本成本率如何变动，A仍是3个方案中的最优方案。再投资收益年利率灵敏度为0，在其他条件不变的情况下，再投资收益年利率的变动不会影响净现值和内含报酬率，且在变动范围中，投资收益年利率如何变动，A仍是3个方案中的最优方案。（4） 总结最佳方案及其灵敏度。实际环境中，投资额过剩的情况不常出现，因而最佳方案是A方案。A方案对初始投资额的变动最为敏感，其他条件不变，过度投资会带来很大损失。4.第6章练习题(1) 练习题的投资指标决策分析模型如实验结果如图3-6所示：图3-6结果分析：净现金值均为正值且大小依次是：DABC。但要注意的是，D在0期投入的资金是A的两倍。内含报酬收益率大小依次是：ADBC。修正内含报酬率的大小依次是DABC，其中，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方案进一步试错和优化。期间A方案净现金流量优化方案0-30000当初始投资额=9700时, D方案方案的内含报酬收益率和修正内含报酬率均大于D方案。变动范围是700213500当第2期净现金流量=14350时, D方案方案的内含报酬收益率和修正内含报酬率均大于D方案。变动范围是850321400当第3期净现金流量=22400时, D方案方案的内含报酬收益率和修正内含报酬率均大于D方案。因而，总体说来，D方案的某一期净现金流量增加800元左右时，即可使本方案最优。所以，施行D方案时，可以尽量使净现金流量增加800元左右，达到最优。(3) 通过模型对投资方案进行灵敏度分析。1) 分析指标：净现金值、内含报酬收益率、修正内含报酬率。2) 不确定因素：资本成本率、在投资收益的年利率、初期投资成本。3) 变化幅度：三个因素变化幅度分别取：-30%，-20%，-10%，10%，20%，30%。4) 当变动因素变动时，净现金值、内含报酬率、修正内含报酬率变动情况如图3-9所示。图3-95） 灵敏度分析：变动因素灵敏度分析初始投资额净现值、内含报酬率和修正内含报酬率对初始投资额的变化很敏感。随着初始投资额的变动，这三个指标的变动幅度很大。当投资额增加10%时，D的修正内含报酬率大于A方案。资本成本率灵敏度为0。在其他条件不变的情况下，资本成本率的变动不会影响内含报酬率和修正内含报酬率，且在变动范围中，无论资本成本率如何变动，D仍是3个方案中的最优方案。再投资收益年利率修正内含报酬率对再投资收益年利率变动敏感。且当再投资收益年利率增加10%以上时，A方案的修正内含报酬率大于D方案的。(4) 总结最佳方案及其灵敏度。总体而言，A方案对于条件改变的灵敏度大于D方案，即A方案比D方案更易受环境改变影响，风险更大些。所以，如果投资者有足够的资金，D方案是最佳选择，在投资运营的过程中，努力达到优化模型即使净现金流量增加800元左右，D方案即为最佳方案。如果投资者资金较少，A方案是最佳选择，投资小，内含报酬率高，努力使净现金流量增加50元到100元，方案达到最优。【实验总结】通过本次实验，了解了Excel对模型实现的支持方式和Excel提供的Dss模型，掌握了Excel提供的财务函数NPV、XNPV、IRR、XIRR 和 MIRR的应用方法，能够建立投资指标决策分析模型，并进行试错、优化和灵敏度分析。在实验的过程中体会到，决策者才是决策的核心。Excel能够帮助用户便捷的进行数据操作，但是无法帮忙进行决策。本次实验中的练习题，选择A方案还是D方案就是一个决策。建立的模型虽然给出了结果，但是最终决策还是要根据实际情况比如投资人的经济条件、目标等情况并结合模型给出的结果做出决策。不可否认的是，模型在帮助决策者快速明确目标、找到关键问题方面功不可没。实验过程中，受限于自己对Excel的使用能力，没有得到excel的一些“支持”，例如不会作复杂的图。所以，要注重对DSS使用方法的学习。还实验中的灵敏度分析和试错都假定其他条件不变，分析一个条件改变对结果的影响。但是实际情况中往往多个条件都在变动，决策者要注意模型与实际的不同，不能一味依赖模型和分析结果。