敏感性分析.xls

《敏感性分析.xls》由会员分享，可在线阅读，更多相关《敏感性分析.xls（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 敏敏 感感 性性 性性 分分 析析 例例：有有一一个个生生产产城城市市用用小小型型电电动动汽汽车车的的投投资资方方案案，用用于于确确定定性性分分析析的的现现金金流流量量表表如如下下，所所采采用用的的数数据据是是根根据据对对未未来来最最可可能能出出现现的的情情况况估估算算的的。由由于于对对未未来来影影响响经经济济环环境境的的某某些些因因素素把把握握不不大大，投投资资额额、经经营营成成本本和和销销售售收收入入均均有有可可能能在在20%的的范范围围内内变变化化。设设基基准准收收益益率率为为11%，不不考考虑虑所所得得得得税税，试试就就以以上上三三个个不不确确定定性性因因素素做做敏敏感感性性分分析析

2、。年年 份份0 01 1 投投 资资0 0 销销 售售 收收 入入 经经 营营 成成 本本 残残 值值 净净现现金金流流量量0 00 0 解解：设设投投资资为为K，年年销销售售收收入入为为B，年年经经营营成成本本为为C，期期末末资资产产的的残残值值L为为L L。投投资资的的变变化化率率为为x x，经经营营成成本本的的变变化化率率为为y y，年年销销售售收收入入的的变变化化率率为为z,z,则则有有 NPV=-15000+NPV(10%,0,4600,4600,6600)IRR=IRR(-15000,0,4600,4600,6600)具具体体求求解解如如下下：K=1500015000 x=C=15

3、20015200y=B=1980019800z=残残 值值20002000 年年 份份0 01 1 投投 资资1500015000 销销 售售 收收 入入 经经 营营 成成 本本 残残 值值 净净现现金金流流量量-15000-150000 0 累累 计计净净现现值值-15000.00-15000.00 -15000.00-15000.00 NPV=11396.4511396.45 I R R=22%22%下下面面就就三三个个不不确确定定性性因因素素做做敏敏感感性性分分析析 单单因因 素素敏敏感感性性分分析析xNPVIRRy0%0%11396.4511396.4522.03%22.03%0%0%

4、-20%-20%14396.4514396.4527.73%27.73%-20%-20%-15%-15%13646.4513646.4526.12%26.12%-15%-15%-10%-10%12896.4512896.4524.64%24.64%-10%-10%-5%-5%12146.4512146.4523.28%23.28%-5%-5%0%0%11396.4511396.4522.03%22.03%0%0%5%5%10646.4510646.4520.86%20.86%5%5%10%10%9896.459896.4519.77%19.77%10%10%15%15%9146.459146.

5、4518.76%18.76%15%15%20%20%8396.458396.4517.81%17.81%20%20%单单因因素素敏敏感感性性分分析析图图30%30%20%20%10%10%0%0%-10%-10%-20%-20%-30%-30%-20000-20000 -10000-10000 0 0 1000010000 2000020000 3000030000 4000040000 x,y,zx,y,zNPVNPV投投资资变变化化的的影影响响经经营营成成本本变变化化的的影影响响销销售售收收入入的的变变化化的的影影响响 两两因因素素 敏敏感感性性分分析析11396.4511396.45-2

6、0%-20%-15%-15%-10%-10%-20%-20%31377.8031377.8027132.4627132.4622887.1222887.12-15%-15%30627.8030627.8026382.4626382.4622137.1222137.12-10%-10%29877.8029877.8025632.4625632.4621387.1221387.12-5%-5%29127.8029127.8024882.4624882.4620637.1220637.120%0%28377.8028377.8024132.4624132.4619887.1219887.125%5%

7、27627.8027627.8023382.4623382.4619137.1219137.1210%10%26877.8026877.8022632.4622632.4618387.1218387.1215%15%26127.8026127.8021882.4621882.4617637.1217637.1220%20%25377.8025377.8021132.4621132.4616887.1216887.1211396.4511396.45-20%-20%-15%-15%-10%-10%-20%-20%-7723.99-7723.99 -2193.88-2193.88 3336.233

8、336.23-15%-15%-8473.99-8473.99 -2943.88-2943.88 2586.232586.23-10%-10%-9223.99-9223.99 -3693.88-3693.88 1836.231836.23-5%-5%-9973.99-9973.99 -4443.88-4443.88 1086.231086.230%0%-10723.99-10723.99-5193.88-5193.88 336.23336.235%5%-11473.99-11473.99-5943.88-5943.88 -413.77-413.77 10%10%-12223.99-12223.9

9、9-6693.88-6693.88 -1163.77-1163.77 15%15%-12973.99-12973.99-7443.88-7443.88 -1913.77-1913.77 经经营营成成本本的的变变化化(y)投资的变化xNPV投资的变化x销销售售收收入入的的变变化化(z)NPV单单因因素素敏敏感感性性分分析析图图30%30%20%20%10%10%0%0%-10%-10%-20%-20%-30%-30%-20000-20000 -10000-10000 0 0 1000010000 2000020000 3000030000 4000040000 x,y,zx,y,zNPVNPV投

10、投资资变变化化的的影影响响经经营营成成本本变变化化的的影影响响销销售售收收入入的的变变化化的的影影响响20%20%-13723.99-13723.99-8193.88-8193.88 -2663.77-2663.77 11396.4511396.45-20%-20%-15%-15%-10%-10%-20%-20%6257.366257.362012.022012.02-2233.32-2233.32 -15%-15%11787.4711787.477542.137542.133296.793296.79-10%-10%17317.5817317.5813072.2413072.248826.9

11、08826.90-5%-5%22847.6922847.6918602.3518602.3514357.0114357.010%0%28377.8028377.8024132.4624132.4619887.1219887.125%5%33907.9133907.9129662.5729662.5725417.2425417.2410%10%39438.0239438.0235192.6835192.6830947.3530947.3515%15%44968.1344968.1340722.7940722.7936477.4636477.4620%20%50498.2450498.244625

12、2.9046252.9042007.5742007.57x=75.98%75.98%y=13.42%13.42%z=-10.30%-10.30%xyz75.98%75.98%13.42%13.42%y-20%-20%-71.49%-71.49%-12.63%-12.63%-20%-20%-15%-15%-34.63%-34.63%-6.12%-6.12%-15%-15%-10%-10%2.24%2.24%0.40%0.40%-10%-10%-5%-5%39.11%39.11%6.91%6.91%-5%-5%0%0%75.98%75.98%13.42%13.42%0%0%5%5%112.84%1

13、12.84%19.94%19.94%5%5%10%10%149.71%149.71%26.45%26.45%10%10%15%15%186.58%186.58%32.96%32.96%15%15%经经营营成成本本的的变变化化(y)销售收入的变化xNPV投资的变化x20%20%223.45%223.45%39.47%39.47%20%20%三三因因素素 敏敏感感性性分分析析13.42%13.42%-20%-20%-15%-15%-10%-10%-20%-20%-9.10%-9.10%-2.58%-2.58%3.93%3.93%-15%-15%-9.98%-9.98%-3.47%-3.47%3.0

14、5%3.05%-10%-10%-10.86%-10.86%-4.35%-4.35%2.16%2.16%-5%-5%-11.75%-11.75%-5.23%-5.23%1.28%1.28%0%0%-12.63%-12.63%-6.12%-6.12%0.40%0.40%5%5%-13.51%-13.51%-7.00%-7.00%-0.49%-0.49%10%10%-14.40%-14.40%-7.88%-7.88%-1.37%-1.37%15%15%-15.28%-15.28%-8.77%-8.77%-2.25%-2.25%20%20%-16.16%-16.16%-9.65%-9.65%-3.14

15、%-3.14%两两因因素素敏敏感感性性分分析析图图30%20%10%0%-10%-20%-30%-100.00%-50.00%0.00%50.00%100.00%150.00%200.00%250.00%z zx,yx,yx_zx_zy_zy_z两两因因素素敏敏感感性性分分析析图图30%20%10%0%-10%-20%-30%-50.00%0.00%50.00%100.00%150.00%200.00%250.00%y yx,zx,zx_yx_yz_yz_y投资的变化x销销售售收收入入的的变变化化(z)y=三三因因素素敏敏感感分分析析图图30%20%10%0%-10%-20%-30%-20.0

16、0%-10.00%0.00%10.00%20.00%30.00%40.00%50.00%x xy yz=-20%z=-20%z=-15%z=-15%z=-10%z=-10%z=-5%z=-5%z=0z=0z=5%z=5%z=10%z=10%z=15%z=15%z=20%z=20%三三因因素素敏敏感感分分析析图图30%20%10%0%-10%-20%-30%-20.00%-10.00%0.00%10.00%20.00%30.00%40.00%50.00%z zy yx=-20%x=-20%x=-15%x=-15%x=-10%x=-10%x=-5%x=-5%x=0 x=0 x=5%x=5%x=10

17、%x=10%x=15%x=15%x=20%x=20%三三因因素素敏敏感感分分析析图图30%20%10%0%-10%-20%-30%-20.00%-10.00%0.00%10.00%20.00%30.00%40.00%50.00%x xy yz=-20%z=-20%z=-15%z=-15%z=-10%z=-10%z=-5%z=-5%z=0z=0z=5%z=5%z=10%z=10%z=15%z=15%z=20%z=20%三三因因素素敏敏感感分分析析图图30%20%10%0%-10%-20%-30%-20.00%-10.00%0.00%10.00%20.00%30.00%40.00%50.00%z

18、zy yx=-20%x=-20%x=-15%x=-15%x=-10%x=-10%x=-5%x=-5%x=0 x=0 x=5%x=5%x=10%x=10%x=15%x=15%x=20%x=20%2 23 34 45 519800198001980019800198001980019800198001520015200152001520015200152001520015200460046004600460046004600460046000%0%i0=10%10%0%0%NPV=11396.4511396.450%0%IRR=22.03%22.03%2 23 34 45 5 敏敏 感感 性性 性

19、性 分分 析析 例例：有有一一个个生生产产城城市市用用小小型型电电动动汽汽车车的的投投资资方方案案，用用于于确确定定性性分分析析的的现现金金流流量量表表如如下下，所所采采用用的的数数据据是是根根据据对对未未来来最最可可能能出出现现的的情情况况估估算算的的。由由于于对对未未来来影影响响经经济济环环境境的的某某些些因因素素把把握握不不大大，投投资资额额、经经营营成成本本和和销销售售收收入入均均有有可可能能在在20%的的范范围围内内变变化化。设设基基准准收收益益率率为为11%，不不考考虑虑所所得得 解解：设设投投资资为为K，年年销销售售收收入入为为B，年年经经营营成成本本为为C，期期末末资资产产的的

20、残残值值L为为L L。投投资资的的变变化化率率为为x x，经经营营成成本本的的变变化化率率为为y y，年年销销售售收收入入的的变变化化率率为为z,z,则则有有 NPV=-15000+NPV(10%,0,4600,4600,6600)IRR=IRR(-15000,0,4600,4600,6600)1980019800198001980019800198001980019800152001520015200152001520015200152001520046004600460046004600460046004600-11198.35-11198.35 -7742.30-7742.30 -460

21、0.44-4600.44 -1744.20-1744.20 NPVIRRzNPV11396.4511396.4522.03%22.03%0%0%11396.4511396.4528377.8028377.8035.90%35.90%-20%-20%-10723.99-10723.99 24132.4624132.4632.72%32.72%-15%-15%-5193.88-5193.88 19887.1219887.1229.37%29.37%-10%-10%336.23336.2315641.7915641.7925.82%25.82%-5%-5%5866.345866.3411396.45

22、11396.4522.03%22.03%0%0%11396.4511396.457151.117151.1117.92%17.92%5%5%16926.5616926.562905.782905.7813.41%13.41%10%10%22456.6722456.67-1339.56-1339.56 8.32%8.32%15%15%27986.7827986.78-5584.90-5584.90 2.34%2.34%20%20%33516.8933516.89单单因因素素敏敏感感性性分分析析图图30%30%20%20%10%10%0%0%-10%-10%-20%-20%-30%-30%-10%

23、-10%0%0%10%10%20%20%30%30%40%40%50%50%x,y,zx,y,zIRRIRR投投资资变变化化的的影影响响经经营营成成本本变变化化的的影影响响销销售售收收入入的的变变化化的的影影响响单单因因素素敏敏感感性性分分析析图图30%30%20%20%10%10%0%0%-10%-10%-20%-20%-30%-30%-20000-20000 -10000-10000 0 0 1000010000 2000020000 3000030000 4000040000 x,y,zx,y,zNPVNPV投投资资变变化化的的影影响响经经营营成成本本变变化化的的影影响响销销售售收收入入

24、的的变变化化的的影影响响-5%-5%0%0%5%5%10%10%18641.7918641.7914396.4514396.4510151.1110151.115905.785905.7817891.7917891.7913646.4513646.459401.119401.115155.785155.7817141.7917141.7912896.4512896.458651.118651.114405.784405.7816391.7916391.7912146.4512146.457901.117901.113655.783655.7815641.7915641.7911396.4511

25、396.457151.117151.112905.782905.7814891.7914891.7910646.4510646.456401.116401.112155.782155.7814141.7914141.799896.459896.455651.115651.111405.781405.7813391.7913391.799146.459146.454901.114901.11655.78655.7812641.7912641.798396.458396.454151.114151.11-94.22-94.22 -5%-5%0%0%5%5%10%10%8866.348866.341

26、4396.4514396.4519926.5619926.5625456.6725456.678116.348116.3413646.4513646.4519176.5619176.5624706.6724706.677366.347366.3412896.4512896.4518426.5618426.5623956.6723956.676616.346616.3412146.4512146.4517676.5617676.5623206.6723206.675866.345866.3411396.4511396.4516926.5616926.5622456.6722456.675116.

27、345116.3410646.4510646.4516176.5616176.5621706.6721706.674366.344366.349896.459896.4515426.5615426.5620956.6720956.673616.343616.349146.459146.4514676.5614676.5620206.6720206.67单单因因素素敏敏感感性性分分析析图图30%30%20%20%10%10%0%0%-10%-10%-20%-20%-30%-30%-20000-20000 -10000-10000 0 0 1000010000 2000020000 3000030

28、000 4000040000 x,y,zx,y,zNPVNPV投投资资变变化化的的影影响响经经营营成成本本变变化化的的影影响响销销售售收收入入的的变变化化的的影影响响单单因因素素敏敏感感性性分分析析图图30%30%20%20%10%10%0%0%-10%-10%-20%-20%-30%-30%-10%-10%0%0%10%10%20%20%30%30%40%40%50%50%x,y,zx,y,zIRRIRR投投资资变变化化的的影影响响经经营营成成本本变变化化的的影影响响销销售售收收入入的的变变化化的的影影响响2866.342866.348396.458396.4513926.5613926.5

29、619456.6719456.67-5%-5%0%0%5%5%10%10%-6478.65-6478.65 -10723.99-10723.99 -14969.33-14969.33 -19214.67-19214.67 -948.54-948.54 -5193.88-5193.88 -9439.22-9439.22 -13684.56-13684.56 4581.574581.57336.23336.23-3909.11-3909.11 -8154.45-8154.45 10111.6810111.685866.345866.341621.001621.00-2624.33-2624.33

30、15641.7915641.7911396.4511396.457151.117151.112905.782905.7821171.9021171.9016926.5616926.5612681.2212681.228435.898435.8926702.0126702.0122456.6722456.6718211.3318211.3313966.0013966.0032232.1232232.1227986.7827986.7823741.4423741.4419496.1119496.1137762.2337762.2333516.8933516.8929271.5529271.5525

31、026.2225026.22xzz75.98%75.98%-10.30%-10.30%x-10.30%-10.30%189.19%189.19%-25.66%-25.66%-20%-20%-13.02%-13.02%160.88%160.88%-21.82%-21.82%-15%-15%-12.34%-12.34%132.58%132.58%-17.98%-17.98%-10%-10%-11.66%-11.66%104.28%104.28%-14.14%-14.14%-5%-5%-10.98%-10.98%75.98%75.98%-10.30%-10.30%0%0%-10.30%-10.30%

32、47.67%47.67%-6.47%-6.47%5%5%-9.63%-9.63%19.37%19.37%-2.63%-2.63%10%10%-8.95%-8.95%-8.93%-8.93%1.21%1.21%15%15%-8.27%-8.27%-37.23%-37.23%5.05%5.05%20%20%-7.59%-7.59%-5%-5%0%0%5%5%10%10%10.44%10.44%16.96%16.96%23.47%23.47%29.98%29.98%9.56%9.56%16.07%16.07%22.59%22.59%29.10%29.10%8.68%8.68%15.19%15.19%

33、21.70%21.70%28.22%28.22%7.79%7.79%14.31%14.31%20.82%20.82%27.33%27.33%6.91%6.91%13.42%13.42%19.94%19.94%26.45%26.45%6.03%6.03%12.54%12.54%19.05%19.05%25.57%25.57%5.14%5.14%11.66%11.66%18.17%18.17%24.68%24.68%4.26%4.26%10.77%10.77%17.29%17.29%23.80%23.80%3.38%3.38%9.89%9.89%16.40%16.40%22.92%22.92%两两

34、因因素素敏敏感感性性分分析析图图30%20%10%0%-10%-20%-30%-15.00%-10.00%-5.00%0.00%5.00%10.00%15.00%20.00%x xy,zy,zz_xz_xy_xy_x两两因因素素敏敏感感性性分分析析图图30%20%10%0%-10%-20%-30%-50.00%0.00%50.00%100.00%150.00%200.00%250.00%y yx,zx,zx_yx_yz_yz_y销销售售收收入入的的变变化化(z)三三因因素素敏敏感感分分析析图图30%20%10%0%-10%-20%-30%-20.00%-10.00%0.00%10.00%20.

35、00%30.00%40.00%50.00%z zy yx=-20%x=-20%x=-15%x=-15%x=-10%x=-10%x=-5%x=-5%x=0 x=0 x=5%x=5%x=10%x=10%x=15%x=15%x=20%x=20%三三因因素素敏敏感感分分析析图图30%20%10%0%-10%-20%-30%-20.00%-10.00%0.00%10.00%20.00%30.00%40.00%50.00%z zy yx=-20%x=-20%x=-15%x=-15%x=-10%x=-10%x=-5%x=-5%x=0 x=0 x=5%x=5%x=10%x=10%x=15%x=15%x=20%

36、x=20%6 67 78 89 91010198001980019800198001980019800198001980019800198001520015200152001520015200152001520015200152001520046004600460046004600460046004600460046006 67 78 89 91010198001980019800198001980019800198001980019800198001520015200152001520015200152001520015200152001520046004600460046004600460

37、04600460046004600852.38852.383212.913212.915358.845358.847309.697309.699083.199083.19IRR22.03%22.03%-0.07-0.07 0.030.030.100.100.170.170.220.220.270.270.310.310.360.360.400.40单单因因素素敏敏感感性性分分析析图图30%30%20%20%10%10%0%0%-10%-10%-20%-20%-30%-30%-10%-10%0%0%10%10%20%20%30%30%40%40%50%50%x,y,zx,y,zIRRIRR投投资

38、资变变化化的的影影响响经经营营成成本本变变化化的的影影响响销销售售收收入入的的变变化化的的影影响响15%15%20%20%1660.441660.44-2584.90-2584.90 910.44910.44-3334.90-3334.90 160.44160.44-4084.90-4084.90 -589.56-589.56 -4834.90-4834.90 -1339.56-1339.56 -5584.90-5584.90 -2089.56-2089.56 -6334.90-6334.90 -2839.56-2839.56 -7084.90-7084.90 -3589.56-3589.56

39、 -7834.90-7834.90 -4339.56-4339.56 -8584.90-8584.90 15%15%20%20%30986.7830986.7836516.8936516.8930236.7830236.7835766.8935766.8929486.7829486.7835016.8935016.8928736.7828736.7834266.8934266.8927986.7827986.7833516.8933516.8927236.7827236.7832766.8932766.8926486.7826486.7832016.8932016.8925736.782573

40、6.7831266.8931266.89单单因因素素敏敏感感性性分分析析图图30%30%20%20%10%10%0%0%-10%-10%-20%-20%-30%-30%-10%-10%0%0%10%10%20%20%30%30%40%40%50%50%x,y,zx,y,zIRRIRR投投资资变变化化的的影影响响经经营营成成本本变变化化的的影影响响销销售售收收入入的的变变化化的的影影响响24986.7824986.7830516.8930516.8915%15%20%20%-23460.00-23460.00 -27705.34-27705.34 -17929.89-17929.89 -2217

41、5.23-22175.23 -12399.78-12399.78 -16645.12-16645.12 -6869.67-6869.67 -11115.01-11115.01 -1339.56-1339.56 -5584.90-5584.90 4190.554190.55-54.79-54.79 9720.669720.665475.325475.3215250.7715250.7711005.4311005.4320780.8820780.8816535.5416535.54y13.42%13.42%16.96%16.96%16.07%16.07%15.19%15.19%14.31%14.3

42、1%13.42%13.42%12.54%12.54%11.66%11.66%10.77%10.77%9.89%9.89%15%15%20%20%36.50%36.50%43.01%43.01%35.61%35.61%42.12%42.12%34.73%34.73%41.24%41.24%33.85%33.85%40.36%40.36%32.96%32.96%39.47%39.47%32.08%32.08%38.59%38.59%31.20%31.20%37.71%37.71%30.31%30.31%36.82%36.82%29.43%29.43%35.94%35.94%两两因因素素敏敏感感性性分分析析图图30%20%10%0%-10%-20%-30%-15.00%-10.00%-5.00%0.00%5.00%10.00%15.00%20.00%x xy,zy,zz_xz_xy_xy_x1111198001980015200152000 046004600111119800198001520015200200020006600660011396.4511396.45