Hello,
I am working on Strategic Planning where I have actuals for 2019 and I am projecting for 2020, 2021, and 2022. I have a formula where I want to apply a 3% escalation starting in 2021. My current formula uses ISACTUALVERSION and Offset.
Here is the formula: IF ISACTUALVERSION() THEN 'Actuals'.Period[SUM: 'Actuals'.AU Lookup] ELSE OFFSET('Actuals'.Period[SUM: 'Actuals'.AU Lookup], -12, 0) * Cost Escalation Factor.
The formula works for calculating 3% escalation for 2020 as it is able to pull actuals from 2019. The current formula does not work for 2021 and 2022. How can I update the formula so that for 2021 it looks for what has been forecasted for 2020 and then applies the 3% escalation to that and also for 2022 it looks at what has been forecasted for 2021 and then applies the 3% escalation for that.
Your help is appreciated.
Thanks
Solved! Go to Solution.
Make sure you are using offset on the same line item "$ Amount Forecast - 3". Also, the ELSE should not be zero but rather the actual amount.
So something like this:
$ Amount Forecast - 3 = IF NOT Is Actual Version? THEN OFFSET($ Amount Forecast - 3, -12, 0) * (1+Cost Escalation Factor) ELSE '$ Amount Actuals'.
@svbhagat that will work if your calendars are aligned for those extra years. Make sure you've got your model calendar or your time range appropriately set.
Another idea for you which is a little more aligned with the DISCO method is to create a system module that has all the annual increases in it. This cuts down on the number of calculations in your output module and improves performance.
Start by creating a time systems module like this:
Then change your formula to refer to your system module. Calculate once, refer often!
Hello @JaredDolich ,
Thank you for the suggestions.
My Time Settings do include the 3 out years 2020 through 2022. Would you be able to help me understand what else could be missing in the current formula for 2021 and 2022 not to be calculating the 3% correctly? Is there a way for me to update my current formula without building out the Time Setting system module you mentioned?
The Time Setting system module is definitely a good suggestion and something I learned about during the Level 1 Model Building training. I do plan on setting that up for our models as we progress through.
Thanks
@svbhagat can you take a screenshot of the time settings and a screenshot of your module blueprint?
Let's take a look at what's going on.
Hello @JaredDolich ,
Thanks for your help.
Here is a screenshot of my Time Settings. We are currently not using Time Ranges.
Here is my blueprint for the calculation module: Please note I had sent a simplified formula in my initial comments, but it is a longer formula below due to the different sum we have in the formula.
One more quick question before I try to replicate this. Is your module using the model calendar? You can see it in the module blueprint, scroll to the right a bit.
@svbhagat you were actually really close.
You just need to change the offset to itself. Don't sum on actuals because it ends after 12 periods.
Solution below...
Some pointers, if I may be a little presumptuous:
- Use system modules: much more efficient both in space and in performance.
- Avoid using subsidiary views like the one you used for the 3%. Confusing, and doesn't meet the PLANS standard, particularly Auditable.
Here is the actual data (no versions, just actuals)
If the time period is not an actual then look back on itself 12 periods and multiply times 1.03.
If the time period is an actual then give the actual.
This will produce the values you are looking for.
Hello @JaredDolich
Thank you for the detailed suggestions. I've incorporated those formulas, but for some reason my FY 2021 and FY 2022 are still not calculating correctly. Please see below:
Here is the formula I used for the $ Amount Forecast - 3 = IF NOT Is Actual Version? THEN OFFSET('$ Amount (Final)', -12, 0) * Cost Escalation Factor ELSE 0.
I'm not sure what else could be missing?
Thanks
Make sure you are using offset on the same line item "$ Amount Forecast - 3". Also, the ELSE should not be zero but rather the actual amount.
So something like this:
$ Amount Forecast - 3 = IF NOT Is Actual Version? THEN OFFSET($ Amount Forecast - 3, -12, 0) * (1+Cost Escalation Factor) ELSE '$ Amount Actuals'.