SEGRO Courses

Home | Excel | Power BI | Copilot | Visualising Data

Test Phase | Spreadsheet Modelling Best Practices (SMBP) Course

The test phase considers how we can ensure the model is error free and robust. The benefits of a test phase include:

• reduces the probability that the model has serious mistakes or errors,
• forces testing to be complete before the model is used,
• allows people to help in a non-confrontational manner, and
• increases the thoroughness of the tests performed.

Consequences of poor (or no) testing include:

• the company loses money or reputation,
• the stakeholders lose trust / the model loses credibility, and
• lots of hard work gets wasted.

Best Practice Testing Approaches

There are many testing approaches from small informal habits to formalised methodologies.

• Explicit testing activity to reduce bugs and errors
• Formalised peer review tests against an in-house test guideline.
• Check formulas with Excel debugging technique e.g., precedent arrows, F2, Evaluate, FORMULATEXT(), IFERROR()
• Create test cases where results are known and reconcile.
• If appropriate unit tests, RPA testing (with Power Automate?)
• Visualise intermediate and final results: plausible?, moving in the right direction?
• For important models, use proper spreadsheet testing software, e.g. Spreadsheet Pro, which will tell you things like if it looks like you have over-written a formula with a value
• Ask an AI tool, such as Copilot in Excel, to check the model for errors and inconsistencies. (This is a new approach, so we are still learning about its effectiveness.)

Case Study

Here is the test phase case study.

You may want to read these observations after you have completed the exercise.