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.
- 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
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
If YouTube doesn't work, click here
Testing the Spreadsheet
Another way to ensure the integrity of a budgets/ forecasts 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 budget or forecast.
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.