This is an important decision to get right – it is one of the most difficult things to change once construction of a model has started, so it warrants careful thought before you start.
I’m dealing with business forecasting models here, not scientific, climatic, currency exchange or any of the hundreds of other types of spreadsheet, so I’m suggesting anything less than a week is probably too detailed and in most cases monthly periods are enough detail.
Weekly forecasts are sometimes used where cash management is critical – in turnaround situations for example, but when I’ve seen this done, it has just been an unwind of the balance sheet to forecast when cash will be received from debtors and paid to creditors, to ensure that the company stays within its finance facilities.
Monthly forecasts are common, particularly for near future (ie the next year) and to provide cash forecasting, where this is a particular focus. In the retail world, I have seen blocks of 4 weeks, giving a 13 period year (with an extra week every fourth year or so) or periods that are 4,4 and 5 weeks long repeated to give a 12 period year. Both of these can present challenges in building formulae or interpreting output. The 13 x 4 period timeline ends up with (calendar) monthly payments (eg salaries) occurring in only 12 of the periods, and the period end positions not being comparable with each other. 4,4,5 patterns require many of the calculations to include the number of days in order to make reasonable forecasts.
Any monthly forecast requires fairly detailed assumptions to be made to drive the forecast and unless this level of detail is required, it may be worth considering quarterly, semi-annual or annual periods. Monthly forecasts present their own challenges, which I’ll tackle in another blog.
In all these cases, annual summaries are virtually essential – this is the level at which the Board of Directors will be thinking and they may not even look at the shorter period forecasts.
What about mixed timelines – monthly for the first year or two, changing to annual? This seems like an attractive idea, apparently combining the benefits of both. It does however add considerable complexity to the calculations and I would recommend keeping the same periodicity throughout the model if possible. If you really need a mixed timeline (common in project finance, where the construction period is more detailed than the rest) I would take the following approach:
Let’s take an example of a wind farm, with a 20 year life, constructed over 2 years. Monthly cash flow is important early on for the capital expenditure and debt facilities, but after that, semi-annual is accurate enough. I would recommend two separate timelines, both starting on the same day; a monthly timeline for the first 2 years and (often on a separate sheet) the semi-annual timeline, where the first two years are the sum of the corresponding monthly calcs. This may appear cumbersome, but avoids horribly complex formulae to deal with the changeover in periodicity.