Spreadsheet Integrity

The concept of integrity is to ensure that where at all possible, the model is correct, AND indicates to the user that it is correct. Error checks are therefore critical.

Build as many error checks as you can think of

Where at all possible build some form of error check.

For example:

• With a matrix, add the columns and the rows and compare the results with an IF function.
• If you have developed a balance sheet make sure it actually balances (without a forced balancing figure!!).
• Prove the cash flow to the balance sheet and the flow of the retained income to the balance sheet.
• Check if stock balances go negative
• Check that the mass balance doesn’t go negative
• etc

If you have any other manual checks you typically use, build them into the spreadsheet. If you find yourself checking a spreadsheet with a calculator, build that same tests into the spreadsheet itself. Ask your bosses how they check a spreadsheet. Build those tests directly into the spreadsheet.

By making use of the IF feature you can easily compare two numbers and see if they are equal. An idea is to have a separate section of your spreadsheet with a listing of the integrity checks. This way, when you are making last minute changes, you can go look at one sheet and check that everything is still working.

Below a video clip on some examples of error checks

To follow along go to 1.20 and 1.21

Testing the Spreadsheet

Another way to ensure the integrity of a financial model is to perform detailed testing on them. The time spent on this will depend on the importance of the spreadsheet. Some thoughts on testing a spreadsheet

Levels of spreadsheet testing

There are different levels of testing a spreadsheet. These can be defined as

• Formula Testing- most people do this already. You build a formula and if it doesn’t work you fix it. But there is more that you can do
• Component Testing- It’s great that the formulas work, but do they fit into that component correctly. If you are calculating fixed assets, an incorrect negative sign can make the whole thing wrong. Do the formulas work well together?
• Model Testing- do all the components work together? Does it correctly create a balance sheet or mass balance?
• Business User Acceptance Testing- Can the users use the model?

Spreadsheet Review and Auditors

Who does the testing and how it is done is very dependant on the importance and type of model. Excel has a number of tools built in that help you test a financial model.

There are also  Excel add-ins that take the testing one step further and assist the user to find the riskiest areas of the model. If spreadsheets are your working life, you should invest in one of these tools.