How Spreading Data Works

Factors such as account type, the Time Balance property, existing distribution, member hierarchies, and data type affect how values are distributed, assuming that no data cells are locked.

For information on locking cells, see Locking Cells.

Note:

Date and text values are excluded when spreading data.

The following table shows examples of the effect on data of entering or changing a currency or non-currency value:

Table 14-1 Examples: The Effect on Data of Entering or Changing a Currency or Non-currency Value

Time Balance Property of the Account	New Value Distribution	Examples
FLOW Revenue, Expense, Saved Assumption (where the Time Balance property is set to Flow)	To all its children and its parents proportionally, based on the existing distribution. The value affects the entire Summary Period Rollups hierarchy so that the parent time period is the sum of its children. If no distribution exists (that is, the values for all the children are zeros or are missing), and the changed value is a Quarter, the value spreads down proportionally, based on the weekly distribution (which can be 4-4-5, 4-5-4, 5-4-4, or evenly distributed if the account's spreading is set to None). If the changed parent is a Year Total or some other kind of summary time period, the value is spread evenly.	Example 1 You change Qtr 1 from 250 to 500, with these current values for its months: Jan = 100 Feb = 50 Mar = 100 Result: 500 is distributed to its children proportionally, replacing their previous values with: Jan = 200 Feb = 100 Mar = 200 The increment of 250 is aggregated to the parents of Qtr 1. If Year Total was 1000, its new value is 1250. Example 2 You change March from 100 to 200. Result: March, Qtr 1, and Year Total all increment by 100. Jan and Feb remain unchanged.
FIRST All types of Accounts	Upward to its first parent and downward to its child only if the changed cell is the first child of its parent time period. The summary time period equals the first of its child time periods. If no distribution exists (that is, values for all children are zeros or are missing), the value is copied to each of the children.	Example You change Qtr 1 from 20 to 40, with these current values for its months: Jan = 20 Feb =15 Mar = 05 Q1 = 20 Result: 40 is distributed to its children, replacing their previous values with: Jan = 40 Feb = 15 Mar = 05 Q1 = 40
BALANCE Asset, Liability, Equity, Saved Assumption (where the Time Balance property is set to Balance)	Downward to its last child and upward to its parent only if the changed cell is the last child of its parent time period. The summary time period equals the last of its child time periods. If no distribution exists (that is, the values for all children are zeros or are missing), the value is spread across its children.	Example 1 You change Qtr 1 from 30 to 50. Result: March also changes to 50. Jan and Feb don't change. Year Total does not change because Qtr 1 is not its last child. Example 2 You change Qtr 4 from 100 to 50. Result: Dec changes to 50 because it is Qtr 4’s last child. Oct and Nov remain unchanged, as do Qtrs 1, 2, and 3. Year Total changes to 50 because Qtr 4 is its last child. Example 3 You change Qtr 2 to 100 with these current values: Apr = 0 May = 0 June = 0 Result: Apr = 100 May = 100 June = 100 Year Total is unchanged.
AVERAGE Revenue, Expense, Saved Assumption, (where the Time Balance property is set to Average)	To all its children and its parents proportionally, based on the existing distribution. The value affects the entire Summary Time Period Rollups hierarchy so that the parent is the average of its children. Assumes an equal number of days in each period, such as 30 days for each month.	Example You change Qtr 1 from 5 to 10 with these current values: Jan = 05 Feb = 10 Mar = 00 Q1 = 05 Result: Jan = 10 Feb = 20 Mar = 00 Q1 = 10
FILL All types of Accounts	The value set at the parent is filled into all its descendants.	Example You change YearTotal from 100 to 200. Result: Values for Q1, Q2, Q3, Q4 and all months are changed to 200 Consolidation operators and member formulas overwrite FILL values when the members are recalculated.
Weighted Average - Actual_365 Revenue, Expense, Saved Assumption, (where the Time Balance property is set to Average)	Weighted daily average based on 365 days in a year, assuming that February has 28 days. This does not account for leap years. About Weighted Average - Actual_365: You can't customize month labels, although you can use aliases. Years must have 12 months, and quarters must be the sum of three base months. You can't change the fiscal start month after Planning is set up. All months are included in the calculation. #MISSING is treated as 0 in the numerator, and all the days are included in missing months in the denominator. This means, for example, that QTR means three months, not QTD, and Total Year means all 12 months, not YTD.	Example You enter values for Jan, Feb, and Mar. For any year, including leap years, February is assumed to have 28 days, and Q1 is assumed to have 90 days. Value Entered and Number of Days Jan = 9,000 31 days Feb = 8,000 28 days Mar = 8,000 31 days Q1 = 90 days (the total days for Jan, Feb, and Mar) Result: Q1 = 8,344 The average for Q1 is calculated thus: (1) Multiply the value for each month in Q1 by the number of days in that month, (2) Sum these values, and (3) Divide the total by the number of days in Q1. Using 28 for the number of days in Feb, and 90 for the number of days in Q1, the result is: (9,000 times 31 plus 8,000 times 28 plus 8,000 times 31) divided by 90 = 8,344.
Weighted Average - Actual_Actual Revenue, Expense, Saved Assumption, (where the Time Balance property is set to Average)	Weighted daily average based on the actual number of days in a year. This accounts for leap years, in which February has 29 days. About Weighted Average - Actual_Actual: You can't customize month labels, although you can use aliases. Years must have 12 months, and quarters must be the sum of three base months. You can't change the fiscal start month after Planning is set up. All months are included in the calculation. #MISSING is treated as 0 in the numerator, and all the days are included in missing months in the denominator. This means, for example, that QTR means three months, not QTD, and Total Year means all 12 months, not YTD. This time balance property is only supported for dimensions bound to a block storage cube. Aggregate storage cubes do not support the Weighted Average - Actual_Actual time balance property.	Example 1 For a leap year, you enter values for Jan, Feb, and Mar. February is assumed to have 29 days, and Q1 is assumed to have 91 days. Value Entered and Number of Days Jan = 9,000 31 days Feb = 8,000 29 days Mar = 8,000 31 days Q1 = 91 days (the total days for Jan, Feb, and Mar) Result: Q1 = 8,341 The average for Q1 is calculated thus: (1) Multiply the value for each month in Q1 by the number of days in that month, (2) Sum these values, and (3) Divide the total by the number of days in Q1. Using 29 for the number of days in Feb, and 91 for the number of days in Q1, the result is: (9,000 times 31 plus 8,000 times 29 plus 8,000 times 31) divided by 91 = 8,341. Example 2 For a non-leap year, you enter values for Jan, Feb, and Mar. February is assumed to have 28 days, and Q1 is assumed to have 90 days. Value Entered and Number of Days Jan = 9,000 31 days Feb = 8,000 28 days Mar = 8,300 31 days Q1 = 90 days (the total days for Jan, Feb, and Mar) Result: Q1 = 8,344 Using 28 for the number of days in Feb, and 90 for the number of days in Q1, the result is: (9,000 times 31 plus 8,000 times 28 plus 8,000 times 31) divided by 90 = 8,344.
CUSTOM	Spreading is disabled and the application designer is expected to add customized spreading. For instance, you can feed a value into the period total (Q1) using Groovy rules and it will calculate the summary time periods using the Flow method.	NA
DISABLE	Spreading is disabled and the summary time period is read-only. Disable prevents data from being entered on non-level zero periods. The Flow method is used to aggregate into the period total (Q1), but it won’t spread down to the summary time periods.	NA

Time Balance Property of the Account

New Value Distribution

Examples

FLOW

Revenue, Expense, Saved Assumption (where the Time Balance property is set to Flow)

To all its children and its parents proportionally, based on the existing distribution. The value affects the entire Summary Period Rollups hierarchy so that the parent time period is the sum of its children.

If no distribution exists (that is, the values for all the children are zeros or are missing), and the changed value is a Quarter, the value spreads down proportionally, based on the weekly distribution (which can be 4-4-5, 4-5-4, 5-4-4, or evenly distributed if the account's spreading is set to None).

If the changed parent is a Year Total or some other kind of summary time period, the value is spread evenly.

Example 1

You change Qtr 1 from 250 to 500, with these current values for its months:

Jan = 100
Feb = 50
Mar = 100

Result: 500 is distributed to its children proportionally, replacing their previous values with:

Jan = 200
Feb = 100
Mar = 200

The increment of 250 is aggregated to the parents of Qtr 1. If Year Total was 1000, its new value is 1250.

Example 2

You change March from 100 to 200.

Result: March, Qtr 1, and Year Total all increment by 100. Jan and Feb remain unchanged.

FIRST

All types of Accounts

Upward to its first parent and downward to its child only if the changed cell is the first child of its parent time period.

The summary time period equals the first of its child time periods.

If no distribution exists (that is, values for all children are zeros or are missing), the value is copied to each of the children.

Example

You change Qtr 1 from 20 to 40, with these current values for its months:

Jan = 20
Feb =15
Mar = 05
Q1 = 20

Result: 40 is distributed to its children, replacing their previous values with:

Jan = 40
Feb = 15
Mar = 05
Q1 = 40

BALANCE

Asset, Liability, Equity, Saved Assumption (where the Time Balance property is set to Balance)

Downward to its last child and upward to its parent only if the changed cell is the last child of its parent time period.

The summary time period equals the last of its child time periods.

If no distribution exists (that is, the values for all children are zeros or are missing), the value is spread across its children.

Example 1

You change Qtr 1 from 30 to 50.

Result: March also changes to 50. Jan and Feb don't change. Year Total does not change because Qtr 1 is not its last child.

Example 2

You change Qtr 4 from 100 to 50.

Result: Dec changes to 50 because it is Qtr 4’s last child. Oct and Nov remain unchanged, as do Qtrs 1, 2, and 3. Year Total changes to 50 because Qtr 4 is its last child.

Example 3

You change Qtr 2 to 100 with these current values:

Apr = 0
May = 0
June = 0

Result:

Apr = 100
May = 100
June = 100

Year Total is unchanged.

AVERAGE

Revenue, Expense, Saved Assumption, (where the Time Balance property is set to Average)

To all its children and its parents proportionally, based on the existing distribution. The value affects the entire Summary Time Period Rollups hierarchy so that the parent is the average of its children.

Assumes an equal number of days in each period, such as 30 days for each month.

Example

You change Qtr 1 from 5 to 10 with these current values:

Jan = 05
Feb = 10
Mar = 00
Q1 = 05

Result:

Jan = 10
Feb = 20
Mar = 00
Q1 = 10

FILL

All types of Accounts

The value set at the parent is filled into all its descendants.

Example

You change YearTotal from 100 to 200.

Result:

Values for Q1, Q2, Q3, Q4 and all months are changed to 200

Consolidation operators and member formulas overwrite FILL values when the members are recalculated.

Weighted Average - Actual_365

Revenue, Expense, Saved Assumption, (where the Time Balance property is set to Average)

Weighted daily average based on 365 days in a year, assuming that February has 28 days. This does not account for leap years.

About Weighted Average - Actual_365:

You can't customize month labels, although you can use aliases.
Years must have 12 months, and quarters must be the sum of three base months.
You can't change the fiscal start month after Planning is set up.
All months are included in the calculation. #MISSING is treated as 0 in the numerator, and all the days are included in missing months in the denominator. This means, for example, that QTR means three months, not QTD, and Total Year means all 12 months, not YTD.

Example

You enter values for Jan, Feb, and Mar. For any year, including leap years, February is assumed to have 28 days, and Q1 is assumed to have 90 days.

Value Entered and Number of Days

Jan = 9,000 31 days
Feb = 8,000 28 days
Mar = 8,000 31 days
Q1 = 90 days (the total days for Jan, Feb, and Mar)

Result:

Q1 = 8,344

The average for Q1 is calculated thus: (1) Multiply the value for each month in Q1 by the number of days in that month, (2) Sum these values, and (3) Divide the total by the number of days in Q1. Using 28 for the number of days in Feb, and 90 for the number of days in Q1, the result is: (9,000 times 31 plus 8,000 times 28 plus 8,000 times 31) divided by 90 = 8,344.

Weighted Average - Actual_Actual

Revenue, Expense, Saved Assumption, (where the Time Balance property is set to Average)

Weighted daily average based on the actual number of days in a year. This accounts for leap years, in which February has 29 days.

About Weighted Average - Actual_Actual:

You can't customize month labels, although you can use aliases.
Years must have 12 months, and quarters must be the sum of three base months.
You can't change the fiscal start month after Planning is set up.
All months are included in the calculation. #MISSING is treated as 0 in the numerator, and all the days are included in missing months in the denominator. This means, for example, that QTR means three months, not QTD, and Total Year means all 12 months, not YTD.
This time balance property is only supported for dimensions bound to a block storage cube. Aggregate storage cubes do not support the Weighted Average - Actual_Actual time balance property.

Example 1

For a leap year, you enter values for Jan, Feb, and Mar. February is assumed to have 29 days, and Q1 is assumed to have 91 days.

Value Entered and Number of Days

Jan = 9,000 31 days
Feb = 8,000 29 days
Mar = 8,000 31 days
Q1 = 91 days (the total days for Jan, Feb, and Mar)

Result:

Q1 = 8,341

The average for Q1 is calculated thus: (1) Multiply the value for each month in Q1 by the number of days in that month, (2) Sum these values, and (3) Divide the total by the number of days in Q1. Using 29 for the number of days in Feb, and 91 for the number of days in Q1, the result is: (9,000 times 31 plus 8,000 times 29 plus 8,000 times 31) divided by 91 = 8,341.

Example 2

For a non-leap year, you enter values for Jan, Feb, and Mar. February is assumed to have 28 days, and Q1 is assumed to have 90 days.

Value Entered and Number of Days

Jan = 9,000 31 days
Feb = 8,000 28 days
Mar = 8,300 31 days
Q1 = 90 days (the total days for Jan, Feb, and Mar)

Result:

Q1 = 8,344

Using 28 for the number of days in Feb, and 90 for the number of days in Q1, the result is: (9,000 times 31 plus 8,000 times 28 plus 8,000 times 31) divided by 90 = 8,344.

CUSTOM

Spreading is disabled and the application designer is expected to add customized spreading. For instance, you can feed a value into the period total (Q1) using Groovy rules and it will calculate the summary time periods using the Flow method.

DISABLE

Spreading is disabled and the summary time period is read-only. Disable prevents data from being entered on non-level zero periods. The Flow method is used to aggregate into the period total (Q1), but it won’t spread down to the summary time periods.

Note:

The Skip option does not apply to data spreading but affects only the calculation of the member hierarchy.

If you change a percentage:

Regardless of account type, existing distribution, or 4-4-5 setting, the value is spread evenly across its children. If the changed cell is the last child of its parent time period, the value is copied upward to its parent.

Example 1

You change Qtr 1 from 10 to 20.

Result: Jan, Feb, and Mar also change to 20. However, Year Total does not change because Qtr 1 is not its last child.

Example 2

You change Feb from 10 to 20.

Result: Jan and Mar do not change because neither one is a child or parent of Feb. Qtr 1 (and therefore Year Total) does not change because Feb is not its last child.

Example 3

You change Qtr 4 from 30 to 20.

Result: Oct, Nov, and Dec also change to 20 because the value is copied to Qtr 4’s children. Year Total also changes to 20 because Qtr 4 is its last child.

Note:

See Adjusting Cell Values and Spreading with Multiple Currencies.