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
Ownership

- One analyst should build and own the model
- Save all old model versions in a separate folder, these act as a back up.
Set-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.
Structure
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.
Inputs
- 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.
Formatting
- 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.
Calculations
- 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.
Outputs

- Have one summary sheet displaying all key results and metrics.
- Additional reports, charts and dashboards can be added to suit business needs.
Documentation
- Models should be logical and reasonably self-explanatory.
- Use comments and user guidelines where necessary for more complex calculations.
Protection

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