How Billing Spreadsheets Are Bad for Charity

I recently encountered another example of a "very large" charity (using the ACNC definition) relying on Microsoft Excel for its core business process—in this case, billing Services Australia for aged care services (in-home). It's easy to imagine the genesis of this spreadsheet back in the early days of consumer-directed care when this charity had a handful of clients. Someone involved likely had strong programming skills in Microsoft Excel macros, so it might have seemed unnecessary to invest in software or custom solutions at the time. All perfectly reasonable. This is where low-cost tools (including Microsoft Excel) serve an important role in an organisation.

But, like the story of the boiling frog, years of complexity have gradually been added to this spreadsheet. At some point—probably within a year of its creation—the spreadsheet should have been used as a blueprint for a fully automated and well-documented solution. More on that later. Fast forward to 2024, and we have a spreadsheet with over 270 tabs at the centre of our business—that is, collecting cash.

Let's use this as an example to explore the problems and solutions associated with having manually operated spreadsheets at the foundation of our financial processes.

Problem 1: Manual Workload

The first problem is the significant amount of manual work required to operate this spreadsheet every month. This includes running reports in downstream systems, exporting them in multiple CSV formats, loading them into separate tabs in the mega billing spreadsheet, pressing various buttons in the correct sequence, and then printing these individually to PDFs in a laborious process best described as "death by a thousand clicks." I pause here to reflect on the stoicism of those who operate these spreadsheets—I have yet to see someone complain; they do what's needed to get the job done.

But really, this initial manual effort is just the beginning. Because the process is so fragile, errors have occurred in the past, leading to the creation of various reconciliation processes to check and recheck the results before sending the mega spreadsheet to Services Australia. There's even a separate spreadsheet with its own macros used downstream in the financial system to recheck and balance these figures. My gut feeling is that more work goes into downstream reconciliation than the already significant amount of work required to operate the spreadsheet in the first place. So, a spreadsheet initially conceived (very validly) as a timesaver for one person has grown into a behemoth that demands manual effort from several people in the organisation just to bill the federal government for services rendered.

Problem 2: Business Continuity Risk

The second problem is business continuity risk. It's already inconvenient if the one person who initiates this process with the main spreadsheet is on leave. As a result, considerable handholding, training, and preparation go into planning this person's leave. In reality, the process rarely goes smoothly in their absence, often involving a fair degree of stress when some minor exception occurs. Such a concentrated key-person risk in the middle of cash receipting makes everyone nervous.

And sometimes it's not just a matter of the job not getting done at all. From an effort perspective, it can be worse if the job gets done but introduces errors. With the spreadsheet in question, each month's billing run requires taking opening balances from the previous month's closing balances. There have been instances where errors were detected in previous periods and compounded over two or three months. To describe the rectification efforts as "intense" would be an understatement, considering they required not only a bunch of re-work but also some embarrassing conversations with clients and Services Australia. This degree of fragility is stressful—just to keep the business operating in a steady state.

Problem 3: Supportability

Real business operations don't stay static. Services Australia requests changes in reporting formats, new systems are introduced internally, and growth in client numbers places additional demands on our business process. The problem with business logic being implemented in Microsoft Excel macros is that they're very difficult to structure and document in a supportable fashion. Compounding this, the normal best practices learned by professional software developers are rarely, if ever, followed by the citizen developers who first offered their time to help out. Even the person who wrote the macros often spends significant time re-learning what they wrote when they revisit the project a year later.

So, as the organisation evolves, updating a spreadsheet-based solution becomes expensive. In the early days, the original developer of these macros was available at reasonably short notice and was more than happy to make edits here and there. But as the solution grew larger and became more mission-critical, it also became harder to get hold of the original developer. Now, the original developer is no longer available.

Problem 4: Information Security (Cyber)

The final problem is information security (cyber). There are literally hundreds of copies of the spreadsheet scattered across multiple drives and email inboxes. Every copy contains hundreds of clients' details, including personally identifiable information (PII). A serious breach would only take one small mistake from one of the individuals with access to these drives or an emailed copy of the spreadsheet. While these situations often begin with perfectly acceptable practices (e.g., automating someone's work with a few macros in a spreadsheet used by one person), they often end up being unacceptable from a business risk perspective.

The Solution

The trick is to recognise this "boiling frog" scenario and invest a small amount in professionally written code to avoid years of hidden human resource costs and ballooning technical risk. The good news is that this situation is easy and cheap to fix if you choose the right technologies (open source + cloud v2). This process could be easily automated and documented for less than a month's coding by a mid-tier developer. If done internally, the cloud hosting costs would be negligible (maybe $10/month). Even as a managed service, you could operate tens of such solutions for less than the cost of an FTE.

Andrew Walker
Technology consulting for charities
https://www.linkedin.com/in/andrew-walker-the-impatient-futurist/

Did someone forward this email to you? Want your own subscription? Head over here and sign yourself right up!

Back issues available here.