Compounding Growth In Forecast Model

eew24

Afternoon,

  Composing a typical growth forecast.  I am having an issue when my raw data ends.  (FY23 is the last year).  Then once I get into my forecast years, the lag function (and previous function) have nothing to pull from (zero value).

 

LAG(Research Allocation Rate (Original), 1, 0) * (1+Growth Rate).  So essentially for FY24, I recieve a value with growth, but all items past FY24 are blank.  I feel like there is a simple solution, but been at this for awhile.

 

I essentially need it to take the previous years actual cell value instead of recalcing every time thus compounding the growth across time.

 

I have the growth rate by year in a seperate module.

 

Thanks in advance,

  Evan

 

KirillKuznetsov

@eew24 

Do you know about switchover?

You can select period which will be taken as the beginning of your forecast (the rest will be considered as Actuals and the data will be pulled from the version selected as actual)

Also I'd recommend you to replace LAG by PREVIOUS. In your case it will do the same, but the formula will have less arguments and will perform better.