The Account dimension

Every FP&A financial model has an Account dimension. The Account dimension is always a parent-child dimension, and its members are accounts. The members are described by means of a key (often the general ledger account code) and a name. The members are arranged in a tree structure, which is a hierarchical ordering of the accounts, also known as the Chart of Accounts. The hierarchy can be created manually or imported from external files.

You use Model Manager to manage the Account dimension, including adding members and editing the member properties that define the basic characteristics of the account. In addition, an account also has a calculation definition that determines its behavior.

These properties are largely self-explanatory except for Time Conversion, which controls the aggregation behavior of accounts across the Time dimension.

Account type

The Account type property determines whether FP&A treats the account value as negative or positive.

The values for Account type are: Expense, Revenue, Asset, Liability/Equity, and Statistical. Defining an account as one of the types does not change the value stored in the account.

 Note:  The signs of the account values are only affected during data import; hence the account type must be defined before importing data.

To specify the account type for a member, in the Member Properties section, select from the drop-down list.

 Tip:  By default, all new accounts are set up as expense accounts. To change the default, select Options.

Debit/Credit

The Debit/Credit property is associated with the Account type property.

  • By default, if Account type is Expense or Asset, this property is set to Debit.
  • If Account type is Revenue or Liability/Equity, Debit/Credit is set to Credit.
  • Choosing Statistical for Account type sets Debit/Credit to Not Set.

To override these default settings, select the Debit/Credit property after setting the Account type.

 Note:  Correctly setting the Debit/Credit property is important for adjustments.

Time conversion

The Time conversion property determines how each account aggregates data across different periodicities, such as from monthly values to quarterly values. (Aggregation across other dimensions is not affected by this setting.)

 Tip:   By default, the time conversion method is assigned automatically based on the Account Type selected. To change the default, select Options.

Time conversion calculations are implemented as MDX directly in the cube and execute each time the cube is queried.

The default time conversion method of an account type can be overridden by the user.

Account type Default debit/credit Default time conversion
Revenue Credit Sum
Expense Debit Sum
Asset Debit Last (including empty)
Liability/Equity Credit Last (including empty)
Statistical Not Set Sum

Time conversion methods

Time conversion uses one of the following methods:

  • Sum: Adds the leaf/detail member values together to derive the values for higher levels. Sum is the most commonly used time conversion method and is the default.
  • Last (excluding empty): Uses the last value in the last non-empty leaf member to derive the values for higher levels.
  • Last (including empty): Uses the value in the last leaf member to derive the values for higher levels.
  • First: Uses the first value in the leaf member to derive the values for higher levels.
  • Average (excluding empty): Averages the values of the leaf member to derive the parent member value. Empty cells are excluded.

     Note:  Empty cells are not the same as cells with the value of zero.

  • Average (including empty): Averages the values of the leaf member to derive the parent member value. Empty cells are included and given the value of zero.
  • Formula: Calculates the values for higher levels, using the specified formula.

Examples of Time conversion methods

The following table shows examples of how the various Time Conversion Methods calculate results:

Account

Jan

Feb

Mar

Qtr 1

Time conversion method

Gross Sales

100

250

375

725

Sum

Headcount A

12

16

-

-

Last (including empty)

Headcount B

13

22

-

22

Last (excluding empty)

Opening Balance

100

110

130

100

First

Product A Sales

100

250

175

Average (excluding empty)

Product B Sales

225

150

125

Average (including empty)

Gross Sales per Employee

8.33

15.62

18.75

36.25

Formula (Gross Sales/Headcount)

Numeric format

Each account has a default numeric format defined. This format is applied during data entry and is also the default format for reporting, though it can be overridden. Numeric formats are defined in the same way as in a spreadsheet.

 Example:  #,##0.0 applied to the value 12345.67 causes it to appear as 12,345.7.

The numeric formats available are as follows:

Format 1234.5678 Description
#,### 1,235 Displays significant digits, with a thousands separator. A zero value appears as blank.
#,###.00 1,234.57 Displays significant digits, with a thousands separator and always includes two decimal places (even if the value is without decimals).
#,##0 1,235 Displays significant digits, rounded to the nearest thousand. A zero value appears as 0.
#,###, 1 Displays significant digits, rounded to the nearest thousand. The second comma causes the number to scale by a thousand; more examples: 91234.5678 is formatted as 91; 901234.5678 is formatted as 901.
0 1235 Displays significant digits, rounded, and without a thousands separator.
0% 123457% Displays percentage, rounded to full percentage.
0.00 1234.57 Displays significant digits, without a thousands separator, and always includes two decimal places.
0.00% 123456.76% Displays percentage, rounded to two decimals.
General 1234.5678 No numeric formatting

 Tip:  To change the default numeric format, select Options.

Currency conversion method

This property is present if the model contains a dimension of type Currency. The options are Current (the default) and Historical.