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.