Analysis QA Checklist: Check the Choices Before the Cells

The famous error was the small one

In 2013, three researchers at the University of Massachusetts Amherst tried to replicate a finding by the economists Carmen Reinhart and Kenneth Rogoff: that average real growth in advanced economies with public debt above 90% of GDP was −0.1% a year. Working from the authors' own spreadsheet, Thomas Herndon, Michael Ash and Robert Pollin found that the correct figure was +2.2% ¹.

The detail that is easiest to explain is a spreadsheet slip: a formula averaged rows 30 to 44 instead of 30 to 49, which dropped five countries (Australia, Austria, Belgium, Canada and Denmark) from the analysis ¹. Read the paper and that is not the main story. Together with a transcription error, the spreadsheet slip accounted for about 0.4 of a percentage point. Almost two percentage points came from two choices: excluding some early years of data for three countries, and weighting each country equally regardless of how many years it spent above 90% ¹. Under that weighting, a single year of New Zealand data carried one-seventh of the weight of the whole category ¹.

Herndon and colleagues allow that there could be an argument against giving every year of data its full weight ¹. Their complaint is that the original paper did not indicate or discuss the choice, which without a detailed justification they call arbitrary ¹. That is the lesson for anyone checking a strategy analysis. The formula error was findable by anyone. The choices that moved the answer were invisible unless someone asked.

An analysis quality checklist is a short list of checks an analysis must pass before its numbers go into a deck or a decision. The one on this page is ordered by where the expensive errors are: the question first, then the choices, then the arithmetic, then the claim on the slide. Its point of view: check the choices before the cells.

A wrong formula changes a number. An undisclosed choice changes the answer.

How to run it

The UK government's guidance on analytical quality assurance, the AQuA Book, makes a distinction worth borrowing. Quality assurance “is more than checking that the analysis is error-free and satisfies its specification (verification). It should also include checks that the analysis is appropriate and fit for the purpose for which it is being used (validation)” ². Sections A and D below are validation. Sections B and C are mostly verification. Both matter; most teams only do C.

1. Author passThe analyst works through all four sections and writes down, in an assumptions and exclusions log, every choice that could change the answer.
2. Independent passSomeone who did not build the analysis goes through it again, starting with the log. The AQuA Book's rule is that the assurer and the analyst shall be independent, with the degree of separation depending on the importance of the output and the size and complexity of the analysis ².
3. Fix or discloseEvery finding is either fixed or written onto the slide or into the appendix as a stated choice. Nothing is quietly resolved.
4. Sign offThe reviewer initials the log. The analysis goes into the deck only after that.

Scale the effort to the decision. A number in an internal update needs the author pass. A number in a board paper or an investment case needs both passes, and the reviewer should recompute the headline figure from raw data.

A. The question

1. The decision this analysis informs is written in one sentence, and the analysis answers that question rather than a nearby one.
2. The person who asked for it agrees with that sentence.
3. The unit of analysis matches the decision: customers or accounts, products or SKUs, plants or regions.
4. The time period fits the decision, and you can say why it starts and ends where it does.
5. There is a comparison: against plan, last year, a control group or a rival. A number with nothing to compare it to cannot support a choice.
6. The answer could have come out differently. If no result would have changed the recommendation, the analysis was decoration.

Most failures here are failures of fit, not error. The hypothesis-driven problem solving page explains why writing the expected answer first is the fastest way to see whether an analysis could change your mind.

B. The choices

This is the section the Reinhart and Rogoff episode is about. Every item asks you to make a choice visible.

1. Exclusions are logged. Every record, period, customer, region or outlier left out is listed with the reason and the number of rows affected.
2. The answer was rerun with the exclusions put back, and the difference is stated.
3. Weights are deliberate. Averages say whether they weight by count, revenue, volume or entity, and the alternative was tried. An average of averages is flagged.
4. The denominator counts the right thing. Per customer, per order, per line, per dollar: the one that drives the result in question.
5. The sample is not only survivors. Lost customers, closed stores, canceled products and failed rivals are included where they matter. Jerker Denrell shows that leaving out failures can reverse the apparent effect of a risky practice ⁴.
6. Definitions are consistent across sources and years: what counts as a customer, as revenue, as active.
7. Every assumption is written down with its source, who made it and how sensitive the answer is to it. The AQuA Book asks assumption logs to describe each assumption, quantify its effect and reliability, and record who made it and who signed it off ².
8. The base case is tested against the outside view. Kahneman, Lovallo and Sibony ask whether the base case is overly optimistic and whether the worst case is bad enough ³.

C. The arithmetic

1. Ranges are complete. Every sum, average and lookup covers all the rows it should; check the first and last row of each range by hand.
2. Totals tie. Subtotals add to totals, segments add to the company figure, and the company figure ties to a published or audited number.
3. Units and scale match. Thousands against millions, monthly against annual, nominal against real, local currency against dollars.
4. No hard-coded numbers hide inside formulas, and every input sits in one labeled place.
5. The headline number was recomputed independently, by a different person or a different method, and the two agree.
6. Signs and directions are right. Growth rates, variances and changes point the way the text says.
7. Rounding is consistent, and percentages on a chart add to 100 where they should.
8. Copied data matches its source. Spot-check five values against the original document or system.
9. Sensitivity is shown for the two or three inputs the answer depends on most.

For market sizes, the market sizing calculator runs the top-down and bottom-up versions side by side, which is a quick independent recomputation in itself.

D. The claim on the slide

1. The title says no more than the evidence shows. A finding from one region is not titled as a company-wide fact. The action-title drill practices this.
2. Every number on the slide has a source note a reader could follow.
3. Correlation is not written as cause unless the analysis tested the cause.
4. Charts do not mislead: axes start where they should, scales match across panels, and the comparison is the one in check A5.
5. Uncertainty is stated where it matters: a range, a confidence level or the sensitivity from C9.
6. The choices from section B appear on the slide or its backup, in plain words: “excludes accounts closed before 2025; including them lowers the figure by 2 points.”
7. You know where every number came from. Kahneman, Lovallo and Sibony's question is worth asking literally: which numbers are facts, which are estimates, and who put the first number on the table ³?

A worked example

The company and figures are invented.

Every formula in the original was correct. The recommendation was still wrong, because of four choices that were reasonable on their own and invisible on the slide. Section C found one error. Sections A and B changed the answer.

When the checklist becomes the problem

The fair objection: checklists slow fast teams, and after the tenth review they turn into box-ticking. A reviewer who initials 30 items in four minutes has assured nothing.

The AQuA Book's answer is proportionality: the assurance a piece of analysis needs rises with its complexity and the business risk it carries, from analyst-led testing up to external audit ². In practice, that means three tiers. For a routine number, run sections A and C. For anything going to a decision meeting, run all four, with an independent reviewer on section B. For a board paper, an investment case or anything that will be published, the reviewer recomputes the headline from raw data.

Box-ticking has a second cure: rotate what the reviewer must produce. Instead of initials, ask for the one exclusion that moved the answer most, the one assumption the answer is most sensitive to, and one number recomputed. Those three cannot be faked in four minutes.

At the level of a strategy team, keep a short log of errors caught and their causes. After a year it tells you which checks earn their place in your team. If most findings are in section B, train analysts to keep the exclusions log from day one. If the same arithmetic error keeps recurring, fix the template, not the analyst.

Monday: one log, one recompute

Take the analysis your team is about to present. Write its exclusions log: what was left out, why, and how many rows. Rerun the headline with the exclusions back in. Then ask someone who has not seen it to recompute the single most important number.

If both come back clean, the slide is stronger and you can say why. If not, you found it before the steering committee did. In the Reinhart and Rogoff spreadsheet, anyone who opened the formula could see the slip. The weighting choice mattered more, and nobody could see it without asking.

Questions

What should an analysis quality checklist include?

Four groups of checks: whether the analysis answers the decision's question with a fitting unit, period and comparison; whether choices such as exclusions, weights, denominators and assumptions are logged and tested; whether the arithmetic ties, with complete ranges, matching units and an independent recomputation; and whether the slide's claim stays within what the evidence shows, with sources and uncertainty stated.

What is the difference between verification and validation?

In the UK government's AQuA Book, verification checks that analysis is error-free and meets its specification, while validation checks that it is appropriate and fit for the purpose it is being used for. A correct calculation that answers the wrong question passes verification and fails validation.

Who should review an analysis?

Someone independent of the person who built it. The AQuA Book requires the assurer and analyst to be independent, with the degree of separation set by the importance and complexity of the work. For high-stakes numbers, the reviewer should recompute the headline figure from raw data rather than only reading the formulas.

What was the Reinhart and Rogoff spreadsheet error?

In a 2013 replication, Herndon, Ash and Pollin found that a spreadsheet formula averaged rows 30 to 44 instead of 30 to 49, leaving five countries out. They also found that excluding some early years for three countries and weighting each country equally moved the result much more than the formula slip did.

Where to go next

• Action Titles: Rewrite the Slide Title in Ten Practice Rounds
• How Strategists Think: Hypothesis-Driven Problem Solving
• Strategy Deck Checklist
• Evidence & Source Ledger
• Halo Effect