8
Modeling Data

Chapter summary

This chapter describes how to create and use models.

Who should read this chapter

You need to know the information in this chapter if you are:

• An Administrator who creates and maintains models for personal use and for distribution to other users of the shared database that you administer

• A Budget workstation user who creates and maintains models for personal use and uses models that have been distributed to you

• An Analyst workstation user who needs a basic understanding of models and their uses

List of topics

This chapter includes the following topics:

• About Models

• Creating and Modifying Models

• Model Equation Syntax

• Modeling Functions

• Examples of Models

About Models

Definition: Models

Models are sets of up to 8,000 interrelated equations that can be used to calculate new data values. They can also be used in worksheets for performing what-if analyses. Models are:

• Database objects that you create and maintain

• Separate from the financial data items whose data they calculate

To use a model to calculate new data for a financial data item, you include the model in a solve definition, and associate the solve definition with a financial data item. You then run the solve definition to calculate the data. The same model can be used to calculate data for multiple financial data items, as long as they have the same dimensions.

Using models to manage financial data

Financial Analyzer models can be used to manage a wide range of financial data issues including:

• Simple relationships between dimension values

• Complex reference calls involving data stored in separate data cubes

• Simultaneous equations where the sequence of multiple calculations is essential to deriving the desired results

Who can work with models?

Administrators

As an administrator, it is your responsibility to maintain the models that your users access in the shared database that you administer. When you create or modify a model and want other users to be able to access it, you must distribute the model to the shared database and to other users. Typically, administrators create and distribute the basic line item models for their groups.

Budget workstation users

As a Budget workstation user, you can create and modify models for your own personal use. You can modify models that your administrator has created for you, but you cannot save the modifications unless you assign a new name to the modified model, retaining the original.

Creating and Modifying Models

Creating models

You choose Model from the Maintain menu to create a new model. This opens the Maintain Model dialog box where you can define a model. You can create an entirely new model, or you can create a model based on an existing model.

Process for creating models

Follow these general steps to create a model.

1. Create and name the database object that stores the model definition.

2. Specify the financial data item or items whose data the model will calculate.

3. Define equations and associate them with the values of the financial data item's dimensions. For more information, see "Model Equation Syntax.

4. If necessary, specify model options that determine the processing order for simultaneous equations. For more information, see "Specifying how simultaneous equations are solved.

5. Compile and save the model. For more information, see "Compiling and saving models.

Specifying how simultaneous equations are solved

A simultaneous equation occurs when an equation in a model indirectly depends on itself as the result of the dependencies among other equations in the model. A dependency occurs when the expression on the right-hand side of the equal sign in one equation refers to the assignment target of another equation.

You control how simultaneous equations are solved by selecting options in the Model Options dialog box. The model options enable you to specify the following:

• The solution method to use

• The factors to use in testing for convergence and divergence

• The maximum number of iterations to perform

• The action to take if an equation diverges or fails to converge

Compiling and saving models

When you have created the model, you must compile and save it. You can then include the compiled model as part of solve and group solve definitions and apply it to worksheet data.

Modifying models

You choose Model from the Maintain menu to modify a model. This opens the Maintain Model dialog box, where you can choose the model that you want to modify. From this starting point you can perform the following functions:

• You can give a model a new name.

• You can delete a model from your personal database. As an administrator, you can also delete a model from the shared database by deleting it from your personal database, and then distributing this change to the shared database.

• You can move a model to a new position in the list of models maintained by the system.

• You can change the equations that are associated with each value of the model's base dimension values. Note that if you modify the equations associated with a model, you may need to modify the option selections that govern how simultaneous equations are solved.

• You can view a list of the dependencies between the model's base dimension values and see how dimension values are grouped.

• You can change model option selections.

Related information

For more information about working with models, search for the following topics in the Financial Analyzer Help system:

"Creating Models"

"Defining Equations for Dimension Values"

"Deleting Models"

"Moving Models"

"Renaming Models"

"Specifying Model Options"

Model Equation Syntax

Overview

Model equations can include operators, numeric constants, and condition statements. You can also include functions, which are described in the topic "Modeling Functions.

Operators

Operators include the unary minus symbol (-) and the standard binary operators for addition (+), subtraction (-), multiplication (*), and division (/). Conventional rules for evaluating algebraic expressions apply.

If a formula contains multiple subexpressions (distinguished from the main expression by parentheses), the subexpressions are evaluated first; then the main expression is evaluated.

Operators are evaluated from left-to-right using the conventional order of precedence: unary operators (negative symbols) are evaluated first, followed by multiplicative operators (*,/), followed by additive operators (+,-).

Numeric Constants

Numeric constants are real numbers (decimal numbers) or integers (non-decimal numbers).

Real numbers:

• Have a decimal point with digits on both sides

• Can begin with the unary minus symbol (to indicate a negative value)

• Can include exponential notation (d, D, e, or E)

Integers:

• Are whole numbers

• Can include the unary minus symbol to indicate a negative value

• Do not include a decimal point or exponential notation

Condition statements

Overview

You can use models to derive data based on certain conditions. You must use the condition statements IF, THEN, and ELSE. All three statements are required.

Example: Using condition statements

In this example, the TAX line item is calculated based on SALES - EXPENSE and a TAXRATE, where, if the result is negative, the tax is zero.

IF SALES - EXPENSE GT 0 THEN (SALES - EXPENSE) * TAXRATE ELSE 0

Multiple line equations

Overview

When typing a command or equation into a model, you can continue it onto another line by ending the partial command with a hyphen and then continuing on the next line.

Note: Financial Analyzer models are limited to one command per If statement.

Example: Multiple line equations

Note in the following model, the hyphens (-) at the end of the 3rd, 8th, and 9th lines.

if sales - expense gt 0 
   then 
     conssales * -1 -
     sales(line 'advert') / lastyrsales (line 'advert') 
   else 
    if gross.profit gt 100 
      then 
        maint.marg / maint.rev - 
        service.marg / service.rev - 
        .
        .
        .
       else 
    NA

Referring to another financial data item

When referring to another financial data item, you must create an alias financial data item. In models, you cannot reference financial data items directly; you must always refer to them using their alias. Although the model will compile if a financial data item is referenced directly, a solve that is based on the model will not run.

Procedure: Creating an alias

Follow this procedure to create an alias for a financial data item.

1. From the Maintain menu, choose Financial Data Item.

2. Choose New.

3. In the New Financial Data Item dialog box, specify the same dimensions as the Financial Data Item for which you are creating the alias.

4. Choose Save.

5. Choose Edit Formula.

6. Enter the name of the Financial Data Item for which you are creating the alias.

7. Choose Save.

Example: Referring to another financial data item

For example, if you have two financial data items called MAYACTL and APRACTL, and you want to create a formula to see the variance in their data, you must create alias financial data items for them. If the aliases you create are called MMAYACTL and MAPRACTL, the formula in the variance financial data item would be:

MMAYACTL - MAPRACTL

Modeling Functions

Uses for functions

Financial Analyzer has many functions that you can use to make calculations and analyze data. You can use functions to perform the following tasks:

• Calculate totals and moving totals, averages, and moving averages

• Reference values from different time periods or derive data based on other line item values

• Compute logs, square roots, absolute values, and random numbers

• Calculate statistical and financial functions

To use a function, you type its name and, in parentheses, some additional information. Each piece of information needed by a function is called an argument.

Types of functions

Financial Analyzer includes the following types of numeric functions.

Type of Function	Description
Advanced Mathematical	Functions that perform advanced mathematical calculations
Time-Series	Functions that retrieve values from a previous or future time period and perform calculations on those values
Financial	Functions that perform calculations for financial analysis
Aggregation	Functions that return an aggregate value, generally consisting of a single value for many values of the input expression

Advanced mathematical functions

Advanced mathematical functions operate on each dimension in the expression. The dimensions of the result are the union of the dimensions of the inputs. The following table describes these functions.

Function	Description
ABS	Calculates an absolute value
INTPART	Calculates the integer part of a value
LOG	Calculates the natural logarithm
LOG10	Calculates the logarithm base 10
MAX	Calculates the maximum between 2 expressions
MIN	Calculates the minimum between 2 expressions
RANDOM	Calculates a random number
REM	Calculates the remainder after a division
SQRT	Calculates the square root

Most advanced mathematical functions operate on a single numeric variable, but the MAX and MIN functions make comparisons between the values of two variables. For dimensioned variables, the MAX and MIN functions compare each value of one variable with the corresponding value of the other variable.

Time-series functions

The time-series functions retrieve and perform calculations on values from a previous or future time period. The following table describes these functions.

Function	Description
CUMSUM	Calculates cumulative sums over time
LAG	Calculates the value for a previous time period
LAGABSPCT	Calculates the percent difference between current and previous values and indicates the direction of change
LAGDIF	Calculates the difference between current and previous values
LAGPCT	Calculates the percent difference between current and previous values
LEAD	Calculates the value for a subsequent time period
MOVINGAVERAGE	Calculates a series of averages over time
MOVINGMAX	Returns a series of maximum values over time
MOVINGMIN	Returns a series of minimum values over time
MOVINGTOTAL	Calculates a series of totals over time

The data you retrieve with a time-series function is usually dimensioned by a time dimension, such as MONTH, PERIOD, or YEAR. LAG and LEAD actually can retrieve data of any type, but the other time-series functions handle numeric data only.

The LAG, LAGDIF, LAGPCT, LAGABSPCT, and LEAD functions let you compare data for the time dimension values in the current status with data from a previous or future time period. You specify the data expression you want to analyze, the number of time periods the function should go back or forward for the comparison, and the time dimension. LAG and LEAD simply return the past or future value.

The LAGDIF and LAGPCT functions make the most commonly used types of comparisons, that is, the difference and the percent difference, between the current data and the comparison value.

The moving functions (such as MOVINGTOTAL or MOVINGMAX) return a value for each time period in the status of a time dimension. The value is based on the data for a range of time periods that precede, include, or follow the period for which the value is being calculated. The range is always relative to the current period, so the range is always moving forward as you process each time value in the current status.

Financial functions

The financial functions provide standard calculations needed for financial analysis. The following table describes these functions.

Function	Description
DEPRDECL	Calculates declining-balance depreciation
DEPRSL	Calculates straight-line depreciation
DEPRSOYD	Calculates sum-of-year's-digits depreciation
FINTSCHED	Calculates the interest on a series of fixed-rate installment loans
FPMTSCHED	Calculates payments for a series of fixed-rate installment loans
IRR	Calculates the internal rate of return for a stream of cash flows
NPV	Calculates the net present value of a stream of cash flows

Aggregation functions

Functions that return an aggregate value ordinarily return one value for many values of the input expression. The following table describes these functions.

Function	Description
ANY	Tests if any values match a criterion (returns Boolean result)
AVERAGE	Calculates an average value
COUNT	Counts values that match a criterion
EVERY	Tests if every value matches a criterion (returns Boolean result)
LARGEST	Calculates the largest value
NONE	Tests if no value matches a criterion (returns Boolean result)
SMALLEST	Calculates the smallest value
STDDEV	Calculates the standard deviation using N-1
TALLY	Counts the number of dimension values corresponding to related dimension values
TCONVERT	Aggregates or allocates data based on time periods
TOTAL	Calculates a total value

Related information

For complete descriptions of modeling functions, refer to the Oracle Express Language Reference Manual or the Oracle Express Language online help.

Examples of Models

Example 1: Generating basic revenue and expense values

Revenue and expense line items which are commonly derived include:

• Gross Sales

• Cost of Goods Sold

• Gross Profit

Line items not stored in the general ledger or reflected in the standard income statement are often used as the basis for some of these calculations and may be calculations themselves. The following table shows examples of these calculations.

IF the line item is...	THEN it is derived using the equation...
Cost of Goods Sold	Unit Volume * Unit Cost
Gross Sales	Unit Volume * Unit Price
Net Sales Revenue	Gross Sales - (Sales Discount + Sales Return)
Gross Profit	Net Sales Revenue - Total Cost of Goods Sold
Unit Volume	LAG (Unit Volume, 12, TIME) * 1.05 * (1 + LAGPCT (Advertising Expense, 12, TIME) / 100)
Average Sales Price	Gross Sales / Unit Volume
Gross Profit Percentage	Gross Profit / Total Revenue * 100

In this example, Unit Volume, Unit Price, Unit Cost, Sales Discounts and Sales Returns are input values. The Unit Volume equation is based on the previous year's Unit Volume value.

The combination of these and other equations is referred to as a model. By applying this model to financial data, these equations are calculated and the resulting figures are available for review, publishing, or further analysis.

Example 2: Modeling employee headcount

One use of models within organizations with many employees is the modeling of number of employees, or headcount. In organizations where compensation expenses account for a large percentage of total operating expenses, effectively budgeting and planning the compensation expense line items through headcount modeling provides significant level of cost controls.

The following table shows some options for modeling consulting employee headcount and related revenue.

IF the line item is... . . .	THEN it is derived using the equation... . . .
Monthly Revenue	n * Billing Rate * Billability Percentage/100
Monthly Margin	Monthly Revenue - (Average Salary / 12)
Consulting Revenue	Monthly Revenue at Job Grade A * (Number of EEs at Job Grade A + Monthly Revenue at Job Grade B * Number of EEs at Job Grade B + ... Monthly Revenue at Job Grade J * Number of EEs at Job Grade J)

In this example, analysts can vary the Billing Rate, Billibility, or number (n) of employees (EEs) at each Job Grade (V.P., Director, Senior Consultant, etc.). They can then apply the model and see the impact of that change on overall corporate Consulting Revenue.

Example 3: Top down expense allocation based on prior year data

This sample model calculates the proportion of Advertising Expenses to Marketing Expenses from last year as a means of allocating the targeted, annual budget amount that was entered into Marketing Expense on the worksheet. This is an example of top/down budgeting with models that spread higher level values to the detail based on historical ratios. This same approach will work for Time, Org and all other dimensions. In this example, Marketing Expenses (MKTEXP) consists of Advertising and Selling Expenses.

The following table describes the steps taken to create the model.

1. Create a financial data item formula called Budref. Budref will serve as an alias for the Budget financial data item.

2. Equate the formula for Budref to the Budget as follows:

   Budref = Budget

3. Create a worksheet that allows you to enter into Budget the high level numbers you want to spread (for example, Marketing Expenses, Sales, Operating Expenses).

4. Enter the numbers you want to budget into that worksheet.

5. Create a model called Allocate Lines Based On Last Year.

6. Write the model equation for the Advertising line item as follows:

   lastyear(line `Advert')/lastyear(line `MKTEXP') * budref(line `MKTEXP')

   Write the model equation for the Selling Expense line item as follows:

   lastyear(line `Sellexp')/lastyear(line `MKTEXP') * budref(line `MKTEXP')

   Note: The item "lastyear" represents a financial data item that contains last year's data.

Example 4: Using the LEAD function

The LEAD function retrieves values from a subsequent time period. A model equation can refer to data from a subsequent time period by incorporating the LEAD command in the equations. For example, if December's budgeted Plant & Equipment asset balance has been determined, to derive September's budgeted amount based on that year end balance, you could use the following equation.

LEAD(P&E, 3, TIME) * .90

Where:

• P&E is the financial data item (Plant & Equipment) dimensioned by TIME

• 3 is the number of time periods to look ahead (based on 0 being the current period)

• TIME is the dimension along which to lead occurs

Example 5: Using the LAG function

The LAG function retrieves data from a previous time period. A model equation can refer to data from a previous time period by incorporating the LAG command in the equation. In the equation shown below, a month's revenue (REVENUE) is derived based on the prior month's estimate.

LAG(REVENUE, 1, TIME) * 1.05

Where:

• REVENUE is the financial data item dimensioned by time

• 1 is the number of periods to lag (based on 0 being the current period)

• TIME is the dimension along which the lag occurs

Example 6: Using the LAGDIF function

The LAGDIF function calculates the difference between current and previous values. When the difference between two values is useful in deriving a value, this function can be incorporated into the model equation. For instance, in statement of cash flow balances, net change in accounts receivable can be quickly calculated using the LAGDIF function. In the example below, the end of year balances are compared (lag one year time period).

LAGDIF(ACCTS.REC, 1, TIME)

Where:

• ACCTS.REC is the financial data item dimensioned by TIME

• 1 is the number of time periods to lag (based on 0 being the current period)

• TIME is the dimension along which the lag occurs

Example 7: Using the LAGPCT function

The LAGPCT function calculates the percent difference between current and previous values. The percent difference calculation is often referenced in deriving budget, forecast and plan data as well as when creating key indicators for tracking performance growth. In the example below, Computer Expense (CMPTR) is tied to Employee Headcount totals (HEADCOUNT). Here, monthly Computer Expense is budgeted based on last month's Computer Expense, multiplied by the increase/decrease in Headcount from the previous month.

LAG(CMPTR, 1, TIME) * (LAGPCT(HEADCOUNT, 1, TIME) + 1)

Where:

• HEADCOUNT is the financial data item dimensioned by TIME

• 1 is the number of time periods to lag (based on 0 being the current period)

• TIME is the dimension along which the lag occurs

Example 8: Using the MOVINGAVERAGE function

The MOVINGAVERAGE (abbreviated MVAVG) function computes a series of averages over time or over another dimension. This function can be used to maintain a three-month moving average balance on inventory, as shown in the following equation.

MVAVG(INVENTORY, -2, 0, 1, TIME)

Where:

• INVENTORY is the financial data item whose values you want to average.

• -2 represents the starting point of the range over which you want to average. Because 0 represents the current period, -2 represents 2 months prior to the current month.

• 0 represents the ending point of the range over which you want to average (with 0 being the current period).

• 1 specifies whether to average over every period in the range. In this case, 1 means average every period; a 2 would mean average every other period, TIME is the dimension over which the moving average is calculated.

Example 9: Using the MOVINGTOTAL function

The MOVINGTOTAL function (abbreviated MVTOT) computes a series of totals, usually over time, but can also be used over another dimension. Asset management may require monitoring Property Plant and Equipment (PP&E) in six-month moving totals as shown in the following equation.

MVTOT(PP&E, -5, 0, 1, TIME)

Where:

• PP&E is the financial data item whose values you want to total.

• -5 represents the starting point of the range over which you want to average. Because 0 represents the current period, -5 represents 5 months prior to the current month.

• 0 represents the ending point of the range over which you want to average (with 0 being the current period).

• 1 specifies whether to average over every period in the range. In this case, 1 means average every period; a 2 would mean average every other period, TIME is the dimension over which the moving total is calculated.