One of the things we find when we first start speaking with companies is that they know they have a problem with using spreadsheets, such as Excel and Google Sheets, but don’t understand how bad the issue is.
This isn’t surprising as often a spreadsheet addiction is one that creeps up on you and before you know it you are almost running your finances on them.
So how do you set about understanding the problem and more importantly, how do start to put it right?
In this article, we’re looking at how and why people get sucked into a cycle of using spreadsheets, even though they might not be the right tool for the job, and what to do about it.
Here’s what we cover:
- The cycle of change
- The double-edged sword of spreadsheets
- Discovering how bad the problem is
- Doing a spreadsheet audit
- Identifying the change you need
- Prioritizing the change you want
- Final thoughts: Use spreadsheets wisely
The cycle of change
There’s a cycle that every business goes through.
Knowing where you are on this is useful because it helps you to identify what you should be doing at your particular point of finance evolution.
- Pre-contemplation: With no intention of changing behavior, businesses often find themselves stuck at this point. They don’t know there’s a problem so they don’t seek change.
- Contemplation: This is where teams are aware there is a problem but they have no commitment to change. Think of this as a fear of breaking out of their comfort zone.
- Preparation: The team is intent on making a change but they have not started yet. Planning, but not doing. If you are not careful you can allow the search for perfection to be the enemy of action.
- Actions: Active modification of behaviors, processes and systems. This is where the project is in full flow.
- Maintenance: Sustaining the change, embedding modified behavior and making sure that momentum is continued.
- Relapse: Falling back into patterns of behavior. You’ve made the change and become happy in a brand-new comfort zone.
The aim has to be to keep going around the circle, never falling into the relapse trap and constantly seeking improvement and upgrades.
The double-edged sword of spreadsheets
We all love using spreadsheets because they’re cheap, easy to use, flexible and it’s what we know.
But for every positive aspect of spreadsheets, there can also be a negative side.
It’s almost as though they’re a double-edged sword, with each function hiding a negative yin to a positive yang.
- Accessibility: Spreadsheets are a superb piece of software and it’s pretty easy to obtain and learn to use them. But sometimes spreadsheets can be too accessible, allowing anyone to change, modify and even destroy data. Sometimes they are even hidden and nobody can find them.
- Editability: In a similar way, almost anyone can edit spreadsheets. But the more complex they are, the more dangerous this is. If you lock them down, they can lose the very flexibility that makes them such a useful tool.
- Maintenance: Spreadsheets can be maintained – but are they? Do you know? Maintenance can actually end up being a massive chore when the aim was to save time and avoid work.
- Security: Spreadsheets are portable – but holding confidential data on a USB stick, really? They have a lot of security features but in reality, very few people use them as they tend to become inconvenient.
- Scalability: Spreadsheets are scalable because you can just add more as you need them. But as your business grows, you suddenly realize this just isn’t a good move. The workbooks get more and more complex, leading to a bigger maintenance burden.
Discovering how bad the problem is
So you think you have a problem but how do you know?
The starting point is to understand the different types of spreadsheets you could be using and how many of each are in existence.
We categories spreadsheets into six different types:
- Working spreadsheets: Ad hoc calculations, one-off analyses. Workbooks that you actively use to solve problems.
- Regular reporting: The same reports being done again and again in exactly the same way.
- Ad hoc reporting: Quick one-off solutions that are designed to answer a specific question but probably won’t be used again.
- Data storage: Using spreadsheets as a database. Think GDPR – not easy to interrogate, not secure.
- Imports: Where systems really don’t link well and where you need to take information from one system to another such as from payroll into the financial system.
- Process sheets: Spreadsheets that are needed to do a process. Expenses claims, purchase orders, etc.
Doing a spreadsheet audit
The next step you need to take is to do a spreadsheet audit (this audit template can help you) to analyses the size of the problem.
This is a semi-formal process of identifying and classifying each of the spreadsheets you have on your drives.
You can choose to appoint one person to do an audit but often it’s useful to do this as a group exercise.
It’s important to stress, though, that this isn’t about apportioning blame. It’s just about understanding what you could do better as a team.
The questions you want to ask are:
- The name of the spreadsheet
- The classification
- The owner
- The location
- How frequently it’s used
- How much time the process takes
- How important it is.
You can add in extra questions if they are helpful and once you’ve conducted an audit, you’ll have a clear sight of the size of the problem and the types of risk you are running.
Identifying the change you need
The changes you need that will allow you to get away from your spreadsheet jungle are likely to be different, not just between companies but also between departments and between workbooks.
In many cases, it’s about making systemic changes so people don’t have to do things on an external piece of software.
Sometimes, it’s about behavioral change and getting them to use functionality that already exists. Other times, it’s about understanding that you actually don’t need to do anything at all.
There are some common solutions we see when we are looking at issues with using spreadsheets:
Using custom fields or analysis codes
These probably exist on your system and using analysis codes on your data means people don’t need to export to spreadsheets and then spend time slicing and dicing.
Integrations between systems remove the need to export to a spreadsheet, manipulate and then import into another system.
These save huge amounts of time and effort and can actually be incredibly easy to achieve.
Often, people are using spreadsheets to perform processes because they can’t get them done in the main system.
Using automation apps such as Zapier can remove the need to have a spreadsheet to manually do what can often be fairly simple tasks.
Using existing functionality
We often find that people are doing things in workbooks that could actually be done in the main system – if only people knew that the functionality exists.
If you find you’re doing things such as bank reconciliation by downloading information into a spreadsheet then take a look at what might be available in your existing finance and accounting software.
Putting in a new system
This is probably one of the last steps that we get to because it tends to cost money and take a lot of time.
That having been said, it’s amazing how a system refresh can revolutionize the way a finance team works.
Extra reporting training
Oddly, it can be really small things that push people to report using spreadsheets.
Maybe someone can’t work out how to make a title bold, change a description or add in a calculated field.
And yet with just a little training, you can get rid of your reporting workbooks.
Using expenses systems
This is probably the most common thing we see when people are overusing spreadsheets.
Getting people to produce expenses claims with them and often printing them out is incredibly wasteful.
And yet really good expenses systems are readily available and very cheap these days.
New reporting tools
Older systems can be a little bit clunky to use, especially when it comes to reporting. However, you don’t need to change your system just to get great reporting.
There are plenty of modern, third-party applications that will provide superb reporting without breaking the bank.
Revising your Chart of Accounts
We’ve all seen Chart of Accounts that have got out of control. Accounts added in, new subsidiary charts that don’t match up, accounts that are out of sequence.
Cleaning up your Chart of Accounts can make a huge difference to the amount of work you have to do away from your main system.
And once it’s done, it gives you an enormous sense of wellbeing.
Prioritizing the change you want
Once you’ve identified the problems and you have worked out what the solutions are, it can be tempting to just launch into a massive change and transformation program and get it all done super quick.
But that can be a mistake.
After all, you still have to do business as usual tasks, and trying to push through massive and sudden change can be incredibly unsettling for your team and the wider business.
Remember also that doing small projects that are quick, easy and have a visible impact can produce a bit of a buzz in your team and get them excited about making more fundamental and larger-scale change in the near future.
So here’s our six-step plan for prioritizing your change program
- Analyze the time saved: Work out which changes will provide the biggest benefit.
- Work out the ease of implementation: If something is ‘low-hanging fruit’, it’s a candidate to be done first
- Understand the time to implement: Longer projects require more planning and resources
- Be clear on the cost: If it’s cheap, quick and provides great benefits, it’s a no-brainer.
- Analyze the risk: Think about the risk of a failed project not just on the finance team but also on the wider business.
- Think about other impacts: How will the change affect other departments? Your customers? Your suppliers? Your team?
Once you’ve assessed each change against these six criteria, you’ll have a much clearer idea about what order you should do things in.
Final thoughts: Use spreadsheets wisely
After all that, you probably think we’re totally against spreadsheets – but you couldn’t be more wrong.
They’re a superb tool that can do almost everything. And therein lies the problem.
Just because they can do everything doesn’t mean to say they should do everything.
Using spreadsheets purely for ad hoc work, calculations and analysis means you’re using them to their best advantage.
But if you’re doing regular reporting, using spreadsheets as a way to integrate other systems or you’re using them just because, then maybe you need to reassess.
# # #
TimeLinx delivers innovative project & service management software as a complete solution that perfects the sell-track-manage-support-bill cycle that services organizations must have to delight their customers; TimeLinx brings the cycle together in a single application that offers less frustration, better project management, complete reporting, and improved profitability – all specially designed for Infor and Sage.