Number of days needed to repair copier breakdown
The discrete probability distribution given in the problem is displayed on the worksheet entitled “repair time”. The cells in the range {C5:D8} comprise the lookup table named “repair_time”. This lookup table serves to provide the simulated number of repair days needed for each of the 52 randomly generated instances (on the worksheet entitled “Summary”.
Weeks between copier breakdown
The continuous distribution graph provided in the problem is represented by the equation f(x) = 1/18(x), where x = number of weeks, and 0 <= x >= 6. Using calculus (in a prior example in the text), we can find the value for x given a random number as follows – x = 6(^1/2). I have used this formula in cells C8:C59 to simulate the interval between breakdowns for each of the 52 randomly generated instances.
Revenue lost due to copier breakdown
The probability of copy volume is given in the problem as a uniform distribution between 2,000 and 8,000. I used intervals of 1,000, giving a uniform probability of 7/100. This uniform distribution is displayed on the worksheet entitled “copy volume”. This tab serves as the lookup table.
SUMMARY
I have combined each of the components into a comprehensive summary and included the descriptive statistics on the “Summary” tab.
Based on this simulation, JET copies should invest in a back-up copier
LIMITATIONS AND CONFIDENCE LIMITS
This simulation was only run for one 52-week period. To achieve results that more accurately represent the probabilistic nature of the variables (breakdowns, repair time, and copy volume), the simulation would need to be run many, many more times.
However, based on this singular simulation, we can be 95% confident that the actual annual revenue lost due to breakdowns will be between $10,482.63 and $14,362.42. I have illustrated these confidence limits on the “Descriptive Statistics” tab.