Spread the love

When One Cell Cost Millions: 5 True Spreadsheet Stories

A spreadsheet looks precise. Rows line up. Totals add up. For many teams it is still the place where bids are ranked, dividends are estimated, contracts are listed and risk is measured.

But a spreadsheet is only as careful as the person who last touched it. A cut-and-paste that shifts one row, a missing minus sign, a hidden line that reappears in a PDF, a formula that divides by the wrong thing, or five countries left out of an average: each of these has happened in public, with real money and real reputations on the line.

This week we look at five verified cases. None of them is about one brand of software. They are about what happens when critical decisions rest on a fragile grid of cells.

Table of Contents

TransAlta’s cut-and-paste that cost millions

In June 2003, TransAlta Corporation told investors that a one-time clerical error would cut second-quarter earnings by US$24 million pre-tax. The company had submitted an erroneous bid to the New York Independent System Operator for May 2003 transmission congestion contracts (TCCs), which hedge the cost of moving electricity across New York’s grid.

According to the Globe and Mail and TransAlta’s own SEC filing, someone preparing the bid file misaligned the rows in the spreadsheet. High bids meant for certain transmission paths were applied to lower-demand routes instead. TransAlta bought more contracts, at higher prices, than it intended. Under New York ISO rules, bids cannot be changed after they are submitted.

It was literally a cut-and-paste error in an Excel spreadsheet that we did not detect when we did our final sorting and ranking of bids prior to submission.
Steve Snyder, TransAlta president and CEO, to The Globe and Mail (June 2003)

Takeaway: When a spreadsheet drives a sealed bid or any irrevocable filing, treat the final sort as a second person’s job. One misaligned paste can lock you into the wrong price.

The missing minus at Magellan Fund

In late 1994, Fidelity’s Magellan Fund, then the largest mutual fund in the United States, told shareholders it expected a year-end distribution of about $4.32 a share. In December, Fidelity withdrew that plan. There would be no year-end dividend.

As CIO magazine later summarised from a letter by Magellan president J. Gary Burkhead, a tax accountant copying figures from the fund’s financial records onto a separate distribution spreadsheet omitted the minus sign on a $1.3 billion net capital loss and treated it as a gain. That single transcription error threw the dividend estimate off by $2.6 billion. The fund’s underlying records had been correct the whole time.

The Associated Press reported that the November estimate was only that: an estimate. Customers and the fund’s price were not rewritten by the withdrawn $4.32 figure. Still, Michael Lipper of Lipper Analytical Services called it the largest such miscalculation he knew of.

Takeaway: Copying a signed number into another sheet is not a clerical afterthought. Require a second check whenever a sign, a unit or a scale changes hands.

Hidden rows that rewrote a deal

In September 2008, Barclays Capital was racing to buy selected Lehman Brothers assets after Lehman’s collapse. Cleary Gottlieb Steen & Hamilton, counsel for Barclays, worked from a dense Excel list of contracts emailed by a Lehman representative: nearly 1,000 rows and more than 24,000 cells, according to court filings summarised by the ABA Journal.

Around 11:30 p.m. on 18 September, a junior associate was asked to reformat the spreadsheet and convert it to a PDF for the bankruptcy website. Resizing the rows made previously hidden contracts visible. About 179 contracts that Barclays had marked with an “N” (not to be assumed) appeared in the published schedule as if they were part of the deal. Lawyers spotted the problem on 1 October, and Cleary Gottlieb moved the bankruptcy court to exclude those contracts from the purchase agreement.

The episode did not invent a neat dollar figure for “damages.” What it did show, in public court papers, is how a formatting step can change the legal record when hidden content survives into a PDF.

Takeaway: Before you publish or file a spreadsheet-derived PDF, unhide every row and column and scroll the full used range. What you cannot see can still travel with the file.

When a VaR model muted the risk

JPMorgan’s 2012 “London Whale” losses on a synthetic credit portfolio have many causes. One detail that surfaced in the bank’s internal task force report, and that James Kwak highlighted in The Baseline Scenario, concerned the value-at-risk (VaR) model used by the Chief Investment Office.

The new model ran through a series of Excel spreadsheets that staff completed by manually copying and pasting data from one sheet to another. After the trade blew up, reviewers found that a key formula, after subtracting an old rate from a new rate, divided by the sum of the two rates instead of their average.

This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .
JPMorgan internal task force report appendix, as quoted by James Kwak, The Baseline Scenario (9 Feb 2013)

That bug did not, by itself, create the multi-billion-dollar trading loss. It sat alongside other model flaws, weak automation and governance gaps. But it is a clear example of how a single wrong denominator can make risk look calmer than it is.

Takeaway: Mission-critical risk models need version control, automated feeds and independent tests. A manually pasted spreadsheet is a prototype, not a control.

Five countries left out of the average

In 2010, economists Carmen Reinhart and Kenneth Rogoff published “Growth in a Time of Debt,” which reported that advanced economies with public debt above 90% of GDP had average real growth of about −0.1%. The finding shaped years of austerity debate.

In April 2013, Thomas Herndon, Michael Ash and Robert Pollin at the Political Economy Research Institute (University of Massachusetts Amherst) replicated the work. They found that a spreadsheet coding error had omitted Australia, Austria, Belgium, Canada and Denmark from the relevant averages. Selective year exclusions and an unusual weighting method also mattered. When they recalculated, countries above the 90% debt threshold averaged about 2.2% growth, not −0.1%.

Reinhart and Rogoff later acknowledged the coding error. Herndon, Ash and Pollin were careful to say the spreadsheet mistake was one of several issues, not the whole story. Even so, one overlooked selection range changed a headline number that policymakers had treated as a hard threshold.

Takeaway: Any average that drives a policy or board decision should be rebuildable from raw rows. Document which countries, years and weights enter the formula, and have someone else reproduce the total.

What this means for your business

These five stories stretch from a power trader’s bid file to a mutual-fund estimate, a bankruptcy PDF, a bank risk model and a famous economics paper. The common thread is not Excel itself. It is the habit of treating a personal workbook as if it were a controlled system.

You do not need a trading desk to face the same risks. Any team that prices, forecasts, reconciles or reports from spreadsheets can tighten a few basics:

Two pairs of eyes on irreversible numbers. Bids, filings, distributions and board packs get a second review before they leave the building.
Signs, units and scales are checked on every copy. A missing minus or a wrong currency turns one cell into a mirror-image error.
Hidden content is hunted before publish. Unhide rows and columns; search for filters; open the PDF and scroll every page.
Formulas that measure risk are tested like code. Change inputs on purpose and confirm the output moves the way you expect.
Critical models leave the paste-and-pray stage. Automate feeds, lock logic sheets and keep an audit trail of who changed what.
Averages and thresholds can be rebuilt from source. If you cannot recreate the headline number from the raw table, do not trust it yet.

At SOD Infotech, we help businesses move critical numbers out of fragile personal workbooks and into systems with clearer ownership, history and controls, so a single cell is less likely to rewrite the quarter.

Sources

BLOG