Page 554 -

534 CHAPTER 12 SIMULATION

simulation results displayed for all 500 trials. Let us describe the details of the Excel
worksheet that provided the PortaCom simulation.
First, the PortaCom data are presented in the first 14 rows of the worksheet. The
selling price per unit, administrative cost, and advertising cost parameters are
entered directly into cells C3, C4 and C5. The discrete probability distribution for
the direct labour cost per unit is shown in a tabular format. Note that the random
number intervals are entered first followed by the corresponding cost per unit. For
example, 0.0 in cell A10 and 0.1 in cell B10 show that a cost of $43 per unit will be
assigned if the random number is in the interval 0.0 but less than 0.1. Thus,
approximately 10 per cent of the simulated direct labour costs will be $43 per unit.
The uniform probability distribution with a smallest value of E80 in cell E8 and a
largest value of E100 in cell E9 describes the parts cost per unit. Finally, a normal
probability distribution with a mean of 15 000 units in cell E13 and a standard
deviation of 4500 units in cell E14 describes the first-year demand distribution for
the product. At this point we are ready to insert the Excel formulas that will carry
out each simulation trial.
Simulation information for the first trial appears in row 21 of the worksheet. The
cell formulas for row 21 are as follows:

Cell A21 Enter 1 for the first simulation trial
Cell B21 Simulate the direct labour cost per unit*
¼VLOOKUP(RAND(),$A$10:$C$14,3)
Cell C21 Simulate the parts cost per unit (uniform distribution)
¼$E$8+($E$9–$E$8)*RAND()
Cell D21 Simulate the first-year demand (normal distribution)
¼NORMINV(RAND(),$E$13,$E$14)
Cell E21 The profit obtained for the first trial
¼($C$3–B21–C21)*D21–$C$4–$C$5
Cells A21:E21 can be copied to A520:E520 in order to provide the 500 simulation
trials.
Ultimately, summary statistics will be collected in order to describe the results of
the 500 simulated trials. Using the standard Excel functions, the following summary
statistics are computed for the 500 simulated profits appearing in cells E21 to E520.

Cell E523 The mean profit per trial ¼ AVERAGE(E21:E520)
Cell E524 The standard deviation of profit ¼ STDEV(E21:E520)
Cell E525 The minimum profit ¼ MIN(E21:E520)
Cell E526 The maximum profit ¼ MAX(E21:E520)
Cell E527 The count of the number of trials where a loss occurred
(i.e., profit < E0) ¼ COUNTIF(E21:E520,‘‘<0’’)
Cell E528 The percentage or probability of a loss based on the 500
trials ¼ E527/500
The F9 key can be used to perform another complete simulation of PortaCom. In
this case, the entire worksheet will be recalculated and a set of new simulation
results will be provided. Any data summaries, measures, or functions that have been
built into the worksheet earlier will be updated automatically.

*The VLOOKUP function generates a random number using the RAND() function. Then, using the table
defined by the region from cells $A$10 to $C$14, the function identifies the row containing the RAND()
random number and assigns the corresponding direct labour cost per unit shown in column C.

Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.