Reviewing & correcting clients' data

The client has provided you with their information and it’s now your job to turn it into a set of accounts that you are willing to put your firm’s name to. The process is always the same – review, analyse and correct – but how you go about this work will determine whether this year-end job is profitable or not. Your processes will be governed by what you have to work with. 

Paper-based “books” and spreadsheets 

It is unusual for the client to provide a trial balance when they are using paper-based records or Excel, so you will have to compile this, and all required summaries and schedules, yourself. This obviously takes time and adds cost - but you can claw-back some of the efficiency lost, because you will be reviewing and analysing the client’s information as you work. 

For clients who insist on using Excel for their accounting records, it is better if they use a standard template that you have provided. The more consistent in shape the spreadsheets you receive from clients are, the more efficient you are going to be. 

Paper-based books and spreadsheets represent unstructured data. Computers are what drive efficiency savings and computers work best when presented with structured data. You should therefore be encouraging your clients to use bookkeeping software instead.  

Bookkeeping software 

Efficiency and productivity gains are almost exclusively obtained through the use of software and other technology. When your client uses bookkeeping software you have opportunities to work smarter that are simply not available when working with paper-based or spreadsheet records. 

Deciding whether to work with the software or the data

For clients using bookkeeping software to manage their accounting records you need to decide whether you are going to use their software, or just work directly with the underlying data. There are advantages and disadvantages with each option here and your choice should be made on a client by client basis: or perhaps more likely, a software by software basis. 

Why would I want to work directly with the data? 

If you can extract the client’s data to (typically) Excel Pivot Tables and then use these to review and analyse the accounting records, you can avoid the user interface of the software itself. The advantage here is that you can use software you are very familiar with (Excel) rather than the client’s own bookkeeping software: which you may not like, may not be familiar with or may not have a licensed copy of yourself. 

The disadvantage, of course, is that you have to create the reports and schedules you need yourself in Excel. By using the client’s bookkeeping software, you have available all of the reports that come with it. 

Desktop software and ODBC

Most desktop bookkeeping software allows access to the underlying transactional and lists tables using something called ODBC. If a client provides you with a desktop software backup file, it is most likely to be from Sage (50 or Instant) or QuickBooks. The good news is working with the Sage ODBC driver is easy. The bad news is QuickBooks does not come with its own driver and you are required to purchase a third party tool called “QODBC”. For details see  

Cloud software and APIs 

ODBC drivers are desktop technology and the equivalent in the cloud world is called an API. Most of the cloud bookkeeping services have publicly accessible APIs which third party software can connect to and interact with. So, if you want to work directly with a client’s cloud data, you need to collect it using software which can retrieve it via the source’s API. 

Desktop or cloud – does it make a difference? 

Being designed more recently, cloud bookkeeping software usually supports drill-down into report numbers, the ability to use multiple windows (tabs) simultaneously and compatibility with any device. Older, desktop software tends to either not have these features available or makes them harder to access. Add to this the fact that cloud software is likely to allow your client to provide you with a free login (no software for you to buy or install) and it probably always makes more sense that you work with the client’s cloud software, unless you have a great, purpose-designed tool that can work with the data directly. 

Analysing the data 

Accountants use their training and experience to understand what steps to take, and what to look for, when analysing a client’s accounts at year-end time. Unfortunately, this means that different individuals in a firm might perform this stage of the work slightly differently. It is therefore desirable to have a method to ensure, as far as is practical, consistent methods, workflow and results across the whole practice.

Software is fantastic at searching, sorting, grouping, subtotaling, filtering and comparing data. It can do so at speed, with 100% consistency. When analysing the client’s accounting records you should therefore use software specifically designed for this purpose, for accountants in practice. 

The key to efficient and consistent results is to ensure that you have a systemized way to ensure that everyone in the firm considers the same set of review areas and, within those areas, looks for the same list of review points. 

A minimum list of review areas might look like this: 

  • Fixed Assets
  • Debtors
  • Bank and Cash
  • Creditors
  • Completeness Checks
  • Consistency Checks
  • Tax and VAT Checks 

Your analysis software should report on exceptions and potential anomalies in each review area: using the same query criteria to ensure that the same review points will be surfaced consistently across all clients and all year-ends. The only variables here should be client-specific parameters to allow customisation for specific clients, of different shapes and sizes. 

It is also important to have a consistent method of tracking the status of individual review points at any point in time during the review and analysis process. A standard set of statuses might be: 

  • Outstanding
  • Resolved
  • Accepted
  • Queried

Making corrections 

Improving efficiency in year-end work is all about getting from the client’s trial balance to your own trial balance as quickly and painlessly as possible. Processing adjustments and arriving at your final trial balance for use in accounts production and tax computations is your next step. 

Here you have two choices: you can correct the client’s data directly in the source software (by posting changes into their Sage file for instance) or you can maintain your own extended trial balance (typically in an Excel workbook) and enter adjustment journals there. 

Correcting the client’s data directly 

The advantage of correcting the client’s data file is that you can let the bookkeeping software automatically recalculate all of the new balances for you. As you post journals and adjusting transactions, the software will update the trial balance (and sales and purchase ledgers) as you go. This saves you from having to do this manually in Excel and is less prone to error. When you have all of the changes entered, you can then use the new trial balance in the source software to produce your final accounts. 

There are significant downsides to this approach though – not least the fact that some desktop software can be difficult to correct: Sage data is definitely harder to correct than QuickBooks data, for instance. It is harder to keep an acceptable audit trail when editing the client’s records directly and it is also harder to ensure that all members of your firm process corrections consistently. 

Creating Adjustments in Excel

Because of the limitations of correcting the client’s data directly, most accountants prefer to record adjustments in an Excel workbook, manually maintaining an extended trial balance to reflect the effect of the changes made. 

The challenges here are to ensure that the extended trial balance is accurate and up to date at all points in time, taking into account all adjustments made to date. It also takes time to maintain the integrity of all cross-references from the figures in the trial balance to the adjustments, analyses and schedules that underpin them. There is also a need to have a system to track which member of the firm made which adjustments and when. 

Here, again, the use of purpose-designed software can provide all of the advantages and exclude all of the limitations of using Excel workbooks for year-end accounts work. 

How Checkmybooks can help 

Checkmybooks collects, analyses and reports on your clients' accounting records. Corrections can be made quickly and easily, with your extended trial balance constantly updated as you go.