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!
Solved! Go to 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).
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
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
Hi @TahaU ,
There are likely quite a few ways to accomplish this. Here's how I would do it:
Hope that helps - let me know if additional help is needed!
Thanks,
Timmy
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
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
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).
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
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?
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 -
Time & Date Functions - it will give you better clarity> to use it as a condition for formula application.
Hope this helps,
Kind Regards,
Adwait