I have recently been using SAP IP distribution based on reference data planning function to perform the allocation of budget and plan expense data based on certain key values. In my scenario all the costs generated by shared cross company functions such as HR, IT or Transportation are allocated to lower level departments based on 'allocation keys'. These allocation keys indicate what percentage share of the total cost each department should pay.
Using the three categories I mentioned earlier as an example the types of allocation methods you could you for each shared function are:
- HR - Total number of staff in each department
- IT - Total number of PC's used by each department
- Transportation - Total number of company cars used by the department
In some cases different measures can be used together in a formula to create more complex keys, an example would be:
- Transportation - (Total number of company cars used by the departments * 0.90) + (Total number of staff in the department * 0.10)
In the example above the total number of staff could be used as an indicator as to how to share out the company wide taxi and train fare costs. The company cars would have by far the higher cost so that value makes up 90% of the formula and the number of staff just 10%. This is just a simple and unrealistic example but you get the idea, I have seen accountants create so crazy formulas so it can be a powerful option if used correctly. One of the hardest parts of this exercise is compiling all the data, accurately and in a format you can work with, once that is done you can start work in the SAP Planning Modeller.
The basic high level steps are as follows: Firstly step is to create your aggregation level on top of your (likely) Multi-Provider that will join; your financial expenses data, the reference data that contains the allocation keys used to distribute the costs fairly and the key figure that you want to populate once the distribution logic has run. Next you can create the distribution planning functions, select Distribution by Reference Data as the type.
Step two is to select the key figure that will contain the expense amounts you want distributed:
(In the example I used previously this would be the shared cross company function costs)
Then select the key figure that contains the reference data distribution keys and reference values:
(In the example I used previously this would be the total number of staff, PCs or cars etc.)
 | You also identify the key of reference data to read, that is specify how the reference data differs from the plan data; if you choose year form example the the logic will select all reference data that has the same values, except year.
|
| Once the planning function is executed the total expense is distributed by all departments using the reference data keys. |  |
There are a couple of things worth pointing out about using this method;
- If you distribute £100 from HR costs to four departments at £25 each then behind the scenes -£100 is posted to the HR amount key figure, which is understandable and correct as otherwise your data would be doubled up. If however you are required to report on that original figure at a later date then you need to ensure you have taken a copy of it first, this can easily be achieved in another planning function and the two can be linked together in a planning sequence - you just need to be careful in your reporting that you don't report these and the distributed values together in the same report for the aforementioned reasons.
- It's worth noting that if you allocate by percentages and the total sums to over 100% then that total is taken as 100 and all other values weighted accordingly, for example: 10,90,20 = 120, so the new weighted values are: 8.3, 75, 16.7 = 100.
And I'll finish with a few troubleshooting tips I picked up:
- Ensure that key of reference data is correct whenever the structure of the underlying InfoProviders changes.
- Don't go for the big bang approach! Once the planning function is built create a test query on a small sub-set of data and validate it works first.
- Any changes to InfoObjects used by the underlying InfoProvider that your Aggregation Level is built on will cause it to de-activate.