Hi Jasper

There is an option in Sage to use cash accounting. In Settings/company preferences/VAT/tick box for cash accounting.

Regards

Gordon

I/we haven't used SAGE yet for the actual returns.

What I am trying to work out is the remaining balance left in the control accounts ie)the VAT due figure on the Balance Sheet. The figure has had a journal posted into it at the year end by the Accountants as they felt it was incorrect. The balance to me looks too high and I'm trying to find a method to prove what the control accounts balances should be.

An illustration if it proves helpful:

VAT Qtr end April 13.

Payment made for quarter - £20K (cash accounting)

SAGE (accrual accounting)
DR balance on BS as at April 13 - £10K
CR balance on BS as at April 13 - £50K
Net Credit on BS as at April 13 - £40K

The difference between the payment for the quarter and the net Credit on the BS is therefore £20K.

From my understanding is that this difference should be the unpaid trade debtors less the unpaid trade creditors. So for example debtors is £30K and Creditors is £20K. Net credit should therefore be £10K.

Does anyone disagree with my methdology? What can I do on SAGE to develop reports showing how to solve this problem?

Rgds

Jasper

"Somewhere along the lines it is being made unnecessarily complicated... "

Doubly so if the VAT is being done on a cash basis, and Sage isn't set up that way - which is what it sounds like.

If I were to hazard a guess, what Jasper is probably doing is running reports to show all the sales receipts, bank receipts, purchase payments, bank payments (and cash/card etc) for each quarter and producing the VAT return from those - which sounds simple enough, because it's pretty much what we did before we had computers, and it probably is simple enough the first time, possibly even the first 'n' times, but manual records and those from an accounts package are sufficiently different that as time progresses it has the potential to become more and more difficult and time consuming.

Ah, okay - not what I thought you were doing, then. In fact, you're doing what we did before we had accounts packages on computers, but using the spreadsheets instead of paper. Fair enough on that point, but if you're okay doing that then I don't see the point of using Sage at all. Why can't you produce the management accounts from the same information?

To me, it just seems counter productive to input all the information on the spreadsheets, then input it all again onto Sage for the management accounts - even if it does take less than a day. That just sounds like a day of doing work you've already done!

But now that we've clarified all of that, onto your question, which is now a lot more meaningful:

"What I'm trying to do is reconcile the difference between the VAT balance on SAGE based on accrual accounting and the VAT balance due from cash accounting."

(the latter being from the spreadsheets)

So you've entered all of the transactions in the quarter - sales invoices, purchase invoices, payments, receipts - onto Sage and told it to run a VAT return.

And, unsurprisingly, it's come up with a different figure to your spreadsheets, since the VAT is on a cash basis and Sage isn't set up that way: There will be invoices on Sage, with the VAT included in the return, that haven't yet been paid, and which therefore shouldn't be included.

"I would have thought that would be the o/s ledger balances given than a debtor balance will have been included in accrual VAT accounting on SAGE and not the cash accounting."

I think that sounds plausible if this was the first time, but as it isn't then it's not just the outstanding ledger balances at the end of the quarter you are looking at - but also the figures for the previous quarter will be relevant: Just as there will be invoices on Sage this time, with the VAT included on the return, and which therefore shouldn't be included, but the same will have been true last time.

That means that part of your difference is that your cash-based VAT calculation includes payments/receipts against invoices that were included by Sage in the previous quarter; the previous difference needs to be a part of your calculation.

Consider this incredibly simple set of figures, with just two sales invoices and no purchase invoices in the first ever VAT quarter for Fictional Made-up Co Ltd:

Invoice 01: £100 + VAT of £20

Invoice 02: £150 + VAT of £30

Invoice 01 is paid in the quarter - and since the company's VAT is on a cash basis, that means the VAT to be paid is £20.

Putting those figures (and the receipt) on Sage, which isn't set up for a cash basis, results in it claiming the VAT payable is £50 - a difference of £30.

The sales ledger, though, has a balance of £180 - the second invoice - and, yup, 1/6 of that, the VAT on invoice 2, is the difference, because Sage has included it on the return.

Now, look at what happens in quarter 2. Two more invoices are raised (and again there are no purchase invoices. I like this business. Why isn't my business like this?):

Invoice 03: £200 + VAT of £40

Invoice 04: £250 + VAT of £50

Invoices 02 and 03 are paid in this quarter, leaving invoice 04 outstanding.

Clearly the VAT due this time is £70 - the VAT on invoices 02 and 03. However, putting this quarter's transactions in Sage result in it telling us the VAT due is £90 - the VAT on invoices 03 and 04; a difference of £20.

The sales ledger shows a balance of £300; the total of invoice 04, and 1/6 of that (the VAT on that invoice) is £50 - which is out from the difference by £30.

For the reason why, I refer you back to the first quarter.

HTH

 

 

 

 

 

 

"For the purposes of accounts, given that we use cash accounting is it possible to have cash accounting amount payable as the balance sheet amount payable? This actually seems more appropriate."

I assume, since you are using Sage for the management accounts, you mean the balance sheet as produced by Sage.

In which case, "sort of" - if Sage was set up for the VAT on a cash basis.

Sage tots up the outputs VAT on an ongoing basis in 2200 by default, and the inputs VAT in 2201. These are based on sales and purchase invoices, and bank/cash receipts and payments - but not receipts/payments against the sales and purchase ledgers.

Come the day when you run the VAT return, part of the process is to journal the outputs and inputs shown on the VAT return from those two codes to 2202. (This used to be a manual thing, which people often didn't do, then it became a wizard that people often don't do, and now it's a VAT task, which I expect people often don't do).

2202 therefore shows the amount payable - until it's paid; when the payment is made, it also goes into that code.

The default balance sheet layout nets off all three codes - so it's not just the amount payable, but you can change the layout so that 2202 is shown separately.

You'll still have the balances in 2200 and 2201 on the balance sheet - that's pretty much fundamental. The balances in them (at the date of the return) should in effect be the VAT on outstanding sales and purchase invoices respectively.

That's how things would be if you were using Sage properly.

Since you aren't, even though Sage is set up incorrectly for the VAT, you could in principle do the same thing:

When you produce a VAT return using your method, on Sage manually journal the output and input VAT on the return to 2202 (outputs: debit 2200, credit 2202. Inputs: credit 2201, debit 2202). The result of doing this would leave balances in 2200 and 2201 which, as above, should equate to the VAT on the trade debtors and creditors - and 2202 is the amount actually payable for the quarter (which, again, you could show on a separate line on the balance sheet if you wish).

However, for this to work, it's not just a case of making the journals for this quarter: You'll have to work backwards and make the journals for previous quarters - looking out for anything else that has been posted into that code. (For example, as part of the year end/opening balances, has anyone posted a single journal that deals with the previous year's VAT quarters.)

Not quite: The total VAT shown on the balance sheet by Sage will be the whole, accrual-based, amount, because that's how the VAT is isolated to start with. What I'm saying is that it's possible to split that, and show the amount due for the quarter, calculated on a cash basis, and show that separately from the rest.

Probably best to explain this by example, so...

For the sake of simplicity, we'll ignore the possibility of previous quarters; in this example, this is the first quarter.

Say you've made sales in the quarter of £10,000 plus VAT. £5,000 plus VAT of that has been paid.

You've received invoices from suppliers totalling £6,000 plus VAT, and paid £4,000 plus VAT of that.

On Sage, regardless of whether it is set for cash or accrual based VAT, the VAT codes will show:

2200 CR £2,000 (the VAT on all the sales)
2201 DR £1,200 (the VAT on all the purchases)

Your cash-based VAT return will show:
Outputs: £1,000 (the VAT on the paid sales invoices)
Inputs: £800 (the VAT on the paid purchase invoices)
Payable: £200

You post journals on Sage as follows:

Debit 2200 and Credit 2202 with £1,000
Credit 2201 and Debit 2202 with £800

The balances on Sage are now:

2200 CR £1,000 (the VAT on the outstanding sales invoices)
2201 DR £400 (the VAT on the outstanding purchase invoices)
2202 CR £200 (the VAT to be paid to HMRC this quarter)

By default, Sage will group those together on the balance sheet - so it'll tell you there's a VAT liability of £800 (£1,000 minus £400 plus £200).

However, you can edit the chart of accounts, so that 2202 is shown on a separate line. Perhaps have 2200 and 2201 grouped together, on a line that reads "Accrued VAT liability", and 2202 on the next line, reading "Cash VAT liability"

So for the above example figures, the balance sheet would show:

Accrued VAT liability £600
Cash VAT liability £200

Does that make sense?

As for that final difference you speak of, if you've gone back and dealt with each quarter's VAT journals on Sage (checking whether they've been done in different ways, such as with year-end or opening balance stuff), as I mentioned in a previous post, there really shouldn't be a difference. With nil balances on the sales and purchase ledgers, there should be no remaining accrued VAT.

If there is, something else has been done incorrectly that has affected the VAT figures on Sage - which could be just about anything; things incorrectly posted to the VAT accounts, VAT entered on transactions that don't have VAT, the payments for the VAT being posted somewhere other than 2202, transactions entered incorrectly... anything.

And given the way you've described things being done - manual records in a spreadsheet, then input into Sage for the purposes of management accounts - I'm inclined to say "I'm not surprised it's gone awry." You said you've inherited the job this way... in which case, I don't envy you at all.

Actually, no, scrap that: The truth is I'd probably enjoy getting stuck into a job like that and putting it right! (Well, assuming they'd be willing to pay me to do so - sometimes companies get into a mess and they'd rather struggle on that pay someone like me to sort it out. Otherwise, as I said, I don't envy you at all.)