Automating an annual forecast in Predictive planning with SAP Analytics Cloud

[Nverhoeven]

There is very little discussion within enterprises when it comes to the value of financial forecasting, as most of us agree it makes sense to look ahead to see where we are going. Despite this overall alignment, how one should forecast is not as clear cut. If you would use your preferred search engine for the best forecasting methodology, you will find passionately written blogs that are quite contradictory. One of these conflicting schools of thought is rolling forecasting vs the annual forecast. Simply put: rolling forecasting forecasts a fixed range that keeps moving crossing years, annual forecasting forecasts the current year with a decreasing range as the year progresses. This blog will not attempt to settle this discussion, but is relevant for a  group dealing with the method that has simply been chosen, in this case the annual forecast. Even more specific: how to deal with an annual forecast in SAP Analytics Cloud, when one wants to use its native predictive planning functionalities which is more aimed at the rolling forecast (but you already knew that if a search engine or AI brought you here).

Now what is the problem? Using SAP Analytics Cloud’s native predictive planning functionalities one can decide how many periods need to be predicted. Choosing a fixed number of periods gives you a fixed range, ideal for rolling forecasts but not so much for the annual method. This seems like a minor issue as for every forecast one can simply change the number of periods no? Well yes this would be true, however in an enterprise you might have found the power of predictive planning and want to apply it to not one scenario but many. This would mean a person would need to adjust N number of forecast each and every cycle which would not really live up to the vision of autonomous finance. Having an intern that can change all the scenarios every cycle also does not equal autonomous finance if you’re thinking that right now.

So what to do? As with many things in software we’ll be pragmatic. We’ll simply generate 12 periods (in the case of a monthly granularity) and delete the periods that are generated in the next year. To illustrate this I’ll show some advanced formulas actually used in my project with our central forecast team (see my blog on that project here). In this case we actually had 2 problems in making our predictions “autonomous”. 1 being outlined already and the other that we had Actuals data already for the current month which was not final yet. So to start our forecast from the last loaded Actuals, we also needed to get rid of this incomplete Actuals data. So for both of these we could hard code our advanced formulas with dates, but that again would not really be autonomous so we want to program the advanced formula to dynamically adapt based on the current date.

Removing actuals:

//USED TO SCOPE ONLY ACTUALS FOR THIS MONTH -1

MEMBERSET [d/Date] = "201801" TO PREVIOUS(1,"MONTH",TODAY())

//COPIES OVER DATA FROM ACTUALS VERSION

DATA() =RESULTLOOKUP([d/Version]="public.Actual")

With our Actuals aligned to signal the starting period for our Predictive planning, we can predict for 12 periods from that date. Now subsequently everything generated in the next year we want to delete using the next advanced formula:

Deleting the non-current year values:

//DELETING ALL THAT IS NOT THE CURRENT YEAR

IF DATEDIFF(TODAY() ,[d/Date].[p/YEAR] , "YEAR")=1 THEN

DELETE()

ENDIF

With that we end up with a clean set of generated data only for the current year. Combining the predictive flow(s) and these provided advanced formulas into a multi action help us automate the process entirely, moving us one step closer to autonomous finance!

As a reading tip I'd recommend the blog from my Controlling counterpart Eric Schuenemann on his perspective on Predictive Planning, find it here!