Simon Selkrig, Strategize Financial Modelling, simon@strategizefm.com, Montreal, QC

Article series: Avoiding financial spreadsheet mistakes

Part 1 of 3

Financial spreadsheets are often compromised due to some rather basic mistakes by spreadsheet developers. No matter how basic or thorough a financial spreadsheet is, it is important to maintain a simple and concise approach to developing it. While putting together spreadsheets, the developer should take a thoughtful approach and avoid cutting corners. This will prevent the following rookie mistakes.

Worksheet Structure

There are many spreadsheets that are not user-friendly because they lack structure. It is often because there is one primary user who builds, manages and operates the model. They get to know the unique and quirky structure of the spreadsheet which other users find difficult to understand. Begin by adding a separate cover sheet and table of contents at the front of the spreadsheet. Then segment, as shown below, into certain worksheets such as assumptions/inputs, source data or model imports, calculation and outputs.

Avoiding financial spreadsheet mistakes

Ensure the structure of the worksheet has a logical information flow, which will make it easier for other users to navigate and operate the spreadsheet.

Hard-coding numbers

One of the most frustrating and time-consuming activities for spreadsheet users is understanding where hard-coded numbers came from. Hard-coding involves entering a number into a spreadsheet cell, rather than sourcing information from another source (such as a formula that applies data from another cell(s) or previously inputted cell).

Ironically, spreadsheet developers perceive hard-coding as the easiest and quickest solution at the time of building the spreadsheet. They are often not aware that the users will spend so much time and effort later on trying to understand what the model means. This is a real audit risk for your company’s financial spreadsheets.

It is wiser to ring-fence all external cell referencing to designated model import sheets; this will uphold the auditability of your spreadsheet. Ring-fencing involves isolating a specific informational element of the financial model, such as externally referenced spreadsheets, in order to prevent spreadsheet risk i.e. cell errors or compromised external data links.

Ad-hoc data inputting should be confined to assumptions worksheets.

Check out the rest of the articles in the series Avoiding financial spreadsheet mistakes:

  1. Avoiding financial spreadsheet mistakes: Structure, hard-coding
  2. Avoiding financial spreadsheet mistakes: Email referencing, formatting
  3. Avoiding financial spreadsheet mistakes: Data links, formulae

Get up to $60,000
in financial support,
and the support of one
of our 2,400+ mentors.

Learn More →