Level 2 DEM03 - Baseline Forecast

TomQueens
edited November 2022 in Academy Discussions

Hi,

 

I have recently started my Level 2 and was wondering if someone could offer me some guidance.

 

I'm currently working through sprint 2 but I'm struggling with the formulas for the Baseline forecast FY21.

 

This is what I have so far.

 

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

 

Would someone help me with where i'm going wrong? I've looked at Anapedia for help, but I'm still quite stuck.

Thanks in advance.

Comments

  • davidmccarty
    edited November 2022

    @TomQueens- Welcome to Level 2!

    So full disclosure - I am trying to wrap up Sprint 3 of Level 2 so please don't consider me a "Master Anaplanner".

    But a couple of questions that might help you:

    1.  What are you seeing that makes you think that your formula is incorrect?  My hunch is that you are not seeing values in FY21.  If that is the case, you should look at the parameters that OFFSET utilizes, specifically the offset value itself.  Read th first paragraph in Anapedia closely.

    2.  Why are you multiplying by 1.1?  Remember, this is the baseline forecast...I don't think it should take into account any growth rate.

     

    Hopefully this helps

     

    David 

  • Misbah
    edited November 2022

    @TomQueens 

     

    I think the formula is incorrect at two places

     

    1. If you are pulling the numbers forward (for example in Baselines) - You need to reverse the sign of 52 in OFFSET

    2. Else statements should simply say keep it zero

  • TomQueens
    edited November 2022

    Hi Misbah,

     

    I have changed my formula to the following: 

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

     

    But I am now getting a time range error. Would best practice be adjusting DEM03 to match the same time ranges as DAT03?

    Thanks for your help

  • TomQueens
    edited November 2022

    Thank you David,

     I looked over OFFSET on Anapedia and it makes a lot more sense now. I also went back and read over what is needed in the baseline forecast so I took out the multiplication

    Appreciate your help! 

  • sunilramakumar
    edited November 2022

    You have put two then statements. Try removing that.

  • einas.ibrahim
    edited November 2022

    Hi @TomQueens 

    I hope you found the answer you were looking for and moved ahead, if not then read on.
    First, let's go back to the requirement 

    image.png

    We have 3 FYs in play here:

    1. FY 19. This is actual historical data from the past and we don't need - or shouldn't - change it.
    2. FY 20 which is our 1 st forecasting year (current/base year)
    3. FY 21 which is the 2nd forecasting year.

    The "issue" we are trying to address is to find a base forecast for the base forecast year FY20 only

    • The exercise suggested that you use the same data from FY19 and preserve seasonality. You have actually calculated this Base Forecast value in another module (Historical). I hope you used the right sign for the 52 to determine you want to read from the prior year

     

    Now look at the last bullet point of the instructions

    • If it is the first forecast year, then offset the values from the volumes 52 weeks earlier, else 0. 

    It is directing you to how to get the base forecast - which is only for the base forecast year/1st forecast year/FY20

    Any other values are not considered Base Forecast and the value should be 0 as instructed.

     

    I hope that helps you and/or anyone who might be having the same question.

  • CommunityMember113513
    edited November 2022
    Hello Einas,

    I read your explanation below and found it really useful. I tried to follow the steps and input the following formula the the 'Baseline Forecast' account:

    IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN 'DAT03 Historic Volumes'.Offset Volumes for 1st Forecast Year ELSE 0

    But this still returns an error 😞

    Any tips you can offer where I am still going wrong?

    Many thanks!
    Ben
  • AleksandraShariy
    edited November 2022

    Hello everyone, hello support Team

    I stuck with the same issue -  different time ranges 

    Please see system msg:

     

     

    Invalid formula:
    'DEM03 Demand Forecast'.Baseline Forecast = IF 'SYS01 Time Settings By Week'.'1st Forecast Year?' THEN OFFSET('DAT03 Historic Volumes'.Volumes, -52, 0) ELSE 0
    For function OFFSET, all line item arguments that vary by time must use the same Time Range as the result line item.
    Result line item 'DEM03 Demand Forecast'.Baseline Forecast uses Time Range Model Calendar. But line item 'DAT03 Historic Volumes'.Volumes uses Time Range FY19-FY20.

     

     

     

     

    Could you please help me?

  • faragh
    edited November 2022

    Hi Einas,

    I think the following print screen is a bit confusing if we go back to the requirement, we don't need to calculate Baseline Forecast for FY21 and it should be 0 but the print screen is showing Forecast values for FY21?

     

     

     

    faragh_0-1615658696525.png

    Do we need to populate DEM03\FY21 Baseline forecast before creating the line chart view or is should stay 0?

    faragh_1-1615659761908.png

    Thank you for your help,

    Hamed

  • einas.ibrahim
    edited November 2022

    Hello @faragh 

     

    In the module DAT03 Historic Volume, you calculate the base forecast for 2020, so you can use it to later calculate the base forecast for 2021 in the module DEM03 Demand Forecast
    If you follow the green line you will see that the value in Week1 2021 is calculated based on the forecast in the same week of 2020 multiplied by the growth rate.

    So yes, the Baseline forecast should be 0 in DAT03 but then you have to calculate it's value based on a different formula (growth) 

  • faragh
    edited November 2022

    Hi Einas,

     

    Thank you for your helpful comments and for taking the time to reply to my questions.

     

     

    Hamed

  • JohnnyC
    edited November 2022

    Personally, I actually thought it would be logical if "Baseline Forecast" to OFFSET "Final Forecast" instead of "Default Forecast".

     

    For e.g. for "Week 1 FY21", it is showing as "Baseline Forecast = 59.47" from "Week 1 FY20, Default Forecast = 59.47". However, there is a "Override Forecast" input and hence the "Final Forecast = 62". So, I would have thought it would be logical to have "Week 1 FY21, Baseline Forecast = 62" instead of "59.47".

     

    With that being said, I guess this is overall depending on the business needs to determine which is more logical!

  • einas.ibrahim
    edited November 2022

    @JohnnyC 

    When you think about the "order of events", first we have last year's forecast then we create a base forecast to be able to build this year's forecast on top of it.
    If we already have the current forecast then why do we need the base forecast?

  • ElwinJongeling
    edited November 2022

    This was really helpful! I would never expect that Anaplan allows this type of modeling.

     

    So, Baseline Forecast of next year depends on Default Forecast of the current year, which again depends on Baseline Forecast of the current year.

     

    So in the end, Baseline Forecast depends on Baseline Forecast in a different year, but it totally works!