Dangers of using spreadsheets for managing parish, town and community council finances

new-blog

AUTHOR: JOHN FAGAN, CHIEF EXECUTIVE OFFICER AT SCRIBE


Did you know 88% of spreadsheets have errors?

This was the result of a USA study carried out by MarketWatch. The UK government has an embarrassingly long history of spreadsheet horror stories.

We hope this fact grabs your attention and provides food for thought regarding the use of spreadsheets for managing precepts, income and expenditure and budgeting at parish, town and community councils across England and Wales.

Its cheap and easy

We hear you. We understand why local councils use spreadsheets.

You will have a PC or laptop with Microsoft Office installed. Many clerks and responsible financial officers have experience using Excel. It’s the quickest, cheapest and most convenient application to use to manage council accounts. But are you making the right cost versus convenience tradeoffs?

#1 Human error and frustration

Spreadsheets are extremely sensitive to human errors.

Minor errors can result in significant problems.  Worst case, these could go undetected for many years.

Common human errors are typos, misuse of negative signs, copy and paste, misaligned rows, circular references, leaving cells out of sums, not normalising data, adding blank rows or merging cells to make it look nice, not using data validation.

Common frustrations when inheriting someone else’s spreadsheet are multiple versions of the same file, lost files, no documentation, no support, locked cells, hidden cells, calculated values with no formulas.

To illustrate the impact of human error, let’s look at some real examples.

Typos & formatting errors:

  • MI5 bugged the wrong phones
  • Barclays spent millions on worthless contracts
  • AstraZeneca released confidential information
  • Kodak suffered an $11 million severance

Copy and paste errors:

  • JP Morgan lost $6 billion
  • TransAlta lost $24 million

Formula mistakes:

  • Emerson lost $3.7 million 

Data manipulation errors:

  • NHS missed 16,000 positive cases of COVID-19

#2 Fraud

Spreadsheets are very easy for opportunistic and unscrupulous clerks or RFOs to manipulate finances for personal gain.

Spreadsheets do not have any controls, no audit trails, no access control, no role-specific permission handling. This makes it extremely easy to alter data and formulas, whether that’s proactively or retrospectively.

#3 Collaboration and troubleshooting

Spreadsheets are very portable, easy to copy and share via email. This is a bad thing.

Clerks and RFOs are the key people that manage council accounts. There are many other “users” of financial accounts:

  • Councillors 
  • Auditors
  • General Public
  • HMRC - Making Tax Digital for VAT
  • Banking systems

Within local councils, spreadsheets are copied, reformatted and transformed into other documents.  Creating many different versions, and many different “versions of the truth”.

Spreadsheets don't enable easy collaborative sharing, in which multiple users can collaborate at the same time, along with controls in place regarding audit logging and permissions users have to create, read, update and delete specific records.

Spreadsheets do not offer any support for easy integration into external systems. In particular, HMRC’s system for Making Tax Digital for VAT. As of April 2022 registered parish and town councils must submit VAT via software that integrates with HMRC systems.

#4 Regulatory Compliance

After reading points 1,2 and 3, you will see this creates a big challenge for ensuring your accounts are compliant.

Spreadsheets are highly susceptible to human error and fraud. They make it hard to collaborate and share information, data is scattered everywhere in folders, PCs, laptops and inboxes. It makes it impossible to adhere to regulations such as GDPR.

What’s more, over the last two decades, we’ve seen a surge in regulations that directly affect spreadsheet-based systems.

Next Steps

We hope you found this blog informative, the good news is this is very easy to fix. If you would like to have a friendly chat with our team of qualified and part qualified accountants, expert trainers and problem solvers or a free consultation. Scribe are happy to have a chat.

Otherwise, if you want to know more about Scribe Accounts?

  • Super easy to use
  • Multiple users with different permission controls
  • Audit trails and daily backups
  • Instant AGAR reports
  • VAT Returns via HMRC portal
  • FREE training and FREE support

Register for a demo

It is time to level up town and parish councils
The Localism Act ten years on

By accepting you will be accessing a service provided by a third-party external to https://www.nalc.gov.uk/