EXCEL財(cái)務(wù)模型庫-存貨組合模擬

1、基基本本信信息息每每日日需需求求量量提提前前期期 單位存儲(chǔ)成本0.02概率累計(jì)概率隨機(jī)數(shù)可能值概率累計(jì)概率隨機(jī)數(shù) 每次訂貨成本250.030.0301400.110.110 單位缺貨成本0.10.090.1231450.650.7611 期初庫存6000.160.28121480.160.9276 訂貨點(diǎn)2000.240.52281500.08192 訂貨批量5000.290.8152155 0.110.9281160 0.050.9792165 0.03197170 模擬過程 時(shí)間隨機(jī)數(shù)需求量庫存量訂貨批量隨機(jī)數(shù)提前期到貨量控制值訂貨次數(shù)存儲(chǔ)成本 0600 1941654350000008.

2、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 時(shí)間 缺貨量模擬 1112131415161718191 101 111

3、 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 時(shí)間 782160425002020910.84 81314800000000 952155345000500006.9 101014520050038201214 1171155450000000.9 1268155390000500007.8 13631552350000004.7 14321508550022201611.

4、7 158416000000000 1613148352000500007.04 178416019250025201913.84 1838150420000000.84 1986160382000500007.64 20271482340000004.68 21561557950069202311.58 226315500000000 2378155345000500006.9 241514819750096402813.94 2560155420000000.84 269717000000000 277415500000000 2844150350000500007 29461502005

5、0026203114 3052155450000000.9 3141150395000500007.9 32781552400000004.8 33551558550051203511.7 344415000000000 3520148352000500007.04 36111452070000004.14 37201485950098404111.18 389116000000000 392014800000000 405615500000000 4171155345000500006.9 425615519050071204413.8 4327148420000000.84 4450150

6、392000500007.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

7、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

8、 74101452100000004.2 75551555550086307811.1 769016000000000 772514800000000 789145355000500007.1 796115520050023208114 8084160400000000.8 8169155385000500007.7 82871602250000004.5 83611557050025208511.4 84314500000000 8557155345000500006.9 866215519050065208813.8 8731150400000000.8 88161483920005000

9、07.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

10、103141482420000004.84 1048716082500342010611.64 1052915000000000 10670155345000500006.9 10760155190500984011113.8 10850150400000000.8 109314000000000 1109616500000000 11194165335000500006.7 1125515518050071011313.6 113751555250005000010.5 114141483770000007.54 115711552220000004.44 11622148745001520

11、11811.48 1175415500000000 11862155345000500006.9 11947150195500913012213.9 12082160350000000.7 1215115000000000 12261155345000500006.9 12319148197500954012713.94 12434150470000000.94 1252815000000000 1261114500000000 1279145355000500007.1 12811402150000004.3 1297615560500152013111.2 1303215000000000

12、 13198170330000500006.6 13261155175500562013413.5 13368155200000000.4 13421148372000500007.44 135211482240000004.48 1361914876500152013811.52 1377415500000000 13848150350000500007 13940150200500342014114 14087160400000000.8 14137150390000500007.8 142781552350000004.7 1433615085500843014611.7 1442014

13、800000000 1456415500000000 14692165335000500006.7 14725148187500272014913.74 14883160270000000.54 14965155372000500007.44 150421502220000004.44 1518116062500362015311.24 1527015500000000 15345150350000500007 15445150200500652015614 15524148520000001.04 15680155397000500007.94 157851602370000004.74 1

14、588316077500332016011.54 159314000000000 1603145355000500007.1 16159155200500863016414 16292160400000000.8 1632014800000000 16481160340000500006.8 16598170170500702016713.4 16632150200000000.4 16764155365000500007.3 168331502150000004.3 1693415065500813017211.3 1703715000000000 1718416000000000 1726

15、1155345000500006.9 1738145200500793017614 1746145550000001.1 1751914800000000 17676155345000500006.9 17716148197500332017913.94 17819148490000000.98 17987160389000500007.78 180461502390000004.78 1813315089500994018511.78 182014000000000 1832815000000000 1841414800000000 18551150350000500007 18612145

16、2050000004.1 1879817035500944019110.7 1881814800000000 1898616000000000 1908316000000000 19119148352000500007.04 192251482040000004.08 1939016044500322019510.88 1947215500000000 19550150350000500007 19673155195500412019813.9 19720148470000000.94 19894165382000500007.64 199241482340000004.68 20093165

17、69500752020211.38 2017115500000000 20249150350000500007 20385160190500162020513.8 2048145450000000.9 20511145400000500008 206101452550000005.1 20734150105500282020912.1 2084915000000000 20972155345000500006.9 21011402050000004.1 2112514857500242021311.14 2127715500000000 2136145355000500007.1 214231

18、482070000004.14 2156015552500462021711.04 216814500000000 21760155345000500006.9 21867155190500542022013.8 21949150400000000.8 22098170370000500007.4 221381502200000004.4 2222614872500954022611.44 2231214500000000 2243715000000000 2253515000000000 22637150350000500007 22776155195500582022913.9 22891

19、160350000000.7 22951150385000500007.7 230151482370000004.74 2312514889500974023511.78 2329817000000000 2336215500000000 234914500000000 23525148352000500007.04 236291502020000004.04 2374115052500532023911.04 238414500000000 23967155345000500006.9 24038150195500622024213.9 24166155400000000.8 2423115

20、0390000500007.8 243581552350000004.7 2448416075500783024711.5 2454215000000000 2464115000000000 24727148352000500007.04 24860155197500111024913.94 249161485490005000010.98 250501503990000007.98 251101452540000005.08 252914510950031025312.18 25324148461000500009.22 254631553060000006.12 2555315515150

21、0883025813.02 2560140110000000.22 257414500000000 25855155345000500006.9 25991160185500362026113.7 26043150350000000.7 26131150385000500007.7 262611552300000004.6 2631214585500944026711.7 2649016000000000 265114000000000 2666815500000000 26791160340000500006.8 2689145195500472027013.9 26984160350000

22、000.7 27058155380000500007.6 271821602200000004.4 2729717050500111027311 27370155395000500007.9 274581552400000004.8 2757515585500783027811.7 276214000000000 2771014500000000 27821148352000500007.04 279511502020000004.04 2802514854500132028211.08 2814015000000000 28224148352000500007.04 283701551975

23、00752028513.94 28472155420000000.84 28593165377000500007.54 286851602170000004.34 2873815067500242028911.34 2884815000000000 28962155345000500006.9 29053155190500352029213.8 2916145450000000.9 29231150395000500007.9 293171482470000004.94 2945815592500944029811.84 2958015500000000 2962114800000000 29

24、73015000000000 29879155345000500006.9 29911402050000004.1 3005015055500232030211.1 3019616500000000 30263155345000500006.9 30379155190500142030513.8 30448150400000000.8 30534150390000500007.8 306311502400000004.8 307514595500974031111.9 3088516000000000 3093915000000000 3106615500000000 311101453550

25、00500007.1 312331502050000004.1 3134815055500212031511.1 3141214800000000 31545150350000500007 316151482020000004.04 317814557500492031911.14 3184215000000000 31930150350000500007 32084160190500893032313.8 32170155350000000.7 3224115000000000 32355155345000500006.9 32475155190500964032813.8 32527148

26、420000000.84 3269216500000000 3273615000000000 32845150350000500007 32966155195500762033113.9 33039150450000000.9 33112145400000500008 332731552450000004.9 3336145100500732033512 3343515000000000 33574155345000500006.9 3365715519050081033713.8 337361505400005000010.8 338731553850000007.7 33981452400

27、000004.8 3409817070500372034211.4 3417215500000000 34287160340000500006.8 34320148192500924034713.84 34467155370000000.74 3454215000000000 3469716500000000 34753155345000500006.9 34821402050000004.1 3495115055500662035111.1 3501714800000000 35124148352000500007.04 3526015519750061035313.94 353191485

28、490005000010.98 354251484010000008.02 355781552460000004.92 356451509650021035711.92 35765155441000500008.82 358511502910000005.82 35945150141500692036112.82 360414500000000 合計(jì)-1197.66 模模擬擬結(jié)結(jié)果果 可能值存儲(chǔ)成本1197.66 1缺貨成本921.60 2訂貨成本2275.00 3存貨相關(guān)總成本4394.26 4 缺貨次數(shù)缺貨量缺貨成本訂貨成本相關(guān)總成本 00008.7 00005.6 0002527.3 1

29、40404 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 時(shí)間 缺貨量模擬 1112131415161718191 101 111 121 131 141 151 161 171 181 191 201 211 221 231 241 251 261 271 281 291 301

30、 311 321 331 341 351 361 0 20 40 60 80 100 120 140 160 180 時(shí)間 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

31、.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 0

32、0007.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

33、.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 000

34、00.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 000

35、07.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 1

36、11311.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 0000