In the realm of financial management for parish and town councils, transparency is not just a buzzword; it’s a cornerstone of public trust and accountability. While the use of spreadsheets for managing finances is widespread due to their accessibility and familiarity, they pose hidden risks that can undermine this crucial transparency. Recent incidents across various councils in the UK have shone a light on the pitfalls of relying on traditional spreadsheets. From substantial financial losses due to errors and fraud to the challenges in meeting regulatory compliance, the shortcomings are becoming increasingly apparent.
In this post, we delve into the intricate web of risks associated with spreadsheet management, specifically focusing on how they can obscure transparency in financial operations. We will explore real-life cases, common errors, and the implications of these on public trust. Moreover, we'll discuss how alternative, more robust solutions can enhance transparency, ensuring that parish and town councils not only manage their finances more effectively but also uphold the highest standards of accountability to their communities.
Join us as we unravel the complexities of spreadsheet management and make a case for more transparent, reliable, and efficient financial management practices in our local councils.
😱 Scary facts...
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.
- Maesteg Town Council loss due to fraud - £34k loss
- Denbighshire County Council loses £21m
- Flintshire County Council school £1M cash blunder
- COVID-19 spike with 16,000 positive results going unreported
- Blackpool Teaching Hospitals fined £185k
- Edinburgh hospital opening delay causing £16M loss
We hope these facts grab your attention and provide 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.
But, it’s 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? Here are 6 things you should think about if you are choosing to manage public funds on spreadsheets.
#1 Human error and mishaps
Spreadsheets are extremely error prone. It's easy to make mistakes when entering data into a spreadsheet, especially if the spreadsheet is large or complex
Seemingly minor mishaps can result in significant problems. Worst case, these could go undetected for many years. Without proper formatting and validation checks, it is easy for data entered into a spreadsheet to become inconsistent or incorrect over time.
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.
It can be stressful and frustrating when inheriting someone else’s spreadsheet, with multiple versions of the same file, 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
Whilst spreadsheets do not prevent fraud, they do make it easy for 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 tamper with data and formulas, whether that’s proactively or retrospectively (backtracking). Spreadsheets can be vulnerable to unauthorized access, especially if they are shared among multiple users or stored in the cloud.
Think fraud does not happen? I found these articles after a 5 minute google search, I am sure there are many more.
- Joanne Wills, 47, stole £135,000 from Towersey Parish Council
- Man arrested on suspicion of fraud after £163,000 disappeared from Cheshire parish council bank account
- Fomer parish clerk evades jail sentence after £36,000 fraud
- Maesteg Town Council clerk, 76, jailed over £238k fraud
- JAILED: Town clerk stole £218,000 from Rugeley Town Council
- Assistant town clerk jailed for £200,000 fraud
- Falmouth’s former deputy town clerk and finance officer has denied five counts of fraud totalling £2,070.48.
- Rolvenden Parish Council Clerk guilty of £80,000 fraud is jailed for three years
#3 Collaboration and troubleshooting
Spreadsheets are very portable, easy to copy and share via email. Whilst you might love this, its actually 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 and data are compliant.
Spreadsheets make it hard to collaborate and share information, data is scattered everywhere in folders, PCs, laptops, phones, and inboxes. It makes it impossible to adhere to regulations such as GDPR.
Ask yourself.... if you had a Freedom of Information or GDPR Right to erasure ('right to be forgotten') request would you know where all your data is? Can you remember where all your copies are stored, who you have emailed and where their copies are?
What’s more, over the last two decades, we’ve seen a surge in regulations that directly affect spreadsheet-based systems.
#5 Business Continuity
When it comes to business continuity spreadsheets excel (pun intended) at failing to meet the needs of the council as a whole.
Spreadsheets are usually created and owned by a single individual, its on their laptop, they are proud of their system, which they fully understand. The rest of the council staff (if any) and the councillors will usually not be familiar with the financial function.
But what happens when the Clerk leaves and a new clerk joins? What happens if your Clerk needs to take a sudden long term leave of absence due to illness? What happens if the laptop or PC picks up virus, the file or disk corrupts, the laptop is lost, stolen or damaged?
What happens if these situations coincide with a regulatory deadline, like submitting your AGAR or an important query is asked by an auditor?
If any of these events happen, are you able to easily access the accounts spreadsheet, related data and offsite backups (if there are any)? If you have the data, do you know if its the latest version or an older version? Is there a handy handover or training document to help you get up to speed.
The above questions and scenarios are real. Hence we get a call as councillors don't want to be in that situation again.
With a modern cloud based system such as Scribe Accounts all you will need is a login and you can access the accounts data from anywhere. Need help with understanding anything, then call our support team. You are in safe hands and there is no disruption to business.
#6 Time and money lost
A false economy is an action that does save money at the beginning but which, over a longer period of time, results in more money being spent or wasted than being saved.
Spreadsheets are a false economy because of all the reasons listed in points 1 to 5 above, but additionally the time it takes to get the job done.
Think about what is required to run a professional and robust financial function at parish and town councils.
- ADMINISTERING - Recording transaction , including receipts, payments, bank reconciliation, VAT Claims etc..
- MONITORING - Financial reports, Budget versus Forecast, Balance Sheet, Yearly comparisons, committed spend
- GOVERNING - Making Tax Digital, AGAR, Account Statements, AGAR, Internal Audits
How much time does your council spend administering your finances on spreadsheet? Is it the right tool for the job, considering there is purpose built software to do that very job?
Next Steps
If you are adamant you want to remain on spreadsheets, then read our blog 10 Tips For Managing Financial Accounts Using Spreadsheets.
Looks like are a lot of work right? The good news is it can be easier with Scribe. If you would like to have a friendly chat with our team of experts at Scribe, we can tell you how our cloud based software is super easy to use and gives you:
- 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 here
https://www.scribeaccounts.com/demo-request
We have a team of qualified and part qualified accountants, expert trainers and problem solvers.
Sources
- https://www.teampay.co/insights/biggest-excel-mistakes-of-all-time/
- https://blog.caspio.com/5-of-the-most-terrifying-excel-spreadsheet-horror-stories-weve-ever-heard/
- https://www.denizon.com/spreadsheet-fraud/
- https://www.businessinsider.com/excel-partly-to-blame-for-trading-loss-2013-2?r=US&IR=T
- https://www.optimaconnect.co.uk/post/why-excel-isnt-to-blame-for-the-nhs-test-and-trace-blunder
- https://theconversation.com/excel-errors-the-uk-government-has-an-embarrassingly-long-history-of-spreadsheet-horror-stories-147606
- https://flatfile.com/blog/7-scary-excel-errors-your-team-is-probably-making/
- https://www.denizon.com/top-10-disadvantages-of-spreadsheets/
- https://www.zdnet.com/article/excel-errors-microsofts-spreadsheet-may-be-hazardous-to-your-health/
%20(1).png)
