Spreadsheets are used everywhere in manufacturing outside core systems, from planning lines and labour requirements through to tracking over time hours, understanding capacity, reviewing materials against order requirements and analysing waste. There comes a point however when far from being a temporary solution for a business, the spreadsheet instead becomes a major part of how an operation functions. When this happens the business increases its risk and can start falling behind in terms of understanding and applying cost saving measures, having restrictions imposed when carrying out improvement initiatives and not providing some key management information either timely or accurately. Here are 10 tips if you’re using spreadsheets why you ought to consider an upgrade to using software and databases.
1. Easy to over type data & formulas
Data and formulas can be easily deleted, amended and over typed either deliberately or by accident. By deliberate, this means someone making an improvement to the spreadsheet. Given this is so easy to do what can happen is:
- Errors in calculations can occur and may not be easily spotted i.e. through noticing something is missing, or by interpreting a set of results
- Data that’s taken hours or days to collect can be wiped out instantly
- Protected cells can have protections removed and not replaced thus making the spreadsheet more vulnerable
2. Simultaneous editing by multiple parties cannot be done
One problem with viewing and editing spreadsheets is that it cannot be done by more than one person at a time. So for example if data is required to be entered by a number of people this cannot happen simultaneously in the master copy. Instead people either have to wait, or create duplicate copies and merge them to the master at a later date or time. Either way this can lead to operational or planning delays, could delay or incorrectly calculate important numbers such as over time or payment calculations and also affect management decisions. Merging spreadsheets also runs the risk that data and formulas can be over typed (see 1 above).
3. Core functionality and coding is accessible to all
When a spreadsheet is accessible it means it is often placed on a PC or network server that gives anyone in the company with the right permissions access. So for example on a shared network drive it could be visible to a large number of people. This is unlike core database driven systems where the database itself and core functionality is not visible or may not be accessible to a large group. A key point here is that whilst a database is hidden away it means it mitigates the risk of being copied, moved or deleted. Unlike a spreadsheet that can be copied, emailed, moved between folders, duplicated and deleted. Being so open it can also be at more threat to virus attacks from unknown or unsuspecting viruses on individual PCs. With a greater lack of control there will also be no guarantee that the right version of a spreadsheet will be backed up using network software i.e. where a spreadsheet version is being worked on, away from the backed up network drives.
4. Version control can become difficult and increase administration
Since spreadsheets are so easy to edit, it is not uncommon for them to have changes made “on the fly” or just prior to a meeting etc. Graphs showing label print failures by manufacturing line for example could be amended and printed just prior to a management meeting however it is easy to close a spreadsheet after making changes and not think twice about correctly saving the version you’ve worked on and consequently not save your edits. Similarly it is easy to load new data into a spreadsheet, do some modelling, make changes, load more data, make a few more changes then realise you’ve lost track of all the changes made. This becomes more serious when your expected numbers or output is way off the mark and not as expected, because back tracking to find out where you went wrong is difficult. Even saving versions of a spreadsheet becomes tricky because the version control still needs to be documented however:
- Since updating is quick and documenting version control consumes time in a busy day, the latter falls down the priority list and may not be done at all
- Multiple spreadsheet users also need to apply version control and the management surrounding this is much more difficult
Consequently in many cases version control management comes down to what an individual remembers or not! If the individual is away from work then the process falls down competely.
5. Audit history can be difficult or impossible
Sometimes you don’t just need to document that changes have happened, but also who made them and when. This becomes difficult if not impossible with a spreadsheet if there are no change controls in place.
6. Spreadsheets are often seen as down graded software so are not given full support
An irony is that whilst many people in management or office roles in a manufacturing environment are given spreadsheet access as part of a PC desktop, laptop or network log on few are trained to use its full capabilities and more to the point companies often do not fully support systems developed in spreadsheets as they are not often viewed as part of a core infrastructure. Consequently:
- Functionality changes or managing errors can be restricted to the available time of one person in a department
- Support calls are not often measured and therefore not managed – it can rest with individuals
- Spreadsheet systems are not always documented or have good training procedures
- Handing over spreadsheet systems when people move on is sometimes by word of mouth training only
- Lack of system updates or timely changes can restrict the improvement of business practices or support business changes
- Spreadsheet support is often seen as a small part of a job role whereas core system support is usually a full time job
- Unlike core systems, back-up and resilience plans and procedures are not as thorough or may not even exist at all
7. Not everyone in the company can access the same spreadsheet due to incompatible software versions
Sharing data within a company or with 3rd parties can be difficult if different software versions are being used i.e. spreadsheets may not be backwards compatible for example. This can lead to:
- Less functionality if the lowest version of a spreadsheet is used
- Unnecessary upgrades including making current spreadsheets compatible with newer software versions
- Spreadsheets not being used at all
8. Multiple versions of “the truth”
By using spreadsheets the same data is easy to manipulate in many different ways to produce many versions of what is happening within a business. Consequently the spreadsheet skills of individuals, their business knowledge and producing figures they want in the way they want them can lead to completely different statistics and information. Standard reports within a website for example will help to eradicate this.
9. Visibility may not be widespread
Spreadsheets can contain huge amounts of detailed data, graphs, formulas and information that is not easy for someone unfamiliar with it to grasp quickly. Also it may contain no work flow management i.e. with the ability to manage, control and authorise or sign off each step in a process. As a result spreadsheet systems may not get high level visibility in a business because the data being produced and business processes being supported are not understood by those not close to the spreadsheet itself. This means key business decisions may not receive full support or backing, or have financial commitment for example. It can also mean that stakeholders in a process may not give the spreadsheet much time because although it could be key to the business, it has a low visibility, poor understanding and is a system that is seen as being outside of the core architecture.
10. Website, software and database development is not as costly as you may think
Since return on investment and business innovation are not often associated with spreadsheet systems, having an upgrade to a database and software or web solution is not always seen as a viable option.