敏感性分析计算表

敏感性性分析例：有一个生产城市用小型电动汽车的投资 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 ，用于确定性分析的 现金流量表 现金流量表excel下载现金流量表excel下载现金流量表excel下载现金流量表excel下载空白现金流量表下载 如下，所采用的数据是根据对未来最可能出现的情况估算的。由于对未来影响经济环境的某些因素把握不大，投资额、经营成本和销售收入均有可能在±20%的范围内变化。设基准收益率为11%，不考虑所得得税，试就以上三个不确定性因素做敏感性分析。年份01234567891011投资0销售收入19800198001980019800198001980019800198001980019800经营成本15200152001520015200152001520015200152001520015200残值0净现金流量004600460046004600460046004600460046004600解：设投资为K，年销售收入为B，年经营成本为C，期末资产的残值L为L。投资的变化率为x，经营成本的变化率为y，年销售收入的变化率为z,则有NPV=-15000+NPV(10%,0,4600,4600,……,6600)IRR=IRR({-15000,0,4600,4600,……,6600})具体求解如下：K=15000x=0%i0=10%C=15200y=0%NPV=11396.45B=19800z=0%IRR=22.03%残值2000年份01234567891011投资15000销售收入19800198001980019800198001980019800198001980019800经营成本15200152001520015200152001520015200152001520015200残值2000净现金流量-1500004600460046004600460046004600460046006600累计净现值-15000.00-15000.00-11198.35-7742.30-4600.44-1744.20852.383212.915358.847309.699083.1911396.45NPV=11396.45IRR=22%下面就三个不确定性因素做敏感性分析单因素敏感性分析xNPVIRRyNPVIRRzNPVIRR0%11396.4522.03%0%11396.4522.03%0%11396.4522.03%-20%14396.4527.73%-20%28377.8035.90%-20%-10723.99-0.07-15%13646.4526.12%-15%24132.4632.72%-15%-5193.880.03-10%12896.4524.64%-10%19887.1229.37%-10%336.230.10-5%12146.4523.28%-5%15641.7925.82%-5%5866.340.170%11396.4522.03%0%11396.4522.03%0%11396.450.225%10646.4520.86%5%7151.1117.92%5%16926.560.2710%9896.4519.77%10%2905.7813.41%10%22456.670.3115%9146.4518.76%15%-1339.568.32%15%27986.780.3620%8396.4517.81%20%-5584.902.34%20%33516.890.40两因素敏感性分析11396.45-20%-15%-10%-5%0%5%10%15%20%-20%31377.8027132.4622887.1218641.7914396.4510151.115905.781660.44-2584.90-15%30627.8026382.4622137.1217891.7913646.459401.115155.78910.44-3334.90-10%29877.8025632.4621387.1217141.7912896.458651.114405.78160.44-4084.90-5%29127.8024882.4620637.1216391.7912146.457901.113655.78-589.56-4834.900%28377.8024132.4619887.1215641.7911396.457151.112905.78-1339.56-5584.905%27627.8023382.4619137.1214891.7910646.456401.112155.78-2089.56-6334.9010%26877.8022632.4618387.1214141.799896.455651.111405.78-2839.56-7084.9015%26127.8021882.4617637.1213391.799146.454901.11655.78-3589.56-7834.9020%25377.8021132.4616887.1212641.798396.454151.11-94.22-4339.56-8584.9011396.45-20%-15%-10%-5%0%5%10%15%20%-20%-7723.99-2193.883336.238866.3414396.4519926.5625456.6730986.7836516.89-15%-8473.99-2943.882586.238116.3413646.4519176.5624706.6730236.7835766.89-10%-9223.99-3693.881836.237366.3412896.4518426.5623956.6729486.7835016.89-5%-9973.99-4443.881086.236616.3412146.4517676.5623206.6728736.7834266.890%-10723.99-5193.88336.235866.3411396.4516926.5622456.6727986.7833516.895%-11473.99-5943.88-413.775116.3410646.4516176.5621706.6727236.7832766.8910%-12223.99-6693.88-1163.774366.349896.4515426.5620956.6726486.7832016.8915%-12973.99-7443.88-1913.773616.349146.4514676.5620206.6725736.7831266.8920%-13723.99-8193.88-2663.772866.348396.4513926.5619456.6724986.7830516.8911396.45-20%-15%-10%-5%0%5%10%15%20%-20%6257.362012.02-2233.32-6478.65-10723.99-14969.33-19214.67-23460.00-27705.34-15%11787.477542.133296.79-948.54-5193.88-9439.22-13684.56-17929.89-22175.23-10%17317.5813072.248826.904581.57336.23-3909.11-8154.45-12399.78-16645.12-5%22847.6918602.3514357.0110111.685866.341621.00-2624.33-6869.67-11115.010%28377.8024132.4619887.1215641.7911396.457151.112905.78-1339.56-5584.905%33907.9129662.5725417.2421171.9016926.5612681.228435.894190.55-54.7910%39438.0235192.6830947.3526702.0122456.6718211.3313966.009720.665475.3215%44968.1340722.7936477.4632232.1227986.7823741.4419496.1115250.7711005.4320%50498.2446252.9042007.5737762.2333516.8929271.5525026.2220780.8816535.54x=75.98%y=13.42%z=-10.30%xyxzzyz75.98%13.42%y75.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%