ICAEW.com works better with JavaScript enabled.

Systems control: Excel errors

In this second of three articles, we look at some other areas of practice where deadline controls can go awry.

The first article in this series, Systems control: monitoring filing and submission deadlines provided a reminder of what can go wrong and the importance of keeping deadline control systems under close review.

Other services provided to the client

While deadline controls for annual tax and accounts work are often as near to watertight as possible, controls over other deadlines can be somewhat lacking and often reliant on a particular staff member’s memory or, at best, personal Outlook calendar.

Consider in particular how your firm monitors deadlines for:

  • VAT returns
  • payroll runs
  • PAYE year end forms (including P11Ds)
  • management accounts.

Are these controls accessible to a sufficient number of team members to enable proper cover for staff sickness, etc?

Excel formulas

There is a variety of ways in which Excel could be hindering a control, and therefore spreadsheets must be reviewed and checked periodically. Below are some examples.

There is no formula

Some individuals treat Excel like a word processor for numbers and do not actually use any of its tools to help users remember the relevant deadlines.

The formula is wrong

It could be that the formula has been entered incorrectly, linked to a cell that is then moved or changed, or just not updated for a rule change.

The formula is overwritten or not used

In some cases, users will not copy the formula into a new cell and will instead just type text. The firm is then relying on the user’s own knowledge rather than the logic in the cell.

The formula is too simplistic

As we saw in the first of this series of articles, there are cases where a simple formula is insufficient to deal with all situations. Therefore firms should consider designing a more comprehensive spreadsheet. For example:

  • add ‘comments’ to cells in Excel to remind users when the formula does not apply;  or
  • add another column so that users can indicate if the period is more than 12 months; then a more advanced formula can be used.

July 2012