Build an interactive and dynamic Rolling Forecast ...

bigonnet · ‎02-02-2023

This blog shows an example of using the powerful Get DATA function to build an interactive and dynamic Rolling Forecast with IBCS Format applied. The beauty of this SAP function is that users can nest them into any Excel standard function such as “IF”; “IFs”, allowing flexible and customized reporting.

How to interact with the report?

User selects Month and year to refresh the data and display the Actuals values up to the selected Month vs the Forecast value for the remaining months.

The sum of the Actual YTD, combined with the sum of remaining Forecast value is then compared to the value of the Plan, or the user’s selected YEAR.

The conditional formatting as native Excel feature is applied to markup which months are reported Actuals from Forecast, following IBCS Standards. It refreshes with user’s Month & Year selection, which brings more clarity on reading the data.

How to build it?

Following steps show how to build and create it with user drop down value selection and conditional formatting:

Insert the Table or a Data source

Users can insert a Microsoft Excel-like function in cell-based custom formulas to retrieve and display data in the grid. SAP.GETDATA function returns the data value for any given combination of a dimension and a member.From last delivery, “SAPGet data” can be set up directly to a Data source, (then having created a table first is not a prerequisite anymore, but still an option) ; in that case the Data source (ALIAS) is expressed as first parameter in the function as “DS_1”. In this example, the Alias/Data Source is used.

Note: it is also possible to specify “SAP.GETDIMENSION” function on Data source, it helps to retrieve the targeted dimensions as “G/L Account, Time, Version”…. and copy their IDs for the later usage within SAP.GETDATA.

More details about “SAPGetData” function usage, read Formula's help guide , Blog and What’s new .

Building the structure of the Report

Cell F7 and F9 contain the user selected month & year. These cells are fulfilled from the list reported respectively in column AO and AP. To populate Cells F7 & F9 with these lists, user shall proceed as follow: Data->Data Validation -> List.

Row 10 contains the relevant ID values for the Time (202210 for 0ctober) which auto populate from the year selected by user in cell F9.

Row 15 is refreshed upon user selected Month update in cell F7, and flag wither columns are reporting “Actual” or “Forecast” value in the Month range. It is purely indicative information that complements conditional formatting information displayed in row 18.And built in Excel =IF(VALUE(RIGHT(K$10,2))<$F$7,"Actual","Forecast")

Row 16 display the month number as Integer.

Row 17 is refreshed upon user selected year update in cell F9.

Row 18 is set up by implementing conditional formatting rules. To create a new formatting rule in Excel: select Data -> Conditional Formatting -> New Rule->Use a formula to determine which cells to format. These rules take as input the month index values from row 16 and compare each with the user selected month in cell F7. Depending on the test value outcome-returning true or false-, it specifies a specific cell color coding for Actuals data and Forecast:

Col (B,F) are the G/L accounts with ID and description.

Col H represents the Plan value for the selected year

The fourteen columns (I:V) represent the Actual and/or Forecast values including a total for the year (U) and the variance with the plan (V) for the selected year (here 2022).

Row 21, 24,30 and 32 are subtotals for calculated rows above.
- Row 21 for the Monthly Revenue, corresponding to the Gross Revenue minus discounts
- Row 24 for the Monthly Gross profits that withdraw the cost of sales & other COGS to the Monthly Revenue
- Row 30 for the Monthly Income before tax, which withdraw all expenses reported between row 25 to 29 to the Monthly Gross profit
- Row 32 for the Net income, withdrawing the income tax expenses to Row 30

Usage of SAP.GETDATA

Let’s finally use the SAP.GETDATA formula nested with native Excel ones .

Formula in cell H19 (Plan for the selected Year):
- SAP_GETDATA(DS_1,"Account", $B19,"Version","public.Plan","Time",F$9): where $B19 refers to the G/L accounts and F$9 refers to the year.
- Drag the formula across Col H

Formula in cell I19: this is where the SAP_GETDATA function is nested within Excel functions to determine which argument is to be used. A logical test is executed to check if the Month reported in the same column (in row 10) is greater than the month selected by user in cell F7. If the test result is TRUE, then it simply means the SAPGETDATA has to return a “Forecast” value, hence "public.Forecast" shall be used as member argument. Otherwise (e.g the Test Result returns FALSE), the SAP_GETDATA member has to return an “Actual” value so the member argument has to be set to "public.Actual".

Formula in Cell I19: "IF(VALUE(RIGHT(J$10,2))<$F$7,SAP_GETDATA(DS_1,"Account", $B19,"Version","public.Actual","Time",J$10 ),SAP_GETDATA(DS_1,"Account", $B19,"Version","public.Forecast","Time",J$10 ))
- Expend the formula in row 19-20,21,25-28 and 31 between column I to J

Formula in cell T20 : as for December, we want to support two different scenarios:
- Beg. of December, Forecast Data are expected/End of December, Actuals Data are expected instead
- This can be solved adding a new logical test combined with the two other ones as follow : “=IFERROR(IF(VALUE(RIGHT(U$10,2))<$F$7,SAP.GETDATA("DS_1","Account", $B19,"Version","public.Actual","Time",U$10 ),SAP.GETDATA("DS_1","Account", $B19,"Version","public.Forecast","Time",U$10 )),SAP.GETDATA("DS_1","Account", $B19,"Version","public.Actual","Time",U$10 ))”
- Drag the formula across Col T

Note : if a Report change is performed on Designer pane(as filter changed, new dimension or member inserted …) it is then desired to Refresh the data