Financial models are planning tools, and therefore consistent and thought-out definitions of time lines is one crucial aspect of the construction.
Use only one period length per sheet
...as long as that increases usability. Obviously, it makes perfect sense to offer the user different levels of detail: yearly, quarterly, monthly, sometimes even weekly (especially when modelling liquidity for companies that are close to bankruptcy). Just make sure to have these views in separate sheets:
- This is one way of making your model more consistent.
- At the same time, usability is improved. If the model user wants to extract, for example, just the monthly financial statements, it will be easier for him or her to just print the sheets needed - instead of selecting the monthly statements within the respective sheets first.
Of course, there is the disadvantage of increasing the number of sheets. This might not be a problem for small to medium-sized models, but as soon as you are modelling the income statements for multiple firms within one file, you should reconsider.
Use period lengths consistently
Whether you decide to put financial statements with different period lengths into separate sheets, or whether you decide to put them one below another into one sheet - the period lengths within one statement must be the same in each column. If column C represents a month, but column D represents two months - even if there is a good reason for that -, that is bound to lead to some mistake somewhere eventually.
Avoid summary columns within time lines
The principle of consistent period lengths implies that you should relinquish summary columns, too. Of course, it is tempting to have 12 columns for all months of a year, then have a 13th column as the sum of the monthly columns and group the monthly columns so that you can just show the yearly summaries with one click. But this creates two problems:
- The formulas within one row are not totally consistent (because, in our example, every 13th column is just a sum).
- If you want to recreate a monthly overview (or some calculation of that) in another sheet that does not contain a yearly column, you will experience inconsistencies.
Instead, have one table for monthly values, one for quarterly, and so on.
Use intuitive period identifiers
It is important for your user to know where he is in the model, or rather: when he is. Therefore, make sure to use period identifiers that are easy to understand, for example:
- 2012, 2013, ... for years
- 1H2012, 2H2012 for half-years
- 1Q2012, 2Q2012, ... for quarters
- 01/2012, 02/2012,... or Jan2012, Feb2012, ... for months
- 01w2012, 02w2012, ... for calendar weeks
- 31.12.2012 (or any other established date format) for days.
The following screenshot shows examples how to realise some of those formats:
Use suffixes to indicate financial figure quality
You should use suffixes after the periods to indicate the quality of the financial figures. "Quality" in this context does not mean "good" or "bad", but rather whether the figures are actuals, estimated, budgeted, forecast, adjusted for specific effects and so on. For example, "2011A" for actuals, "2012B" for budgeted figures et cetera. The article Custom Number Formats in Excel explains how to do that. (This may not work without adjustments for periods other than years, months or days.)