A project requires 2 years of detailed monthly forecasts followed by 3 years of less detailed annual forecasts. That’s driven by the quality of the forecast information available. There is a requirement to present the calculated results on an annual basis. How should the financial modeller prepare their timelines to cover the 5 years? Some options:
1. Single timeline; monthly throughout;
2. Single timeline; annually throughout;
3. Single timeline; monthly for 2 years, annual for 3 years; or
4. Multiple timelines: monthly for 5 years; annual for 5 years
5. Any other option
What help does financial modelling guidance provide? The ICAEW’s Financial Modelling Code refers to multiple timelines: “place any secondary or tertiary timelines near each of the values to which they relate so they can be viewed together without scrolling”. It also refers to single timelines that accommodate different time periods: “Present any distinction clearly when multiple timelines are included in one axis (e.g. a change of periodicity from monthly periods to semi-annual periods), and keep formulas consistent along that axis.”
The FAST Standard points to multiple timelines as being necessary noting the importance of any primary time ruler encompassing the period of time covered by secondary time rulers: “In these cases, ensure that the primary time ruler encompasses the higher-resolution time period such that summarisation of data from both periods can be effected more easily” (FAST 1.01-04).
Two questions:
(i) How would you design your financial model to manage the requirement set out above; and
(ii) What additional guidance would be helpful?
In particular, I’m interested to hear from those that might advocate a single timeline that converts from monthly to annual two years in. As far as I can see, the Financial Modelling Code recognises it as a possible design solution and the FAST Standard does not explicitly rule it out.
Join the discussion on the FAST Standard Group
Author: Andrew Berkley
One Comment
Comments are closed.
I think the best method is always to model to the lowest common denominator … meaning 5 years of monthly inputs and calculations. There is then only 1 formula applied overtime.
This serves 2 purposes:
1. You can then either include Qtrly and Annual Sumif summaries adjacent to the Monthly outputs on the same Worksheet or on separate Worksheets. Outlining or Grouping then makes any combination of viewing options available to Users.
2. Charting is then also possible with the various levels of Outlining or Grouping.
If you do it with any of the other options above, you don’t have the same options and need to create additional formulae, or make modifications later on etc., which is costly in both time and money.