SEGRO Courses

Home | Excel | Power BI | Copilot | Visualising Data

Spreadsheet Modelling Best Practices (SMBP) Course

This is the content for the Spreadsheet Modelling Best Practices (SMBP) course.

What can go wrong with spreadsheets?

• The MoD exposed the identities of thousands of Afghans. Reported in the Guardian
• Bank Of England Inflation Model goes wrong. Reported in the Financial Times
• COVID-19 Tests lost (2020). PHE loaded CSV data into old XLS version (65,000) rows max. No checks on row counts.
• Edinburgh hospital opening delay due to specifications in spreadsheet leading to wrong air flow in critical care rooms.
• Ireland National Treasury Management Agency confused € with $ and lost €7550K as a result.

The last three examples are from the European Spreadsheets Risks Interest Group Horror Stories

Best Practice – It’s about more than just building a spreadsheet

We will work through these key stages in spreadsheet model development:

…and in fact, we will hardly even talk about the Build phase, as the other phases make this much simpler

Case Study – Coffee Cart

At the end of the sections on the scope, specification and design/build phases, we will do short exercise based on fictional a case study. The background is this. Mark is thinking of starting a coffee cart business. Your task is to build a spreadsheet model, using best practices, that will help Mark by estimating some key metrics. Full details are in the exercise instructions.

The scope phase: What business need does the model support?

The benefits include:

• Clarify objectives and model.
• Define boundaries – what the model will and will not do.
• Estimate timescales, resource requirements and costs.
• Understand main risks.
• Identify people involved and their roles in making the model a success.
• Consider broad data requirements.
• Give the go-ahead (or not) to build the model.

Scope Phase – Clarify Objectives

Consider the Objectives

• what the model exists to do
• what the model needs to produce
• how the model goes about producing it

For each, list some objectives and articulate what is in scope and what is out of scope

Consider the Risks. The level of risk depends on:

• Complexity
• Size
• How well the problem is understood
• How urgently the results are required
• The number of people involved

A very simple example

Solar Power Limited wants a model to forecast energy revenue…

Area	In Scope	Out of Scope
Purpose	Supporting a decision to build a new solar farm	Enduring operational financial forecasts
Model Outputs	- 25 year revenue forecast - Monthly granularity - Best, Central & Worst scenarios - Total solar farm forecast - Outputs in tables	- Cost forecasts - Variable sensitivity analyses - Comparing different solar design options - Individual solar panel forecasts - Outputs in graphs or other form
Project Considerations	- Required by 30th March - Will be handed over to new staff member - Must be viewable by full project team	-

Coffee Cart - Scope Exercise

Follow the instructions for the scope phase of the coffee cart case study.

Specification Phase: How does the model logically produce the required outputs?

The benefits include:

• Ensures model solves the right problem
• Forces sponsors and model builders to think the problem and approach through
• Surfaces ambiguity, constraints and trade-offs early
• Establishes a common understanding among the stakeholders
• Makes subsequent phases easier and more effective

The problems that may arise if we don’t do the specification phase include:

• Building the model takes longer than anticipated.
• Frequent changes introduce more errors.
• The design may be haphazard.
• The model is deemed a failure and gets thrown away.

How to specify

1. Define the outputs (reports, which you should partly have from the scope).
2. Determine the calculations and relationships.
3. Determine the inputs (assumptions).

Define the outputs

• What reports are needed?
• Who will use them?
• What purpose does each report serve?
• What information should each report show?
• What time periods should be used?

Determine the calculations and relationships

Two possible methods are bubble diagrams (which we will demo) and prototype models.

Prototype models

• Are useful if the problem is not well defined
• Communicate progress easily
• Sponsors can provide feedback more easily.
• Clarifies what is and is not possible.

Determine the inputs

• Identify the inputs from the bubble diagrams.
• Check the availability of the data. Is it the “right” data? Any constraints on the data?
• Consider how data can be validated.
• Identify constants e.g., VAT rate.

Example Bubble Diagram

View the example bubble diagram

Coffee Cart - Specification Exercise

Follow the instructions for the specification phase of the coffee cart case study.

Design Phase: How should the logic be implemented in Excel?

The benefits include:

• The model functions as intended.
• The calculations are easy to understand, maintain and extend.
• The model’s appearance is professional.
• The model’s user interface is supportive and helpful.
• The model’s layout in Excel does not prevent certain functions being used

The problems that may arise if we don’t do the design phase include:

• It is hard to use and to navigate around the model.
• It is hard to change or extend the model.
• It is hard to test the model and so …
• The model is likely to have errors.
• The model lacks credibility.

Spreadsheet Design - Golden Rules

Here are 10 golden rules for spreadsheet design

Coffee Cart - Design & Build Exercise - Instructions

Design and build a spreadsheet based on the example specification and following the best practice guidelines and golden rules.

Test Phase: How can we 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.
• 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.
• 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 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

Case Study

Here is the test phase case study.

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

Spreadsheet Modelling - Lessons Learned

• If we spend time and effort to scope, specify and design the spreadsheet before we launch Excel and start to build it, we will have a much better result.
• Best practices make a spreadsheet model more robust.
• SMBP & modern Excel can reduce but never eliminate the possibility of errors.
• Peer review and a proper testing process remain essential.
• We should ensure that other people can access and review our spreadsheets.