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

1、决策支持系统实验报告For pers onal use only in study and research; not for commercial use实验名称：电子表格决策分析实验姓名：学号：专业：信息管理与信息系统班级：指导教师：实验成绩：批阅教师签字:实验二 电子表格决策分析实验（ 3 个学时）【实验目的】1. 了解决策支持系统模型的基础知识。2. 掌握EXCEL提供的函数的使用方法。3. 掌握在EXCEL中进行模型开发与决策分析的方法。实验内容】1. 理解模型的概念和类别。2. 练习使用 EXCEL的投资决策函数： NPV XNPV IRR、XIRR和MIRR。3. 根据要求构建投

2、资指标决策分析模型，并用以进行决策分析。实验步骤】1. 理解模型的概念和类别步骤：（1）查阅EXCEL帮助， 对照教材分析 EXCEL寸模型实现的支持方式（2）总结EXCEL提供了哪些类型的 DSS模型2. 掌握EXCEL提供的函数的使用方法（1 ）完成实验教材“ 函数示例实验：NPV、XNPV、IRR、XIRR 和 MIRR。（ 2）对数据分析结果进行讨论。3. 根据要求构建投资指标决策分析模型，并用以进行决策分析（ 1 ）完成实验教程“（ 2）对模型进行方案试错与优化（ 3）通过模型对投资方案进行灵敏度分析（ 4）总结最佳方案及其灵敏度（5）根据以上步骤完成实验教程第6 章练习题4. 写实

3、验报告，字数 >800 字【实验过程及结果分析】1. 理解模型的概念和类别步骤：(1) 查阅EXCEL帮助， 对照教材分析 EXCEL寸模型实现的支持方式。Excel 对模型实现的支持方式体现在建立模型与模型分析、 数据访问与管理以及用户界 面与可视建模三个方面。1) 建立模型与模型分析。 Excel 具有灵活的建模能力和对所建模型进行 What-if 分析的功能，利用公式、大量的内建财务统计函数、以及VBA语言建立案例数值模型，并利用 模拟运算表、 方案管理器等功能模型对 “如果决策变量如此变化的话， 目标变量将如何 变化“进行分析。 Excel 利用单变量求解功能求解“为了使目标变量

4、达到某个特定的目 标值， 决策变量应该如何变化 "的问题。 此外， Excel 具有大量内建函数， 操作者可以利 用 Excel 提供的各种函数公式， 通过确定参数的数值， 按特定的顺序或结构自动执行复 杂计算，大大简化了计算模型建立的过程，并有助于提高模型的准确性，特别是Excel的财务函数、统计函数等对于财务分析相关模型的建立有很大帮助。2) 数据访问与管理。 Excel 具有十分强大的数据分析功能，并且与各种数据库具有良好的 接口。 Excel 不仅可以创建 OLAP 多维数据集的数据源，然后使用该数据源进行分析， 也可以借助于 ODBC 工具，直接访问各种由 dBase 、

5、FoxPro 、 Access 或者 SQLServer 等生成的外部数据库。 因而 Excel 可以通过简单的操作从具有不同结构的数据库中获取 所需要的数据， 利用数据分析功能直接进行一些复杂的计算、 统计工作等， 给模型的实 现提供了强大的数据支持。3) 用户界面与可视建模。 Excel 不仅具有良好的图形显示能力，而且允许用户利用 Excel本身的菜单、 工具条、 对话框等手段方便地进行操作和建立个性化系统界面。 这些功能 与其他功能相结合， 决策者可以在决策模型的基础上经过简单操作制作高质量的动态图 形，将抽象的决策模型与直观生动的图形完美结合，一定程度上实现可视建模与分析， 从而使模

6、型以最有效的方法帮助决策者进行各种必要的决策分析。例如：利用Excel的数据透视表功能，可以十分方便地从大量数据中提取有用的综合统计信息，(2) 总结EXCEL提供了哪些类型的 DSS模型excel 其中比较常见的模型有以下几类：1) 预测与决策模型，包括：项目投资决策、风险性决策、不确定性决策、多目标决策 分析。2) 决策树模型。3) 数学规划模型，包括：线性规划模型和非线性规划模型。 Excel 通过其规划求解功能将最优化算法的能力加入到对工作表数据进行操纵的能力之中，使得 Excel 特别适合建立线性规划、 非线性规划等各种优化模型。 运用 Excel 规划求解的基本原理如下图所示:经济

7、理论数学模型数据输入数据输出决策分析4）财务报表模型。5）管理经济学模型。如：工资成本核算模型、个人所得税计算模型、平均税率计算模型、员工工资管理模型等。6）各类计划模型，比如生产计划。7）计量经济模型。2. 掌握EXCEI提供的函数的使用方法（1 ）完成实验教材“函数示例实验： NPV XNPV IRR、XIRR和MIRR。NPV 语法： NPV（rate,value1,value2,）a.单击【公式】|【函数】命令，在弹出的”插入函数”对话框（图2-1 ）中选择”财务”类别中的NPV函数，再单击【确定】后，弹出如图2-2所示对话框。图2-1b.按图2-2所示输入各参数，可以看到即时计算的结

8、果，再从公式编辑栏可以看出其计算公式，即该项目净现值为：NPV(10%,-10000,3000,4200,6800)=¥ 1,188.44。如图 2-3所示。淫我参数主岁图2-2图2-3C.采用公式编辑器或直接为对应表格单元输入函数的方法来计算。如图2-4所示。A6矗1 =NPV(Al/2/A3,A4/A5)ADEFG110%2-1000033000斗4200568006¥14&8.44)图2-4结果分析：利用NPV计算可知，该项目净现值为1188.44元。XNP 语法：XNPV(rate,values,dates)a. 在工作簿中构建如表 2-5的计算净现值模型。

9、NFVX w 丿.=XNPV (0.0915,61:85)ABC¥-10,000.00;2008/1/1 ：1¥2f75000E2003/3/1；VV50.00：2008/10/30：1¥3f250.00;2009/2/15；¥2750.002009/4/li公式说明UxNPV （O.nAl;A5,Bl：B5>观金流的贴现率为9%图2-5b.可以看到结果显示为（如图2-6）:A7-任=XNPV(0.09f A1Bl: B5)AB匚¥-10,000+002000/1/1¥2,750.002008/3/1R¥4,250.0

10、02008/10/30¥3,230.002009/2/15¥2,750,002009/4/1说明2086.647602现金流的贴现率为9%图2-6结果分析：该项目的定期现金流的净现值为2086.65元。irr（内含报酬收益率）：语法：IRR（values, guess）设某项目计划投资 70000元，并预期随后 5年的净收益为：12000、15000、18000、21000、26000（元）。请分别计算该项目在2、4、5年后的内含报酬收益率。a.在工作簿中构建如表 2-7的计算内含报酬收益率模型。-70,000某项业务的初期成本费用12,000第一年的净收入15,000第二

11、年的净收入18,000第三年的净收入21,000第四年的净收入26,000第五年的净收入公式说明（结果）=IRR(A1:A5)投资四年后的内部收益率=IRR(A1:A6)五年后的内部收益率=IRR(A1:A3,-10%)计算2年后的内含报酬收益率时必须包含guess参数，否则函数 IRR返回错误值#NUM!。图2-7b.结果显示如图2-8：012AJADCDEF¥-70,000.00某项吐务的初那或本苗用Jfl2(0OT.Q0第一年的収入¥15,000.00第二年的;箒收入¥18.000.00第三年弼争收入mouo.oo第匹年的收入¥26, 000. 0

12、0篥五年的挣收入公式说明F2%殳帘匹年后的内部杭益率5年后的內部收盂率r算2年后的内含抿酬收益舸寸贴换包含狄粘占曹数，'吾则至1数IPJ匣叵错誤值#NP>!图2-8结果分析：从结果来看，随着投资时间的延长，内部收益率提高。XIRR()(不定期现金流内含报酬率)：语法：XIRR(values,dates,guess)a.在工作簿中构建如表 2-9的计算内含报酬率模型。BFY-寓Y A=X1 RR(Ai：A5Qi;B5d)A,1-10,0002008/1/122.7502008/3/1314.2502008/10/304£2502009/2/1552,7502009M/16

13、怜式说明7A|=XIRR(A1:A5,B1：B5,O4) 1对雷甕t XIRR计算结果的估计信为0.1 *图2-9b.结果显示如图2-10所示:CIOA |ABT40,0002008/1/127502008/3/1勺2502008/10/303,2502009/7/15f27502WM/4/1松式说明I0373362535|对因数XIRR计算结果的估计值为0仏图 2-10结果分析：该项目的内含报酬率为0.373。MIRR ()(修正内含报酬率函数)：语法：MIRR(values,finance_rate,reinvest_rate)a.在工作簿中构建如表 2-11的计算内含报酬率模型。数据说明

14、-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所示。12值 原 产 资3.IIJO93456700f4689式 公1XTI12T31| 图 2-12结果分析：再投资收益率影

15、响修正内含报酬率。从本次结果来看，再投资收益率越高，修正内含报酬率越高。3.根据要求构建投资指标决策分析模型，并用以进行决策分析（1 ）完成实验教程“实验结果及分析如图 3-1所示：投资指标决策分析模型资金成本率再投资收益的年利目10%16%期间A方案净现金浇量B方案詡现金流量(:方案净现金涼量0-25000£000-140001133001100580021WQ042005100351005200净现金值¥lf788.13¥275.25¥-550.51内含报酬收空率16%12%7%修正内含报酎率16%13%11%分析结论匚方案的淨现俏为员值，不可取方案

16、A在净现值、內含 报酬收益率、修正内含报酬率三个指标上都高于亘余两 个方案。所以应该采纳方案A °I图3-1(2) 对模型进行方案试错与优化。使用What-if分析进行方案试错与优化。从表3-2中可以看出，无论资本成本率和在投资收益年利率如何变化，方案A的其他指标始终优于方案E和方案C。但是，当其他条件不变，初始投资额减少相同的比例时，方案A的各项指标都优于方案E和C。进一步试错，发现当初始投资额增加 7.9%时，A方案净现金值变成了负数，方案不可取。如图3-3且当初始投资额增加17%时，A的内含报酬收益率和修正内含报酬率都小于方案B。如图3-4所示。试诺法与冥齣度甘诺变朗比鱼-30

17、%-20%-100%10%20%30%变幼因素扌总标方案A|8605&334406117&8-2757-5030當现金值日2iS71730100327545211791907C32681945722-551-1B23-3096-4369宁F甘日绘A|16.30%33.76%23.75%15.52%8.62%2,71%2.63%雾内含授酬收益率B30.54%23.09%15.9511.76%731%3.42%0.00°4C30.34%21.07%13.627.46%工拠21 啊LJ占船勺A38.21%29.28%21.8915.63%1.4%修正内含找酬宝H2,93%2

18、1,0%12.7T%9.18%fi.nfi%1.76%C24.52%1910%14.5110.56%740%蚀1,30%A2M&2M32158J78SL0937670"THT"TTT"T7TTTT35179平学c1L2-120-340-511-751-942-1123再按资A14.52%14.90%15.27%1S.63%16,00%16.37®b16.73%欣益年 修正内含抿酬率日11,64%11.99%12.35%12.70%13,06%13,1A%13.76%利率c8.P2%9.40%9.98%10.56%11J4%11.72%12.2%图

19、3-2投資指标决第分桁模型资金成本再投资收帝的年利率10%16%期间A方案净现金渍量B方案净现金瀆量匚方案净现金渍量0-26975-8632-151061133001100580021800042005100351005200净现金值1 2¥-299¥-1,556内含报酬9.98%8,19%3.32%修正内含11.32%9.88%7.79%图3-3按资指标块茉分祈模型资全咸本苒投豈收益的年刑率1U%lt>%期间A方聚净规金凉1R方峯净!c方睪淨观金溢里0-29250-9360-163&011330011UO5800216000斗2005100351005200

20、净现金值Y-Q61¥r714内含械刚0,87修正内刹6.90%6.964.92%图3-4因而，方案A的优化方向是在其他条件不变的情况下降低初始投资额。如果有可能的话,尽可能的提高投资再回收年利率和降低资本成本率，是方案更优。（3）通过模型对投资方案进行灵敏度分析。1）分析指标：净现金值、内含报酬收益率、修正内含报酬率。2）不确定因素：资本成本率、在投资收益的年利率、初期投资成本。3） 变化幅度：三个因素变化幅度分别取：-30%, -20%, -10%, 10% 20% 30%当变动因素变动时，净现金值、内含报酬率、修正内含报酬率变动情况如图3-5所示。灵帥度刃忻变动比車30%-20%

21、-10%0%102D%30%妄就医素抱标方葉A飾幷&33410611708 |说5-2757-5030冑现金值B2巧5211791907C326819157225511B2330%-4369初期投A16.3033.78%J3.75%15.52%g.62%2.74%2.6现内含报酬收益率B30.54%22.09%16.95%11.7%7.21142%0.00% |£20.340421.07%12.62%7.物號2.孙2.14%-5.95%A3&.21c420.28%21.89%15.63%10.25%5.5丘関1.42%修正内含报酬率e26.9

22、39121-40%1673%12.70%9.18%3 26%c24.52%19.10%14.51%10,56%Z1&%伽130%A29462543215S178814341093767e&0361844327S116-35-179cL12-120-340511-751-942-1123冉投資A14.52%14.90%Lh.J/%：L.h.-iuolb.OD'Wi16.37%tb 72%|收益年修正内含傘.硼率B11.64%11.99%1235%12.70%13.0b%13.14%口亍b%禾率C89.40%9 98% 14%11.72%12.29%图3-5结合图3-5进行

23、灵敏度分析:变动因素灵敏度分析初始投资额净现值、内含报酬率和修正内含报酬率对初始投资额的变化很敏感。当 其他条件不变，投资额增加17%寸，方案B优于方案A,但此时所有的方案的净现值都为负数，所有方案都不可取。资本成本率灵敏度为0。在其他条件不变的情况下，资本成本率的变动不会影响内 含报酬率和修正内含报酬率，且在变动范围中，无论资本成本率如何变 动，A仍是3个方案中的最优方案。再投资收益年利率灵敏度为0，在其他条件不变的情况下，再投资收益年利率的变动不会 影响净现值和内含报酬率，且在变动范围中，投资收益年利率如何变动，A仍是3个方案中的最优方案。(4) 总结最佳方案及其灵敏度。实际环境中，投资额

24、过剩的情况不常出现，因而最佳方案是A方案。A方案对初始投资额的变动最为敏感，其他条件不变，过度投资会带来很大损失。4.第6章练习题(1) 练习题的投资指标决策分析模型如实验结果如图3-6所示:1投資指标决集分析樓型2頁投资收益的年利1310%15%4則间A万案.争观金瀝昼B方案淨规金浣童匚方黑爭现绘浇量D方案;爭现金潘量50-15000-J3W0-2CKW3000061BTO0J5Q0760090001279006200880013S00831BOO87009800J11009净规金值¥1,569.56Wf9M,5310内含捏酬收益車19.69%16.81%14.65%18.6?%1

25、1侈正内首氓酬滓17*59%16.32%14.77%17.63%图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方案投资成本小、内含报酬收

26、益率高，因而推荐A方案。(2) 对模型进行方案试错与优化。如图3-7所示:1） 当初始投资额增加10%寸，D方案的3个指标相比其余3个方案都是最好的，但是各个 指标并不是最好的，例如净现值相比于初始投资额不增加时，明显下降。进一步试错，发现当初始投资额投入增加 （8%-25%）时，D方案最优。（图3-8表示初始投资增加 8%时 的情况）。所以D方案适用于投资过剩的情况。2） 当资本成本率变化时，D方案是最优方案。3） 当再投资收益年利率增加 10%上时，A的修正内含报酬率大于 D方案，此时，A方案 最优。变劲因事指标浄现金信叨期投本率再投资恤年制率试诸与灵蝕度井析 注动比率-3IW. 方秦A6

27、175B5220C7024DA内含报M D收益率cDA楼正内含B 报酬率CDA 淨现佥值gDA修正内含B 扌艮酬率CD481240385206 10379-10%0%10%20%30%訓羽 208572161320062B571675493无8948713388 1570-249 J-206?-3385托斗4密二 H-S20-325749血尹却27 哒1匕甲 1王0閃右1曾$2.617o30.02%2771%22.%16.91% 11,66%A57%3.61%38 01%28.58%20.6%14 65%9.32%4 73%0.7440.37%31.M%24.56%侃血刃骗虫 2D 股&

28、;菊32.44%26.67'%21.E0%17占9nu13.91no10 65%二可1%31 Of)%?5JC%?0.48%1&3?%12.58%9 46%6.58%2)26%25.63%18.67j14,77%11,18%S 00%516%22.48%2671% 22.阳始好.6?% 13.95 開 10 币 9%29282606223027812358-J17D._皿15.57%16.21%15.10%15.51013.25%1376%231919541570T&37 wy 16.92 1 尬叫 15.91% 16.33%14.26 14.77%1827158113

29、45139311258691203853519£2亚"TJSH 818.93%I 堆白9%16&瞩 17.22% 17*田16.77%17.13%17.5315.26%18.01%15.79%18.45%16,29%18.8719.60%|18.26%图3-7控资指标决策分祈模型资金成本率再投资收益的年利率10%15%期间已方案净现金浇量B方秦净现金K匸万案净现D方案净现金流量0-16200-14(140-21603-324001870035007800900027900&2008800135003380097W980321400净现金值号中17291152

30、f743内台报酬收益14.31%12.79%10.32%14.52%修正内含报酬丄叫£丄13.37%11.8614.65%图3-84）从净现金流量入手对 A方案进一步试错和优化。以下优化是指其他条件不改变的情况下，改变方案中的某个条件进行优化：经计算，发现只要各期的净现金流量增加50及以上，即可使 A方案的内含报酬收益率和修正内含报酬率均大于 D方案。A方案的变动范围很小。只要保证每期的净现金流量增加50以上，即可使方案最优。5）从净现金流量入手对 A方案进一步试错和优化。期间A方案净现金流量优化方案0-30000当初始投资额29400兀时，D方案的内含报酬收益率和修正内含报酬率均大

31、于 D方案。变动范围是 600兀19000当第2期净现金流量-9700时,D方案方案的内含报酬收益率和修正内含报酬率均大于D方案。变动范围是 700213500当第2期净现金流量-14350时，D方案方案的内含报酬收益率和修正内含报酬率均大于D方案。变动范围是 850321400当第3期净现金流量-22400时，D方案方案的内含报酬收益率 和修正内含报酬率均大于 D方案。因而，总体说来，D方案的某一期净现金流量增加800元左右时，即可使本方案最优。所以，施行D方案时，可以尽量使净现金流量增加800元左右，达到最优。（3）通过模型对投资方案进行灵敏度分析。1）分析指标：净现金值、内含报酬收益率、

32、修正内含报酬率。2）不确定因素：资本成本率、在投资收益的年利率、初期投资成本。3）变化幅度：三个因素变化幅度分别取：-30%, -20%, -10%, 10% 20% 30%4）当变动因素变动时，净现金值、内含报酬率、修正内含报酬率变动情况如图3-9所示。试诸与灵敏度有冲T-30%-20%-to%0%10%20%30%方秦A61754812344820857212006浄现金信B522040382S571675493*6&94871初期投C702520633891S70249-20673BB5D127976524925如心-53U-3257内含报A37.27%22.6019.69%1X

33、06%?衍2,61%H22.60%16.81%11 86%7.57%3.81%资额C38.01%2&.5S%20.96%14.65%932%4.73%o.尹mD40.37%24.56%18.63%12.57%9.20%5*36%AZb.b7°/o2L8O%1759%103%774%修正内合H31.00%2S.3O%2X8%1632%12.66%9.46%56%报酬率C D2925 J3%18 87%14 77%1L1S%S 00%5.16%32.48%26.71%2163%17,63%13.95%10.69%7.76|A2928"5645"曲射2095la

34、iF1S8L1345盘本成净现金値BZ28O1970513931125帥9C27312358195415701203B53519D6385E晦強北359K-FSZ再投资修正內含A15.57%16.2*1%15.9217.5918.26%IB.99 %19.6C%|15.10%15.51015.51%16.32%16.72%17.13%17.53%报酬率C D13.25%1376%14.2614,77%15,2S%15.79%16,29%1639%16.31%17.22%17.63%18.04%18.45%18.87图3-95)灵敏度分析:变动因素灵敏度分析初始投资额净现值、内含报酬率和修正内含报酬率对初始投资额的变化很敏感。随着初始投资额的变动，这三个指标的变动幅度很大。当投资额增加10%时，D的修正内含报酬率大于 A方案。资本成本率灵敏度为0。在其他条件不变的情况下，资本成本率的变动不会影