6 Financial Modeling Best Practices for the Second Reader

On 27 September 2014, Goldman Sachs showed TIBCO Software’s board a valuation whose share count included restricted stock twice, among equity awards and again in shares outstanding. It was a financial modeling error of meaning: each figure in the data-room spreadsheet was accurate. The proxy explains why no one noticed: “The spreadsheet did not specifically highlight this fact.” (TIBCO, 2014)

The error surfaced after TIBCO announced its sale to Vista Equity Partners on 29 September, and when Goldman revised its analysis on 11 October, the implied enterprise value came to about $4.2 billion, against the $4.3 billion announced. The price stayed at $24.00 a share. The board noted that the merger agreement gave it no basis to force the buyer to pay more. The arithmetic ran as written. The number included something its readers did not expect, and the overlap came to light only after the decision.

Cases like this one suggest that financial modeling best practices, from separate input sheets to one formula per row, work best as rules for a second reader, because builders are poor judges of their own errors. Three more public failures, the error research and the two main modelling standards point to the same control.

SolarCity’s valuation carried the same kind of error two years later

In 2016 the spreadsheets Lazard used for its discounted cash flow analysis of SolarCity counted $400 million of projected revolver debt twice. The error surfaced 18 days after SolarCity signed its merger agreement with Tesla. Lazard told the special committee that its conclusion would not have changed, and the valuations prepared separately by Tesla and its adviser Evercore never contained the error (SolarCity, 2016).

The table below sets out four failures with primary documents behind them, TIBCO’s among them. In each, the arithmetic ran as written and the problem surfaced after the decision.

Four costly financial modeling errors, and when they surfaced

Case	What went wrong	Caught by a balance check?	When it surfaced
TIBCO’s sale to Vista Equity Partners (2014)	Restricted stock sat in both the equity awards and the common shares outstanding; Goldman Sachs’s board analysis used the overstated count	No: each figure was correct, and the overlap was unlabelled	After the announcement; implied enterprise value about $4.2bn, not the $4.3bn announced
SolarCity’s sale to Tesla (2016)	$400M of projected revolver debt counted twice in spreadsheets Lazard used for its discounted cash flow analysis	No	18 days after the merger agreement (our arithmetic)
InterCity West Coast rail franchise (2012)	Officials read model outputs in real terms as nominal, understating bidders’ required risk capital by nearly 50%; the model used a GDP elasticity of 1.4, bidders were told 1.8	No	Competition cancelled 49 days after the award (our arithmetic)
Reinhart and Rogoff, “Growth in a Time of Debt” (2010)	An average stopped five rows short, dropping five countries; excluded years and equal country weights did more damage	No	2013, when outside economists replicated it

Sources: TIBCO and SolarCity definitive proxy statements (SEC, 2014 and 2016); Laidlaw Inquiry (2012); Herndon, Ash & Pollin (2013).

The West Coast case adds a warning about models that cannot be shown: the Laidlaw inquiry found that the Department for Transport’s risk-capital model “was not a model that could be shared with bidders without a risk of challenge” (Laidlaw Inquiry, 2012).

The most cited case is also the most misremembered. In 2010 Carmen Reinhart and Kenneth Rogoff reported average growth of −0.1% for advanced economies with public debt above 90% of GDP. Thomas Herndon, Michael Ash and Robert Pollin of the University of Massachusetts Amherst found that they had “averaged cells in lines 30 to 44 instead of lines 30 to 49”, dropping Australia, Austria, Belgium, Canada and Denmark (Herndon, Ash & Pollin, 2013). Corrected, the figure was 2.2%. The row range is what everyone remembers. Yet on the replicators’ own decomposition, it and other errors accounted for −0.3 percentage points of the gap, while excluding available years and weighting each country equally accounted for almost −2.

In hindsight, each of the four errors was visible to a reader with the working file who asked what a number included. Where the record names who identified a problem, it was an outsider: the Amherst economists, and Ernst & Young, appointed to give the Laidlaw inquiry an external perspective.

Builders underrate their own financial modeling errors

The best field evidence on how often such errors occur comes from Stephen Powell, Barry Lawson and Kenneth Baker of Dartmouth’s Tuck School of Business. An earlier audit of 50 workbooks in daily use had found errors in 0.8% to 1.8% of formula cells. For their 2007 study they audited 25 workbooks from five organisations and corrected every confirmed error to measure its effect (Powell, Lawson & Baker, 2007). Sixteen workbooks held 117 errors. Of those, 47 changed nothing, but four moved an output by more than 100% and the largest was worth $110.5 million. Analytical choices lie beyond what such an audit can find, although “these types of errors may be the most consequential”.

The auditors also found “little correlation between the importance of the application or the risk involved and the quality of the spreadsheet”. The sample came from five established organisations. Nothing in it suggests that a model gets extra care because more rides on it.

Laboratory work points to one reason the errors survive. Raymond Panko of the University of Hawaii asked 80 upper-division students of management information systems to build a two-year pro forma income statement from a short word problem (Panko, 2003). Of the 35 who worked alone, 86% made at least one error, yet they put the probability of an error in their own model at 18%. Students assigned to groups of three expected 13% and erred 27% of the time, less than a third as often as students working alone (our arithmetic). These were students on a small task, and the study cannot say whether experienced modellers judge themselves better. On this task, builders underestimated their own error rate several times over.

Software helps less than it seems. In a 2010 study by Salvatore Aurigemma and Panko, Excel’s Error Check and a commercial auditing tool were tested against 97 errors that business undergraduates had made building the same kind of pro forma. Across 970 chances, they usefully flagged 5, “a mere one in 200 errors”, because 84% were planning errors, “which occurred within the developer’s heads” (Aurigemma & Panko, 2010). In Powell’s audits of operational spreadsheets, by contrast, auditing tools caught all but 17.8% of the errors found. Either way, a check catches only the errors someone thought to test for.

Software engineering met this problem decades ago. The inspection method Fagan published in 1976 assumes that engineers detect only 40% to 60% of errors on their own, so it requires a team. It caps a session at about 100 statements “because research has shown that longer inspections produce lower detection rates”, and limits preparation and the meeting to about two hours each. One round usually finds 60% to 80% of errors. Spreadsheet practice, Aurigemma and Panko concluded, is “about where software testing and inspection were in the 1960s”. Models are worse off in one respect: a program error often crashes the program, while a financial modeling error usually yields a plausible wrong number, which the authors call a silent error.

Financial modeling standards are written for the second reader

The FAST Standard, named for flexible, appropriate, structured and transparent, sets the bar above a model free of errors: a good model “must be easily used and reviewed by others” (FAST Standard Organisation, 2019). Its best-known rules are tests a reviewer can apply in seconds: “Do not write a formula longer than your thumb”, and “No formula should take more than 24 seconds to explain”. ICAEW’s Financial Modelling Code, drawn from a review of seven methodologies, covers similar ground, although it warns that “some computations are easier to follow when these rules are broken” (ICAEW, 2024). Both apply the same test: can the next reader follow the formula?

Six conventions, each letting someone other than the builder check the model

Practice	What the standards say	What it lets a second reader do	Failure it guards against
Separate inputs, calculations and outputs	ICAEW: segregate them	Trace any output to its assumptions	Numbers typed into formulas, “strongly correlated with the presence of other problems” (Powell et al.)
One consistent formula per row	ICAEW and FAST: consistent formulas and layout	Check one formula, not every cell	Rows whose formulas change structure, which “often hide errors”
Short formulas	FAST: no longer than a thumb, explainable in 24 seconds	Follow the logic in the time a review allows	Logic too dense to review
Labels and units on every number	ICAEW: clear labels, units and sign convention	See what a number includes, and in which units	Real figures read as nominal; shares counted twice
Checks and a master check	ICAEW: an equality check wherever two values should agree	See whether the model is internally consistent	The errors its builder thought to test for
Review sized to the stakes	ICAEW: a regime “consistent with the risk presented”	Find what the builder did not think to test	Errors of logic and meaning

Source: FAST Standard 02c (2019); ICAEW Financial Modelling Code (2024); Powell, Lawson & Baker (2007).

Neither financial modeling standard does the reviewing. FAST’s remit stops short of the control environment, “such as back-up, version control and testing”. ICAEW tells modellers to “implement a review and testing regime consistent with the risk presented”. It asks them to predict how outputs should move when an input changes, and to arrange peer review “by a colleague or even a professional audit” when the impact is significant.

A reviewer on paper may not be enough. JPMorgan Chase’s task force on the 2012 losses in its Chief Investment Office found that the bank’s Model Review Group approved a new value-at-risk model without comparing its results with those of the model it replaced. The group “theorized that any comparison” was unnecessary because the new model “was more sophisticated and hence was expected to produce a more accurate VaR”. The task force found some evidence that the review was accelerated under pressure from the trading side (JPMorgan Chase, 2013). In that case, a review under deadline pressure, with nothing to compare the output against, added a signature and little else.

Review effort should follow the money that rides on the model

After the West Coast failure, the UK Treasury’s Permanent Secretary, Sir Nick Macpherson, was asked in October 2012 to review the quality assurance of models across government. Departments identified just fewer than 500 business-critical models (HM Treasury, 2013). The review’s rule was proportion: assurance should match “the significance of the decision, the complexity of the model (including key inputs and assumptions) and the degree of risk and uncertainty”. Each business-critical model was to have one named, senior owner who signs it off before use.

A startup has far fewer models and people than a government department, so the review’s procedure does not carry over; its principle does. Sort the models by the decision they feed.

Review tiers for a startup’s models

Tier	Models	Decision they feed	Review before use
1	Monthly cash forecast, budget tracking	Spending within an approved plan	The builder, with a master check and an input-change test
2	Annual operating plan, runway model	Board approval; when to raise	One independent review, plus a comparison with last year’s plan and actuals
3	Fundraising model, cap table, exit waterfall	Price, dilution, what each holder receives	Two independent reviews, and every share count and debt balance reconciled to its source: stock ledger, option grants, loan agreements

Source: CX Cash proposal, applying the proportionality rule in ICAEW (2024) and HM Treasury (2013).

An independent review here means a session of no more than about two hours, following Fagan’s limit, in which someone who did not build the model works from the file and its notes alone. The reviewer traces each key output to its inputs and asks what every number in a material row includes.

A hypothetical seed-stage fundraising model with 2,000 formula cells shows what a second review buys. If its cells erred at the rates in Powell’s earlier audit, 0.8% to 1.8%, it would hold 16 to 36 errors. One round that finds 50% to 80% of them, the range Panko reports for individual inspectors in laboratory studies, would leave 3 to 18, and a second would leave about 1 to 9. That assumes each round finds errors independently, which two reviewers with the same blind spots will not. Most survivors will not matter, since 47 of the 117 errors in the 2007 audit changed nothing. The figures argue for a second review of a tier-3 model, though they cannot promise a clean one.

Powell’s auditors found most financial modeling errors harmless

The strongest case against this advice comes from the auditors themselves. Powell, Lawson and Baker concluded that “Devastating errors are rare” and that the impact of many errors “is negligible or zero”. Review is also expensive. One of Aurigemma and Panko’s authors puts commercial spreadsheet inspection at about 50 hours per person, with a team of three to five. At 150 to 250 person-hours, that is more than a seed-stage company can spare for a monthly forecast.

Devastating errors are indeed rare. The tiers concede as much: a forecast that steers next month’s spending needs its checks and little more. The concession stops at the few models that set a price, a dilution or a date. The 2007 audit found a $110.5 million error in 25 workbooks, and each public case fed a company sale, a rail franchise or a fiscal debate. A formula audit would not have found Reinhart and Rogoff’s weighting choice or the Department for Transport’s real-terms outputs; a reader asking what each number meant might have.

Who reads the model, and before which decision

1. Build every model for a stranger. Keep inputs apart, use one formula per row, and label every key number with what it includes (fully diluted or basic, real or nominal, gross or net of debt).
2. Write down the judgement calls beside the model (which periods, which comparables, which weights), since a formula check cannot see them.
3. Give the fundraising model, the cap table and the waterfall two independent reviews and a reconciliation to source documents, well before the deadline.
4. When one model replaces another, run both for a period and explain every difference, the comparison JPMorgan’s reviewers skipped.
5. As an investor or director, ask who other than the builder has read the model and what they compared it against. Ask for the fully diluted share count and net debt reconciled to the stock ledger and loan agreements, the reconciliation that would have exposed TIBCO’s double count.
6. Treat a model the company will not share as probably unchecked.

The conventions in the FAST Standard and ICAEW’s code make a model legible to someone who did not build it, and that someone is the control that works. The reader belongs in the process while the decision is still open, with effort that rises with the price, the dilution or the runway the model sets. Even then, review leaves a residue. On the hypothetical 2,000-cell model, two independent rounds would still leave about 1 to 9 errors, most of them harmless.

References

• Aurigemma, S. and Panko, R. R. (2010). The detection of human spreadsheet errors by humans versus inspection (auditing) software. Proceedings of the EuSpRIG 2010 Conference, 73–85.
• FAST Standard Organisation (2019). The FAST Standard, version 02c.
• Herndon, T., Ash, M. and Pollin, R. (2013). Does high public debt consistently stifle economic growth? A critique of Reinhart and Rogoff. PERI Working Paper 322; later in the Cambridge Journal of Economics.
• HM Treasury (2013). Review of quality assurance of Government analytical models: final report (the Macpherson review).
• ICAEW (2024). Financial Modelling Code.
• JPMorgan Chase & Co. (2013). Report of JPMorgan Chase & Co. Management Task Force Regarding 2012 CIO Losses, 16 January.
• Laidlaw Inquiry (2012). Report of the Laidlaw Inquiry, HC 809, 6 December.
• Panko, R. R. (2003). Reducing overconfidence in spreadsheet development. Proceedings of the EuSpRIG 2003 Conference, 49–66.
• Powell, S. G., Lawson, B. and Baker, K. R. (2007). Impact of errors in operational spreadsheets. Proceedings of the EuSpRIG 2007 Conference, 57–68.
• SolarCity Corp. (2016); TIBCO Software Inc. (2014). Definitive proxy statements.

Error rates come from the published audits and laboratory studies listed, and the four cases from the proxy statements, the Laidlaw inquiry and the Amherst replication; Fagan’s figures are as summarised by Aurigemma and Panko. Rates and counts without a cited source are our calculation from the studies and filings listed. The review tiers are our proposal, and the 2,000-cell fundraising model is hypothetical.