Do you Excel at Spreadsheets - Are your spreadsheets robust enough?

Do you Excel at Spreadsheets? Most of us now use spreadsheets routinely as part of our daily business life. Ever since the introduction of Visicalc and more notably Lotus 1-2-3 in the early 1980's, spreadsheets have been used more and more to take the pain out of often simple financial calculations but more frequently modelling of a more complex nature.

Spreadsheet Frustration

Do you often wonder whether the spreadsheets you use are robust enough to deal with rogue data? Are they protected to prevent accidental or deliberate overwriting of formulas in key cells?

Research has shown that more than 50% of spreadsheets have fundamental flaws in their construction and this is borne out by high profile cases. In 2011, Support services group Mouchel discovered an £8.6m hole in its profits causing its share price to plummet. Of this £4.3m was attributed to an incorrect valuation of its pension deficit as a result of a spreadsheet error that was spotted by its outside actuaries. Emergency talks were required with its three main lenders to avoid breaching debt covenants.

More recently the high profile botched bidding process on the West Coast Main Line rail franchise contract has been attributed to modelling errors within spreadsheets, although there has been some discussion as to whether the flaws were inherently down to over optimistic economic and financial data being used rather than down to the calculations themselves.

So what can you do to try to prevent spreadsheets going wrong?  Here are a few simple tips to help you excel with spreadsheets:

  • Keep your raw data and reporting on separate sheets. Effectively spreadsheets should be structured so that you do not enter any data on the report pages.
  • Use protection on cells that contain formulas to prevent them being overwritten.
  • Use names for cells or ranges of cells. So if turnover is in cell A10, cost of sales A12 and gross profit A14 (being a formula +A10-A12), Name cell A10 as 'SALES', A12 as 'COS' and therefore the formula in A14 will be +SALES-COS making it more intuitive.
  • Use conditional formatting to highlight cells where the outcome does not fall within an expected range.
  • Use drop down boxes and other input control techniques to prevent entering plainly incorrect data. So if for example you have a stock valuation model and there is a need to enter the number of units held for each type of stock, you can put in a control to prevent a negative number being entered.
  • Use colours and other formatting to distinguish results and different key parts of the report.
  • Thoroughly test the model using both expected and unlikely data to see the results and whether the model works as anticipated.

There are plenty of free resources on the web and of course you can get in touch with your usual Mitchell Charlesworth contact.

This entry was written by Philip Griffiths - Mitchell Charlesworth's Audit Compliance Partner, responsible for the quality of audit work throughout the Practice. To find out more about our Audit and Accounting services, complete a quick enquiry form here and we will be pleased to advise you.

Post a comment

Registered to carry on audit work in the UK and Ireland by the Institute of Chartered Accountants in England and Wales and authorised and regulated by the Financial Conduct Authority for investment business