An alternative to using Excels built-in functions to generate live random numbers that change each time that

Question:

An alternative to using Excel’s built-in functions to generate “live” random numbers that change each time that a model is recalculated is to use Excel’s Random Number Generator tool found under Data | Data Analysis. This tool generates random numbers according to a specified distribution and then pastes them to a range in Excel. An advantage of using such “static” random numbers is that you can use the same set of random numbers in a model when comparing different decisions. In this way, differences in outputs can be more fully attributed to the decisions themselves rather than differences in the random numbers generated between runs of the model.

For the Simkin’s Hardware Store model in Figure 10.9, use Excel’s Random Number Generator to generate 10 “Variables” of 25 “Random Numbers” each for both the Demand in column E and the Lead Time in column K. Use a discrete distribution to generate both sets of numbers. Now you have enough random numbers to plug into the model to run it for 10 trials. For each trial, copy/paste new sets of your generated random numbers into the respective Demand and Lead Time columns and record the Total Cost per month. Then change your policy to Q = 20 and R = 8 and record the Total Cost per month for that set of decisions. Repeat this procedure for all 10 trials. Compute the average cost for each policy (Q = 10, R = 5 vs. Q = 20, R = 8), averaged over the 10 trials. Which policy performed better? This problem illustrates the more general approach of using the same random number “seed” when using simulation to compare policies. If you wanted to have 200 or more trials, you might consider utilizing macros or VBA code to assist.

Figure 10.9