Modeling for Multiple Scenarios

Instead of calculating a single set of figures for a month and division, you might want to calculate several sets of figures, each based on different assumptions.

You can define a scenario model that calculates and stores forecast or budget figures based on different sets of input figures. For example, you might want to calculate profit based on "optimistic," "pessimistic," and "best-guess" figures.

Building a Scenario Model

To build a scenario model, follow these steps.

1. Define a scenario dimension.
2. Define a solution variable dimensioned by the scenario dimension.
3. Enter input data into the solution variable.
4. Write a model to calculate results based on the input data.

Suppose, for example, you want to calculate profit figures based on optimistic, pessimistic, and best-guess revenue figures for each division. The steps for building this scenario model are explained in the following example.

Example 8-2 Building a Scenario Model

You can call the scenario dimension scenario and give it values that represent the scenarios you want to calculate.

These commands give scenario the values optimistic, pessimistic and bestguess.

DEFINE scenario DIMENSION TEXT
LD Names of scenarios
MAINTAIN scenario ADD optimistic pessimistic bestguess

These commands create a variable named plan dimensioned by three other dimensions (month, line, and division) in addition to the scenario dimension.

DEFINE plan DECIMAL <month line division scenario>
LD Scenarios for financials

For this example, you need to enter input data, such as revenue and cost of goods sold, into the plan variable.

For the best-guess data, you can use the data in the budget variable. Limit the line dimension to the input line items, and then copy the budget data into the plan variable.

LIMIT scenario TO 'BESTGUESS'
LIMIT line TO 'REVENUE' 'COGS' 'MARKETING' 'SELLING' 'R.D'
plan = budget

You might want to base the optimistic and pessimistic data on the best-guess data. For example, optimistic data might be 15 percent higher than best-guess data, and pessimistic data might be 12 percent less than best-guess data. With line still limited to the input line items, execute the following commands.

plan(scenario 'OPTIMISTIC') = 1.15 * plan(scenario 'BESTGUESS')
plan(scenario 'PESSIMISTIC') = .88 * plan(scenario 'BESTGUESS')

The final step in building a scenario model is to write a model that calculates results based on input data. The model might contain calculations very similar to those in the budget.calc model shown earlier in this chapter.

You can use the same equations for each scenario or you can use different equations. For example, you might want to calculate the cost of goods sold and use a different constant factor in the calculation for each scenario. To use a different constant factor for each scenario, you can define a variable dimensioned by scenario and place the appropriate values in the variable. If the name of your variable is cogsval, then your model might include the following equation for calculating the cogs line item.

cogs = cogsval * revenue

By using variables dimensioned by scenario, you can introduce a great deal of flexibility into your scenario model.

Similarly, you might want to use a different constant factor for each division. You can define a variable dimensioned by division to hold the values for each division. For example, if labor costs vary from division to division, then you might dimension cogsval by division as well as by scenario.

When you run your model, you specify plan as the solution variable. For example, if your model is called scenario.calc, then you solve the model with this command.

scenario.calc plan

A loop is performed automatically over the current status list of each of the dimensions of plan. Therefore, if the scenario dimension is limited to ALL when you run the scenario.calc model, then the model is solved for all three scenarios: optimistic, pessimistic, and bestguess.