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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,596
Messages
5,832,640
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top