Monte Carlo Simulation Help

avengers321

New Member
Joined
Sep 29, 2014
Messages
1
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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

JB_Scotland

Board Regular
Joined
Dec 21, 2008
Messages
147
Try this

Code:
    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

Board Regular
Joined
Dec 21, 2008
Messages
147
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,612
Messages
5,523,891
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top