Microsoft Excel – HMRC CIS how to record payments and aid reporting

Microsoft Excel – HMRC CIS how to record payments and aid reporting

I have a number of clients that need me to speed up their administrative tasks.  Now this may seem rather dull to some consultants, who much prefer writing use cases and looking into data, however I find that showing people how things can be done easier (Business Process Improvement) is fantastically rewarding.

In the UK’s construction industry scheme we have a method of paying subcontractors and short term employees.  The way it works is that you have a sub-contractor (who has already registered himself with HMRC) and we take their UTR (unique tax reference) their national insurance and then deduct 20% or 30% from their pay.  This deduction gets sent to HMRC each month.  So in fact main contracts managers become little tax collectors.

Now a lot of builders are exactly that they are builders.  They are incredibly skilled in what they do but, with the volumes of paperwork  that they are lumbered with… why not try and make things easier.

HMRC CIS Recording Sub-Contractor Payment Recording

Perfect example:

Builder A runs five sub-contractors.  They have been working on-and-off over the last nine months.

  1. Each month Mr Smith comes into my office with the cheque stubs.
  2. Mr Smith enjoys some of the premium coffee I have (or had should I say) in my kitchen whilst I go through the cheque stubs relating to paying his sub-contractors.
  3. The cheque numbers, the payments (Gross, Net and Deductions) get recorded into a monthly template
  4. The value columns are totalled so that we know how much to pay the HMRC.

Now this may seem simple to those experienced Microsoft Office users.  But put yourself in their shoes they can build a house, change taps, install central heating systems and fix those awkward patio problems – so its not really important that they know how to put in an excel function.

So lets look at how to go about this.  Builder A is only running five contractors so even if they are being paid weekly its only going to be 20 rows in Excel (20 payments)  If you have more contractors then make sure you insert the rows about the totals.

Ok thats what it looks like… but how do you get there.  This is how.  Ok see the box on the right (black outlined).

This is what it looks like when you have a cell selected in an Excel spreadsheet.  Now the following image shows where you can either enter a value or a function.
The Cell Reference is helpful.  Alphabetical goes left to right and rows are numbered down.
Right so now I will show you the functions:
Now these functions are specific to Builder A who has only made 20 payments.
If you need to increase the number of rows (because you’ve made more payments then you need to make sure that you change the functions so that all values are counted.
If you click in the function box at the top (shown in previous slide) you will see the boxes surrounded in a coloured box.  You could just extend the boxes to encompass the new cells.
(I’ll put together a video on this)
The cell at the bottom references D25 which gives us the amount that is due to the HMRC.
COLUMN C – Contains the total amount (including tax).  This is the number of days multiplied by the daily rate e.g. 5 days at £120 per day = £600
COLUMN D – is calculation which works out how much tax should be deducted (remembering that this should be either 20% or 30% depending whether your sub-contractor has been verified).
COLUMN E – is a simply subtraction calculation to work out the amount you should pay your sub-contractor for that day.
Ok this is a great report for month by month headline figures (in English if you want to be able to report to your accountant the amount you paid to HMRC).  However, what about being able to report BY INDIVIDUAL what you paid them and what taxes you deducted from them.
Read on..

HMRC CIS – Individual Reporting

Now look at this image on the right.  It shows the total payments by individual.

Remember its Builder A’s responsibility to give the sub-contractors payment reports.
This information is crucial because not only does it give you an individual view but also allows you to CHECK that the individuals are paying the 20% or 30% tax required.
(n.b. 30% tax is charged on unverified sub-contractors)
See the image below for the functions (and the tax checker)

Ok now this can look kinda scary but its literally the same thing just repeated a number of times.

 

A SUMIF function works in three parts (seperated by a comma).

 

=SUMIF(PART_ONE,PART_TWO,PART_THREE)

 

PART_ONE – is the value that we are looking up in a range.  In this example is the list of names in the payment table.

PART_TWO – is the value that we want to look up from PART_ONE.

PART_THREE – is the value that we want to return, in this case the total sum of payments made

 

 

In all fairness this is likely to be one of the more complicated queries that a small business owner doing this own CIS payments is likely to need.  So don’t worry if it doesn’t work first time.

 

 

Checking the Tax

At the end of the SUMIF functions (past column L) there is a smaller equation.  Now all this is doing is a simple division exercise in order to ensure that the correct tax is being paid.  The result should be 0.2 or 0.3 meaning 20% or 30%.  Anything else means that a mistake has being made.
It takes the total amount you’ve paid your guys against the amount of tax.

Conclusion

Microsoft Excel is an incredibly powerful tool and its great for reporting.  You shouldn’t be worried about trying out new functions.  If you’re worried that you may make a mistake then get in touch via the contact us form and we will try and help you.
It is important to remember that you must make your payments to HMRC by the 19th of each month and they ALSO must have all the paperwork (remember that you can file your monthly CIS returns online at www.hmrc.gov.uk).

Related Links

6 thoughts on “Microsoft Excel – HMRC CIS how to record payments and aid reporting”

  1. Hello

    I would like a spread sheet made up as detailed above for my CIS, its a new firm we have and it would be good to get this set up

    Can you help? happy to pay

    Regards

  2. Hi, would you be able to set me up a spreadsheet to do CIS payments and reporting as per your website but maybe one step further and include an option to generate the monthly returns too. I already have an excel spreadsheet for the statements which I can email to you. Let me know how soon you can do this and how much please? Many thanks, Janet.

Leave a Reply

Your email address will not be published. Required fields are marked *