Using SIMTABLE
The SIMTABLE function is intended to change a variable in the simulation so that each round of the simulation can be used to evaluate a different scenario. This automates the use of simulation for what-if questions or to create a set of possible outcomes to a situation.
In this example, we will use the SIMTABLE function to find the optimal quantity of newspapers to buy. For the purchase quantity in our spreadsheet, we will replace '50' with the following formula in Profit!B16:
Profit!B16 = SIMTABLE(50,60,70,80,90)
Each entry in the list of the SIMTABLE arguments is a value that will be used for the purchased quantity. Each entry corresponds to one round of simulation, as used in Figure 6-6. In this example there are 5 entries to the SIMTABLE list, so '5' will be entered into the 'Last Round #' option in the Options tab of the Simulation dialog.
When this simulation is run with 5 rounds, the summary of results will have one entry for each round, with each round using a different entry from the SIMTABLE function for the purchase quantity. The results for the various rounds can be previewed using the 'Prev. Sim.' and 'Next Sim.' buttons. The output also has one table for each round of the simulation.
As seen in Figure 6-11, each value in the original SIMTABLE statement corresponds to a simulation round, with the Purchase Quantity taking on the value from the SIMTABLE list. The analyst can then record the Profit statistics (mean, variance, skewness, kurtosis, 95% confidence intervals) and determine if the simulation results are of sufficient resolution for the analysts purposes.
The use of SIMTABLE to change parameters within the simulation provides a convenient method to do what-if analysis, and analyze the results as a whole.