Top-Down Distribution 
Use
The distribution functions allow you to determine the way in which totals are distributed to the line items. The following functions are available:
· Distribution Methods
In the appropriate selection list you can first choose between the following methods:
¡ Analog
In this method, values are distributed according to the previous percentages by which the total had been split amongst the line items (addends). This method is set as the default.
The following table represents a cell area in Microsoft Excel. The top cell is the totals cell, the four cells below are the line items. The total is 10. This is distributed to the line items in a ratio of 20%, 50%, 30%, 0%.
Totals Cell |
10,00 |
Cell 1 |
2,00 |
Cell 2 |
5,00 |
Cell 3 |
3,00 |
Cell 4 |
0,00 |
If you enter the value 13 as the total, the Analog method distributes the new values according to the previous ratios:
Totals Cell |
13,00 |
Cell 1 |
2,60 |
Cell 2 |
6,50 |
Cell 3 |
3,90 |
Cell 4 |
0,00 |
¡ Evenly
In this method, values are distributed evenly to all the line items of the total.
If the Evenly method is applied to the table above, the previous ratios are ignored and the values are now distributed evenly.
Totals Cell |
13,00 |
Cell 1 |
3,25 |
Cell 2 |
3,25 |
Cell 3 |
3,25 |
Cell 4 |
3,25 |
It is also important to remember that the Lock Cells and Unlock Cells functions influence this. If, for example you return to the previous table:
Totals Cell |
13,00 |
Cell 1 |
2,60 |
Cell 2 |
6,50 |
Cell 3 |
3,90 |
Cell 4 |
0,00 |
You lock cell 4 using Lock Cells and then apply the Evenly distribution method. The locked cell, cell 4, is not affected by the redistribution of values:
Totals Cell |
13,00 |
Cell 1 |
4,33 |
Cell 2 |
4,34 |
Cell 3 |
4,33 |
Cell 4, locked |
0,00 |
In the next step, you remove the lock for cell 4 (Unlock Cells) and again apply the Evenly distribution method. Values are distributed to all cells evenly:
Totals Cell |
13,00 |
Cell 1 |
3,25 |
Cell 2 |
3,25 |
Cell 3 |
3,25 |
Cell 4 |
3,25 |
In order to avoid rounding errors, the distribution methods use the number and unit formats of the individual cells. Therefore you should always make sure you use consistent formats.
· Redistribute
Redistribution normally only takes place when a new value is entered in the totals cell. The Redistribute function is also useful if:
a. No new total has been entered, but
b. The distribution method has been changed.
This function replaces the need to re-enter the same total just so as to perform redistribution.
· Reference On/Off
Determination of whole reference columns or rows for the distribution method. You use this function to map the existing distribution schema from one column or row to other columns or rows.
Depending on the structure of the planning layout, you are able to highlight a whole column and/or row as a reference for distribution, that is, as its own distribution method. Proceed as follows:
a. Select a complete column or row
b. Choose Reference On/Off
The selected column or row is now displayed in the distribution methods selection list. You are then able to use it as an additional distribution method at any time.
The following table represents a cell area (two columns) in Microsoft Excel. The top cell is the totals cell, the cells underneath it are two sub-totals cells, each with four line items. The total of column 1 is 13, and is distributed to the other cells as shown in the table. The total of column 2 is 0.
Totals Cell (A+B) |
13,00 (Column 1) |
0.00 (Column 2) |
Sub-Total A |
10,00 |
0,00 |
A – Cell 1 |
2,00 |
0,00 |
A – Cell 2 |
5,00 |
0,00 |
A – Cell 3 |
3,00 |
0,00 |
A – Cell 4 |
0,00 |
0,00 |
Sub-Total B |
3,00 |
0,00 |
B – Cell 1 |
0,75 |
0,00 |
B – Cell 2 |
0,75 |
0,00 |
B – Cell 3 |
0,75 |
0,00 |
B – Cell 4 |
0,75 |
0,00 |
You want to copy the existing distribution schema from column 1 to column 2.
i. Select all of column 1
ii. Choose Reference On/Off
Column 1 is included in the distribution methods selection list and is automatically selected as the active method.
iii. Enter the required values in column 2 for the total or sub-totals. In this case, 26 is entered as the total. Values are now distributed in column 2 with reference to column 1. Column 2 now looks like this:
Totals Cell (A+B) |
13,00 (Column 1) |
26.00 (Column 2) |
Sub-Total A |
10,00 |
20,00 |
A – Cell 1 |
2,00 |
4,00 |
A – Cell 2 |
5,00 |
10,00 |
A – Cell 3 |
3,00 |
6,00 |
A – Cell 4 |
0,00 |
0,00 |
Sub-Total B |
3,00 |
6,00 |
B – Cell 1 |
0,75 |
1,50 |
B – Cell 2 |
0,75 |
1,50 |
B – Cell 3 |
0,75 |
1,50 |
B – Cell 4 |
0,75 |
1,50 |
If you want to delete particular columns or rows as references from the distribution method selection list, proceed as follows:
a. Select the appropriate original column or row in Microsoft Excel
b. Choose Reference On/Off
When you use back-end functions (for example, planning functions or Previous/Next/Other Combination), reference columns and rows may no longer be valid. The system checks whether these references still exist as before in the new screen in the same columns or rows with the same characteristic values. Where necessary, the reference is simply deleted from the distribution methods selection list.
· Calculation Order
Determine the distribution order by columns then rows or by rows then columns.
A planning layout can contain column totals and row totals at the same time. If you change the values of “cross-totals” of this type, all line items affected are redistributed, or recalculated, according to the top-down principle. As the default, values are first distributed by column and then by row. However, if it makes more sense for values to be calculated for the rows first and then the columns, you can reverse this order in the selection list of the function.
If the active distribution method is a reference column (see Reference On/Off), distribution is always carried out as follows:
a. According to the reference column for the column
b. According to the Analog method for the row.
The exact opposite is true if the active distribution method is a reference row.