Results 1 to 4 of 4

Thread: Monte Carlo Simulation Help

  1. #1

    Monte Carlo Simulation Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    I've attached an example of what I'm working on.

    Monte Carlo.xlsx

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    Need to describe the info a bit. I can't follow what you want done.
    B2:C8 is historical demand. So if a year is simulated this distribution must be returned. (I don't think you are maintaining this rule as a side note.)

    Your supply is always E24? Fixed supply every week, should this not be variable?

    Then I would change E14 (and below) to be
    =B14-D14+IF(E13<0,0,MIN(B13,E13))
    i.e. this week surplus and the surplus from last week provided it was less than the new supply last week. Assuming a first in first out

  3. #3
    Sorry, let me be a little clearer.

    - B2:C8 is historical demand and you're right, over a year the distribution should approximate that historical demand. The supply of 62 is the amount reordered each week. This is a very simple simulation, so I'm not worried about simulating for a whole year, just 10 weeks.

    Say, for example in week 1 we order 62 loaves of bread and our variable demand is 61. We carry over the loaf of bread that wasn't sold into week 2, so no week two will have 63 loaves available to sell (the 62 we order + the one held from week 1). Say demand in week 2 is 62. Well, we have that one loaf of bread that wasn't sold since the demand wasn't there. So now I need a way to identify these loaves that are held over one week but not sold.

    I've attached a screenshot of the excel file I'm working with, the attachment I uploaded earlier wasn't exactly right
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Screenshot 2014-09-30 09.31.42.png 
Views:	9 
Size:	93.1 KB 
ID:	2699   Click image for larger version. 

Name:	Screenshot 2014-09-30 09.33.40.jpg 
Views:	7 
Size:	68.1 KB 
ID:	2700  
    Last edited by avengers123; 2014-09-30 at 03:34 PM. Reason: attached corrected excel file image

  4. #4
    Andddd that image wasn't right either. Sorry, guys.Click image for larger version. 

Name:	Screenshot 2014-09-30 09.33.40.jpg 
Views:	7 
Size:	68.1 KB 
ID:	2701

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •