How to create a loop formula

Options
TahaU
TahaU Member, ALL USERS, Employee Posts: 5 Occasional Contributor
in Planning & Modeling

Hi everyone,
I am stuck on a formula. 

I have demands for 2021 and I need to calculate the demand forecast until 2030 by using the formula below

Demand 2022= Demand 2021*Growth rate 2022

Demand 2023= Demand 2022*Growth rate 2023

.

.

.

So how can I do that?

Thank you!

Tagged:

Comments

  • AdwaitJha
    AdwaitJha Member, ALL USERS, Partner Posts: 2 New Contributor

    Hi @TahaU ,

     

    For, Creating a Loop Formula - an If/Else loop is a pretty straightforward approach provided your conditions are well defined and not too many as this may make the model slow.

    Over here in this particular case, you can -

    • Create a System module to separate time between Actual/Current & Forecast using a formula, refer <

      Time & Date Functions - it will give you better clarity> to use it as a condition for formula application.

    • Make a Calculation Module keeping Time (Model Calendar) in Column & 3 Line Items
    • Previous Period Growth – for the first period in the calendar (actual) – it’d be zero / for the second period onwards (Current & Forecast) grab it from “Forecast Growth” of the previous period. (IF-ELSE with OFFSET can be used)
    • Expected Growth Rate – for the first period in the calendar (actual) – it’d be zero / for the second period onwards (Current & Forecast) update through a UX /formula or Import, depending on your Use Case.
    • Forecast Growth – for the first period in the calendar (actual) – it’d be the actual value you received/ for the second period onwards (Current & Forecast) grab drive it through the formula “Previous Period Growth” * “Expected Growth Rate”.

    Hope this helps,

    Kind Regards,

    Adwait 

  • TahaU
    TahaU Member, ALL USERS, Employee Posts: 5 Occasional Contributor

    Hi @TimothyThomas,

     

    Thank you for your reply. It is a good idea.

    I have already tried something similar to your answer. My problem is that I need to calculate the monthly demand forecast and when I use the PREVIOUS function it refers previous month, not the same month last year.

    I couldn't figure out how to deal with it.

     

    Demand 2023 Jan = Demand 2022 Jan * Annual Growth Rate 2023

     

    Taha

  • TahaU
    TahaU Member, ALL USERS, Employee Posts: 5 Occasional Contributor

    Hi @ankit_cheeni,

     

    Thank you for your great answers each time I ask a question!

    I was able to do it with the OFFSET function. 

     

    IF NOT 'SYS00 Time Settings bu Month'.Current Year? THEN OFFSET('Demand Forecast', -12, 0) * (1 + Demand Growth Rate) ELSE Demand 2021

     

    In this solution, I had to use 2 line items. Demand 2021 and Demand Forecast. Is there a better solution?

Categories