In Excel, a simulation table is a table that calculates multiple results based on the values of the variables you enter. Simulation tables can be used to analyze a variety of models and situations.
The steps to create a simulation table are as follows:
In Excel, create a blank worksheet.
In the worksheet, enter the input variables and the result of the calculation.
Use formulas to calculate the relationship between the input variables and the results of the calculations.
Use the simulation analysis function to calculate multiple results.
Here's an example of a simple simulation operation:
Input variable |Result--- Sales = **Quantity Quantity |Profit = Sales - Cost
In this simulation table, the input variables are ** and quantity, and the result is sales and profit. Use formulas to calculate the relationship between the input variables and the calculated results as follows:
Sales = A2 * B2 Profit = A2 * B2 - C2
To use the simulation analysis feature to calculate multiple results, you can use the following steps:
Select the area of cells that you want to simulate.
Click the Data tab, and then click Simulation Analysis.
In the Simulation Analysis dialog box, set the simulation analysis parameters.
Click OK.
In the Simulation Analysis dialog box, you can set the following parameters:
Input variable |: Sets the range of the input variables.
Output variables: Specifies the range of the calculation result.
Number of repetitions: Sets the number of simulated analyses.
Random number generator: Set up a random number generator.
For example, to simulate a 100-piece to $200 and a quantity from 100 to 200 pieces to calculate 100 sales and profits, you can set the following parameters:
Input variables: A2:A3, B2:B3 Output variables: C2:C3 Number of repetitions: 100 Random Number Generator: Microsoft Excel Random Number Generator
After clicking OK, Excel will automatically calculate the sales and profit 100 times. The result of the calculation is displayed in the range of output variables.
Here's an example of the results of the simulation analysis:
Sales |Profit--- 10000 | 200012000 | 240020000 | 4000
With the simulation arithmetic table, multiple results can be quickly calculated to analyze models and situations.