No matter how well you believe you have entered your data, or even if the data entry has in the main been automated, it is imperative that some kind of month end routine is carried out in order to ensure the integrity of the data entered.

The nature of the month end routine will be specific to each company.  The timing will depend on many factors as well.  However, for the typical company working towards calendar month processing the person responsible should set a target of completing such a routine within three weeks of the month end.  For those companies requiring management accounts, the date may well be sooner - two weeks or on occasion just one week.

We will endeavour to provide a checklist of the points to review.  Due to the company specific nature of all such month end routines, the list will not be complete and is not intended to be so. It is presented as a guide. We will then demonstrate how the list can be used with Xero to ensure better data accuracy and as a result better financial information.   This is not a substitute for a trained accountant.  However, we hope it is a help to those clients maintaining their own accounting records. It will also hopefully highlight some of the additional work which accountants undertake and help to explain why there is more work required than maybe many clients realise.

So let's start with an example checklist:-

1. Obtain a trial balance from the software at the end of the month;
2. Check the balance on the PAYE account;
3. Check any balance on the net pay control account;
4. Check any balance owing on the pension contribution creditor account;
5. Check the balance on the VAT account;
6. Check the balance shown as the balance on the bank statement;
7. If the end of the VAT quarter, check turnover in the accounts with the VAT return;
8. Review aged debtor report;
9. Review aged creditor report;
10. If any entries have been made, reprint the trial balance;
11. Set transaction lock dates to prevent changes.

Let's now look at these items individually.

1 - Trial Balance

To obtain a trial balance click on 'Accounting' in the menu at the top of the screen.  Then in the Reports section there should be an option of 'Trial Balance'. as shown in the image below.

 

Figure 1-1

If the option for trial balance is not there, then select the 'Reports' option (the second option on the menu.  In reports will be a section 'Accounting', on the left.

There are two trial balance reports.  One labelled 'New'. Choose the option NOT labelled 'New'.

Notice the star next to the menu option.  It will be clear.  Clicking the star will turn it blue and ensure it appears on the main menu, as shown above, in the future.

For our example, we are taking a trial balance at 30 April 2020.  It should appear as below:-

Figure 1-2

2 - PAYE Reconciliation

The point of checking the balance is to ensure the amount shown is the amount actually due for PAYE.  By doing this you are ensuring that the payroll was entered correctly and that any payments of PAYE were entered correctly.  An error with either will result in the amount shown as payable being incorrect.

Provided you are due to pay PAYE to HMRC then the balance should be in the credit column - it represents a creditor - someone that one has to pay money to.

Below is a closer view of the liabilities section of the trial balance i the last picture.

Figure 2-1

The column to look at is the one on the far right hughlighted in green.  The reason is that the column represents the year to date figure.  The one in the second column of figures highlighted in blue is the movement for the month only.  Just for completeness the column to the left of each represents debit columns and would mean that the business was owed money by someone.

The fact that the PAYE balance shows £1,300.00 in the credit column indicates that PAYE is payable - that may not always be the case.  You should know in advance whether that is the case or not from the information provided by the payroll software.

Assuming you have been provided with a report from your payroll software stating the amount due to HMRC for the month, this figure should agree with that report.  Some businesses pay their PAYE quarterley.  In that case one may beed to add some of these reports together.

If you wish to check how the blance is made up, right click on the figure of £1,300.00 highlighted in green, selecting 'open in new tab'.  Below is the details from our example.

 

Figure 2-2

You can see from the details that the only item is the payroll liability from April 2020.  That definitely looks right since the amount is payable 22nd of the following month.  The only matter to check is that it agrees with our payroll sofware.

 

3 - Net Pay Control Reconciliation

Now we need to do exactly the same with the net pay control account.  This account is the account to which the wages from the bank are posted and to which the net pay due to staff should be posted from the payroll software.  The balance on the account for monthly paid staff is usually £Nil at the end of the month, the reason being that the staff are usually paid by the end of the month for the payroll of that month.

For the purposes of our demonstration company, there is a balance as shown in Figure 1-1 of £4,400.00.

As discussed in section 2 on PAYE below are the details from the account.

 

Figure 3-1

It can be seen that two people are due money.  Again, as the person reconciling the information you need to know whether a payment has been made.  If, as is sometimes the case, the payment is made at the start of the following month then this is as expected.  On the other hand, if you know the salaries have been paid, one would need to find the account to which the payment has been posted and correct accordingly.

4 - Pension Creditor

The pension creditor procedure is exactly the same as that for the PAYE account above.  Check the amount owing to the pension liability according to the payroll software.

5 - VAT Reconciliation

This is extremely important.

You must ensure that the VAT liability agrees to the VAT return.  There are different schemes.  In most cases, if the accrual scheme is being used, it is unlikely there will be any differences.  However, with the cash accounting scheme the errors are more likely.

Many clients will carry out this reconciliation every three months to coincide with the VAT return rather than perform the task monthly.

The first exercise is to produce the VAT return.  Referring back to Figure 1-1 you can see that this is produced from the 'Accounting'menu and selecting 'VAT Return'.  The return from the demo company is shown below.

Figure 5-1

The demo company return is a monthly return.

 Note the amount owing on the return amounts to £665.65.  However, the amount in the trial balance is £1,444.62.  Under the accruals scheme the two should normally match.  You can see from this return (shown under VAT Registration details on the top left of the picture) that this company is using the cash accounting scheme.  In other words this company is accounting for VAT based on monies received and paid.  The trial balance in Figure 1-1 is based on transactions dated in the period and before where they have been entered since the last VAT return is produced.

Where there is a difference, it is imperative to produce the 'VAT Reconciliation Report'.

The report can be found under 'Accounting-->Reports' as shown in the list below.

Note the report highlighted in Yellow.  It does not have a blue star next to it.  We suggest clicking on the star to change it to blue which means it will be listed in the Accounting menu as a favourite.

Figure 5-2

Below is the report from the Demo Company

 

Figure 5-3

You can see quite clearly that the VAT balance at the bottom agrees to the trial balance in Figure 1-1.

Since this is the Demo company there is unfiled VAT returns from earlier periods.  These should not appear on your reconciliation.

It can be seen that the figures on the VAT return are shown on the report.  Since the VAT is being calculated on the cash basis, the system is showing VAT due on sales invoices yet to be paid and to be reclaimed on purchase invoices yet to be settled.

It is  best practice to check the figures for VAT on unpaid sales and purchase invoices.  I will cover this is the sections relating the accounts receivable and accounts payable reports.

6 - Bank Reconciliation

This should be the easiest balance to check and ensure is correct.  If you are using the VAT cash accounting scheme, it is imperaitve this is reconciled any error is likely to have an effect on the VAT returns being submitted.

Ensure that you have your month end bank statement to hand and make a note of the balance at that date.

If the way that you deal with bank data entry is to only import or have a bank feed and all entries from the bank statement are posted from the reconciliation routine, then the balance according to your statement should agree precisely with the trial balance amount at the same date.  Irrespective, the next step is required where the balance does not agree or where entries are made to the system which are then reconciled from the bank reconciliation routine.

Figure 6-1

Figure 6-1 shows the dashboard.  The main bank account is usually listed.  However, for other accounts you would need to go to the bank accounts where all accounts will be listed.  Irrespectove, click the three vertical dots to the right of the bank account to see the menu.  Select 'Reconciliation Report' as shown above highlighted in yellow.

Figure 6-2

Refer back to Figure 1-1, the trial balance, and you will note the balance is £10,105.54 as shown at the start of this report.  The two figures should always be the same.

Next check that your bank statement balance is the same as the bottom amount shown as £17,071.99.  IF this figure differs, it is likely you would need to contact us to investigate the reason.  An experienced bookkeeper should be able to solve the issue. It is imperative that the amount has been reconciled at some point previously.  The longer the difference is left the more difficult it will be to find the reason.

Assuming both of the balance discussed so far agree, the next items to consider are the amounts in between.

The first section shows outstanding payments.  These are payments entered in Xero but which have not yet appeared on the bank statement.  These would usually be cheques or BACS payments.  Both of these will clear later than the date entered.  Online payments would normally appear on the statement with the same date unless entered after the bank cut-off time at the end of the working day.  In our example, the items are all in April 2020 and therefore unless you were aware of something it is likely that these entries are satisfactory and will clear in May.

The same applies to the outstanding receipt.  I would query this since 6 days appears a long time for a receipt to appear on the bank.  However, it may be a cheque and due to the small value it was not paid in at the time.

The main issue on this reconciliation are the other items.  These are items appearing in the bank feed or which have been imported and have yet to be entered into the system.  These must be cleared before completing the month end procedure.

7 - VAT Turnover Check

As referred to earlier, this is more difficult when cash accounting for VAT is involved.

Referring back to our VAT return at Figure 5-1 the turnover in box 8 of the return amounted to £4,501.35.

This should be checked to the profit and loss account.  This report can be accessed from the 'Accounting Menu'.

Figure 7-1

At the top of the report set the dates to the period covered by the return - the month of April 2020 for our example.  Since we require this report based upon cash, click the 'Report Settings' link on the right.

Figure 7-2

Change the accounting basis to cash as shown above and click 'Update'.

Figure 7-3

The turnover figure above agrees exactly with the return.  Again, if this does not agree contact us or ask your bookkeeper to amend as necessary.

8 - Aged Debtors

This is the report of customers that owe money to you at any point in time.

In Xero it is referred to as 'Aged Receivables Summary' and is located under the 'Accounting' menu.  By default this report has already been set as a favourite.

After loading the report, change the month to the desired month end being processed and update the report bu clicking the blue button labelled 'Update'.

 

Figure 8-1

The total of £11,417.63 agrees to the trial balance in Figure 1-2.

Now look at the ageing.  The oldest item is 2 months old and only relates to one customer.  With a small set of debtors such as this you should know just by review whether the report is accurate or not.

If there were old items, they should be checked to ensure that the money is still outstanding in the first instance and then a decision made as to whether the amount should be regarded as a bad debt.  Adjustments then need to be made.  There are VAT implications when adjusting for bad debts with time limits as to when a right off can occur.  That is not relevant to this topic.

9 - Aged Creditors

This is a mirror image of section 8 but refers to suppliers or businesses to which money is owed.

In Xero the report is referred to as 'Aged Payables Summary' and is found in the 'Accounting'menu again.

The report is produced in exactly the same way as with 'Aged Receivable'.

Figure 9-1

Again the total figure of £7,474.21 agrees to the trial balance in figure 2-1.

Note the report settings option at the top right of the report.  As a default, the report is set to age based on due date.  That would not agree with the trial balance which accounts for everything by invoice date.  So make sure the report is set to age by invoice date.

Again we are just reviewing to seee if there are any old outstanding amounts which would indicate error.  We may also be checking for negative items which indicate payment made in excess of invoices received.  That could indicate overpayments or missing invoices which would affect the profit report.

As always, make any necessary adjustments.

10 - Final Review

If amendments were made, reprint all the reports so that they all agree.  It is dangerous just to print as you go along in case amendments affect earlier reports.

11 - Set Lock Dates

Locking transaction dates prevents accidental entry of transactions changing figures already reviewed.  The benefit with Xero is that the dates can be locked but can also be unlocked provided you have the right administrative privileges.

The lock dates are set by going in the menus to 'Accounting-->Advanced'.  Then select 'Financial Settings'.

Figure 11-1

Note there are two lock dates.

Setting the bottom date stops all users making or changing any entries on or before the relevant date.

The top item stops all users apart from those with 'Advisor' permissions from doing the same.

Our suggestion is to set both dates to the same which then forces any permission with relevant permissions to have to make a conscious decision to make changes and hopefully prompt them to consider the effects of their actions.