Using Models to Calculate Data

A model is a set of interrelated equations that can assign results either to a variable or to a dimension value. For example, in a financial model, you can assign values to specific line items, such as gross.margin or net.income.

gross.margin = revenue - cogs

If an = command assigns data to a dimension value or refers to a dimension value in its calculations, then it is called a dimension-based equation. A dimension-based equation does not refer to the dimension itself, but only to the values of the dimension. Therefore, if the model contains any dimension-based equations, then you must specify the name of each of these dimensions in a DIMENSION command at the beginning of the model.

If a model contains any dimension-based equations, then you must supply the name of a solution variable when you run the model. The solution variable is both a source of data and the assignment target of model equations. It holds the input data used in dimension-based equations, and the calculated results are stored in designated values of the solution variable. For example, when you run a financial model based on the line dimension, you might specify actual as the solution variable.

Dimension-based equations provide flexibility in financial modeling. Since you do not need to specify the modeling variable until you solve a model, you can run the same model with the actual variable, the budget variable, or any other variable that is dimensioned by line.

Example 8-1 Creating a Model

Suppose that you define a model, called income.calc, that will calculate line items in the income statement.

define income.calc model
ld Calculate line items in income statement

After defining the model, you can use the MODEL command or the OLAP Worksheet editor to specify the contents of the model. A model can contain DIMENSION commands, = commands, and comments. All the DIMENSION commands must come before the first equation. For the current example, you can specify the lines shown in the following model.

DEFINE INCOME.CALC MODEL
LD Calculate line items in income statement
MODEL
DIMENSION line
net.income = opr.income - taxes
opr.income = gross.margin - (marketing + selling + r.d)
gross.margin = revenue - cogs
END

When you write the equations in a model, you can place them in any order. When you compile the model, either with the COMPILE command or by running the model, the order in which the model equations are solved is determined. If the calculated results of one equation are used as input to another equation, then the equations are solved in the order in which they are needed.

To run the income.calc model and use actual as the solution variable, you execute the following command.

income.calc actual

If the solution variable has dimensions other than the dimensions on which model equations are based, then a loop is performed automatically over the current status list of each of these "extra" dimensions. For example, actual is dimensioned by month and division, as well as by line. If division is limited to ALL, and month is limited to OCT96 to DEC96, then the income.calc model is solved for the three months in the status for each of the divisions.

How Dimension Values Are Treated in a Model

If a model contains an = command that assigns data to a dimension value, then the dimension is limited temporarily to that value, performs the calculation, and then restores the initial status of the dimension.

For example, a model might have the following commands.

DIMENSION line
gross.margin = revenue - cogs

If you specify actual as the solution variable when you run the model, then the following code is constructed and executed.

PUSH line
LIMIT line TO gross.margin
actual = actual(line revenue) - actual(line cogs)
POP line

This behind-the-scenes construction lets you perform complex calculations with simple model equations. For example, line item data might be stored in the actual variable, which is dimensioned by line. However, detail line item data might be stored in a variable named detail.data, with a dimension named detail.line.

If your analytic workspace contains a relation between line and detail.line, which specifies the line item to which each detail item pertains, then you might write model equations such as the following ones.

revenue = total(detail.data line)
expenses = total(detail.data line)

The relation between detail.line and line is used automatically to aggregate the detail data into the appropriate line items. The code that is constructed when the model is run ensures that the appropriate total is assigned to each value of the line dimension. For example, while the equation for the revenue item is calculated, line is temporarily limited to revenue, and the TOTAL function returns the total of detail items for the revenue value of line.