Skip to Content
Personal Insights
Author's profile photo Nikhil Anand

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:

  • 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:

TypeForecast 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

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo William Yu
      William Yu

      A great article Nikhil Anand

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

      Author's profile photo Nikhil Anand
      Nikhil Anand
      Blog Post Author

      Thanks William Yu.

      Author's profile photo Jef Baeyens
      Jef Baeyens

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

      Author's profile photo Afshin Irani
      Afshin Irani

      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

      Author's profile photo Nikhil Anand
      Nikhil Anand
      Blog Post Author

      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

      Author's profile photo Marc-Antoine BERTHET
      Marc-Antoine 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

      Author's profile photo Jef Baeyens
      Jef Baeyens

      Thanks for this Nikhil Anand !

      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.