Monte Carlo Simulation Help

[avengers321]

Hi everyone,

I'm having some trouble with running a Monte Carlo simulation. I'm at a complete loss on where I should go next. The situation is a variable demand based on a randbetween function. However, the simulation is representing a business where if supply for a perishable good (say, a loaf of bread) > demand for the good, the excess perishable goods for that week are able to carry over and be sold the next week. However, if the excess is kept two weeks or more, it must be disposed of. I've worked on it for a few hours, trying different formulas but I can't seem to get anywhere.

 

[JB_Scotland]

Try this

    A           B       C       D       E       F       G
1   Week Start	Stock	Fresh	Total	Demand	Waste	Carried Forward
2   01/01/2014	500	1000	1500	812	0	688
3   08/01/2014	688	1000	1688	537	151	1000
4   15/01/2014	1000	1000	2000	617	383	1000
5   22/01/2014	1000	1000	2000	1249	0	751
6   29/01/2014	751	1000	1751	717	34	1000
7   05/02/2014	1000	1000	2000	500	500	1000
8   12/02/2014	1000	1000	2000	806	194	1000
9   19/02/2014	1000	1000	2000	780	220	1000
10  26/02/2014	1000	1000	2000	1231	0	769

A2 is week start date and in A3 formula is "=A1+7" copy down
B2 is starting stock (e.g. 500 loaves) and formula in B3 "=G3" (C/F Stock) and copy down
Fresh is weekly re-supply (e.g. 1000 loaves) copy down
Formula D2 "=B2+C2" is total of week old stock plush fresh, copy down
Formula E2 "=MIN(D2,RANDBETWEEN(500,1500))" is demand, a random number between 500 loaves and 1500 loaves but not exceeding actual stock
Formula F2 "=IF(E2<B2,B2-E2,0)" is waste i.e. week old stock in excess of demand
Formula G2 "=MAX(D2-E2-F2,0)" is carried forward stock i.e. total stock - demand - waste

I'm making the assumption that old stock is sold before fresh stock.

 

[JB_Scotland]

Slightly modified to permit variation of limits here: https://www.dropbox.com/s/ea4iwy2s4f7rulr/Perishable%20Stock%20Simulation.xlsx?dl=0

Correction to previous post:
F2 formula "=IF(E2<B2,B2-E2,0)" waste
G2 formula "=MAX(D2-E2-F2,0)" carried forward

 

[JB_Scotland]

Sorry having trouble posting the F2 and G2 formulas, editor seems to jumble them up together. I'm using a Mac so don't have access to Excel Jeanie etc.

Dropbox has the correct formulas.