Hey Modano team and users,
I have been fiddling recently with creating models where the main time series is monthly, where the client would like to adjust assumptions on an annual basis. So far I've been doing this by making a custom annual assumptions module that feeds into the monthly modules via an xlookup, however this gives freeform formula errors as xlookup inherently references two rows in the annual assumptions module to pull out the given year's assumption.
Overall I'm not super happy with my implementations of this and I'm wondering if there is any content out there showing a best practice way to do monthly models driven by annual drivers?
Cheers and Merry Christmas!
Hi Matthew,
For this I would create the annual assumption inputs (using annual time series) in the same module as the montly calc. If that is not possible and the assumptions are driven from say a Scenario module I normally use an offset in the monthly sheet back to the annual sheet to ensure the link is 1:1 .
T
Matt
James and Ewan created a model that did this for me. They made an annual assumptions tab, then index from the monthly timeseries tab to search for the year, once the year was matched then divide by 12
I later added an override row and the output was either the default divided by 12 result or my override input, Handy for when I knew things were not straightlined.
Some good suggestions here guys.
I think the real question comes down to whether or not the model is a rolling historical and forecast model, as if you've used the sliding doors approach in a rolling model - i.e. grouping and hiding the inactive forecast periods to support rolling without needing to move assumptions - then you need separate periodicity forecast sheets for each periodicity in which assumptions are entered - as Tarjei suggests.
If, however, you're doing a forecast only model, one approach we've used is to use conditional formatting to support different periodicities on the same sheet, showing inactive periods in gray, as we've done below in some project finance modules we're building:
This works really well for any sheet that doesn't using grouping and hiding of columns to hide inactive periods.
When including annual assumptions in a non-annual model, we add a Year Number row to the outputs and then use an INDEX function to source the applicable annual assumption for each forecast month.
I hope this helps.
Michael Hutchens
Managing Director | Modano
Thanks for the input everyone. I am indeed using the model on a rolling basis (whether or not that was actually necessary is up for debate but we're here now).
Would anyone be able to give me an example of using an offset formula to link the annual assumptions, which are in their own periodicity forecast sheet, into the monthly forecast sheet? Struggling to visualise how this works exactly, primary issue being if I change the amount of rows in the annual sheet won't it ruin the offsets?
Thanks again.
Disregard the above - have figured out your indexing method Michael.
For anyone else trying to do this, I added a row to the bottom of my monthly periodicity assumptions sheet denoted "Year Number" which I populated with the current financial year number less the first historical year in my model - 1, allowing the first historical year to be Year 1 and each progressive financial year to be labelled sequentially.
Following this, the formula to use to populate the monthly time series assumptions from annual assumptions is =@INDEX([Annual Time Series Assumption Sheet]AllAssumptionColumns,0,[Year Number in time series module]).
Cheers!