Spreading

The spreading feature allows you to enter data "top-down:" you type a value into a cell at the parent level, and the value is automatically allocated among a set of lower-level members.

 Example:   When budgeting for a new fiscal year, you type 60,000 in the year column for Overtime Pay, and spreading automatically puts 5,000 in each month column.

Spreading comes in two flavors (described below): Time Spreading and Dimension Spreading.

How to use Spreading

Note that to use Spreading the feature needs to be turned on.

  1. Type an amount into a cell that represents a non-leaf member.

  2. A prompt appears asking you to choose a method to apply.

     Tip:  You can choose to turn off prompting and have a default spreading method applied automatically.

Time Spreading

In Time Spreading, you enter a value at a higher level in the Time dimension and the amount is then allocated among members at a lower-level time period.

 Example:  In the Time dimension, you enter a value in Year and the amount is allocated among members at a lower-level time period such as Quarters, Months, or Days).

The methods for Time spreading are as follows:

  • Spread evenly: Allocate the amount equally among all leaf-level members of the Time dimension.

  • Spread based on existing data: Allocate the amount based on the distribution pattern of the existing values in the current Account/Time/Version dimension member combination.

     Caution:  The Spread based on existing data method, when used on a non-leaf member, spreads the value evenly among the children; however, the sum of the child values will not equal the original value in the parent. For reporting purposes, the discrepancy is normally so slight as to be negligible. You must determine whether this level of precision is acceptable for your reporting or data export needs.

  • Spread based on member property values: Allocate the amount using a value stored in a member attribute.

     Example:  A Time member such as Month could have the custom attribute NumberOfDays (used to store the number of business days in a given month); by selecting this attribute, you could spread the amount among a set of months, pro-rating it by the number of business days in each.

  • Spread based on data in dimension: Allocate the amount based on the spread of values in the dimension member combination that you select.

Dimension Spreading

In Dimension Spreading, you enter a value to a high-level member combination and the amount is then allocated among the leaf members.

 Example:  In the Organization dimension's Total Corporate member, you enter a value to the Overtime Pay account; this amount is then allocated among the leaf members of Organization (Finance, Human Resources, Sales, etc.).

The methods for Dimension Spreading are as follows:

  • Spread evenly: Allocate the amount equally among all leaf-level members of the selected member combination.

  • Spread based on existing data: Allocate the amount based on the distribution pattern of the existing values in the current Account/Time/Version dimension member combination.

  • Store: Allocate the amount to every leaf-level member.

Remind your users

Spreading is powerful and useful, but some users may forget it is available. In this case, in your data-entry templates you can use data cell formatting to flag the cells where Spreading is available.

Spreading FAQ

Where is Spreading available?

Spreading is available in Ad Hoc Analysis and in templates in data-entry mode.

Is Spreading available by default?

In Ad Hoc Analysis you need to turn on spreading for the current data view; in Template Studio, spreading is a data view property that the template designer controls.

Is there a limit on Spreading?

The limit for a single spread is 10,000 member combinations.

Can I paste a value into a cell for spreading?

Yes. Do it like this:

  1. Double-click the spreading cell.

  2. Paste the value into the cell (Ctrl + V).

 Note:  You can only paste to spreading cells one cell at a time.

Does Spreading use the Force Calculations function?

If you use Spreading with a data view that has Force Calculations turned on and if any of the data uses calculations, the Spreading function triggers the Force Calculations function.