Alternative to IF Historical Period? formulas

cbrookes
cbrookes
in Modeling

Hi All,

It seems like almost every model I've taken over has relied heavily on something to the effect of:

 

IF Historical Period? THEN Actuals ELSE Forecast

 

I've seen some really **** formulas that have separate business logic for historical and forecast periods nested within the two conditions. Generally, this issue is easy enough to simplify by breaking out the business logic into multiple line items then applying the historical IF/THEN in one line item. 

The thing I've had trouble simplifying with this type of formula is historical overrides. Essentially you need to add something else that checks for overrides before the historical period such as:

 

IF Override? THEN Override Value ELSE IF Historical Period? THEN Actuals ELSE Forecast

 

This can get overly complicated pretty quickly when you have to add in other business logic. 

I'm wondering it there is a better way to approach using Actuals/Snapshots in historical periods than using an IF/THEN statement like above?

Tagged:

Comments

  • andrewtye
    andrewtye

    Hi @cbrookes 

     

    Could you not have the overrides and actuals in almost the same place so that you're passing one value through to this final line item?

    So it's LI1: if override? then override else actual, LI2 if historical? then LI1 else forecast

    You could also use native versions here... where the final item is if isactualversion() then L1 else 0 so you'd have switchover being used and the formula scope set to actual version only leaving the other ones as inputs.

     

    Hope that helps

    Andrew

  • cbrookes
    cbrookes

    Thanks, @andrewtye. Good suggestions. I'm hoping to get away from the "IF Historical Period? THEN Actuals ELSE Forecast" completely.

    I've taken over a lot of models that have a significant number of line items with the above type of formula. Simplifying it the ways I already do or what you suggested increases daisy chaining which further degrades model performance. I feel that I have to choose between the lesser of two evils when formulas are constructed that way, daisy-chaining vs super complicated formulas

    Native versions might do the trick. I'll have to play around with them. All my mentors said no one uses native versions anymore so I haven't bothered seeing what they are capable of!

Categories