Level 2 Model Builder- Sprint 2 DEM03

jenniferhallce8995
jenniferhallce8995
edited January 2023 in Academy Discussions

In module DEM03 there is a formula to pull forward the historic volumes data from the previous period, I have been able to do this up to the FY20 however beyond this the cells are blank. Below is my working formula which populates the forecast up to the end of FY20. 

 

IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN 'DAT03 Historic Volumes'.'Offset Volumes for 1st Forecast Year' ELSE OFFSET('DAT03 Historic Volumes'.'Offset Volumes for 1st Forecast Year', -52, 0)

 

I'm really stuck and not sure how to progress from here! 

Screenshot 2020-12-02 at 12.54.08.png

Tagged:

Answers

  • abbyjohnsonj
    abbyjohnsonj

    I'm having the same issue as well!

  • AjayM
    AjayM

    Hi,
    As per the below instruction, you are doing right until FY20 (by offsetting FY19 actual volumes to FY20 as baseline forecast).

    image.png


    However, the baseline forecast for FY21 is the baseline forecast of FY20 times the volume growth%. For this, you need to establish the growth rate applied volume in a different lineitem ("Default Forecast") and then use this for FY21 year. It goes something like "If 1st forecast year then 'FY19's offset volume from DAT03' else if 2nd forecast year then 'offset default forecast from FY20' else 0"

    Hope this helps.

    Cheers!

Categories