In this Post, I would like to describe a typical business challenge in the context of integrated financial- and cost planning and how it can be handled within SAP Analytics Cloud.
Problem definition:
In the area of product cost calculation, it is often necessary to break down the parts list for the ingredients or raw materials contained in a product. There are several challenges within this process. On the one hand, the materials must be assigned to the products or the raw materials need to be mapped to an ingredient which is a component of certain product. On the other hand, there is a need to simulate the price development of individual raw material classes and their effects on the total material costs of a product.
Technical Solution:
For this case I have created two Planning Models within SAP Analytics Cloud. The first planning model named BOM with the Product dimension and an included hierarchy for the combination of Products and included raw materials. And a second planning model named BOM_PARAMETER to store the price simulation parameter for the raw material classes.
Planning Model: BOM
The specific topic on the planning model is the structure of the dimension BOM_Product. This dimension contains of all Products with are structured in a hierarchy. Each product contains the materials from which it is built as subordinate child elements. In addition, the “Material Category” property (Attribute) is used to assign the raw materials to a material class. This allows to define which raw material belongs to which material class. That enables the business user to simulate the impact of the price development of a material class to the raw materials and to simulate the total material costs development based on the price development.
Dimension: BOM_Product
The BOM_PARAMETER planning model is used to store the raw material price development for the raw material classes. The Account dimension includes in this case two elements: The “Index Change” and the “Percent Change”. So, it is possible to simulate total index changes as well as the changes in percent.
Planning Model: BOM_PARAMETER
The dimension BOM_MaterialCategory contains the raw material classes which are used in the BOM planning model in the dimension BOM_Product as elements for the “Material Category” property. This allows to store and simulate index and percentage factors over the timeline which are used for DataAction calculation on the BOM planning model values.
Dimension: BOM_MaterialCategory
For the simulation of Material Cost in the BOM planning model, a DataAction is used, which reads out the existing raw material price from the BOM planning model then it looks into the BOM_PARAMETER planning model regarding the raw material class values and calculates increases or decreases of the raw material prices for the related product ingredients for the simulated scenario.
DataAction: Visual
The DataAction represents a very useful option to calculate and simulate planning values in SAC planning model. In this case the two models are used. Of course, it would be also possible to include all the parameter in one combined planning model. The decision to split the models was done to demonstrate the concept of parameter models which can be used to store parameter for any kind of planning and forecasting topis. The advantage is to be very flexible in the design of the planning models. This is the DataAction Advanced Formular Script to loop over the needed month and to read out the initial values in the source version / scenario. Than to read out the parameter from the parameter planning model and to calculate the changes in material prices and to write back into the BOM planning model.
DataAction: Script
#########################################################
MEMBERSET[d/Date]=”202101″ TO “202112”
FOREACH[d/Date]
IF [d/BOM_Product].[p/MaterialCategory] = “” THEN
DATA([d/BOM_Measure] = “M2200”) = ( RESULTLOOKUP([d/BOM_Measure] = “M2200”, [d/Version] = %SourceVersion%) * 1 )
ENDIF
IF [d/BOM_Product].[p/MaterialCategory] = “Rest” THEN
DATA([d/BOM_Measure] = “M2200”) = ( RESULTLOOKUP([d/BOM_Measure] = “M2200”, [d/Version] = %SourceVersion%) +
RESULTLOOKUP([d/BOM_Measure] = “M2200”, [d/Version] = %SourceVersion%)*
LINK([BOM_PARAMETER] ,[d/Version]=”public.Actual”, [d/BOM_MaterialCategory]=”M1000″,[d/BOM_Measure]=”2000″ )
)
ENDIF
IF [d/BOM_Product].[p/MaterialCategory] = “Steel” THEN
DATA([d/BOM_Measure] = “M2200”) = ( RESULTLOOKUP([d/BOM_Measure] = “M2200”, [d/Version] = %SourceVersion%) +
RESULTLOOKUP([d/BOM_Measure] = “M2200”, [d/Version] = %SourceVersion%)*
LINK([BOM_PARAMETER] ,[d/Version]=”public.Actual”, [d/BOM_MaterialCategory]=”M2000″,[d/BOM_Measure]=”2000″ )
)
ENDIF
IF [d/BOM_Product].[p/MaterialCategory] = “Aluminium” THEN
DATA([d/BOM_Measure] = “M2200”) = ( RESULTLOOKUP([d/BOM_Measure] = “M2200”, [d/Version] = %SourceVersion%) +
RESULTLOOKUP([d/BOM_Measure] = “M2200”, [d/Version] = %SourceVersion%)*
LINK([BOM_PARAMETER] ,[d/Version]=”public.Actual”, [d/BOM_MaterialCategory]=”M3000″,[d/BOM_Measure]=”2000″ )
)
ENDIF
IF [d/BOM_Product].[p/MaterialCategory] = “Copper” THEN
DATA([d/BOM_Measure] = “M2200”) = ( RESULTLOOKUP([d/BOM_Measure] = “M2200”, [d/Version] = %SourceVersion%) +
RESULTLOOKUP([d/BOM_Measure] = “M2200”, [d/Version] = %SourceVersion%)*
LINK([BOM_PARAMETER] ,[d/Version]=”public.Actual”, [d/BOM_MaterialCategory]=”M4000″,[d/BOM_Measure]=”2000″ )
)
ENDIF
ENDFOR
#########################################################In an SAP Analytics Cloud Story, it is now possible to create a simulation page with a table which includes the products, raw material and raw material classes in the rows and the specific version and timeline in the columns.
Story: Material Cost
Additionally, part of the SAP Analytics Cloud Story is, a second table below based on the BOM_PARAMETER planning model. Now it is possible for the business user to change percentage price developments of raw material classes over the timeline. And with the execution of the DataActionTrigger, the Source- and TargetVersion can be selected and the calculation will be performed.
Story: DataAction Trigger Execution
After the execution of the DataAction, the simulation results are perfectly visible within the planning page. In the table on the top the results are displayed which are calculated based on the price development simulation in the BOM_PARAMTER planning table below.
Story: Simulation Result
Outlook:
In this case, 2 data models were used for the implementation. This has the advantage of being flexible. If the task had been implemented in one data model, some of the calculations could have been carried out as online calculations without DataAction. However, this would have made the data model a bit more complicated. For this reason and to show the concept of the parameter models, 2 planning models were used. The product dimension was set up in such a way that the products have their components as child elements in the hierarchy. Depending on the number of products and product components, it would also be conceivable to split the products and the components into 2 separate dimensions.
