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
user-6

 

  • 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

controls-4

  • 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.

notepad-1

 

  • 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

funnel

  • 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

folder-12

  • 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.magic-wand
  • 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
send

  • 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.file-2
  • Use comments and user guidelines where necessary for more complex calculations.

 

 

Protection
lock

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