Here are my top five financial modelling key rules and top tips:
FIVE KEY RULES:
1. Never include numbers in a formula
Otherwise the model is inflexible.
The exceptions are 0 and 1 or, for example, ^0.5 for a square root.
2. Always copy a formula across a time-based row
There are no exceptions to this rule (try using a narrow column to the left of the first period).
To check for row consistency, select the range and use Ctrl \ on a QWERTY keyboard (or F5 -> Special -> Row Differences).
3. Never repeat a formula
A formula should be generated only once.
4. Never include circular references, iterative macros or iterations
Circular references are the result of flawed logic and should never be included. Neither copy paste iterative macros nor switching the calculation method to iteration should be used as alternatives. Just sort the logic and mathematics out.
5. Keep time periods consistent
Usually the same column in different sheets should refer to the same time-period (try to use rows not columns for shorter time-periods).
FIVE TOP TIPS:
1. Keep formulae very short
Formulae should never be incomprehensible.
Short formulae can be achieved by choosing suitable functions and using extra lines.
2. Avoid IF functions
Depending on their purpose, IF functions can usually be replaced with either:
- Logical statements (for an on/off switch)
- MAX or MIN (for comparisons)
- CHOOSE (for a few choices)
- INDEX with MATCH, or XLOOKUP for a wider range of choices.
3. Be organised
A disorganised model is hard to follow and likely to be inaccurate. Use:
- different sheets for different sections
- colour for inputs (to find the inputs use F5 -> Special -> Constants and Numbers)
- consistent and clear formatting
- checks with conditional formatting
4. Use shortcuts and function keys
Shortcuts and function keys save time. Take the trouble to learn them and put your mouse in the dustbin.
5. Think
The brain needs to be fully engaged. Take breaks, disable email pop ups and keep your phone out of sight.