Skip to Content
Technical Articles
Author's profile photo Thomas Haeusel

Bill of Material (BOM) and Material Cost Simulation with SAP Analytics Cloud Planning

In this article, 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.

Assigned tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Thorsten Kalweit
      Thorsten Kalweit

      Very nice Blog Post, Thank You 🙂

      Do you think there is an option for handling different unit of measures (e.g. kg, volume, length etc)? I am still missing this feature in SAC (Currencies no Problem...)

       

      KR

      Author's profile photo Thomas Haeusel
      Thomas Haeusel
      Blog Post Author

      Hi Thorsten,

      yes, it is possible. For this case the Product Dimension need additional Properties and the Account Dimension need addition Accounts/Measures to calculate the price per unit like KG or Liter etc..

      Author's profile photo Miguel Domenech Aparisi
      Miguel Domenech Aparisi

      Thank you very much... very useful as we are preparing a model that will update all the SAP cost calculations (agrgated by product family)... and would like to make simulations like this one!