Even in software you could run such things as that rubbish supermarket's receipts through the PDB by setting them up as a supplier. This provides useful data for spend at that supplier as well as the usual way of processing which just then gives useful data at cost level.
The missing figures part- this would be unusual if they are running all their clients through this process, in fact lazy, but I'm suspecting they don't and they put you on that task for experience. Do they also use software and other processes? Anyway, you state you have your level 4 AAT but you need to cast your mind back to your level 3 incomplete records.
Have you asked them?
Reckon the spreadsheets might be linked even though it might not be obvious. Any chance of posting a blank version of one?
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
Excel is fired up. (there is no accounting software)
Within the workbook, there are around 15 worksheets.
The main ones in use are SDB, PDB, Bank. The other sheets get their data from these three main tabs.
It has been driving me insane all weekend.
As there are no debtors or creditors reports, the only way I can see this working is like this -
Every single invoice is entered into the PDB (for purchases) so there is a total for purchases (net and gross vat) then check the bank statement against the PDB, and what isn't on the bank statement becomes a creditor, by virtue of a missing figure.
I would ask there, well I'll guess I will have to, only those with the knowledge are not the friendliest - They think if you've level 4 AAT, you should know this, but, well....
I've had it myself where accountants need an extended trial balance built in Excel from a similar start point.... Lol, the spreadsheets that they get back are never anything like the one's that they originally sent me! Thankfully though I have really good accountants who know that if I fix something for them in Excel it will work better for them in the long run.
Is it not a case here that anything that cannot be reconciled to the bank must therefore be the debtors / creditors / DLA.
Just wait until you get some that have a dozen or so bank accounts (I include PayPal and other similar payment engines as bank accounts) and you are also sorting out whats a movement between accounts rather than externally influenced.
Worst part I find with this is where they have mass payments / receipts but completely omit from any paperwork which sales / purchases / credit notes makes up those transactions! Drives me scatty how slap dash some clients (and some accountants / bookkeepers) can be.
Basically, the bank has to be the driver and if it's not banked then the perception is that it must be outstanding (in reality directors not banking everything and paying from their own pockets spoils that theory).
It is owever much faster to process that way than chasing down absolutely everything in a mini audit and it will give a result that should not be materially incorrect so one has to factor in that practices are businesses and the faster the throughput the more profit that they can make.... Not an approach that I personally would adopt (ex banker.... anything that is not completely right is wrong) but I understand those who do adopt that approach as there are commercial considerations to factor in.
Approach to take.
Ensure that the bank, sales and Purchases all relate to underlying documentation.
Use Pivot tables to find your totals to carry to the trial balance
reconcile Sales and Purchases to the bank. Whats left is what will take the time (the old 80/20 rule of 80% of the work takes 20% of the time and 20% of the work takes 80% of the time)
Use multiple spreadsheets across multiple monitors (worksheets in a single spreadsheet are good for what you return but not for working on).
HTH and good luck,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
The other sheets are made up of a fixed asset register/schedule, HP schedule, cash in, cash out. Bank reconciliations. SDB. VAT. Wages and such like.
After the first hurdle of the PDB, everything else 'sort of' makes sense.
What threw me was that I was always under the impression that the PDB was solely for credit purchases.
Yet, thinking about it and understanding what you guys are saying does put things into perspective.
I don't mind saying that it had my mind muddled, I've tried every combination I can think of this weekend to get it to made sense. The PDB combined with the bank statement does make the most sense.
