|
> Once we enter a formula in the worksheet, we can perform a hypothesis analysis to see how the results are affected when some values in the formula are changed. Simulating a two-dimensional table provides a shortcut to operate on all changes.
A simulated values table is a cell area that displays the result of replacing different values in one or more formulas. There are two types of simulated two-dimensional tables: one-input simulated two-dimensional table and two-input simulated two-dimensional table. In a single input simulation table, you can type different values for a variable to view its impact on one or more formulas. In a dual-input simulation table, you can enter different values for two variables and view their impact on a formula.
13.2.1 Single-Input simulated tables
When a variable in the formula is replaced with different values, this process generates a data table showing its results. We can use a column-oriented simulated two-dimensional table or a row-oriented simulated two-dimensional table.
Column-oriented simulated two-dimensional table
For example, we simulate the model in Figure 13-3, assuming that the variable costs are 10%, 15%, 20%, 25%, and 30% of the fixed costs, respectively, what will happen to the company's profit if other conditions do not change?
The procedure is as follows:
(1) In the input cell of a single column, enter the sequence of values to be replaced by Excel. We enter the sequence below in cell A6. In the cell on the right of the preceding row and value column of the First value, type a formula to reference the input cell. The input cell can be any empty cell on the worksheet, we specify the "A5" cell as the input cell. Enter the appended formula to the right of the first formula in the same row, that is, enter "= a2 + A3-B2 * A5-B2 ". As shown in figure 13-4.
(2) Select the rectangular area that contains the formula and the sequence of replacement values, as shown in 13-5.
(3) run the "simulate External table" command in the "data" menu to display the 13-6 dialog box.
(4) In the "input cell in the reference column" box, enter the variable cell address. Here we enter the "A5" cell. Click OK. Then, Excel replaces all the values in the input cell and displays the results on the right of each input value, as shown in 13-7. You can also provide a new value to replace the original input value on the worksheet, so that excel will use the new value for re-calculation. The process of simulating a two-dimensional table based on rows is similar to that of columns. You can perform the exercises on your own.
If you want to observe the influence of changes in one input value on multiple formulas, you can add one or more formulas in an existing single input data table. The procedure is to enter a new formula in a row or column that contains an existing formula, select a region that contains the formula and input value, and then run the "simulate a two-dimensional table" command.
13.2.2 double-input simulated tables
In complex situations, we can also use two variables to simulate various situations. For example, when the interest in the above example is changed to a variable interest rate, how should the profit change under different circumstances. When two variables in the formula are replaced with different values, this process generates a data table showing the results.
Enter a formula for referencing the replacement value in a cell. The formula should reference two input cells, or directly or reference other cells. These cells reference the input cells. The input cells are the cells whose values will be replaced, for example, we enter the following formula "= A1 + A2-B2 * A5-B3 * A4" in cell B5 ".
Start from the cell below the formula, enter the value to be replaced in cells in the same column as the formula, start from the cell on the right of the formula, and enter the value to be replaced in cells that are in the same row as the formula, as shown in Figure 13-8.
Select the cell area of the row and column that contain the formula and input values. Run the "simulate External table" command in the "data" menu to display the "simulate External table" dialog box. In the input cell of the reference row box, enter the address of the variable cell. Here, we enter cell A4. In the "input cells in the reference column" box, enter the "A5" cell. Click OK.
After you press the "OK" button, Excel replaces all the values in the input cell and displays the result as a table, as shown in 13-9. We can also provide a new value to replace the original input value on the worksheet, so that excel will use the new value for re-calculation.
13.2.3 clear the results from the simulated computation table
You can clear unnecessary computation results from the worksheet. Since the calculation result is in an array, we cannot clear a single value, but must clear all values. Note that formulas and input values cannot be selected. Otherwise, Excel will clear the entire table including formulas and input values.
The procedure is as follows:
(1) All result values in the selected data table.
(2) In the "edit" menu, select "clear" and then select the "all" command. |
