How to create centrally managed Forecast Templates in SAC Stories

[N1kh1l]

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: Today: The current date; this is the default choice. Specific Date: In Select member for <Time>, select the date. You can also set a dynamic time filter. Last Booked (Actuals): This means the latest date of data entered for the Actuals version.

Timeframe: options include the following:

Type: Forecast	Granularity: Quarter Month
Look back additional: number of intervals	Year Quarter Month
Look ahead additional: number of intervals	Year Quarter Month

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

[William_Yu]

A great article nikhil_1486! 

BTW, calculation input control in ODE will be supported in 2023 QRC1

[N1kh1l]

Thanks william.yu

[former_member6443]

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

[N1kh1l]

afshin.irani3

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

[JefB]

Great to hear! Does that include API's on calculation input control like getActiveSelectedMembers() & setSelectedMembers()?

[Marc-An_BERTHET]

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

[JefB]

Thanks for this nikhil_1486 !

We tried to use it that way, it's also described in help docs but we found it :

• Hard to manage this setup across many planning models. Because version dimension is local, the maintenance of the version properties is not workable.


• Satisfy version copy/snapshot requirements, as SAC behavior of version copy was suddenly  updated to copy property values as well... so you could easily end up having multiple versions marked as 'X' which breaks all forecast layouts.


• Hard to manage this setup in reporting (for all tables & charts) especially to setup quarterly & full-year reports including comparisons (forecast vs budget), story filters on version, etc. etc.

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.

[vasileios_zarkadoulas]

Hello nikhil_1486 ,

 

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,

 

[N1kh1l]

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

[mahadev_9]

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

[kocomandi]

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

 

 

[hrishikeshhvg]

nikhil_1486

 

Thank you very much for this blog. This was very helpful for our requirement.

 

I have a further question on this.

How do we manage forecast layout for December month of every year.

In our story we are using Forecast Layout with 'Look Back on' Actual Version and look forward to Plan version. We have setup properties in Version dimension to identify the cutoff period.

Our ask is that at the end of December month we want to show data for entire year from Actual version and next year onwards from Plan version. Our Date dimension is 'System Managed'.

What value can we set for 'Cut Off period' so that our story will show Current Year Data from 'Actual Version' and next year onwards from 'Plan Version' ?

 

Thank you in advance.