蒙特卡洛风险分析
某 企 业 准 备 投 资 2 百 万 元 建 立 一 条 生 产 某 种 产 品 的 生产 线 , 估 计 该 产 品 在 第 1 年 的 销 量 是 一 个 均 值 为 2 百 万件 而 标 准 差 为 0 .6 百 万 件 的 正 态 分 布 随 机 变 量 , 第 2 年的 销 量 将 增 长 2 0 % , 第 3 年 的 产 量 则 将 下 降 5 0 % , 产品 生 产 的 年 固 定 成 本 为 1 百 万 元 。 产 品 售 价 是 也 是 一个 随 机 变 量 , 等 于 4 元 /件 、 5 元 /件 、 6 元 /件 的 概 率 分别 是 0 .3 、 0 .5 、 0 .2 。 单 位 变 动 成 本 是 一 个 在 区 间 2 ,4 中 均 匀 分 布 的 随 机 变 量 。 试 确 定 这 个 投 资 项 目 净 现值 的 样 本 分 布 直 方 图 与 它 大 于 零 的 概 率 。某 企 业 准 备 投 资 2 百 万 元 建 立 一 条 生 产 某 种 产 品 的 生产 线 , 估 计 该 产 品 在 第 1 年 的 销 量 是 一 个 均 值 为 2 百 万件 而 标 准 差 为 0 .6 百 万 件 的 正 态 分 布 随 机 变 量 , 第 2 年的 销 量 将 增 长 2 0 % , 第 3 年 的 产 量 则 将 下 降 5 0 % , 产品 生 产 的 年 固 定 成 本 为 1 百 万 元 。 产 品 售 价 是 也 是 一个 随 机 变 量 , 等 于 4 元 /件 、 5 元 /件 、 6 元 /件 的 概 率 分别 是 0 .3 、 0 .5 、 0 .2 。 单 位 变 动 成 本 是 一 个 在 区 间 2 ,4 中 均 匀 分 布 的 随 机 变 量 。 试 确 定 这 个 投 资 项 目 净 现值 的 样 本 分 布 直 方 图 与 它 大 于 零 的 概 率 。产品价格随机数生成表-2.66 0 0初始投资额 (百万元) 2 1 14.71 4 0.3 0.3初始销量均值 (百万件) 2 2 3.22 5 0.5 0.8初始销量标准差 (百万件) 0.6 3 2.25 6 0.2 1销量第2年增长率 20% 4 0.62销量第3年增长率 -50% 5 6.96年固定成本 (百万元) 1 6 -2.99贴现率 10% 7 8.29初始销量 (百万件) 2.78 8 0.47产品价格 (元) 4 9 6.90单位变动成本 (元) 3.72 10 7.00第1年 第2年 第3年 11 0.94销量 2.78 3.34 1.67 12 14.88销售收益 11.12 13.35 6.67 13 13.05总成本 11.35 13.41 7.21 14 0.18利润 (现金收入) -0.22 -0.07 -0.53 15 -0.5716 -0.56特定净现值 (百万元) -2.66 17 13.331000次模拟净现值均值 (百万元) 4.36 18 0.941001次模拟净现值标准差 (百万元) 5.18 19 -1.8520 1.77999 2.941000 4.30in F 1 0 : = N O R M IN V (R A N D (),F 4 ,F 5 )in F 1 1 : = IN D E X (N 2 :N 5 ,M A T C H (R A N D (),P 2 :P 5 )+ 1 )in F 1 2 : = 2 + 2 *R O U N D (R A N D (),2 )AB C D E F GH I J K LM N O P Q12345678910111213141516171819202122100110021003in F10: =NORMINV(RAND(),F4,F5)in F11: =INDEX(N2:N5,MATCH(RAND(),P2:P5)+1)in F12: =2+2*ROUND(RAND(),2)in D14: =F10 in D16: =$F$8+D14*$F$12 in E14:in D15: =D14*$F$11 in D17: =D15-D16 in F14:in F19:in F20: =AVERAGE(K3:K1002) in F21: =STDEV(K3:K1002)in F 1 0 : = N O R M IN V (R A N D (),F 4 ,F 5 )in F 1 1 : = IN D E X (N 2 :N 5 ,M A T C H (R A N D (),P 2 :P 5 )+ 1 )in F 1 2 : = 2 + 2 *R O U N D (R A N D (),2 )R S T U V W X12345678910111213141516171819202122100110021003=D14*(1+F6)=E14*(1+F7)=-F3+NPV(F9,D17:F17)in F 1 0 : = N O R M IN V (R A N D (),F 4 ,F 5 )in F 1 1 : = IN D E X (N 2 :N 5 ,M A T C H (R A N D (),P 2 :P 5 )+ 1 )in F 1 2 : = 2 + 2 *R O U N D (R A N D (),2 )Y12345678910111213141516171819202122100110021003结果样本 Bin Frequency26.182 0.001 净现值下限值 0 -5 0 0 in F3:25.440 0.002 累计概率 0.788 -4 22 0.02222.519 0.003 -3 46 0.04621.666 0.004 样本极大值 26.182107 -2 45 0.04521.223 0.005 样本极小值 -4.486852 -1 38 0.03820.696 0.006 0 63 0.06320.692 0.007 1 64 0.06420.045 0.008 2 87 0.08719.929 0.009 3 74 0.07419.874 0.01 4 96 0.09619.734 0.011 5 74 0.07419.306 0.012 6 64 0.06418.788 0.013 7 59 0.05918.576 0.014 8 55 0.05518.366 0.015 9 34 0.03418.343 0.016 10 33 0.03318.287 0.017 11 28 0.02817.901 0.018 12 24 0.02417.794 0.019 13 25 0.02517.724 0.02 14 10 0.0117.642 0.021 15 19 0.01917.549 0.022 16 15 0.01517.421 0.023 17 7 0.00717.335 0.024 18 4 0.00417.298 0.025 19 6 0.00617.199 0.026 20 1 0.00117.032 0.027 21 4 0.00417.020 0.028 22 1 0.00116.975 0.029 23 0 016.812 0.03 24 0 016.402 0.031 25 0 016.372 0.032 26 1 0.00116.350 0.033 27 0 016.305 0.034 28 1 0.00116.295 0.035 29 0 015.976 0.036 30 0 015.845 0.037 100015.721 0.03815.688 0.03915.643 0.0415.629 0.04115.316 0.04215.195 0.04315.100 0.04415.024 0.04514.779 0.04614.707 0.04714.692 0.04814.625 0.04914.566 0.0514.551 0.05114.499 0.05214.428 0.05314.242 0.054A B C D E F G H I J K P1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545514.156 0.05514.069 0.05613.923 0.05713.906 0.05813.901 0.05913.847 0.0613.843 0.06113.761 0.06213.690 0.06313.665 0.06413.608 0.06513.475 0.06613.466 0.06713.421 0.06813.406 0.06913.359 0.0713.357 0.07113.319 0.07213.302 0.07313.187 0.07413.135 0.07513.121 0.07613.091 0.07712.972 0.07812.879 0.07912.835 0.0812.830 0.08112.694 0.08212.684 0.08312.668 0.08412.654 0.08512.609 0.08612.598 0.08712.509 0.08812.500 0.08912.483 0.0912.414 0.09112.341 0.09212.324 0.09312.303 0.09412.302 0.09512.276 0.09612.255 0.09712.173 0.09812.067 0.09911.960 0.111.838 0.10111.800 0.10211.755 0.10311.728 0.10411.723 0.10511.722 0.10611.693 0.10711.573 0.10811.545 0.109A B C D E F G H I J K P565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011.524 0.1111.497 0.11111.399 0.11211.393 0.11311.369 0.11411.341 0.11511.338 0.11611.317 0.11711.304 0.11811.302 0.11911.295 0.1211.275 0.12111.270 0.12211.092 0.12311.085 0.12411.041 0.12511.034 0.12611.024 0.12710.990 0.12810.974 0.12910.958 0.1310.956 0.13110.912 0.13210.798 0.13310.773 0.13410.763 0.13510.721 0.13610.590 0.13710.527 0.13810.517 0.13910.432 0.1410.424 0.14110.408 0.14210.399 0.14310.365 0.14410.355 0.14510.347 0.14610.327 0.14710.228 0.14810.190 0.14910.183 0.1510.171 0.15110.131 0.15210.077 0.15310.072 0.1549.949 0.1559.938 0.1569.909 0.1579.885 0.1589.841 0.1599.788 0.169.786 0.1619.693 0.1629.693 0.1639.653 0.164A B C D E F G H I J K P1111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641659.629 0.1659.606 0.1669.590 0.1679.558 0.1689.547 0.1699.522 0.179.515 0.1719.500 0.1729.494 0.1739.411 0.1749.385 0.1759.378 0.1769.346 0.1779.329 0.1789.296 0.1799.291 0.189.267 0.1819.210 0.1829.186 0.1839.175 0.1849.133 0.1859.095 0.1869.042 0.1879.023 0.1888.998 0.1898.995 0.198.985 0.1918.955 0.1928.917 0.1938.913 0.1948.912 0.1958.912 0.1968.892 0.1978.857 0.1988.855 0.1998.843 0.28.784 0.2018.765 0.2028.761 0.2038.728 0.2048.687 0.2058.686 0.2068.647 0.2078.640 0.2088.605 0.2098.583 0.218.337 0.2118.335 0.2128.322 0.2138.318 0.2148.290 0.2158.262 0.2168.247 0.2178.210 0.2188.099 0.219A B C D E F G H I J K P1661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192208.081 0.228.068 0.2218.029 0.2228.025 0.2237.964 0.2247.912 0.2257.905 0.2267.895 0.2277.875 0.2287.850 0.2297.806 0.237.773 0.2317.764 0.2327.749 0.2337.717 0.2347.715 0.2357.700 0.2367.698 0.2377.694 0.2387.653 0.2397.647 0.247.641 0.2417.612 0.2427.590 0.2437.579 0.2447.575 0.2457.571 0.2467.542 0.2477.504 0.2487.503 0.2497.495 0.257.456 0.2517.405 0.2527.347 0.2537.347 0.2547.326 0.2557.317 0.2567.300 0.2577.261 0.2587.177 0.2597.157 0.267.154 0.2617.145 0.2627.134 0.2637.103 0.2647.094 0.2657.080 0.2667.074 0.2677.058 0.2687.007 0.2696.939 0.276.925 0.2716.921 0.2726.907 0.2736.890 0.274A B C D E F G H I J K P2212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742756.882 0.2756.838 0.2766.830 0.2776.819 0.2786.815 0.2796.798 0.286.781 0.2816.775 0.2826.755 0.2836.754 0.2846.730 0.2856.723 0.2866.710 0.2876.701 0.2886.689 0.2896.669 0.296.629 0.2916.624 0.2926.595 0.2936.594 0.2946.565 0.2956.551 0.2966.523 0.2976.518 0.2986.494 0.2996.475 0.36.454 0.3016.453 0.3026.449 0.3036.448 0.3046.443 0.3056.423 0.3066.412 0.3076.407 0.3086.393 0.3096.388 0.316.376 0.3116.366 0.3126.327 0.3136.298 0.3146.277 0.3156.275 0.3166.267 0.3176.266 0.3186.259 0.3196.224 0.326.222 0.3216.216 0.3226.213 0.3236.199 0.3246.181 0.3256.177 0.3266.176 0.3276.139 0.3286.130 0.329A B C D E F G H I J K P2762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293306.116 0.336.103 0.3316.100 0.3326.088 0.3336.078 0.3346.074 0.3356.061 0.3366.058 0.3376.041 0.3386.039 0.3395.996 0.345.970 0.3415.969 0.3425.964 0.3435.954 0.3445.915 0.3455.904 0.3465.901 0.3475.901 0.3485.897 0.3495.891 0.355.879 0.3515.879 0.3525.875 0.3535.874 0.3545.865 0.3555.831 0.3565.818 0.3575.797 0.3585.771 0.3595.754 0.365.740 0.3615.696 0.3625.696 0.3635.694 0.3645.674 0.3655.642 0.3665.624 0.3675.603 0.3685.586 0.3695.560 0.375.554 0.3715.537 0.3725.529 0.3735.515 0.3745.510 0.3755.460 0.3765.437 0.3775.428 0.3785.404 0.3795.387 0.385.365 0.3815.351 0.3825.344 0.3835.326 0.384A B C D E F G H I J K P3313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843855.315 0.3855.305 0.3865.289 0.3875.274 0.3885.256 0.3895.243 0.395.228 0.3915.219 0.3925.161 0.3935.157 0.3945.156 0.3955.154 0.3965.140 0.3975.139 0.3985.130 0.3995.118 0.45.108 0.4015.105 0.4025.099 0.4035.085 0.4045.082 0.4055.059 0.4065.058 0.4075.054 0.4085.051 0.4094.985 0.414.984 0.4114.962 0.4124.955 0.4134.935 0.4144.933 0.4154.926 0.4164.902 0.4174.898 0.4184.895 0.4194.895 0.424.893 0.4214.891 0.4224.888 0.4234.868 0.4244.866 0.4254.836 0.4264.825 0.4274.822 0.4284.817 0.4294.807 0.434.781 0.4314.769 0.4324.725 0.4334.711 0.4344.692 0.4354.683 0.4364.667 0.4374.652 0.4384.651 0.439A B C D E F G H I J K P3863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404.626 0.444.612 0.4414.612 0.4424.605 0.4434.588 0.4444.584 0.4454.579 0.4464.575 0.4474.568 0.4484.568 0.4494.565 0.454.532 0.4514.518 0.4524.490 0.4534.463 0.4544.459 0.4554.417 0.4564.406 0.4574.392 0.4584.390 0.4594.387 0.464.380 0.4614.368 0.4624.362 0.4634.360 0.4644.316 0.4654.311 0.4664.308 0.4674.307 0.4684.300 0.4694.298 0.474.285 0.4714.274 0.4724.257 0.4734.228 0.4744.188 0.4754.184 0.4764.148 0.4774.144 0.4784.131 0.4794.115 0.484.096 0.4814.085 0.4824.078 0.4834.075 0.4844.070 0.4854.069 0.4864.057 0.4874.052 0.4884.038 0.4894.027 0.494.021 0.4914.016 0.4924.015 0.4934.002 0.494A B C D E F G H I J K P4414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944953.973 0.4953.969 0.4963.960 0.4973.959 0.4983.928 0.4993.917 0.53.914 0.5013.893 0.5023.861 0.5033.855 0.5043.842 0.5053.840 0.5063.833 0.5073.824 0.5083.797 0.5093.797 0.513.782 0.5113.776 0.5123.753 0.5133.729 0.5143.722 0.5153.708 0.5163.706 0.5173.700 0.5183.693 0.5193.681 0.523.662 0.5213.650 0.5223.633 0.5233.621 0.5243.591 0.5253.573 0.5263.556 0.5273.553 0.5283.530 0.5293.516 0.533.516 0.5313.511 0.5323.507 0.5333.492 0.5343.490 0.5353.489 0.5363.486 0.5373.475 0.5383.451 0.5393.439 0.543.413 0.5413.407 0.5423.402 0.5433.395 0.5443.377 0.5453.370 0.5463.354 0.5473.346 0.5483.335 0.549A B C D E F G H I J K P4964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495503.333 0.553.316 0.5513.315 0.5523.302 0.5533.248 0.5543.241 0.5553.223 0.5563.191 0.5573.179 0.5583.158 0.5593.142 0.563.140 0.5613.045 0.5623.038 0.5633.032 0.5643.024 0.5652.992 0.5662.963 0.5672.956 0.5682.918 0.5692.916 0.572.888 0.5712.879 0.5722.877 0.5732.870 0.5742.869 0.5752.857 0.5762.856 0.5772.852 0.5782.848 0.5792.848 0.582.839 0.5812.829 0.5822.819 0.5832.804 0.5842.797 0.5852.777 0.5862.757 0.5872.705 0.5882.696 0.5892.693 0.592.689 0.5912.660 0.5922.638 0.5932.630 0.5942.627 0.5952.611 0.5962.608 0.5972.605 0.5982.588 0.5992.540 0.62.539 0.6012.523 0.6022.500 0.6032.491 0.604A B C D E F G H I J K P5515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046052.481 0.6052.470 0.6062.464 0.6072.454 0.6082.454 0.6092.445 0.612.430 0.6112.422 0.6122.417 0.6132.404 0.6142.394 0.6152.393 0.6162.391 0.6172.370 0.6182.367 0.6192.363 0.622.310 0.6212.303 0.6222.262 0.6232.250 0.6242.240 0.6252.237 0.6262.230 0.6272.221 0.6282.208 0.6292.205 0.632.191 0.6312.188 0.6322.164 0.6332.154 0.6342.147 0.6352.143 0.6362.140 0.6372.130 0.6382.119 0.6392.093 0.642.088 0.6412.084 0.6422.078 0.6432.078 0.6442.077 0.6452.057 0.6462.052 0.6472.010 0.6481.992 0.6491.991 0.651.981 0.6511.973 0.6521.971 0.6531.965 0.6541.949 0.6551.922 0.6561.910 0.6571.893 0.6581.883 0.659A B C D E F G H I J K P6066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596601.868 0.661.855 0.6611.855 0.6621.849 0.6631.840 0.6641.839 0.6651.835 0.6661.827 0.6671.798 0.6681.781 0.6691.755 0.671.705 0.6711.688 0.6721.661 0.6731.656 0.6741.650 0.6751.645 0.6761.634 0.6771.621 0.6781.614 0.6791.607 0.681.606 0.6811.603 0.6821.590 0.6831.582 0.6841.541 0.6851.536 0.6861.491 0.6871.482 0.6881.471 0.6891.458 0.691.415 0.6911.415 0.6921.403 0.6931.388 0.6941.386 0.6951.374 0.6961.371 0.6971.322 0.6981.307 0.6991.284 0.71.281 0.7011.245 0.7021.223 0.7031.199 0.7041.187 0.7051.180 0.7061.159 0.7071.147 0.7081.143 0.7091.123 0.711.105 0.7111.088 0.7121.063 0.7131.056 0.714A B C D E F G H I J K P6616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147151.047 0.7151.027 0.7161.027 0.7171.003 0.7181.002 0.7190.989 0.720.984 0.7210.974 0.7220.966 0.7230.945 0.7240.943 0.7250.933 0.7260.916 0.7270.916 0.7280.896 0.7290.888 0.730.858 0.7310.851 0.7320.849 0.7330.828 0.7340.828 0.7350.821 0.7360.815 0.7370.811 0.7380.782 0.7390.778 0.740.771 0.7410.720 0.7420.693 0.7430.692 0.7440.653 0.7450.649 0.7460.648 0.7470.637 0.7480.635 0.7490.625 0.750.577 0.7510.564 0.7520.564 0.7530.552 0.7540.525 0.7550.524 0.7560.519 0.7570.498 0.7580.490 0.7590.478 0.760.443 0.7610.433 0.7620.428 0.7630.421 0.7640.384 0.7650.383 0.7660.366 0.7670.361 0.7680.350 0.769A B C D E F G H I J K P7167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697700.325 0.770.287 0.7710.263 0.7720.244 0.7730.242 0.7740.212 0.7750.207 0.7760.198 0.7770.194 0.7780.186 0.7790.181 0.780.134 0.7810.125 0.7820.101 0.7830.091 0.7840.073 0.7850.043 0.7860.023 0.7870.001 0.788-0.007 0.789-0.008 0.79-0.009 0.791-0.011 0.792-0.038 0.793-0.055 0.794-0.105 0.795-0.110 0.796-0.147 0.797-0.148 0.798-0.174 0.799-0.175 0.8-0.205 0.801-0.210 0.802-0.216 0.803-0.216 0.804-0.244 0.805-0.252 0.806-0.262 0.807-0.270 0.808-0.280 0.809-0.287 0.81-0.293 0.811-0.333 0.812-0.347 0.813-0.369 0.814-0.410 0.815-0.410 0.816-0.437 0.817-0.443 0.818-0.456 0.819-0.480 0.82-0.485 0.821-0.506 0.822-0.507 0.823-0.530 0.824A B C D E F G H I J K P771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825-0.552 0.825-0.559 0.826-0.584 0.827-0.627 0.828-0.628 0.829-0.643 0.83-0.650 0.831-0.690 0.832-0.691 0.833-0.692 0.834-0.702 0.835-0.725 0.836-0.737 0.837-0.745 0.838-0.786 0.839-0.795 0.84-0.808 0.841-0.835 0.842-0.849 0.843-0.852 0.844-0.909