Truth in the World of Excel
February 2, 2016 – Mike McCann
February 2, 2016
A friend recently asked me if I hate Excel. I don’t. “I love Excel”, I said. I could not do my job without Excel; it’s hard to image how we would function without the spreadsheet tool.
But Excel is not an accounting system — and doesn’t do what accountants do.
I did my basic college Accounting work in the days before PCs. Years of class work began with writing entries by hand in proper journal format, then carrying amounts to subsidiary ledgers and ultimately the general ledger. We closed the books by drawing period totals in each ledger, and carrying them to the trial balance so we could develop the financial statements.
We did this in pencil, using adding machines to verify totals by footing (adding down) and cross-footing (adding across) every schedule, before moving on the next step. The trial balance was a “trial” because it tested the prior work, proved that the (ledger) books balanced, and credits correctly offset debits. The final entry posted the difference between the two columns as profit or loss for the period, and made the net total zero. Once the books were closed, they stayed closed. Hard numbers, written on paper, and typed up as financial statements.
Moving forward a few decades, in 2010, we researched property titles as part of the State-mandated closure of the Redevelopment Agencies in California. We had to pull original budgets and accounting records from deep storage to find records of the capital projects planned for the City and Agency’s real property.
We worked through budget authorizations: recording purchases and transfers by hand on paper journals, and tracing them through the general ledger to the statements. In the end, we had hard proof of ownership in our hands, along with layers of old dust, sore eyes and paper cuts.
Many of our younger colleagues may have never closed the books by hand, or prepared a budget on ledger paper. But we all benefit from the lessons of those days. Our systems are full of error detection and balancing check points. Accounting software records actions on audit trails, guards access, backs up data, and audits results every year. The work can be cumbersome and tedious, but the processes are tested and sound, providing quality and reliable results that let us properly manage complex governments.
Contrast these controls with working in Excel. We easily and endlessly revise numbers, restate our results and model alternative stories. Excel is powerful, but fraught with risk. I have never met a colleague who does not have a story of flying high then being burned by the software’s lack of discipline and process control.
The first year I controlled a city budget process, I rewrote my predecessor’s Excel budget system. I used everything I learned over the years to build a powerful and flexible set of worksheets that I could use to handle requests for every department in the city, track the totals to keep us in balance, and produce the final schedules.
I sat with each department head and analyst, huddled over my screens, or better, used the new projector to put the numbers on the wall for all to see. The system worked; we produced a budget on time and the Council adopted it. But I learned some good lessons along the way.
This was hard work, consuming my schedule for weeks that Spring. I did not dare share control over the worksheet — the whole budget depended on this one file. So I attended every meeting, keyed in every decision, and tried out every possible scenario, in real time in front of an audience.
Then it was time to present the draft budget to the City Manager. I tried to explain how changes to last year’s budget were made, how revenue forecasts were calculated, and how capital projects were prioritized.
Like any experienced Excel hand, I had saved early versions of the file at various points along the way. Still, pulling up worksheets, comparing one to another, interpreting changes, keeping versions straight, and recalling the underlying stories made discussion difficult.
And we found an error. We were pretty error-free for the most part, but I had made the mistake of letting the Recreation department do some work in the file, and they had added a new program. I did not catch that the total formula ranges had not been updated to include the new program, which was fortunately nearly self-funded. Still, we had to pull most of our small budget reserve to cover the shortfall in the final pass before going to Council.
Lessons learned? Accounting systems have controls and audit trails for a reason. Excel worksheets are not as safe as a secure database. The ability to model on the fly is great, but building retrievable history is equally important. Spreading the work out to a large team with security and safety saves your sanity. Excel is the analyst’s friend – but data that cannot be accidentally altered is what lets me sleep at night.
Mike McCann moved into government service in Ukiah, then Monterey CA, after beginning his career in corporate (ADP, Wells Fargo Bank, Blue Shield of CA), not-for-profit (Blue Shield of Ca, Mendocino Private Industry Council), and start-up accounting. For the last 20 years, Mike has been hands-on with budget, financial reporting and accounting operations, including City budgets and CAFRs. He holds a B.S. in Accounting from SJSU and M.S. in Instructional Technology from CSUMB.
Contact Mike with questions or comments at email@example.com.
Category: Government Finance