Modeling for Multiple Scenarios

Calculating several sets of figures

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, you 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 next few sections.

Defining a scenario dimension

You can call the scenario dimension SCENARIO, and give it values that represent the scenarios you want to calculate. For this example you can give it the values OPTIMISTIC, PESSIMISTIC and BESTGUESS.

define scenario dimension text
ld Names of scenarios
maintain scenario add optimistic pessimistic bestguess

Defining a solution variable dimensioned by the scenario dimension

For this example the solution variable should be dimensioned by DIVISION as well as by SCENARIO. Like the BUDGET variable in the demo analytic workspace, your solution variable can also be dimensioned by MONTH and by LINE. You can call the variable PLAN.

define plan decimal <month line division scenario>
ld Scenarios for financials

Entering input data into the solution variable

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')

Writing a model to calculate results based on the input data

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.

Related information

For more information, see the following table.

IF you want documentation about . . .	THEN see . . .
overall understanding of the modeling capabilities of the OLAP DML,	the entry for the MODEL command in OLAP DML Reference
individual OLAP DML commands,	the entry for the command in OLAP DML Reference