vert line

The Scenario Inspector creates tables which take values for one or two variables, and the result of each value or set of values in a given formula.

Mesa scenarios allow you to use your spreadsheet to make calculations based on more than a single formula and set of variables. For example, you might want to calculate the monthly payments and total payments on a loan for payment plans with different interest rates. You could also use scenarios to find the orbital angular momentum of a body given different theoretical values of its semi major axis and orbital period. Scenarios are especially useful when you are concerned with a set of predicted or theoretical values and varying conditions.

Mesa has two types of scenarios.

Single Input scenarios show the result of changing the value of a single variable in different formulas.

Double Input scenarios compute the result of a single two-variable formula for different values of each variable.

Both types of scenarios are created using the Scenario Inspector.

The Scenario Inspector creates a scenario table. A single input scenario will create a table which shows the result of using each different value of the variable in each formula. Double input scenario tables show the result of each different set of variables in the equation.

Using the Scenario Inspector

Mesa scenarios are calculated using the Scenario Inspector. To open the Scenario Inspector select Tools -> Inspector -> Scenario Inspector... in the menu. Give the Scenario a name by typing it into the space at the bottom. Enter the cell name for the input cell or cells, and the range of the scenario table. Once the information is entered, click on the Add button. The name of the Scenario will appear in the list in the middle of the Scenario Inspector. You can remove, change, or run a Scenario by clicking on its name in the list, which will highlight it, and then clicking the appropriate button.

Mesa runs all "Run before each recalc" Sybase queries before running each data point in a scenario. Scenarios recalculate the worksheet after they execute, even if auto-recalc is off.

Creating Single Input Scenarios

To create a single input scenario, choose a column to enter the different values of the variable into. Leave the first cell of the column blank and enter the values starting with the second cell of the column. For example, if you wished to make a scenario using ten different values of a variable starting in the upper left corner of the spreadsheet you would leave cell A1 blank and enter the values in cells A2 through A11.

Next, enter the your formulas in the row corresponding to the blank cell at the top of the data column, and starting in the next column over. In our example, if you had five formulas you would enter them in cells B1, C1, D1, E1, and F1. When entering the formulas, refer to the variable using a cell name. This cell is the input cell, and can be any cell on the spreadsheet that is not part of the scenario table and does not contain a formula. You can also use the blank cell at the top of the input column. For example, you might enter the formula =A1^2 in cell B1 to find the square of the values in column A.

Note that as a result of Mesa's normal formula computation, the cell you enter the formula in will display the value of the formula with the variable equal to the contents of the input cell.

Once you have entered the variable values and the formulas, use the Scenario Inspector to run the scenario. The Scenario Inspector will create a table with the values of the formulas computed for each of the variable's values in the appropriate cell. For example, cell D9 would contain the value of the formula in column D computed with the variable value in row 9.

The table is a range on the worksheet that includes the row and column with the input values and formulas. In our example above the range of the scenario would be A1:F11.

Creating Double Input Scenarios

To create a double input scenario, choose a column to enter one set of variables into, and a row to enter the other set of variable. The formula is entered in the cell where the two intersect. For example, if you wanted to place your scenario in the upper left corner of the spreadsheet, you would enter one set of variables in column A starting with cell A2, and one set of variables in row 1 starting with cell B1. The formula would be entered into cell A1. If there were ten values of each variable, the range of this scenario table would be A1:K11. When the scenario is run, a table will be created in the range with the value of the formula using each pair or input values in the appropriate cell. For example, cell C6 would contain the value of the formula using the input values in column C and row 6.

When you enter the formula into the formula cell, refer to the variables using two input cells. The two input cells may be any two cells on the worksheet that are not part of the scenario range and do not contain formulas. As long as these two conditions are met, the input cells can be anywhere on the sheet, and do not have to be blank cells. Once you have entered both the input values and the formula use the Scenario Inspector to run the scenario.

Also go to:

Index help

Contents help