Planning our sales for the next quarter of different product categories and countries is a time-consuming task since each will show different variations, thus requiring a separate planning for each product category and country. So why not use machine learning to predict these values for the next quarter to help our planning colleagues to make the planning more accurate and help them to be more efficient? Therefore, SAP Analytics Cloud with Smart Predict offers a toolset to the business user (in this example our planning colleagues) that addresses common predictive use cases. With this the business analyst can solve predictive use cases on his own.
This guide will walk us through the process of enriching a planning model with a sales forecasting for an online shopping portal that sells beauty and pharma products in 6 categories in 3 different countries. This data can be downloaded here.
Remark: The explained feature is delivered on wave 2020.11 and for now available on some tenant but for others it is planned QRC availability is for mid-August. So, it can happen that this feature is not available on your SAP Analytics Cloud tenant yet, but definitely something to look forward to😉. While you are waiting and still want to do some time series forecast in SAP Analytics Cloud have a look at this blog post which uses a similar data set and shows you how to do the prediction in Smart Predict on a data set rather than on a planning model:https://blogs.sap.com/2018/10/31/hands-on-tutorial-sap-smart-predict-product-forecast/
|Sales||Amount of sales by product category|
|ProductCategory||Description shows the product category|
|CountryCode||Code C1-C3 for the country where the product was sold|
First, log on to a SAP analytics Cloud instance.
Before we start, have a look at you profile setting and make sure the number formatting is set to “1,234.56”.
After the logon the dataset needs to be uploaded. To do this we click on the menu on the top left, select “Create” and click on “Model”.
On the new screen, we select “Import a file from your computer”.
On the pop up we select the source file “product_forecast_planning.csv”, click “Import”.
Now, that we have uploaded the data set, we can start to finalize our model. We can check that all columns are detected correctly which means in detail: “Date” as a Date, “Sales” as Measure and “ProductCategory” and “CountryCode” as Generic Dimensions. Furthermore, we need to select “Enable Planning” on the menu on the right to make sure that we can use this model for our planning purposed.
After this we click on “Create Model” on the lower right corner and confirm our decision by selecting “Create” on the pop up. To save this model we select a folder and give it a name like product_forecast_planning on the pop up that now appeared. Then we click “ok” and now we see that our model is being created.
On the screen that now appears we have the option to get an overview of the build model and also make some adjustments and few other things, but since such aspects are not on focus for this Hand-On tutorial we will start directly by creating a story based on this model.
We navigate to the menu and select “Create” followed by “Story”.
We want to compare the time series forecast of product groups or different product categories from the same product group.
To do this we start with a new story and select “Add a Canvas Page”.
On the pop up we click on “Select other model” to choose the model we just built and then we click “ok”.
Our screen should now look like this:
Before we adjust the table so that it fits to our needs, we want to create a new and private version for our sales planning. To do this we click on the 3 dots under “More” on the upper bar and select “Version Management”. Small hint: make sure the table is selected. Otherwise, we can’t select the “Version Management”.
On the right we now click on “Copy”.
On the pop up we give the new version a name like “sales forecast” and select the Category “Forecast”.
After clicking on “ok”, we now see in the panel on the right that a private version was created.
We can close this in the lower right corner, so that we now can adjust our table so that it shows more than just one value. First, we click on “Add Measure/Dimension” in the builder panel on the right.
On the dropdown menu we select “CountryCode” and “ProductCategory”.
Now, we can see the sum of the actual values for each product category in each country.
This is a good start, but we want to see it on a monthly basis and plan the next quarter.
For this we choose the “forecast layout” from the dropdown menu which appears by clicking on the “CrossTable” icon in the builder panel on the right and confirm our selection with ”Ok”.
On the “Forecast Layout” panel that now appears on the right we select “Last booked actuals” as “Cot-over Date” and select “3” and “Month” for “Look ahead additional”. Then we click “Apply”.
Now our table should look like this:
We can click on the arrow behind “Q3” to see the monthly fields. As we can see the values for Q3 are empty.
For these 3 Months we now want to generate a forecast to support our business analysts to build their business plans faster and more accurate. But before we start the predictions, we need to save our story, by clicking on the little disc icon and selecting “Save”. On the pop up we select the folder where we want to save it and give it a name like “product_planning”.
Now we can start our predictions by selecting “Create” and then “Predictive Scenario” on the menu.
A predictive scenario set of use cases with common characteristics. SAP Analytic Cloud’s Smart Predict currently offers 3 predictive Scenarios: Classification, Regression, Time Series. The tool gives you guidance with a quick explanation and an example for each Scenario.
The user now has to follow 3 simple steps:
- Choose the predictive scenario that matches his use case.
- Train the model with historic data, i.e. use a data set where sales figures are known. The statistical algorithm will “learn” from this data set, i.e. find trends, seasonal variations and fluctuations that characterize the sale of a certain product. There should be enough (3-5 years) data available to learn from.
- Save the forecast back to the model, i.e. forecast sales for a given period of times. The statistical algorithm will apply the patterns learnt in the previous step to the new data and predict sales for the chosen number of time periods.
The variable that contains the sales in the learning phase and is predicted in the application phase is called the target variable.
The following screen shot shows the three options classification, regression and time series. Under each option there is a description to make it easier for the user to select the right scenario for each use case. In this exercise, we want to do a sales forecast for each product. Based on the descriptions of predictive scenario types, you can see that a time series will be able to address our needs. So we select Time Series at the bottom.
On the pop up we give the model a name, e.g. “product_planning_forecast” and save it in our preferred folder.
Now we can create our Predictions.
On the “Settings” panel on the right we need to select the input data for our predictive scenario. This data contains historical data that we use for training.
We select our planning Model “product_forecast_planning” from the folder.
The signal variable is our target variable, i.e. the variable to predict. We select “Sales” as the signal variable.
The date variable contains the time dimension. In this example the date variable refers to the date column in the data set.
Since we want to predict our Q3 on a monthly basis, we select “3” as “Number of Forecast”.
The “Entity” variable allows us to automatically create one time series for each member of an entity. Furthermore, we can select multiple entities. In our example we want to create one time series per country code and the product category. Therefore, we select “CountryCode” and “ProductCategory”.
We could define the last date for the training dataset, but we stick with the default setting, so we select “Process” as “All Observations” and “Until” as “Last Observation”.
We could choose the date of the last observation and we could define the last date. But for this scenario we leave it as it is.
Let’s run the predictive model with these settings.
We click “Train&Forecast” on the bottom right.
Please be patient since this might take a couple of minutes.
Upon completion of the training process, we see that a forecast was created for each country and product category. Among others, the horizon-Wide MAPE (Mean Absolute Percentage Error) is shown. It is the evaluation of the “error” made when using the model to estimate the future values of the signal. In this case you see the mean MAPE value of all the segments, the average quality of the segmented time series is 4.08%. In short just like in school we have to give our predictive model points. Based on these points the forecasts are listed in top and bottom entities.
We select one from the top segments, e.g. C6-perfume.
When forwarded to the next screen we see the detailed time series chart as well as all forecasts.
We see the following information in this line chart:
|Signal||The green curve||The information contained in the training dataset|
|Predicted Signal||The blue curve||The signal predicted by the generated model|
|Error Bars||The blue area around the end of the blue curve||
The zone of possible error where the predicted signal could be. The error bars are only displayed for the forecasts.
Note – The error bars are equal to twice the standard deviation computed on the Validation dataset.
|Outlier||A red circle||
A point where the predictive curve is very distant from the real curve.
Note – An outlier is detected when the absolute value of the residuals is over three times the value of the standard deviation computed on the Estimation dataset.
We go back and have a look at the bottom segments to see the prediction here. We can also check the other time series of interest. For some you may find outliers that have been detected by the tool. Outliers are values that we could not explain with the given data. These are indicators that we might include more explanatory variables like information about special events, marketing campaigns etc.
The outliers are points where the predictive curve is very distant from the real curve. They are represented by a red circle on the plot.
We could also use the drop-down menu on the top left to switch the entities.
We can also select signal analysis which displays shows the detected signals like cycles, trend and fluctuation.
The trend is the general orientation of the signal.
The cycles are periodic elements that can be found at least twice in the training dataset.
The fluctuation is what is left when the trend and the cycles have been extracted.
After we had a deeper look at the forecast and were convinced of its quality, we now can save it in our planning model. We click on the little factory icon on the top left to select the version, in this case our private version “sales forecast”, we created in the beginning and click on “Save”.
On the bottom we can see that the model is being applied. When it is finished, we have a look at the result.
We navigate to the menu and select “Browse”, followed by “Files”.
We navigate to the folder where we saved our Story “product_planning” we build before and select it.
Our values for Q3 are now filled with the forecast we just created so we can use them as a starting point for our sales planning. Of course, we can adjust them manually in case our forecast is not sufficient, or we know things our data was not aware of. This way we can combine both our forecast based on the historic data and the expertise of our planning colleagues to get the best results.
As a last remark in this tutorial let me point out that SAP Analytics Cloud has a lot more visualization options than just the table we focused on in this scenario. So, you could enrich this story with more plots to make some findings and insights easy to consume and make better business decisions. Be creative and add the company logo or adjust the styling to the company’s corporate identity, add more data, nice charts etc. Just try it out.
The official SAP analytics Cloud tutorial and the playlist really helped me to get started:
Have fun with doing your own predictions and building amazing dashboards.