Member Article

Why you can’t Excel with spreadsheets

Written by Paul Bywater, Managing Director of Manufacturing Solutions at Sanderson.

When first starting out, many businesses track their business performance and metrics using what’s at hand. In most cases, companies start with a PC or two, creating spreadsheets to track day-to-day processes and recording their financials into basic accounting or business software.

Over time, the business expands the collection of spreadsheets to monitor and record data as they require. Each delivers a function which is not supported by the central business system. So data needs to be manually copied between the spreadsheets and the business system daily – this brings in human errors and bottlenecks, but works for a time. As an organisation expands that’s when big problems quickly become apparent that threaten the integrity of the business.

Here are seven such pitfalls to consider:

1. Rekeying: a warning sign

Copy and paste - it’s easy to do once, but risky to rely on if done dozens of times a day. For example, a customer rings to confirm an order: hooray, let’s make a sales order! But not so fast – you need to copy the quotation out from a spreadsheet into the business system.

Not only is this a time-consuming step which can cause a bottleneck, but it introduces the risk of mistakes due to human error. A similar example is rekeying a sales order as an invoice on an accounting system.

Instances like this occur hundreds or thousands of times a week in many small-to-medium sized businesses and cost hundreds of pounds in man-hours to complete.

Consider the extra costs and risks your business is incurring if mistakes are made or delays caused. It makes it easy to see why manufactures often experience rapid productivity gains, payback and better compliance when replacing disparate systems with an integrated ERP system.

2. Data duplication: where is the truth?

Spreadsheets are incredibly easy to set up, amend and move, perfect for getting business data recorded quickly. However this simplicity is also the Achilles heel of spreadsheets.

Files can be moved and lost. Tracking down missing files is a waste of time and money.

Files can be duplicated, creating multiple versions - none of which are authoritative.

Files can be moved or emailed, again creating duplicates which need to be tracked and re-integrated.

Files can ‘escape’, raising security issues.

Duplicated files invariably result in duplicated effort.

“19.8 per cent of business time – the equivalent of one day per working week – is wasted by employees searching for information to do their job effectively“ – Customer Analysis Survey, Interact

Replacing the spreadsheets that run your business with a true ERP solution instantly negates the duplication problem. Using ERP, the platform becomes the definitive data source, with all the information your employees need to use stored centrally.

With ERP there is never any need to copy or move files, and emailing information also becomes redundant. Data can be consolidated and extracted when required, reducing IT requirements and saving staff time that would be otherwise spent hunting down relevant information.

“Too much digital information creates the problem of organising it in a way that makes it useful. Many businesses have more digital data than they can intelligently work with and often can’t extract what they need when they need it or create business intelligence from it.“ – ’The Expanding Digital Universe’, IDC.

3. Data veracity: the sums don’t quite add up

If you use multiple spreadsheets for similar tasks or recording related information, don’t be surprised to find that when put side-by-side the sums don’t add up. Why? Each document is not based on real-time information, but rather the most recent information each had available at the time. This can vary widely according to department or internal processes.

A typical example is:

Accounts say we invoiced £££

Sales say we sold £££££

Despatch say we delivered ££££

Production say we built ££

For managers this makes access to accurate real-time information largely impossible – as having to consolidate spreadsheets makes the report out of date before it’s even finished.

When businesses apply an integrated ERP system, the move to real-time visibility is a huge boost for the effectiveness of management decision making.

4. Data ownership: is it secure?

Careful management of a spreadsheet is essential to prevent discrepancies and errors creeping in. In theory, this requires one user taking ownership of the file and managing all interactions.

In practice, many companies are also run on “shadow” spreadsheets - documents owned and maintained by individual users which are critical to their workflow and contain valuable data which is not available elsewhere. If one of these employees retires or moves on, that critical data may never come to light, putting the business at a disadvantage.

Sharing data also becomes risky and difficult, as it requires sharing valuable spreadsheets between individuals. Often an individual’s or department’s spreadsheets contain information other parts of the business need but aren’t shared, for fear they will damage the spreadsheet’s data or set-up.

Again, the answer is to centralise data using a dedicated ERP system. This way staff know where to store information, and where they can find it. Flexible permission levels enable individuals to see or interrogate data without risking its security. A full audit trail is recorded.

Staffing changes will not cause data to be lost, creating seamless continuity. And rather than having to dedicate resources to managing individual files, staff can get on with performing their core responsibilities instead.

5. Data recovery: what’s not being backed up?

Where spreadsheets are stored correctly on the company file server, recovery of lost information is generally possible. Restoring files from backup tape or removable drives is timely however, and the process occasionally returns corrupt data which is of little or no use.

“Personal” spreadsheets can also be missed by backup routines, particularly when stored on local PCs. If one of these files is corrupted or deleted accidentally, recovery is all but impossible.

Regardless of where the file is stored, that aspect of your production will be halted until the file can be recovered.

“Only 35% of SMBs have a comprehensive disaster recovery plan in place.“ Gartner Inc

ERP systems are based on transactional databases, which are far more resilient and less prone to corruption. Even if there is an issue which requires the system to be recovered from back-up, at least your business can be confident that all relevant data has been archived correctly and can be recovered.

6. Data archiving: segmentation in disguise

Spreadsheets are undoubtedly powerful tools for analysing and reporting on data. However, as noted before, the data duplications may cause problems further down the line. Spreadsheet efficiency is also significantly reduced the more data stored, and the larger the file becomes.

Although information can be archived, manually removed from spreadsheets, or split into new files, doing so fragments your company data. For the greatest benefits, data needs to be retained in one place so that deep analysis can be performed to recognise greater value. ERP also offers greater functionality to oversee every aspect of your manufacturing process, rather than needing to segment it into separate spreadsheets for each process.

“Data is a precious thing and will last longer than the systems themselves.“ Tim Berners-Lee, Web pioneer

ERP systems built on relational databases are only limited by storage capacity, meaning that you can always increase storage and processing by investing in more hardware as required. Ultimately where your ERP data platform reaches its limits, so too does your business.

Spreadsheets are perfect for starting a manufacturing business. But in the long term they cannot grow or adjust in the ways needed to suit the changing needs of your business.

7. Four sides of the elephant: can you see the full picture?

When you can only see your business one spreadsheet at a time, getting a full picture isn’t possible. This means you really can miss the elephant in the room – a big issue that needs resolving or an opportunity to be taken.

This is neatly expressed in the well-known Indian parable about men around an elephant:

“A group of blind men (or men in the dark) touch an elephant to learn what it is like. Each one feels a different part, but only one part, such as the side or the tusk. They then compare notes and learn that they are in complete disagreement [as to what it is].“

Wikipedia

Without integrating your operations into one business ERP system, how do you clearly identify the big issues that your business needs to be addressing? Relying on multiple data sources stops you having a quick, consistent view of what is truly occurring across your company.

You know the issue - perhaps expressed differently:

· “…can’t see the wood for the trees”

· “…missing a piece of the jigsaw”

· “…need a 360 degree view”

The important thing is for your business to recognise the significance of this happening and take action to address it.

This was posted in Bdaily's Members' News section by Paul Bywater .