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
Builder A runs five sub-contractors. They have been working on-and-off over the last nine months.
- Each month Mr Smith comes into my office with the cheque stubs.
- 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.
- The cheque numbers, the payments (Gross, Net and Deductions) get recorded into a monthly template
- 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).
HMRC CIS – Individual Reporting
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).
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.