IF and Then for ISACTUALVERSION and Forecast periods
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.
@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!
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.