Highlighted
Contributor

IF and Then for ISACTUALVERSION and Forecast periods

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

13 REPLIES 13
Master Anaplanner/Community Boss

Re: IF and Then for ISACTUALVERSION and Forecast periods

@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:

Annual Increase 01.png

Then change your formula to refer to your system module. Calculate once, refer often!

Annual Increase 02.png

Jared Dolich - Retail, Wholesale, eCommerce Analyst
Highlighted
Contributor

Re: IF and Then for ISACTUALVERSION and Forecast periods

Hello @Jared Dolich ,

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

 

Highlighted
Master Anaplanner/Community Boss

Re: IF and Then for ISACTUALVERSION and Forecast periods

@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.

Jared Dolich - Retail, Wholesale, eCommerce Analyst
Highlighted
Contributor

Re: IF and Then for ISACTUALVERSION and Forecast periods

Hello @Jared Dolich ,

Thanks for your help. 

Here is a screenshot of my Time Settings.  We are currently not using Time Ranges.

svbhagat_0-1577728427764.png

 

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.  

svbhagat_1-1577728785972.png

 

 

 

Highlighted
Master Anaplanner/Community Boss

Re: IF and Then for ISACTUALVERSION and Forecast periods

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.

Jared Dolich - Retail, Wholesale, eCommerce Analyst
Highlighted
Contributor

Re: IF and Then for ISACTUALVERSION and Forecast periods

@Jared Dolich ,

Yes it is using the model calendar.  Please see revised screenshot.  Thanks.

svbhagat_0-1577729244711.png

 

 

Highlighted
Master Anaplanner/Community Boss

Re: IF and Then for ISACTUALVERSION and Forecast periods

@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)

Annual Increase 03.png

 

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.

Annual Increase 04.png

 

This will produce the values you are looking for.

Annual Increase 05.png

Jared Dolich - Retail, Wholesale, eCommerce Analyst
Highlighted
Contributor

Re: IF and Then for ISACTUALVERSION and Forecast periods

Hello @Jared Dolich 

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:

svbhagat_0-1577738622158.png

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

 

Highlighted
Master Anaplanner/Community Boss

Re: IF and Then for ISACTUALVERSION and Forecast periods

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'.

Jared Dolich - Retail, Wholesale, eCommerce Analyst