Personal Insights
How to create centrally managed Forecast Templates in SAC Stories
In this blog post we will learn how to create centrally managed/maintained dynamic Forecast Layout templates to support planning and analysis process in SAC stories.
Business Scenario:
Financial forecasting is a crucial aspect of any business or financial planning. It involves predicting future financial performance based on past performance and current trends. By creating accurate financial forecasts, businesses and individuals can make informed decisions about future investments, expenses, and revenue streams.
A rolling forecast is a financial forecasting method that involves regularly updating a forecast as new data (Actuals) becomes available. This type of forecasting is particularly useful for businesses that operate in rapidly changing environments.
Forecast can have varied granularity like Monthly/Quarterly/Annual etc. and they allow businesses to continuously update their financial forecasts and adjust their plans accordingly, in order to stay on track and meet their goals.
A sample Forecast layout looks like below.
Forecast Layout in SAC Stories:
If you are using a planning Model, SAC offers a forecast layout to be chosen in table widget in stories. In the builder panel one can select the forecast layout option as shown below.
The builder panel offers a host of option to customize the layout as per the needs.
| Layout fields | Information |
|---|---|
| Look back on | Select the version to use for looking back. |
| Look ahead on | Select the version for forecasting or looking ahead. |
| Cut-over date |
Options for cut-over date:
|
Timeframe: options include the following:
| Type: Forecast | Granularity:
|
| Look back additional: number of intervals |
|
| Look ahead additional: number of intervals |
|
Derive the Forecast version and the cutoff dates from attributes of version and thus creating centrally managed Forecast Templates
Above section highlights the options to choose the Forecast version and cutoff dates for Actual. But sometimes business needs require us to maintain the Forecast versions and cutoff dates centrally especially if we have more than one Forecast layout and do not want to update all the stories due to change in forecast cycle.
In below steps I will show you how we can achieve a dynamic Forecast template using some attributes in Version dimension and use of FIND() formula.
In SAC All the three layout configuration option namely Look Back on, Look Ahead on and Cutover date offer to select the Calculation Input Control option. This option allows us to add the dynamic character to all these 3 variables.
In below example I will use the Calculation Input Control option to derive my Forecast Version (Look Ahead on ) and the cutoff Date by reading the attributes of my version dimension.
The version Dimension will be configured as below. We add two attributes Forecast_Version and Cutoff_Date to our version dimension.
Now that our Version attributes are done we create our Forecast layout in SAC stories.
I will select the SAC table widget and choose the Forecast Layout option. On the Forecast layout configuration I select Calculation Input Control for both Look back on and Cutoff Dates. The granularity for Forecast has been set to Months.
In both the calculation input control option we choose the formula option to determine the variables.
Formula to derive the version as below.
FIND('X', [d/Version].[p/Forecast_Version], [d/Version].[p/ID])Formula to derive the cutoff dates for Actuals
FIND([@ForecastVersion],[d/Version].[p/ID],[d/Version].[p/Cutoff_Date])Output:
Now that we are done with the configuration steps, lets see the template in Action.
With Cutoff Date set as 202203 (March 2022) in Version dimension and Forecast marked as X for selected version.
With Cutoff Date set as 202206 (June 2022) in Version dimension
Points to Note:
- Use of Calculation Input Control is currently not supported in Optimized Design mode for the stories.
- The above steps can be extended to also derive more than one Forecast version with different cutoff dates. (Exciting possibilities)
Summary:
The above steps outlines the process to create centrally managed dynamic Forecast layouts to be used in the planning process. The combination of attributes in Version dimension and use of FIND () function opens up numerous ways to add dynamisms to the forecast layout configuration.
If you think this post has helped you, please like and share your thoughts in the comments section below. Incase of any queries please ask the same in the comments section and I would be happy to answer the same.
Thanks for reading. I hope you enjoyed the blog.
Check the SAP Analytics Cloud topic page for more up to date information https://community.sap.com/topics/cloud-analytics
follow and read other informative blog posts on SAC browse the below link
https://blogs.sap.com/tags/67838200100800006884/
Regards
Nikhil
A great article Nikhil Anand!
BTW, calculation input control in ODE will be supported in 2023 QRC1
Thanks William Yu.
Great to hear! Does that include API's on calculation input control like getActiveSelectedMembers() & setSelectedMembers()?
Hi Nikhil,
Do you know anyway how we can dynamically lock the actual cells or actual months based on the Cutoff Date ?
This is to ensure that users don't accidentally lock these cells.
A blog on that would be great .
Thank you
Afshin
Afshin Irani
As version is in the column of the forecast layout. Setting up the Data Access Control on the Version dimension and then Marking Actuals and Read only should help you achieve it. The cutoff date will decide the number of months for Actuals and they will be read only.
Nikhil
Hello,
Most of our customers need to display : Actual + Forecast + Budget in the same table. How to do that ? We are limited only to 2 version here.
Thank you
Thanks for this Nikhil Anand !
We tried to use it that way, it's also described in help docs but we found it :
So often we still decided that just copying the actuals into the planning version (for actual periods) was, although not ideal (because it's data duplication), the easiest way to satisfy more advanced requirements.
Hello Nikhil Anand ,
First of all congratulations for this excellent blog. I have tried it and it works. There is only one last step:
I would like to pass the dynamic cut off date as as parameter in a data action, but i am getting an error about the different time hierarchies that are used in the date dimension.
Is there any way to convert it?
Best Regards,
Vasileios Zarkadoulas
It seems to me that you have both calendar and fiscal hierarchy in your time dimension. In your Advanced Formula try using CONFIG.TIME_HIERARCHY = CALENDARYEAR or CONFIG.TIME_HIERARCHY = FISCALYEAR based on what hierarchy you want the code to execute.
Nikhil
Thanks Nikhil, very nice informative blog.
We want to do formatting for the Forecast category different than Actual category in the forecast template but when we change the forecast (i.e. Forecast1, Forecast2 and etc.) category then formatting is getting cleared out.
Do you have any ideas, how to do it dynamically?
Forecast Category Formatting-
We changed the Forecast1 category then formatting got cleared.
Thanks in Advance!!!
Regards,
Mahadev
Hi Nikhil
How do you accomplish the version configuration where you add two column, one for the Forecast Version and one for the cut-off date?
Is this something that an admin can do?
I am clear on creating the basic forecast where I have the monthly actual data, the cut-off date and the look ahead on a budget version.
I want also to use the simple forecast version in any reports that I build.
However, I am lost on the rolling forecast.
Any info is greatly appreciated.
Thanks