# 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 (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:

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 1

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 1

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 1

You change YearTotal from 100 to 200.

Result:

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

Note:

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 cannot customize month labels, although you can use aliases.

• Years must have 12 months, and quarters must be the sum of three base months.

• You cannot change the fiscal start month after the application 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 1

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 cannot customize month labels, although you can use aliases.

• Years must have 12 months, and quarters must be the sum of three base months.

• You cannot change the fiscal start month after the application 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 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.

 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: