Outlined below are some key best practices that will help you save time and build clear, robust and well-structured models.
1. Keep it simple
Whilst the project you are modelling might be complex, it is always possible to break complex or large formulas into several, shorter, easier to understand formulas. This should result in the logic of the model’s calculations being obvious for an external user or a user not familiar with complex / large formulas.
2. Use 0/1 conditional flags
When the same condition applies to several rows in the model, create a formula that will return 1 if the condition is true and 0 if false. This will simplify the model by creating clear and shorter calculations.
3. Be consistent
Separate the spreadsheets into: (i) inputs; (ii) model calculations; and (iii) outputs.
Colour code the inputs, so that the user can immediately identify them. Follow the steps below to quickly isolate constants (hard-coded numbers):
- Select the data in which you want to isolate the constants;
- Press F5;
- Click Special;
- Select Constants;
- Select Numbers;
- Choose the colour for the cells.
- Make sure that the same column always corresponds to the same time period in every spreadsheet.
4. Do not hide anything
The user may not see that rows and columns are hidden and hence erase them by mistake. Instead of hiding rows or columns, group them so that the user knows that they are not visible.
5. Add as many checks as possible
Every time you can calculate the same result with two different methods, add a check formula to make sure the two results are identical. An error-checking sheet should always be incorporated into the model (e.g. balance sheet balances, cash in Cash Flow Statement is equal to cash in Balance Sheet, etc.).
6. Save as many versions of the model as possible
Combining a naming convention (e.g. date and version number) with the discipline of recording and savings changes reduces the risk of work being lost and provides an audit trail that is useful in checking the results of changes made.
Read the Part 1 and Part 2 Insights on avoiding common Financial Modelling mistakes.
