Dear enthusiasts, experts and followers of SAP Profitability and Performance Management, my name is Olga, and in my blog post I’d like to introduce the implementation of interactive calculations in SAP Profitability and Performance Management by means of the View and Writer functions. As a part of SAP Profitability and Performance Management team, I’ve gained experience around various functions of this product to answer corresponding business questions. One of such most popular questions concerns profitability allocations which require iterative calculations. Let me give you a step-by-step overview of one of the possible solutions to this question based on a sample business use case.
The SAP Profitability and Performance Management covers various business cases, e.g. to calculate tax, profitability or sustainability KPIs. In this business case example, the requirement is to calculate profitability of the end product (P3) for each month (January, February, March). To produce P3, another products (P2) and additional ingredients are used, and P2 consists in its turn of another product (P1) and additional ingredients; for the production of P1, specific ingredients are also used. I would like to point out here that the selected months described in this business case are the sample months – the below described iterations and calculations can be applied to any period.
The starting point is that the production and consumption amount of each product is known as well the unit price for each ingredient:
Legend:
To calculate the profit of P3 in each month, it is necessary to calculate the weighted average costs for P3, for which purpose, the average weighted costs should be calculated for P1 and then for P2 accordingly.
To use the weighted average model, one divides the cost of the goods that are available for sale by the number of those units still on the shelf. This calculation yields the weighted average cost per unit—a figure that can then be used to assign a cost to both ending inventory and the cost of goods sold.
Let us start with calculating the costs for P1, for which we have the necessary data:
Then we use the P1 costs data to calculate the unit price at the end of term for all three months for P1 as one of P2 ingredients:
The unit price at the end of term can be calculated when the amount and the price by the end of term are known, for which the “cumulated” and “used in production” steps are needed. The iteration in this case goes row by row for each category in the period. So, the amount for January at the term start is 0, new production corresponds to production amount, which is given. The cumulated result is the sum of term start and new production. Then there is a given amount used in production, and at the end of term is the subtraction of the end of term amount from the cumulated results. For the next period, the amount of the term start equals the amount at the end of term from the previous period. The price and unit price for P1 are then calculated accordingly.
The P1 unit price at the end of each term is used to calculate P2 costs:
So, in our example, the process of weighted average costs calculation is twofold iterative and interrelated: on the one hand, the costs for each product are calculated based on the other products and ingredients used in the production, and on the other hand, where the actual iteration will be shown later, to calculate price and unit price for each period category: new, produced, cumulated, used in production, end of term. To calculate costs for P2 for each month, we need to calculate the unit price in the end of term for P1, and the same calculation process is needed for P2.
Create Iterations with View and Writer in SAP Profitability and Performance Management
The first step is to calculate the costs emerged for production of P1. Then iteratively row for row, the amount, price and unit price are calculated for each product lifecycle category. The term start category in January is always 0 for each product, for other months it is the amount not used in production, and thus taken in the next month into account. Thus, to calculate the weighted unit price for each product and month, three iteration cycles are needed for the three mentioned months. The results of the weighted unit price for each month are then used to calculate the production costs of the next product (P2), as the previous product (P1) is one of the ingredients for it, and so the iteration continues.
There are several possibilities for iterative calculations in the SAP Profitability and Performance Management, one of which is to use the View and Writer functions.
For this business case, we would need to create three separate iterations for each product, as the results of the first iteration will be used for the input function of the next iteration. To create the Writer + View iteration, we would need
- the Input Model Table or a similar function (with the given and possibly calculated data)
- the Output Model Table (where the results will be written)
- the Writer with the Delete and Insert type, which uses the input function
- the Join/ Allocation or a similar function, where all needed calculations (for Amount, Price and Unit Price) take place. The input function for the Join is the Output Model Table
- the Writer with the Insert type, which inserts the data from the Join/ Allocation into the Output table
- the View, which uses the second Writer with the Insert type and defines the iteration:
Tips and Tricks
After the iteration functions has been run, the output table will show results of all iteration steps. To use the results from the last iteration step, your Output Model Table should have the ID field, and the Join has a rule with the calculation ID = ID +1. The results can be then filtered on the last ID value (in our case it is 4).
As the direction of the calculation is row for row, where the next row uses the results of the previous one, the LAG Window Function is used in the calculations, which returns the value of the offset rows before the current row. To structure the rows correctly, use the numbers for Categories – they would ensure the correct row order. When all iterations are finished, the end results for P3 Unit Price are used to calculate COGS which in its turn is used to calculate P3 Profit for each month.
Conclusion
To sum up, the View and Writer functions of the SAP Profitability and Performance Management is one of the ways to deal with iterative calculations. In the calculation formulas use the LAG Window Function and make sure the rows numbers are used correctly. Don’t forget to add the ID field to the Output Model Table to be able to filter the end results of the last iteration circle.
I hope you enjoyed reading this blog post, and I am looking forward to your likes and follow requests. You are very welcome to comment and ask questions, write your recommendations and suggestions for the SAP Profitability and Performance Management topics that are interesting and useful to you, and I will do my best to fulfil your appetite for knowledge.
Thank you and see you soon!
- SAP Managed Tags:
9 Comments
