敏感性分析计算表

敏感性分析 敏感性性分析 例：有一个生产城市用小型电动汽车的投资 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 ，用于确定性分析的 现金流量表如下，所采用的数据是根据对未来最可能出现的情况估算的。 由于对未来影响经济环境的某些因素把握不大，投资额、经营成本和销售 收入均有可能在±20%的范围内变化。设基准收益率为11%，不考虑所得 得税，试就以上三个不确定性因素做敏感性分析。 年份 0 1 2 3 4 5 6 7 8 9 10 11 投资 0 销售收入 19800 19800 19800 19800 19800 19800 19800 19800 19800 19800 经营成本 15200 15200 15200 15200 15200 15200 15200 15200 15200 15200 残值 0 净现金流量 0 0 4600 4600 4600 4600 4600 4600 4600 4600 4600 4600 解：设投资为K，年销售收入为B，年经营成本为C，期末资产的残值L 为L。投资的变化率为x，经营成本的变化率为y，年销售收入的变化率为z, 则有NPV=-15000+NPV(10%,0,4600,4600,……,6600) IRR=IRR({-15000,0,4600,4600,……,6600}) 具体求解如下： K= 15000 x= 0% i0= 10% C= 15200 y= 0% NPV= 11396.45 B= 19800 z= 0% IRR= 22.03% 残值 2000 年份 0 1 2 3 4 5 6 7 8 9 10 11 投资 15000 销售收入 19800 19800 19800 19800 19800 19800 19800 19800 19800 19800 经营成本 15200 15200 15200 15200 15200 15200 15200 15200 15200 15200 残值 2000 净现金流量 -15000 0 4600 4600 4600 4600 4600 4600 4600 4600 4600 6600 累计净现值 -15000.00 -15000.00 -11198.35 -7742.30 -4600.44 -1744.20 852.38 3212.91 5358.84 7309.69 9083.19 11396.45 NPV= 11396.45 IRR= 22% 下面就三个不确定性因素做敏感性分析 单因素敏感性分析 x NPV IRR y NPV IRR z NPV IRR 0% 11396.45 22.03% 0% 11396.45 22.03% 0% 11396.45 22.03% -20% 14396.45 27.73% -20% 28377.80 35.90% -20% -10723.99 -0.07 -15% 13646.45 26.12% -15% 24132.46 32.72% -15% -5193.88 0.03 -10% 12896.45 24.64% -10% 19887.12 29.37% -10% 336.23 0.10 -5% 12146.45 23.28% -5% 15641.79 25.82% -5% 5866.34 0.17 0% 11396.45 22.03% 0% 11396.45 22.03% 0% 11396.45 0.22 5% 10646.45 20.86% 5% 7151.11 17.92% 5% 16926.56 0.27 10% 9896.45 19.77% 10% 2905.78 13.41% 10% 22456.67 0.31 15% 9146.45 18.76% 15% -1339.56 8.32% 15% 27986.78 0.36 20% 8396.45 17.81% 20% -5584.90 2.34% 20% 33516.89 0.40 两因素敏感性分析 11396.45 -20% -15% -10% -5% 0% 5% 10% 15% 20% -20% 31377.80 27132.46 22887.12 18641.79 14396.45 10151.11 5905.78 1660.44 -2584.90 -15% 30627.80 26382.46 22137.12 17891.79 13646.45 9401.11 5155.78 910.44 -3334.90 -10% 29877.80 25632.46 21387.12 17141.79 12896.45 8651.11 4405.78 160.44 -4084.90 -5% 29127.80 24882.46 20637.12 16391.79 12146.45 7901.11 3655.78 -589.56 -4834.90 0% 28377.80 24132.46 19887.12 15641.79 11396.45 7151.11 2905.78 -1339.56 -5584.90 5% 27627.80 23382.46 19137.12 14891.79 10646.45 6401.11 2155.78 -2089.56 -6334.90 10% 26877.80 22632.46 18387.12 14141.79 9896.45 5651.11 1405.78 -2839.56 -7084.90 15% 26127.80 21882.46 17637.12 13391.79 9146.45 4901.11 655.78 -3589.56 -7834.90 20% 25377.80 21132.46 16887.12 12641.79 8396.45 4151.11 -94.22 -4339.56 -8584.90 11396.45 -20% -15% -10% -5% 0% 5% 10% 15% 20% -20% -7723.99 -2193.88 3336.23 8866.34 14396.45 19926.56 25456.67 30986.78 36516.89 -15% -8473.99 -2943.88 2586.23 8116.34 13646.45 19176.56 24706.67 30236.78 35766.89 -10% -9223.99 -3693.88 1836.23 7366.34 12896.45 18426.56 23956.67 29486.78 35016.89 -5% -9973.99 -4443.88 1086.23 6616.34 12146.45 17676.56 23206.67 28736.78 34266.89 0% -10723.99 -5193.88 336.23 5866.34 11396.45 16926.56 22456.67 27986.78 33516.89 5% -11473.99 -5943.88 -413.77 5116.34 10646.45 16176.56 21706.67 27236.78 32766.89 10% -12223.99 -6693.88 -1163.77 4366.34 9896.45 15426.56 20956.67 26486.78 32016.89 15% -12973.99 -7443.88 -1913.77 3616.34 9146.45 14676.56 20206.67 25736.78 31266.89 20% -13723.99 -8193.88 -2663.77 2866.34 8396.45 13926.56 19456.67 24986.78 30516.89 11396.45 -20% -15% -10% -5% 0% 5% 10% 15% 20% -20% 6257.36 2012.02 -2233.32 -6478.65 -10723.99 -14969.33 -19214.67 -23460.00 -27705.34 -15% 11787.47 7542.13 3296.79 -948.54 -5193.88 -9439.22 -13684.56 -17929.89 -22175.23 -10% 17317.58 13072.24 8826.90 4581.57 336.23 -3909.11 -8154.45 -12399.78 -16645.12 -5% 22847.69 18602.35 14357.01 10111.68 5866.34 1621.00 -2624.33 -6869.67 -11115.01 0% 28377.80 24132.46 19887.12 15641.79 11396.45 7151.11 2905.78 -1339.56 -5584.90 5% 33907.91 29662.57 25417.24 21171.90 16926.56 12681.22 8435.89 4190.55 -54.79 10% 39438.02 35192.68 30947.35 26702.01 22456.67 18211.33 13966.00 9720.66 5475.32 15% 44968.13 40722.79 36477.46 32232.12 27986.78 23741.44 19496.11 15250.77 11005.43 20% 50498.24 46252.90 42007.57 37762.23 33516.89 29271.55 25026.22 20780.88 16535.54 x= 75.98% y= 13.42% z= -10.30% x y x z z y z 75.98% 13.42% y 75.98% -10.30% x -10.30% 13.42% -20% -71.49% -12.63% -20% 189.19% -25.66% -20% -13.02% 16.96% -15% -34.63% -6.12% -15% 160.88% -21.82% -15% -12.34% 16.07% -10% 2.24% 0.40% -10% 132.58% -17.98% -10% -11.66% 15.19% -5% 39.11% 6.91% -5% 104.28% -14.14% -5% -10.98% 14.31% 0% 75.98% 13.42% 0% 75.98% -10.30% 0% -10.30% 13.42% 5% 112.84% 19.94% 5% 47.67% -6.47% 5% -9.63% 12.54% 10% 149.71% 26.45% 10% 19.37% -2.63% 10% -8.95% 11.66% 15% 186.58% 32.96% 15% -8.93% 1.21% 15% -8.27% 10.77% 20% 223.45% 39.47% 20% -37.23% 5.05% 20% -7.59% 9.89% 三因素敏感性分析 13.42% -20% -15% -10% -5% 0% 5% 10% 15% 20% -20% -9.10% -2.58% 3.93% 10.44% 16.96% 23.47% 29.98% 36.50% 43.01% -15% -9.98% -3.47% 3.05% 9.56% 16.07% 22.59% 29.10% 35.61% 42.12% -10% -10.86% -4.35% 2.16% 8.68% 15.19% 21.70% 28.22% 34.73% 41.24% -5% -11.75% -5.23% 1.28% 7.79% 14.31% 20.82% 27.33% 33.85% 40.36% 0% -12.63% -6.12% 0.40% 6.91% 13.42% 19.94% 26.45% 32.96% 39.47% 5% -13.51% -7.00% -0.49% 6.03% 12.54% 19.05% 25.57% 32.08% 38.59% 10% -14.40% -7.88% -1.37% 5.14% 11.66% 18.17% 24.68% 31.20% 37.71% 15% -15.28% -8.77% -2.25% 4.26% 10.77% 17.29% 23.80% 30.31% 36.82% 20% -16.16% -9.65% -3.14% 3.38% 9.89% 16.40% 22.92% 29.43% 35.94%敏感性分析 投资变化的影响经营成本变化的影响销售收入的变化的影响x,y,zNPV单因素敏感性分析图 投资变化的影响经营成本变化的影响销售收入的变化的影响x,y,zIRR单因素敏感性分析图 经营成本的变化(y)投资的变化xNPV投资的变化x销售收入的变化(z)NPV经营成本的变化(y)销售收入的变化xNPVx_zy_zzx,y两因素敏感性分析图x_yz_yyx,z两因素敏感性分析图 z_xy_xxy,z两因素敏感性分析图 投资的变化x销售收入的变化(z)y=z=-20%z=-15%z=-10%z=-5%z=0z=5%z=10%z=15%z=20%xy三因素敏感分析图x=-20%x=-15%x=-10%x=-5%x=0x=5%x=10%x=15%x=20%zy三因素敏感分析图