Lipstick on a Pig: 6 Spreadsheet-based CAFR & Budget Book Tool Flaws

avatar

Posted by Jamie Black

Topic(s): Automating Financial Reporting, CAFR

Read Time: minute(s)

You may be familiar with the expression "Lipstick on a pig". Variations of the expression date back nearly 130 years but the meaning has not changed; Superficial improvements that do not change the underlying nature of something.

Spreadsheets by any other Name

We have seen an increasing number of spreadsheet-based reporting automation tools promoted as ultimate solutions for CAFRs & Budget Books. This is ironic considering many organizations are using spreadsheets today and are looking for a better approach.

Sometimes these solutions are desktop add-ons to enable better connections to a source of data. Sometimes these are cloud-based solutions that rely on spreadsheets to maintain their data and develop their reports.

Hard to Tell They are Spreadsheets

The fact that these are spreadsheet-based tools may be hard to tell on initial review. You can spend hours reviewing their websites and sitting in presentations and still not recognize it.

It's not surprising they don't advertise their reliance on spreadsheets given the scientific evidence of their weaknesses and the numerous stories of very public disasters caused by spreadsheets

The Lipstick

In fairness, these tools often have improvements over the desktop spreadsheets you are used to, 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 / cell.
  • More granular security / permissions.

The Pig

But the core of these products are still spreadsheets. That means they suffer from the traditional spreadsheet weaknesses:

  1. No database - All data resides in spreadsheets / workbooks not in a database. This means you must build all your own personalized systems to:
    1. ensure your G/L balances
    2. identify the 100 - 1000 new G/L accounts added each year
  2. No processes/workflowThere is no structured system that you can leverage to support industry best practices. What steps should be followed and in what order? You must develop all of these processes on your own and maintain them separately from your reporting environment.
  3. No grouping mechanismsThe G/L accounts that sit in your spreadsheet must be summarized in numerous different ways (by object, by fund etc.) to power your CAFR or Budget Book. 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" - which is to say writing spreadsheet formulas of varying complexity. Any mistakes you make means errors in your report. 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 GASB pronouncements all the time and keeping up with them and what new statements, schedules or footnotes are required this year can be daunting. Spreadsheet solutions do not come with any content. It is entirely up to you to figure out what must be presented and how.
  6. No adjusting journal functionality - Adjusting or changing balances is an absolute requirement for financial statement / CAFR production. In spreadsheets you are left with no tools for the following:
    1. The values in your G/L are typically maintained on a modified accrual basis. For many of your statements, you need full accrual.
    2. Debit balances in A/P accounts or credits in A/R are just two examples of balances you need to reclassify.
    3. Prior year adjustment / restatement caused by changes in accounting policy.

Our message in this post is not that the additional features - the "lipstick" - have no value, or that there is no situation when spreadsheets should be used.

We are saying that when finance & budget officers evaluate possible solutions for their most complex reporting tasks (CAFRs & Budget Books),  they must exactly match the critical requirements of their processes to the solution's abilities. If they do this carefully, they will find that spreadsheet-based "solutions" are only marginally better than their own collection of desktop-based spreadsheets.

For more on this topic(s), see: Automating Financial Reporting, CAFR

Originally Posted on 18 October, 2016

Automate your financial reporting

Recent Posts