Probability of demand question? (montecarlo and whatif analysis)

[fomenter]

Demand

From To probaility

Low 5000 10000 25%

Medium 10000 15000 40%

Hi 15000 20000 35%


Period is 12 years, how am i supposed to randomize these?(the demand will be different for each year)

 

[Hui]

It looks like they want 3 scenarios

A High, Medium and Low demand

Each scenario will have a different distribution based on the demand distributions supplied

You could do it in 1 model using an If() or a Choose() function

 

[Hui]

Using the 68-95-99.7 rule

http://en.wikipedia.org/wiki/68-95-99.7_rule

You can get a rough estimate of what the distributions should be like


In your case:

From To probability

Low 5000 10000 25%

Medium 10000 15000 40%

Hi 15000 20000 35%


It really depends on what the Probability means?

Does it mean 25% chance of it being in or outside that range


If 25% means that there is a 25% chance that the value is between 5000 and 10000 then there is a 75% chance it is outside that range


1 SD is +/- 35%

so 25% or +/- 12.5% is about a third of SD

if 7500 - 5000 = 2500 is a third of an SD then 1 SD = 7500

That sound high


If 25% means that there is a 25% chance that the value is outside 5000 and 10000 then there is a 75% chance it is inside that range


1 SD is +/- 34%

1.5 SD is +/- 43%

2 SD is +/- 47.5%

so 75% or +/- 37.5% is about 1.2 SD

if 7500 - 5000 = 2500 is equal 1.2 SD

Then SD then 1 SD = 2100


That sound ok'ish


so I would use formulas like


Low: =NORMINV(RAND(),7500, 2100)

Med: =NORMINV(RAND(),12500, 1800)

High: =NORMINV(RAND(),17500, 1900)

 

[fomenter]

Thanks for the answer. Let me make it clear; 25% of prob that the demand will be between 5000-10000 and 40% prob that the demand between 10000/15000 and so on.Let's assume that the period is 12 years. Example: 25%*12=3 (it means in 3 years out 12, the demand will be between 5000 to 10000) or 40%*12=4.8 (it means in 4.8 years out of 12, the demand will be between 10000-15000. I just don't know hot to implement this into 12 years period by random. (Please note that we are asked to do randomization between demand values, which i already did. So, i press F9 to change the random number)


From To probability

Low 5000 10000 25%

Medium 10000 15000 40%

Hi 15000 20000 35%

 

[Hui]

Just use a rand() in each year

Then use that to determine which range it will fall in