How to create a loop formula

[TahaU]

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!

[TimothyThomas]

Hi @TahaU ,

There are likely quite a few ways to accomplish this. Here's how I would do it:

1. If you don't already have a Time Settings module set up, create one (dimensionalized by time only). Create a simple formula to find your First Period which returns a boolean: CURRENTPERIODSTART() = START(). This will be set up as a Year time scale.
2. Create a new line item in your module called something like "Demand Calc." The formula would be: IF NOT Time Settings.First Period? THEN PREVIOUS(Demand Calc) * Growth Rate ELSE Demand

Hope that helps - let me know if additional help is needed!

Thanks,

Timmy

[TahaU]

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

[TimothyThomas · Accepted solution]

Hi @TahaU ,

Ah, I see - sorry for oversimplifying it. In that case, I would echo @ankit_cheeni's suggestion of using a different function. I think LAG would do the trick here. In fact, it may even help simplify the formula. In this case, my example formula would instead read: LAG(Demand Calc,12,Demand) * Growth Rate

That should return the same month previous year's demand. In the case of your first year, it will instead just take that month's demand * that month's growth rate. Let me know if that solution works!

Thanks,

Timmy

[ankit_cheeni · Accepted solution]

Hi @TahaU   

You seem to be asking really good questions. I enjoyed the discussion on the last question as well. 

While there is no default way to create a Loop function, the ask here seems to be simpler. I will not get into the details of dimensionality, I know you have a good grasp of that already. What you essentially need to do is bring previous year's value to current year (or year in consideration) and multiple it with the growth rate of current year (or year in consideration).

1. If there is a time dimension, a simple PREVIOUS should fetch previous year's demand to current
2. A bad way (just for discussion) is to create a system module that formulaically maps previous year to current, and then do a lookup basis this

What would be helpful is to look at functions that move values across time - LAG/OFFSET/LEAD/POST etc. 

Let me know if this helps

[TahaU]

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?

[ankit_cheeni]

I think having two line items is fine. Honestly in my experience, breaking calculations in multiple line items is never a problem until it affects your space usage.

[AdwaitJha]

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