In business planning, users often want to plan a higher level of a structure (time, organisation, product, Customer etc.) and let the planning solution handle the distribution of values down the structure to the lowest level.
Spreading and distribution in Cloud for Analytics Planning are ways of allocating values from a single source cell in a planning cross tab to one or more target cells.
With Spreading, the value of the source cell is spread across leaf members that aggregate up to that source cell. Spreading can be performed automatically or manually. Essentially we can enter some data at level of a hierarchy and the structure below that node (lower down in the hierarchy) will be effected by the Spreading
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 (i.e. nodes in the same level of the hierarchy as the source member.
We’ll start with the Spreading functionality. In my planning enabled model I have one measure in the account dimension (Sales Revenue, with aggregation type of SUM – this influences how the spreading is performed). There are two dimensions, Business Unit and Time (both of which have hierarchies). Below is my starting and empty crosstab.
Entering a value for 2016 for Europe of 10M, it will distribute evenly across the child nodes in both of the hierarchies used (Business Unit and Time). This is the automatic Spreading.
Adjusting the 2016 values for the Business Units adjusts:
- The Europe total
- The spread to the child quarters (including for the Europe total).
Cells affected by the change you are making are coloured in yellow (in this example where I was changing the Germany 2016 value).
If I now change the Europe 2016 value to 15M it will recalculate all of the cells. Note that it distributes the 15M down the Business Units hierarchy based on the distribution factor that was previously assigned (i.e. when I manually change the values for the countries after the initial even distribution of 10M). Note that as I have not changed the quarterly values, the 15M stays evenly distributed across the 4 quarters.
If we want to use manual Spreading, with our cell selected (such as the Europe 2016 value of 15M) we can select the Allocate Values button and choose Spreading. We than have the ability to change the weighting for our target dimension, drill down the hierarchy and use a cell picker to use the values of other cells in the crosstab to use as the weighting factors.
Next up is to take a look at the Distribution functionality. I’d like to redistribute some of the value across the quarter, starting with redistributing the Q1 value for Europe (3.75M) across its siblings (Q2, Q3 and Q4). With cell selected I can select the Allocate Values button and choose Distribute.
After changing the Q2 to Q4 amounts my values are distributed across the quarters (and spread down the Business Unit hierarchy).
In a future blog we’ll explore the Allocation process which allows us additional flexibility for spreading and distributing values.