Financial Modelling Guidelines

Business reliance on Financial Models has grown exponentially over the past 30 years. Every model differs however, following a few simple guidelines in model development can make for a more robust and user friendly end product. Excel Modelling Guidelines PDF

• One analyst should build and own the model
• Save all old model versions in a separate folder, these act as a back up.

• Avoid multiple workbooks where possible.
• References to external links should be clearly identified.
• Import the links into an “input sheet”.
• Then reference as required.
• Provides more transparency and allows the link to be broken without resulting in hardcoded values being locked into formulas.

All sound models should follow a natural logic and be set up with the following ideally on separate tabs.

• Menu – Hyperlinks to all relevant sheets.
• Inputs – Clearly identified cell for data entry.
• Calculations – This is the engine room of the model and cells should be protected.
• Outputs & Reports.

• Separate the “Inputs” from the calculations.
  • Users know exactly where they need to enter data.
  • Reduces the likelihood of a user inadvertently tampering with calculations.
• Input Data only once – then always refer back to this cell.
  • Future changes to this input only have to be updated once.
  • Avoids potential inconsistencies creeping into the model.

• Use consistent formatting in company models.
• This is beneficial when sharing models with co-workers and allows them to quickly get comfortable with a new model.
• Consistent colour coding and formatting of inputs is a must.

• Simple is best.
• Always opt for formulas over VBA where possible.
• Aim to have one consistent formula on each row or column.
• Keep column and row headings consistent e.g. “Jan 2015” is always in column “C”.
• Add some error checks & balances.
• Test as you build
• Limit the use of modelling tools such as dropdowns and radio buttons as they introduce an extra layer of complexity.

• Have one summary sheet displaying all key results and metrics.
• Additional reports, charts and dashboards can be added to suit business needs.

• Models should be logical and reasonably self-explanatory.
• Use comments and user guidelines where necessary for more complex calculations.

• Password protection of cells containing formulas helps to minimise end-user error.