5 Allocations
5.3 Spreading and Distribution
Spreading and distribution are ways of allocating values from a single source cell in a report or dynamic table to one or more target cells. These features can help you quickly book new values and adjust existing values in a plan. In spreading operations, the value of the source cell is spread across leaf members that aggregate up to the source cell. You can perform spreading automatically or manually.
Distribution is another type of allocation that changes values within a hierarchy by distributing amounts from a source member to leaf members of its siblings.
These operations are available when the following conditions are met:
● The report is based on a planning-enabled model.
● The source cell belongs to a private version.
● For automatic spreading, the source cell must be an aggregation of at least one dimension, not including account or version. For manual spreading, it must also be a visible dimension.
Automatic Spreading
Automatic spreading takes place when you enter a value in a blank source cell in a view, and the value is automatically distributed to leaf items for that cell.
For each dimension with leaf members, the source value is spread according to the aggregation type that was set up for its account when the model was created. The aggregation type can be one of the following:
● SUM: The source value is divided equally among the leaf members. For example, if you enter one million in a cell with two leaf members, the leaf members receive 500, 000 each.
● AVERAGE: Each leaf member receives the same value as the source cell. In this case, the leaf members
receive one million each.
● LAST: The last leaf member receives the same value as the source cell. The second leaf member would
receive one million, and the first leaf member would remain unbooked.
Note
Automatic spreading does not occur for a dimension when the source cell is configured with NONE as the aggregation type.
If you spread an account member to its child accounts, the targets may include different account types such as Income and Asset accounts as well as Expense and Liabilities and Equity accounts. In this case, accounts with different aggregation behavior from the source account do not receive values from spreading.
For more information on the aggregation types and sign switching, see the Attributes of an Account Dimension section.
Note
When you change a booked value, its leaf members are adjusted proportionally, based on the existing values. This type of operation is not considered automatic spreading.
Manual Spreading
If you want to specify values for child members of a booked cell, you can spread the cell’s value manually. For the selected source cell, you can set spreading weights for a perspective and level that you choose. Those values are adjusted accordingly. For children at a lower level of the target dimension, or leaf members of a different
Distribution
While manual spreading allows you to allocate a value to its leaf members, you may also need to change values in a hierarchy by distributing amounts from a source member to its siblings. Distributing values can help you adjust your plan in response to new information such as a change in sales forecasts.
Unlike spreading, distribution subtracts value from the source cell to add it to the target cells. Leaf members that aggregate up to the source cell are decreased proportionally, and leaf members of the target cells are increased proportionally.
Distribution may also trigger an automatic spreading operation. That is, if you distribute a value to an unbooked sibling member, the value will then be spread to the leaf members of that target cell.
Filtering dimension members for spreading
When you perform a spreading or distribution operation, you may want to control the number of facts created by automatic spreading. In particular, if your report contains many dimensions or dimensions with many members, you may not want to spread data to all leaf members for each dimension.
During spreading or distribution, the source cell determines the members that receive values for the dimensions in the grid other than the target dimension. For dimensions that are not added to the grid, you can apply filters from the Confirm Data Input dialog. The following options are available:
● Use Point of View: The value is spread to the members that are selected in the filters applied to the view. For example, if you have set a filter for Q4 of 2016 to plan sales revenue for that quarter, values are not spread to members of the Time dimension that are outside that range.
● Refine Filters: For dimensions that are not added to the grid, you can choose members that will receive values. By default, the current point of view is applied, but you can select dimensions from the list in the Selected filters dialog to refine the members of each dimension that will receive values. For example, you might choose the Time dimension, deselect Q4 2016, and select December 2016 only.
Refining the filters already applied by the point of view can change booked values for members that you filter out, because you are spreading the aggregated value of all the dimension’s members in the point of view to a smaller subsection of those members. For example, if you refine the filter for time to only December 2016, all of the target members’ values for Q4 2016 will be spread to that month only. With sum aggregation, any booked values for the target members in October and November 2016 are reduced to zero.
Note
For a distribution operation that does not have any unbooked target members, the dialog does not appear because no automatic spreading takes place.
Note
If too many items are affected by the spreading, a message appears when you select Continue. In this case, you can refine the point of view, or filter out more dimension members in the Selected filters dialog.
Related Information
Attributes of an Account Dimension [page 21] Example: Spreading a Value [page 107]
5.3.1 Spreading a Value
Prerequisites
To manually spread values, you must have access to a report view based on a planning-enabled model, or a dynamic table based on a planning-enabled model.
Procedure
1. In the grid, select a source value that meets the following conditions:
○ The cell is booked.
○ The cell is not calculated by a formula.
○ The cell is an aggregation of at least one visible dimension, not including account or version.
2. Select Allocate values Spreading to manually specify weights for members of a specific dimension
and level.
The Spreading dialog appears.
3. From the Target Dimension list, select the dimension that you want to specify weights for. Values will be spread automatically to other dimensions, depending on the filters that you apply.
The available dimensions include visible dimensions in the grid that aggregate up to the source cell using SUM aggregation. You can’t spread along Account or Version dimensions.
4. Set the level where you want to specify weights using the (roll up), (drill down), and (set to leaf level) buttons.
When there are multiple target members with the same name, for example, Q1 in a Time dimension, the members are grouped by their parents to distinguish them.
Unless you specify weights at the leaf level, values will be assigned to leaf members using automatic spreading.
5. Set the spreading weights for target members.
If the target cells have values, those values are listed in the Weights column. You can overwrite the values by typing in new weights, or by selecting (Use values of the selected cells) and picking values from the view.
To spread to unbooked cells, switch Show unbooked members on.
Note that weights represent proportions, and not the actual value that will be assigned to members. The
6. Select Apply Spreading.
7. In the Confirm Data Input dialog, choose how to filter the dimension members that will receive values from automatic spreading.
For more information on filtering the spreading operation, see Filtering values for spreading and distribution in the Explore spreading and distribution section.
8. If you selected Refine Filters, select one of the dimensions not added to the grid from the Dimension list, and choose the members that will receive values during spreading. Repeat this step for other dimensions as necessary, and then select OK.
The source value is spread to the target cells using the weights that you specified. For leaf members of the target cells, and for other dimension members included in the spreading operation, values are spread automatically.
5.3.2 Example: Spreading a Value
The following section is a simple example of how to use automatic and manual spreading.
Prerequisites
In a report, you have set a filter on the Category dimension so that only the version that you want to work with is visible in the report view. Also, set filters so that only the Unassigned member is included in the point of view for dimensions that are not added to the grid.
Context
Your company is launching a new product, soccer shoes. You are composing a report on the projected sales of the shoes and want to spread the projected Units Sold figure across the two cost centers in the North America region. You want to weight the distribution of values based on existing figures for the sales of running shoes. You set up a report view, the grid for which is represented by the following table:
Table 17:
A B C D E F
1 Measures
2 Category
3 Version
4 Cost Center North America >
5 Accounts Products
6 Units Sold Footwear 24,200 12,800 11,400 306,600
7 Tennis Shoes 8,600 4,800 3,800 63,500
8 Running Shoes 15,600 8,000 7,600 114,300
9 Soccer Shoes - - - 128,800
Procedure
1. To enter the projected Units Sold figure, select the appropriate cell for the Soccer Shoes product (C9 in the table) and enter a value. In this case, 29,000.
The Confirm Data Input dialog appears.
2. Since you have already filtered out the dimension members that you are not using for this sales projection, choose Use Point of View. Turn the Don't show this dialog again setting on and select Continue.
The cell in which you enter the value is highlighted in yellow. The value 29,000 is equally distributed between United States and Canada cost centers. The cells for the cost centers (D9 and E9) are highlighted in yellow and each contain a value of 14,500. The Units Sold total for Footwear, United States, and Canada are also updated and the relevant cells are highlighted too (C6, D6, and E6).
3. You need to adjust the spreading of the Unit Sold value for soccer shoes based on the Unit Sold values for running shoes. To do this, select the Units Sold value (29,000 in cell C9) and choose Allocate values Spreading .
The Spreading dialog opens. The suggested spreading destination is the two cost centers, United States and Canada.
4. You want to weight the spreading of the Units Sold value according to the Units Sold values for running shoes. To do this, select the weight for the first United States member, then select (Use values of the selected cells) and choose cells D8 and E8 in the table.
The values in the cells are copied to the Weights column. The Preview column shows the adjusted Units Sold values and the percentage they make up of the total 29,000 figure.
5. To apply the new spreading, choose Apply spreading.
The spreading is applied to the current point of view. The new Units Sold values, 14, 872 and 14, 128, are displayed in the cells for United States and Canada (D9 and E9).
5.3.3 Distributing a Value
Prerequisites
To distribute values, you must have access to a report view based on a planning-enabled model, or a dynamic table based on a planning-enabled model in a story.
Procedure
1. In the grid, select a source value that meets the following conditions:
○ The cell is booked.
○ The cell is not calculated by a formula.
○ The cell has sibling members on the same hierarchy level of at least one visible dimension, not including account or version.
2. Select Allocate values Distribute .
3. In the Distribute dialog, pick the dimension along which you want to redistribute from the Target Dimension
list.
4. To distribute to members that don't have a value yet, select Show unbooked members.
5. If necessary, change the level using the (roll up), (drill down), and (set to leaf level) buttons. You can distribute values to the siblings of the source member, or the children of those siblings.
When there are multiple target members with the same name, for example, Q1 in a Time dimension, the members are grouped by their parents to distinguish them.
6. For each target member, type the amount that you want to distribute from the source member.
The dialog shows the percentage of the total amount to distribute for each member, and a preview of the target member values after the distribution.
7. Select Distribute.
If you distributed values to unbooked cells, the Confirm Data Input dialog appears.
8. Choose how to filter the dimension members that will receive values automatically spread from the unbooked cells.
For more information on filtering the operation, see Filtering values for spreading and distribution in the Spreading and Distribution section.
9. If you selected Refine Filters, select one of the dimensions not added to the grid from the Dimension list, and choose the members that will receive values. Repeat this step for other dimensions as necessary, and then select OK.
The target cells are increased by the amounts that you specified, and the value of the source cell is reduced. Cells that aggregate up to the source cell or a booked target cell are adjusted proportionately.
5.3.4 Example: Distributing a Value to Unbooked Cells
The following section is an example of how to use the distribute feature with unbooked cells as the target. In this example, using SUM aggregation for Units sold, you want to adjust your plan by distributing data from the existing number of units sold for North America to Latin America, which is currently unbooked.
Table 18:
A B C D E
1 Account Units sold
2 Product Footwear > Tennis shoes Running shoes Soccer shoes
3 Responsibility Centre 4 All Companies > 350,900 81,100 145,500 124,300 5 HQ - - - - 6 North America 48,800 17,600 31,200 - 7 Latin America > - - - - 8 Mexico - - - - 9 EMEA > 302,100 63,500 114,300 124,300 10 Germany 170,100 38,100 63,500 68,500 11 Italy 132,000 25,400 50,800 55,800
You select the value for Footwear in North America (cell B6), and select Allocate values Distribute .
In the Distribute dialog, you select ResponsibilityCenter as the Target Dimension.
On the same level as North America, you have the option of selecting HQ, Latin America, or EMEA as target members. If you drill down one level to Country, you can distribute to HQ (since it has no children at the Country level), Mexico, Germany, and Italy.
Type 6,000 for Latin America and select Distribute. Since you distributed to an unbooked cell, the Confirm Data Input dialog appears. You can use the point of view that you established, or refine it – your choices will only affect dimensions that aren’t visible in the view. After the distribution, the grid shows the following data, with the changed values shown in bold:
Table 19:
A B C D E
1 Account Units sold
3 Responsibility Centre 4 All Companies > 350,900 80,936 143,664 126,300 5 HQ - - - - 6 North America 42,800 15,436 27,364 - 7 Latin America > 6,000 2,000 2,000 2,000 8 Mexico 6,000 2,000 2,000 2,000 9 EMEA > 302,100 63,500 114,300 124,300 10 Germany 170,100 38,100 63,500 68,500 11 Italy 132,000 25,400 50,800 55,800
As a result, 6,000 units sold are subtracted from North America and added to Latin America. The value is evenly spread to the unbooked cells for different types of footwear for Latin America and Mexico, and reduced
proportionally based on the existing values for Tennis shoes and Running shoes in North America. The overall number of units sold stays the same; only the distribution is changed.
If you wanted to limit the spreading to only Tennis shoes and Running shoes, it is possible to do so by removing the Product dimension from the grid and refining the filter for that dimension in the Confirm Data Input dialog. If you selected only Tennis Shoes and Running shoes, both members would receive 3,000 units, and Soccer shoes would remain unbooked.