EXCEL财务模型库-存货组合模拟.xls

基基本本信信息息每每日日需需求求量量提提前前期期 单位存储成本0.02概率累计概率随机数可能值概率累计概率随机数 每次订货成本250.030.0301400.110.110 单位缺货成本0.10.090.1231450.650.7611 期初库存6000.160.28121480.160.9276 订货点2000.240.52281500.08192 订货批量5000.290.8152155 0.110.9281160 0.050.9792165 0.03197170 模拟过程 时间随机数需求量库存量订货批量随机数提前期到货量控制值订货次数存储成本 0600 1941654350000008.7 2611552800000005.6 3931651155003120512.3 46715500000000 520148352000500007.04 6291502020000004.04 库存量模拟 1112131415161718191 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 331 341 351 361 0 100 200 300 400 500 600 700 时间 缺货量模拟 1112131415161718191 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 331 341 351 361 0 20 40 60 80 100 120 140 160 180 时间 782160425002020910.84 81314800000000 952155345000500006.9 101014520050038201214 1171155450000000.9 1268155390000500007.8 13631552350000004.7 14321508550022201611.7 158416000000000 1613148352000500007.04 178416019250025201913.84 1838150420000000.84 1986160382000500007.64 20271482340000004.68 21561557950069202311.58 226315500000000 2378155345000500006.9 241514819750096402813.94 2560155420000000.84 269717000000000 277415500000000 2844150350000500007 294615020050026203114 3052155450000000.9 3141150395000500007.9 32781552400000004.8 33551558550051203511.7 344415000000000 3520148352000500007.04 36111452070000004.14 37201485950098404111.18 389116000000000 392014800000000 405615500000000 4171155345000500006.9 425615519050071204413.8 4327148420000000.84 4450150392000500007.84 45721552370000004.74 46131488950098405011.78 474315000000000 482414800000000 493615000000000 501140360000500007.2 51221482120000004.24 52491506250019205411.24 534215000000000 5492160340000500006.8 556015518550059205713.7 5665155300000000.6 5719148382000500007.64 58761552270000004.54 59141487950068206111.58 601514800000000 617145355000500007.1 62471502050000004.1 63921654050027206510.8 646215500000000 6545150350000500007 666415519550080306913.9 6727148470000000.94 687415500000000 6954155345000500006.9 704815019550084307313.9 7147150450000000.9 72414500000000 739145355000500007.1 74101452100000004.2 75551555550086307811.1 769016000000000 772514800000000 789145355000500007.1 796115520050023208114 8084160400000000.8 8169155385000500007.7 82871602250000004.5 83611557050025208511.4 84314500000000 8557155345000500006.9 866215519050065208813.8 8731150400000000.8 8816148392000500007.84 89221482440000004.88 90241489650020209211.92 918516000000000 9288160340000500006.8 934315019050016209513.8 9471155350000000.7 9582160375000500007.5 96771552200000004.4 9741457550064209911.5 983715000000000 998145355000500007.1 10060155200500242010214 10190160400000000.8 10251150390000500007.8 103141482420000004.84 1048716082500342010611.64 1052915000000000 10670155345000500006.9 10760155190500984011113.8 10850150400000000.8 109314000000000 1109616500000000 11194165335000500006.7 1125515518050071011313.6 113751555250005000010.5 114141483770000007.54 115711552220000004.44 1162214874500152011811.48 1175415500000000 11862155345000500006.9 11947150195500913012213.9 12082160350000000.7 1215115000000000 12261155345000500006.9 12319148197500954012713.94 12434150470000000.94 1252815000000000 1261114500000000 1279145355000500007.1 12811402150000004.3 1297615560500152013111.2 1303215000000000 13198170330000500006.6 13261155175500562013413.5 13368155200000000.4 13421148372000500007.44 135211482240000004.48 1361914876500152013811.52 1377415500000000 13848150350000500007 13940150200500342014114 14087160400000000.8 14137150390000500007.8 142781552350000004.7 1433615085500843014611.7 1442014800000000 1456415500000000 14692165335000500006.7 14725148187500272014913.74 14883160270000000.54 14965155372000500007.44 150421502220000004.44 1518116062500362015311.24 1527015500000000 15345150350000500007 15445150200500652015614 15524148520000001.04 15680155397000500007.94 157851602370000004.74 1588316077500332016011.54 159314000000000 1603145355000500007.1 16159155200500863016414 16292160400000000.8 1632014800000000 16481160340000500006.8 16598170170500702016713.4 16632150200000000.4 16764155365000500007.3 168331502150000004.3 1693415065500813017211.3 1703715000000000 1718416000000000 17261155345000500006.9 1738145200500793017614 1746145550000001.1 1751914800000000 17676155345000500006.9 17716148197500332017913.94 17819148490000000.98 17987160389000500007.78 180461502390000004.78 1813315089500994018511.78 182014000000000 1832815000000000 1841414800000000 18551150350000500007 186121452050000004.1 1879817035500944019110.7 1881814800000000 1898616000000000 1908316000000000 19119148352000500007.04 192251482040000004.08 1939016044500322019510.88 1947215500000000 19550150350000500007 19673155195500412019813.9 19720148470000000.94 19894165382000500007.64 199241482340000004.68 2009316569500752020211.38 2017115500000000 20249150350000500007 20385160190500162020513.8 2048145450000000.9 20511145400000500008 206101452550000005.1 20734150105500282020912.1 2084915000000000 20972155345000500006.9 21011402050000004.1 2112514857500242021311.14 2127715500000000 2136145355000500007.1 214231482070000004.14 2156015552500462021711.04 216814500000000 21760155345000500006.9 21867155190500542022013.8 21949150400000000.8 22098170370000500007.4 221381502200000004.4 2222614872500954022611.44 2231214500000000 2243715000000000 2253515000000000 22637150350000500007 22776155195500582022913.9 22891160350000000.7 22951150385000500007.7 230151482370000004.74 2312514889500974023511.78 2329817000000000 2336215500000000 234914500000000 23525148352000500007.04 236291502020000004.04 2374115052500532023911.04 238414500000000 23967155345000500006.9 24038150195500622024213.9 24166155400000000.8 24231150390000500007.8 243581552350000004.7 2448416075500783024711.5 2454215000000000 2464115000000000 24727148352000500007.04 24860155197500111024913.94 249161485490005000010.98 250501503990000007.98 251101452540000005.08 252914510950031025312.18 25324148461000500009.22 254631553060000006.12 25553155151500883025813.02 2560140110000000.22 257414500000000 25855155345000500006.9 25991160185500362026113.7 26043150350000000.7 26131150385000500007.7 262611552300000004.6 2631214585500944026711.7 2649016000000000 265114000000000 2666815500000000 26791160340000500006.8 2689145195500472027013.9 26984160350000000.7 27058155380000500007.6 271821602200000004.4 2729717050500111027311 27370155395000500007.9 274581552400000004.8 2757515585500783027811.7 276214000000000 2771014500000000 27821148352000500007.04 279511502020000004.04 2802514854500132028211.08 2814015000000000 28224148352000500007.04 28370155197500752028513.94 28472155420000000.84 28593165377000500007.54 286851602170000004.34 2873815067500242028911.34 2884815000000000 28962155345000500006.9 29053155190500352029213.8 2916145450000000.9 29231150395000500007.9 293171482470000004.94 2945815592500944029811.84 2958015500000000 2962114800000000 2973015000000000 29879155345000500006.9 29911402050000004.1 3005015055500232030211.1 3019616500000000 30263155345000500006.9 30379155190500142030513.8 30448150400000000.8 30534150390000500007.8 306311502400000004.8 307514595500974031111.9 3088516000000000 3093915000000000 3106615500000000 31110145355000500007.1 312331502050000004.1 3134815055500212031511.1 3141214800000000 31545150350000500007 316151482020000004.04 317814557500492031911.14 3184215000000000 31930150350000500007 32084160190500893032313.8 32170155350000000.7 3224115000000000 32355155345000500006.9 32475155190500964032813.8 32527148420000000.84 3269216500000000 3273615000000000 32845150350000500007 32966155195500762033113.9 33039150450000000.9 33112145400000500008 332731552450000004.9 3336145100500732033512 3343515000000000 33574155345000500006.9 3365715519050081033713.8 337361505400005000010.8 338731553850000007.7 33981452400000004.8 3409817070500372034211.4 3417215500000000 34287160340000500006.8 34320148192500924034713.84 34467155370000000.74 3454215000000000 3469716500000000 34753155345000500006.9 34821402050000004.1 3495115055500662035111.1 3501714800000000 35124148352000500007.04 3526015519750061035313.94 353191485490005000010.98 354251484010000008.02 355781552460000004.92 356451509650021035711.92 35765155441000500008.82 358511502910000005.82 35945150141500692036112.82 360414500000000 合计-1197.66 模模拟拟结结果果 可能值存储成本1197.66 1缺货成本921.60 2订货成本2275.00 3存货相关总成本4394.26 4 缺货次数缺货量缺货成本订货成本相关总成本 00008.7 00005.6 0002527.3 140404 00007.04 00004.04 库存量模拟 1112131415161718191 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 331 341 351 361 0 100 200 300 400 500 600 700 时间 缺货量模拟 1112131415161718191 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301 311 321 331 341 351 361 0 20 40 60 80 100 120 140 160 180 时间 0002525.84 110610.6010.6 00006.9 0002529 00000.9 00007.8 00004.7 0002526.7 1757.507.5 00007.04 0002528.84 00000.84 00007.64 00004.68 0002526.58 1767.607.6 00006.9 0002528.94 00000.84 112812.8012.8 115515.5015.5 00007 0002529 00000.9 00007.9 00004.8 0002526.7 1656.506.5 00007.04 00004.14 0002526.18 110110.1010.1 114814.8014.8 115515.5015.5 00006.9 0002528.8 00000.84 00007.84 00004.74 0002526.78 1616.106.1 114814.8014.8 115015015 00007.2 00004.24 0002526.24 1888.808.8 00006.8 0002528.7 00000.6 00007.64 00004.54 0002526.58 1696.906.9 00007.1 00004.1 0002525.8 111511.5011.5 00007 0002528.9 00000.94 110810.8010.8 00006.9 0002528.9 00000.9 110010010 00007.1 00004.2 0002526.1 110510.5010.5 114814.8014.8 00007.1 0002529 00000.8 00007.7 00004.5 0002526.4 1757.507.5 00006.9 0002528.8 00000.8 00007.84 00004.88 0002526.92 1646.406.4 00006.8 0002528.8 00000.7 00007.5 00004.4 0002526.5 1757.507.5 00007.1 0002529 00000.8 00007.8 00004.84 0002526.64 1686.806.8 00006.9 0002528.8 00000.8 110010010 116516.5016.5 00006.7 0002528.6 000010.5 00007.54 00004.44 0002526.48 1818.108.1 00006.9 0002528.9 00000.7 111511.5011.5 00006.9 0002528.94 00000.94 110310.3010.3 114514.5014.5 00007.1 00004.3 0002526.2 190909 00006.6 0002528.5 00000.4 00007.44 00004.48 0002526.52 1797.907.9 00007 0002529 00000.8 00007.8 00004.7 0002526.7 1636.306.3 115515.5015.5 00006.7 0002528.74 00000.54 00007.44 00004.44 0002526.24 1939.309.3 00007 0002529 00001.04 00007.94 00004.74 0002526.54 1636.306.3 00007.1 0002529 00000.8 110810.8010.8 00006.8 0002528.4 00000.4 00007.3 00004.3 0002526.3 1858.508.5 116016016 00006.9 0002529 00001.1 1939.309.3 00006.9 0002528.94 00000.98 00007.78 00004.78 0002526.78 1515.105.1 115015015 114814.8014.8 00007 00004.1 0002525.7 111311.3011.3 116016016 116016016 00007.04 00004.08 0002525.88 111111.1011.1 00007 0002528.9 00000.94 00007.64 00004.68 0002526.38 1868.608.6 00007 0002528.8 00000.9 00008 00005.1 0002527.1 1454.504.5 00006.9 00004.1 0002526.14 1989.809.8 00007.1 00004.14 0002526.04 1939.309.3 00006.9 0002528.8 00000.8 00007.4 00004.4 0002526.44 1737.307.3 115015015 115015015 00007 0002528.9 00000.7 00007.7 00004.74 0002526.78 1818.108.1 115515.5015.5 114514.5014.5 00007.04 00004.04 0002526.04 1939.309.3 00006.9 0002528.9 00000.8 00007.8 00004.7 0002526.5 1757.507.5 115015015 00007.04 0002528.94 000010.98 00007.98 00005.08 0002527.18 00009.22 00006.12 0002528.02 00000.22 113413.4013.4 00006.9 0002528.7 00000.7 00007.7 00004.6 0002526.7 1757.507.5 114014014 115515.5015.5 00006.8 0002528.9 00000.7 00007.6 00004.4 0002526 00007.9 00004.8 0002526.7 1555.505.5 114514.5014.5 00007.04 00004.04 0002526.08 1969.609.6 00007.04 0002528.94 00000.84 00007.54 00004.34 0002526.34 1838.308.3 00006.9 0002528.8 00000.9 00007.9 00004.94 0002526.84 1636.306.3 114814.8014.8 115015015 00006.9 00004.1 0002526.1 111011011 00006.9 0002528.8 00000.8 00007.8 00004.8 0002526.9 1656.506.5 115015015 115515.5015.5 00007.1 00004.1 0002526.1 1939.309.3 00007 00004.04 0002526.14 1939.309.3 00007 0002528.8 00000.7 111511.5011.5 00006.9 0002528.8 00000.84 112312.3012.3 115015015 00007 0002528.9 00000.9 00008 00004.9 0002527 150505 00006.9 0002528.8 000010.8 00007.7 00004.8 0002526.4 1858.508.5 00006.8 0002528.84 00000.74 111311.3011.3 116516.5016.5 00006.9 00004.1 0002526.1 1939.309.3 00007.04 0002528.94 000010.98 00008.02 00004.92 0002526.92 00008.82 00005.82 0002527.82 140.400.4 -921.622754394.26