In this article, I would like to describe a typical business challenge in the context of integrated financial- and cost planning and how they are handled within SAP Analytics Cloud.
Problem definition:
In the area of planning and forecasting, for example in sales planning or as well as in cost planning, typically business user must enter data for the following periods (month, quarters or total next year). Many of these business processes are correlating to seasonal conditions, for example, the sale volumes in the clothing or food industry. Or cost structures in the company such as heating or vehicle fleet costs. Such seasonal conditions must be considered in the planning processes. Often this is supported by a prefilling process, with adjusted actual data from the past converted to future scenarios or by predictive functionalities to create a predictive base forecast. But often it is still a manual process to plan and simulate values for the required planning periods. This article aims to show another way how business users can create their own seasonal curves and then apply them to the distribution of budgeted figures. It is also possible to create and maintain several curves and then use them for distribution as required.
Technical Solution:
In this case I use a typical Sales Planning process with related a planning model. Based on the planning model, a data entry story page is created with the product hierarchy and the key figures “Price”, “Sales” (sales amount), “Revenue” (calculated according to price * sales) and the key figure “Seasonal Curve” to store the seasonal distribution in the rows. The months for the planning year, the year total and the versions / scenarios are shown in the columns.
Story Page: Sales Planning
If we now start planning the “sales amount” for the product “P1110” we can use the SAP Analytics Cloud Planning Panel to create an equal prefilling for all the month in the planning year.
Planning Panel:
The result will be that the targeted sales amount of 360,000 pieces will be evenly distributed over all months. Every month receives 30,000 pieces.
Distribution:
The next step is to create an equal prefilling over all month to the seasonal curve. This can be also done with the SAP Analytics Cloud Planning Panel.
Planning Panel:
As a result, every month in the planning period for the “Seasonal Curve” has the value 1.
Distribution:
Now the business user is able to adjust or to change the values for the “Seasonal Curve” along his requirements and experience manually very easy.
Adjustment:
In this example we change the values for the “Seasonal Curve” for the month May to 4 and for the Month June to 5. All the other month will keep the value 1. With this activity we have created our own “Seasonal Curve” for the product P1110, which we can now apply to the sales volume planning for the measure “Sales”. This can be done by the execution of the DataAction “Seasonal Curve”.
DataAction: Execution
The DataAction is designed that the business user is able to change the settings for the used Version/Scenario, Company, Customer and Product. This gives the user the flexibility to use this DataAction process for all areas of the sales planning. After the data action calculation has be finished, the sales figures for the planning periods are adjusted to the prior defined seasonal curve.
Result:
This functionality gives the business user, the intuitive possibilities to adjust his sales planning according to seasonal conditions without depth knowledge or specialist training of the planning solution. The DataAction which is used to provide this functionality is shown in the next picture.
DataAction Visual:
The DataAction can be created in a visual way and the selection inside a wizard via drag and drop functionalities. Additional it is possible to write a script or to create the script out of the visual configuration. The resulting script is shown below.
DataAction Script:
//###############################################
MEMBERSET [d/I_COMPANY] = (%Company%)
MEMBERSET [d/I_CUSTOMER] = (%Customer%)
MEMBERSET [d/I_PRODUCT] = (%Product%)
MEMBERSET [d/Date] = "202101" TO "202112"
//Initialize buffer cells with 0
Data ([d/Date]="202101", [d/I_SALES_MEASURE] ="#")=0
Data ([d/Date]="202102", [d/I_SALES_MEASURE] ="#")=0
//Loop over all month for given year
FOREACH[d/Date]
//Summing up the sales figures for all months for given year
DATA([d/Date]="202101", [d/I_SALES_MEASURE] ="#")=
RESULTLOOKUP([d/Date]="202101", [d/I_SALES_MEASURE]="#") + RESULTLOOKUP([d/I_SALES_MEASURE] = "MS10000")
//Summing up the curve values for all months for the given year
DATA([d/Date]="202102", [d/I_SALES_MEASURE] ="#")= RESULTLOOKUP([d/Date]="202102", [d/I_SALES_MEASURE]="#") + RESULTLOOKUP([d/I_SALES_MEASURE] = "MS40000")
ENDFOR
// Calculate and write the distribution for all months
FOREACH [d/Date]
DATA([d/I_SALES_MEASURE] = "MS10000") =
RESULTLOOKUP([d/I_SALES_MEASURE] = "MS40000") /
RESULTLOOKUP([d/I_SALES_MEASURE] = "#", [d/Date] = "202102") *
RESULTLOOKUP([d/I_SALES_MEASURE] = "#", [d/Date] = "202101")
ENDFOR
//Clear buffer cells
DELETE ([d/Date]="202101", [d/I_SALES_MEASURE] ="#")
DELETE ([d/Date]="202102", [d/I_SALES_MEASURE] ="#")
//###############################################
Outlook:
This case is an example to give business user the possibilities to create their own seasonal distributions in a flexible way. Another possibility is to use predictive capabilities to support the planning and forecast process. The SAP Analytics Cloud combines both possibilities that can complement each other perfectly.
- SAP Managed Tags:
2 Comments
