Government

Six weaknesses of spreadsheet-based Budget Book solutions

Jamie Black, President, F. H. Black & Company Inc.

Spreadsheets by any other name is still a spreadsheet…

 

Recently there has been several spreadsheet-based Budget Book and financial reporting tools (e.g., CAFR) that have been developed and marketed as all-in-one solutions for complex reporting. This is ironic considering many government agencies are still using Excel spreadsheets today for their reporting, moving to one of these solutions may seem like the right fix – but not so fast. These solutions are sometimes desktop add-ons that enable better connections to a source of data. Or they can be cloud-based solutions that rely on spreadsheets to maintain their data and develop their reports.

 

Can’t tell if it’s a spreadsheet?

 

At first glance, it may be hard to tell if the financial reporting or Budget Book software is a spreadsheet-based tool. You could spend hours reviewing their websites and watching a demonstration, and still not recognize it.

 

It’s not surprising that these solutions don’t advertise their reliance on spreadsheets, given the scientific evidence of the weaknesses of spreadsheets – accuracy, multiple versions, broken formulas – and the organization who’ve’ used them with negative and sometimes publicly disastrous consequences.

 

In fairness, these spreadsheet-based software solutions do have improvements over the desktop spreadsheets, such as:

  • Better collaboration abilities for sharing comments, tracking changes or assigning tasks
  • Better audit trails and change management
  • Ability to tie supporting documents to a given value or cell
  • More granular security and/or permissions.

 

The downside of spreadsheet-based software

 

Because the core of these products are still spreadsheets, it still means your organization is not getting important functionality that saves time and resources. This includes:

 

1. No database:

Since it’s a spreadsheet all data resides there or in its workbook, not in a database. This means you must build all your own personalized systems to:

  • ensure your G/L balances
  • identify the 100 – 1000 new G/L accounts that might be added each year

 

2. No processes/workflow:

There is no structured system that you can leverage to support industry best practices. What steps should be followed and in what order? You and your organization must develop all these processes on your own and maintain them separately from your reporting environment.

 

3. No grouping mechanisms:

The G/L accounts that sit in your spreadsheet must be summarized in many different ways (by object, by fund, etc.) to power your Budget Book and other financial reports like CAFR. If you use spreadsheets, formulas must add the right accounts together. This is fragile and susceptible to error. Moreover, unless you interrogate every formula, there is no way to know what is being included in a given number – there is no legend. Assuming you do get this right in year one, all the new accounts next year means these formulas must all be updated.

 

4. Tons of custom formulas:

All values are derived by “linking”. This is done by writing spreadsheet formulas of varying complexity. Any mistakes you make means errors in your Budget Book. Consistency in how these formulas are written or maintained is entirely dependent on the user’s expertise, accuracy and completeness.

 

5. No content libraries:

Reporting standards are continually evolving. There are new Governmental Accounting Standards Board (GASB) requirements introduced regularly, so keeping up with them and what new statements, schedules or footnotes are required this year can be challenging. Spreadsheet solutions do not come with any of this content. It is entirely up to you and your team to figure out what must be presented and how.

 

6. No adjusting journal functionality:

Adjusting or changing balances is an absolute requirement for Budget Book or other financial statement/CAFR production. In spreadsheets you are left with no tools for the following:

  • The values in your G/L are typically maintained on a modified accrual basis. For many of your statements, you need full accrual.
  • Debit balances in A/P accounts or credits in A/R are just two examples of balances you need to reclassify.
  • Prior year adjustment/restatement caused by changes in accounting policy.

 

When Government Financial Officers evaluate software solutions for creating their Budget Book or other complex financial statement/CAFR, they must exactly match the critical requirements of their processes to the solution’s abilities. When an organization does this carefully, they will find that spreadsheet-based “solutions” are only marginally better than their own collection of desktop-based spreadsheets.

 

About Jamie Black & F. H. Black & Company Inc.

For nearly two decades F. H. Black & Company Incorporated has been implementing, integrating, and optimizing industry leading tools and best practices to improve the finance function for the public sector in North America. They’ve tackled reporting projects in nearly every major reporting framework including GASB, PSAB, IFRS, GAAP & ASPE. With a deep understanding and experience CaseWare products, implementation and training, F. H. Black & Company is the sole authorized provider of implementation and training services for numerous government/education solutions in Canada and is the author of Questica Budget Book. Follow Jamie on Twitter, contact him via email or visit the F. H. Black & Company Inc. website.

LinkedInTwitterEmail