Chapter 4 — Spreadsheets and their risks
Lesson map
Lesson 04 published. Map, example, EX010.
flowchart TD
N0["Learning outcomes"]
N1["4.1 Spreadsheets everywhere"]
N2["4.2 Common errors"]
N3["4.3 Simple controls"]
N4["4.4 When to move on"]
N0 --> N1 --> N2 --> N3 --> N4
N4 --> EX["Worked example"]
EX --> Q["Exercise EX010"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["4.1 Spreadsheets everywhere"]
N2["4.2 Common errors"]
N3["4.3 Simple controls"]
N4["4.4 When to move on"]
N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX010"]
[Learning outcomes]
|
v
[4.1 Spreadsheets everywhere]
|
v
[4.2 Common errors]
|
v
[4.3 Simple controls]
|
v
[4.4 When to move on]
|
v
[Worked example] --> [Exercise EX010]
Learning outcomes
You will (1) recognise common spreadsheet errors; (2) apply simple controls to spreadsheets; (3) decide when data should move out of spreadsheets.
4.1 Spreadsheets everywhere
Harbourline runs many processes in spreadsheets: store rotas, promotion plans, supplier price lists and the weekly sales summary for the board. Spreadsheets are flexible and familiar, which explains their popularity. Amara examines the weekly summary and finds it built by copying figures from three reports, with formulas linking twelve tabs. One person maintains it. She notes that flexibility also makes errors easy, and that critical processes depending on one person's spreadsheet create risks the organisation may not see. The maintainer is often on leave.
4.2 Common errors
Amara reviews research on spreadsheet errors and checks the summary for typical problems. She finds a formula range that stops one row short, so the newest store is excluded from totals. She finds numbers stored as text that are ignored by sums, and a hard-coded figure typed over a formula. None was visible from the final totals. She explains that spreadsheet errors are common partly because results look tidy, which discourages checking. The newest store's sales had been missing from board totals for three weeks before her review.
4.3 Simple controls
Amara proposes controls: a check cell comparing the summary total with the source report total, protected formula cells, a version history in a shared drive and a short description tab explaining sources and steps. She also suggests a second person reviews the summary monthly. These controls cost little and catch the errors she found. Dr. Lindqvist notes that proportionate controls suit spreadsheets better than banning them, which rarely works in practice. The maintainer agrees to all four controls, and the second reviewer is named in the description tab.
4.4 When to move on
Some processes outgrow spreadsheets. Amara suggests criteria: many users editing at once, data feeding other systems, regulatory reporting or volumes too large to review manually. The board summary meets two criteria, so she recommends building it in the reporting tool with direct links to source data, removing copying steps. The promotion plan stays in a spreadsheet with controls. She learns that governance decisions about tools should follow risk and use, not fashion. The board receives its first summary from the reporting tool two months later, with no copying steps.
Worked example
Amara finds an incomplete formula range, text numbers and an overwritten formula in the board summary, proposes check totals, protection and review and recommends moving the summary to the reporting tool.
Synthetic case. Spreadsheets are synthetic.
Errors and limits
Tidy totals do not prove correct formulas; reconcile with sources.
Exercises
- EX010 — Missing store. Why was the newest store excluded? Rubric: 3 = a formula range stopped one row short;; 2 = partly correct;; 1 = vague;; 0 = none.
- EX011 — Check cell. What does the check cell compare? Rubric: 3 = summary total with source report total;; 2 = partly correct;; 1 = vague;; 0 = none.
- EX012 — Moved process. Which process moves out of spreadsheets? Rubric: 3 = the board sales summary;; 2 = partly correct;; 1 = other;; 0 = none.
Continue studying — lesson 04 (chapter 04 of 40)
← Chapter 03 · Course chapter index · Chapter 05 →
Continue to the next authored lesson.