An Excel error caused a $202 million state budget shortfall.

On Monday, the Richmond Times-Dispatch broke the story about a thorny budgeting problem for Gov. Glenn Youngkin that illustrates how bad technical practices can lead to bad public policy outcomes:

Local school divisions in Virginia just learned they will receive $201 million less in state aid than they expected — including $58 million less for the current K-12 school year that is almost three-quarters done.

The Virginia Department of Education has acknowledged the mistake in calculating state basic aid for K-12 school divisions after the General Assembly adopted a two-year budget and Gov. Glenn Youngkin signed it last June. The error failed to reflect a provision to hold localities harmless from the elimination of state’s portion of the sales tax on groceries as part of a tax cut package pushed by Youngkin and his predecessor, Gov. Ralph Northam.

The Washington Post provided more specifics about the source (or perhaps manifestation) of the mistake:

The problem originated with an online tool that allows school districts to see how much funding they should expect from the state, a number that takes into account the district’s number of students, how much it receives in property tax revenue and other factors.

The tool has been up since June 2022, allowing districts to build their budgets around the estimations. But last week, someone — the state would not say who — realized that the numbers were wrong. The miscalculation occurred after the state failed to account for funding changes connected to the elimination of the state’s tax on groceries, which took effect Jan. 1.

It’s not clear whether this was a conceptual problem (a failure to realize that it was necessary to account for funding changes) or a technical problem (an error of implementing that math). If the latter, this is an high-impact error from a software failure.

(I’d be remiss if I didn’t point out that some reasonable people are suspicious of this explanation, pointing out that Youngkin is no supporter of public education, and that it’s convenient that this mistake aligns with his policy preferences. But I think it’s much more likely to be a mistake, one that a governor would have no knowledge of or insight into. But that explanation is awkward for Youngkin, who has presented himself as a hard-nosed budget wonk whose private sector financial experience translates to fiscal competence. And yet, this.)

It’s instructive to look at the “online tool” in question, which turns out to be an Excel file. (Here’s a Wayback Machine link to the Excel file, because I expect that the problematic one will disappear. I’ve also put it in Google Sheets.) It has 38 worksheets, with a heterogeneous and puzzling series of titles like “Enroll. & At-Risk,” “FINAL SOURCE DATA,” “March 31, 2021 ADM,” “ASRFIN Queries,” and “Bedford County-City.” The message on the first worksheet would seem to indicate that the state published this without removing all of the placeholder text, which raises the question of how what else might be unreviewed or incomplete.

Many of these worksheets are dizzying, some hundreds of columns wide, most containing unexplained acronyms like “DABS,” “RLE,” “PPAs,” “ADJ ADM.” I don’t doubt that these make a lot of sense to state and local budget officials, but I have none of the subject-matter expertise to make heads or tails of them.

Excel is a fine way to build lightweight calculation software—you can build some pretty sophisticated systems in Excel and Google Sheets—but it shouldn’t serve as load-bearing infrastructure. Excel files can’t be diffed or version-controlled using standard revision control systems (e.g., Git). It’s impractical to perform automated tests on Excel files, as a part of a continuous integration process. Excel files become unwieldy as the number of worksheets increases—I can’t say where the tipping point is, but it’s for sure lower than 38. For a tool as critical as this one, it’s important to be able to at least perform some smoke tests, so you can check that providing providing particular sets of financial assumptions returns the correct numbers, and those should be run automatically every time that the tool is updated.

No doubt this started as some small, simple file, many years ago, put together by somebody at the Department of Education for internal purposes, shared informally with some municipalities, but gradually shared more broadly and standardized on. And then it grew and grew, without the necessary resources provided to support it that were commensurate with its newfound importance. Surely most other state agencies are vulnerable to similar failures with similar impacts due to the same problem.

Software failures causing public policy failures are a defining feature of our era. In that sense, this is a normal failure, although I’m not familiar with another instance of an Excel error in a government budgeting documenting leading to such a large financial problem. But history does give us one example to draw on: Fidelity Investments’ 1994 omission of a minus sign (-) from a spreadsheet, which rendered their $1.3 billion loss into a profit of $1.3 billion. They dutifully notified the three million investors in the Magellan mutual fund that they’d receive a dividend of $4.32 per share…only to have to notify all of them that they’d actually receive nothing, after outside auditors caught the mistake.

Spreadsheets are great tools. But some applications require more rigor, and we can see here that mutual funds and state budgeting are two strong examples.

Published by Waldo Jaquith

Waldo Jaquith (JAKE-with) is an open government technologist who lives near Char­lottes­­ville, VA, USA. more »

One reply on “An Excel error caused a $202 million state budget shortfall.”

  1. The Excel file is just a complicated display tool. The real calculations are done in a custom software system where the basic aid standards are applied, enrollment projections updated, sales tax incorporated, and so many other things. I don’t know for sure, but it’s probably within that system that the error happened – and it could have been as simple as inputting the wrong sales tax figure (due to the complicated hold harmless the GA put in). My guess is it truly was just human error, precipitated by a needlessly complicated grocery tax reduction (and then hold harmless) by Youngkin and the GA. It’s not related to Excel being used, the Excel file is likely well downstream of where the error occurred.

Comments are closed.