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:
You can also set a dynamic time filter.
|
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)
