How do you use Excel to create random variates of a variable and do simulation to find optimal value(s)?

Question

Suppose I have discrete probabilities for four values representing demand, for example .2 that D=5, .3 that D=6, .4 that D=7, and .1 that D=8. What am I actually randomizing? The D values right? So I'd get for example 100 simulations and all 100 each one would have 4 values for D? Or is it that each one would have only one value, either 5, 6, 7, or 8? What would I set as the seed? Do I need to highlight any cells before I do the rand or sim command?

After I do the randomization, how do I use that to do the simulation. Does the simulation represent an exemplary data set? Of what? How do I use that info to recommend a level of inventory?

I intend to learn these concepts only; this is not an exam question. I am adhering to my uni's academic honesty policies. Thanks in advance; cheers!

Answer 1

Each of the simulations would have one of the possible values for D.
It's not too hard to set this up in Excel. In columns B and C, enter the PMF and D value for each case. Then in column A, set the first entry to 0 and enter the formula (e.g. for cell A3) "=A2+B2", filling down to the end of the list. This should give you in each row the CMF for the previous value of D. Create a name for the range of your D-values, from column A to column C. I'll call it "Table".

Now, for however many simulations you want, fill that many cells with the formula "=VLOOKUP(RAND(), Table, 3, TRUE)". (Note that the values will change every time the sheet is recalculated.)

This represents an example data set of 100 (or whatever number) observations of D. How you use this will depend on your specific problem. Presumably you'll start off by seeing how many of each value of D you got.

Answer 2

If you want to generate random variables by a pdf, you need to use its z-function.
D , . f . , . z
5 , 0.2 , 0.2
6 , 0.3 , 0.5
7 , 0.4 , 0.9
8 , 0.1 , 1.0
This is a short table, so rather than set up a look-up, i would proceed this way (I don't know the sim function, as I don't have Excel). First, go into Tools and set calc to manual (requires pressing F9 key to calculate--if you don't do this the spreadsheet will generate an entirely different data set every time you enter something)
Pick a cell, say B2. In that cell enter =RAND().
In C2 enter =if(B2 < 0.2,5,if(B2 < 0.5,6, if(B2 < 0.9,7,8))). Copy the pair of cells down for as much data as desired. I know that Excel has a seed number generator, but Works doesn't, so I "kick" the generator by using =Mod(NOW()*RAND(),1)

Having set up 100 or so draws per unit time You can use any number of approaches to arrive at an inventory level.

Answer 3

Each simulation should take one of the values. But your observation (the Monte Carlo process) should aggregate a number of simulations. Then you pick a strategy based on the distribution of the results. Have a look at Monte Carlo simulation on the web, usual places should do it.

It's possible to do all this with raw Excel but it's a bit tedious to generate PDF's from RAND() - you need to do some kind of lookup function. Plugins like @RISK make it all easier. Have a look at their website, probably some white papers in there to help you. Good luck.