When does an Excel spreadsheet system need to be replaced?

Excel is very powerful financial and reporting software. Its graphical tools, pivot tables and macros are just some of Excel’s strengths. It’s also readily available on nearly everyone’s computer.

So it’s no surprise that Excel is often the first software to which people turn to develop a new financial application, in businesses large and small.

But Excel has significant limitations:

• Data storage is simplistic, so does not cater well for real-life situations
• A spreadsheet cannot be updated by more than one person simultaneously, and security is simplistic, so is not suitable for many multi-user situations
• It is difficult to make a spreadsheet system robust, and keep it robust as it evolves.  
• It is easy to make mistakes and inconsistencies in formulae.
• It is also difficult to maintain links between spreadsheets, especially if some are being circulated for completion by other people.
• Data entry is often done manually or semi-manually, and is not subject to validation. So data entry is slow and prone to error. 
• There is no audit trail

As a result, many people know of situations where material errors have arisen from using spreadsheets, and occasions where spreadsheet systems have held back the business. Ringing a bell with you?

There comes a time when a spreadsheet system needs to be replaced by a more suitable database system. Excel can be retained for reporting purposes.

BENEFITS OF REPLACEMENT SYSTEMS

Replacement systems use a database to store data centrally and provide a number of standard facilities that automate the system:

• Data can be structured exactly as required
• Systems can automatically and reliably aggregate and consolidate departments, regions, divisions and companies
• Data entry facilities automate the load of data from source systems, which can then be done in more detail if required
• Multi-user systems allow simultaneous use by people in the same department or different parts of the business, with differing levels of access permissions
• Audit trails and other management controls are available

As a result, a replacement system can be far more reliable, quicker and more powerful than the spreadsheet system it replaces. Excel can still be used for reporting, so its strengths can be retained.

Here are some examples where Camwells has helped find and implement replacement systems, working with FTSE250 to smaller private businesses across different industries.
 

(1) GROUP CONSOLIDATION

This FTSE250 housebuilding group has devolved many of the finance functions to regional teams, whilst running a shared service centre for cash transactions.

The Group reporting function was entirely dependent on Excel, which resulted in a number of critical issues, including:

• The process for submission of reports and forecasts from regions was cumbersome and time-consuming for regional and Group staff alike
• Data entry lost the available detail, and there were examples of keying errors
• Where spreadsheets were linked, it was also difficult to control versions, so also prone to error

As a result Group reporting staff were spending most of the time running the spreadsheets rather than analysing the data, such as comparing regional performance. With additional regions being added as the business grew, the spreadsheet system was nearing breaking point.

The work carried out included:

• Understanding current reporting, forecasting and related processes, and opportunities for improvement, by talking to MDs, FDs and accounting staff at regional and Group level
• Producing an agreed specification that was then used to assess available software, principally through software demonstrations and “proof of concept”
• Selecting multi-dimensional software and configuring it to best represent the business, including the import of detailed information from the trading system

The result is a more effective, quicker and more reliable system.

(2) BUDGETING AND FORECASTING

This quoted IT company had a budgeting and forecasting system consisting of a suite of complicated Excel spreadsheets which were sent out to relevant departments. The system had evolved over time so that the controller said “it was held together with sticking plaster”, in particular:

• Linking submitted spreadsheets and controlling versions was difficult and risked material error
• Finding correct versions to compare to actuals was difficult

Again a specification was agreed that would allow the entire budgeting process to be carried out in a centralised system, which would clearly handle different versions. Software was selected that would:

• Allow the company’s overall forecast financial position to be produced reliably at the press of a button, including balance sheet and cash flow projections
• Make the detail of each version easily available to authorised people to review forecasts and compare to actuals

(3) PROJECT ACCOUNTING

This quoted software house had a packaged software system for the basic accounting. But there was no module suitable for their project-based order processing, which involved the sale of their own software, buying in hardware and supplying various associated services.

This was controlled in an integrated suite of Excel spreadsheets. But as they had become so unwieldy and difficult for the order processing team to use, the manager was threatening to resign.

The spreadsheets were analysed and a specification agreed. Various software options were considered including:

• Third party add-on module to existing accounting software
• Custom-written add-on module
• Complete replacement

The decision was taken to keep the existing accounting software, and get an add-on module custom written.

The end result was a system so good, when the business was acquired the system was used for the enlarged group. Unusually the acquired company personnel kept their jobs, from FD to junior clerk. Clearly a most welcome benefit!

(4) EXPENDITURE TRACKING

This privately-owned multinational was developing an offshore oilfield in Africa, which required cost records to be maintained and reported on a cash basis to the national government, alongside normal accruals accounting.

These cash records were being collected in Excel, but the use of multiple currencies and the reconciliation of cash and accruals figures was becoming increasingly difficult. One multi-currency accounting database was the objective, together with a sophisticated new purchasing system.

The management’s preference was for a cloud system that could be easily accessed from a variety of locations. After a specification was produced, various cloud options were assessed alongside the on-premise software commonly used in the oil and gas industry.

One multi-currency cloud accounting system was found that would provide the dual cash/accruals accounting more simply than with the on-premise option.

(5) ORDER PROCESSING

This corporate subsidiary was providing IT network systems where all the components were being bought-in on a “back to back” basis, alongside their own services.

Sales order, purchase orders and accounting records were on separate Excel spreadsheets, with no logical link between them. Growth in the business required additional personnel, which required a multi-user system.

The decision had been made to replace the spreadsheets with standard accounting software and a custom-written order processing system.  This now needed to be implemented, and internal people did not have the time and expertise.

The initial system was implemented on time for the start of the new accounting year, and then various improvements made to cater for changes in the business.

One key benefit of a proper database system was that purchases of equipment supplied to specific customers could be clearly identified. This allowed supplier rebates to be claimed, totaling some £600,000 per annum. This literally doubled the company’s profits.

As the MD said, “We couldn’t have done it without you.”



If you know of any similar situations that need to be resolved, do ring Chris Challis on 01628 632914.