cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Some advice re Undeposited Funds & Bank Feeds.

 Hi

 

I could do with some advice relating to Undeposited Funds & Bank Feeds.

 

A brief summary of the issue is:

 

We don’t use the Bank Deposit feature in Quickbooks. We mark invoices as paid (we receive payments into Undeposited Funds). The accountants then take the bank feed and manually apply incoming payments to an account they have created called Undeposited Bank Funds Contra’ (using deposit, from the 3 options of deposit, transfer or sales receipt)

 

I’m advised that it works in terms of pure accounting, but my Quickbooks Sales Reports have now gone out. The ‘Sales By Location Report’ is almost double what it should be. As well as including transaction type = invoice’ (the original invoice) it now includes transaction type = ‘deposit’ (which comes from being bank feed being posted/deposited to the Undeposited Bank Funds Contra account).

 

More detail below, but the bottom line is if the sales reports didn’t include these extra ‘transaction type = deposit’ entries, all would be fine (Screenshot of report below, where 'Location Not Specified' is all of these deposit entries).

 

Background:

 

Our franchisees use Quickbooks 100% via the IOS app (only the accountants use the web app).

 

After each job is complete (5-7 jobs per day, often for identical amounts), an invoice is issued to the customer (90% of the time the invoice is paid immediately by card, cash or cheque). As soon as payment is taken (or received later by BACS) the invoice is marked paid (by receiving payment into Undeposited Funds).

 

As far as the accounts are concerned, if the franchisee has marked an invoice paid, then the funds have been received (and are sitting in Undeposited Funds).

 

The franchisees don’t use then use the Bank Deposits feature (so everything stays sat in Undeposited Funds rather than being moved into the Bank Account) partly because you can’t process Bank Deposits in the IOS app, but mainly because of the effort involved in splitting out ‘card transactions for the day’ from ‘cash/cheque’ transactions and the errors they would make (by incorrectly allocating payment method).


When the accountants look at Quickbooks, they see something like:

 

Invoices Issued                  £100,000

Undeposited Funds          £95,000

Outstanding Invoices       £5,000

 

Bank Feed:

 

Details of all payments received also come into Quickbooks via the bank feed.

 

Due to the time & cost involved, we decided not to try and match the incoming payments from the bank feed to the records in Undeposited Funds (with the only benefit being to check that a franchisee hadn’t marked an invoice paid by mistake).

 

This is what the accountants are doing instead:

 

Undeposited Funds Bank Contra Account:

 

In the Chart of Account, the accountants have created an ‘Undeposited Funds Bank Contra Account’ as follows:

 

Account Type: Current Assets
*Detail Type: Other Current Assets
Account Name: Undeposited Funds Bank Contra

Default Vat Code: No Vat

 

In the Banking tab, the accountants take the incoming payments from the bank feed, select a record, click the Add button, and then post it as follows:

 

  1. They set transaction type to Deposit (from the 3 options of Deposit, Sales Receipt, Transfer)
  2. They set ‘Account’ to ‘Undeposited Funds Bank Contra’.

 

My Issue:

 

From the accountant’s perspective, everything is working fine, but our Sales Reports now include these ‘deposits to the Undeposited Funds Bank Contra account’ as well as the original invoice (so sales are almost double what they should be).

 

When looking at the report, it first lists ‘transaction type = Invoice’ and then lists ‘transaction type = deposit’. In the screenshot below, it's all the transactions in 'Not Specified' that we need to get rid of!

 

Quickbooks SCREENSHOT.jpg

 

Any clever ideas?

 

The accountants explained:

 

We did try to change the transactions to transfers, but from memory this only allowed a transfer from one bank account to another, and if sales receipt is selected Quickbooks wants to add this is as a new sale.

 

The accountants spoke with Quickbooks Support, but didn’t’ get very far. They explained:

 

Quickbooks cannot understand why we cannot match every transaction from the bank to the suppliers.  They did confirm what we already know, that we cannot add the bank feed transactions to Undeposited funds but cannot grasp why we have had to set up the contra account.  If we could switch off the bank receipts feed to your sales report, then the system as we have it will work.

  

Thanks!

 

James

 

Solved
Best answer 08-30-2018

Accepted Solutions
Highlighted
QuickBooks Team

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi James,

 

I have recreated the scenario in my books and i've managed to fix it on mine. What i want you to do, is where it currently says "Amounts are "Inclusive/Exclusive of Tax" and change that to "NO VAT" please? Do this for all the deposits you don't want including, the system is picking up the amount as a NO VAT "Sales" transaction, whereas if you do as suggested, it sees it as just a NO VAT Transaction. It's the (Sales) bit in the VAT Box that's causing this.

 

You cannot force the system to NO VAT the whole transaction from the Banking Screen, it will always apply that based on (sales) or (Purchases) when choosing a VAT Code.

 

The way around this, is when there's a deposit needing creating from the bank screen, instead of clicking Add, take note of the Date & Amount, and click on the Plus Icon, and then Bank Deposit; create the bank deposit Manually, filling in the information and NO VATing the whole transaction, not just the line you have data in. After you do this, you'll return to the banking screen & be able to MATCH the transaction to the deposit you've just created.

 

Let me know if this helps at all.

 

_______________________________________________________________

 

- Below, i've got a couple of questions about your use of Undeposited Funds & QuickBooks

 

How easy would it be to use the system if the person doing the deposits only had to select the already created payments on QuickBooks? It all depends on how easy it is to know which deposit retains to which payments.

 

Currently, you're not depositing the funds from undeposited funds into the bank account using the methodology that the system gives you. I understand what the accountant is trying to achieve, but it's not necessary. The system has a feature where you can select 5 x £100 payments, and it'll create a £500 deposit for you, which you'd be able to link in with the banking screen.

 

View solution in original post

11 REPLIES 11
QuickBooks Team

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi JamesRead,

 

Thank for your post, it's in detail and very well explained. 5 Stars & Kudos from me!

 

I'm Running out of time tonight, but i will be coming back to this post again tomorrow, Can you click on one of the deposits that's included and you don't want to be, and then onto Transaction Journal under the bottom black bar button "More"?

 

I'm wanting to understand a bit more about what might cause a deposited added from the banking screen to recorded as income? Can i see a screenshot of one of the deposits and the transaction journal behind the scenes?

 

Thanks,

Anonymous
Not applicable

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi James

 

Thanks for the post, much appreciated.

 

Here are the screenshots. I started with with Sales By Location report and drilled down.

I picked the first entry/record for £996 and then went into the journal for that record.

 

Thanks for any advice you might have!

James

 

 

Quickbooks SCREENSHOT.jpg Screenshot Two.jpg Screenshot 3.jpg screenshot 4.jpg

Highlighted
QuickBooks Team

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi James,

 

I have recreated the scenario in my books and i've managed to fix it on mine. What i want you to do, is where it currently says "Amounts are "Inclusive/Exclusive of Tax" and change that to "NO VAT" please? Do this for all the deposits you don't want including, the system is picking up the amount as a NO VAT "Sales" transaction, whereas if you do as suggested, it sees it as just a NO VAT Transaction. It's the (Sales) bit in the VAT Box that's causing this.

 

You cannot force the system to NO VAT the whole transaction from the Banking Screen, it will always apply that based on (sales) or (Purchases) when choosing a VAT Code.

 

The way around this, is when there's a deposit needing creating from the bank screen, instead of clicking Add, take note of the Date & Amount, and click on the Plus Icon, and then Bank Deposit; create the bank deposit Manually, filling in the information and NO VATing the whole transaction, not just the line you have data in. After you do this, you'll return to the banking screen & be able to MATCH the transaction to the deposit you've just created.

 

Let me know if this helps at all.

 

_______________________________________________________________

 

- Below, i've got a couple of questions about your use of Undeposited Funds & QuickBooks

 

How easy would it be to use the system if the person doing the deposits only had to select the already created payments on QuickBooks? It all depends on how easy it is to know which deposit retains to which payments.

 

Currently, you're not depositing the funds from undeposited funds into the bank account using the methodology that the system gives you. I understand what the accountant is trying to achieve, but it's not necessary. The system has a feature where you can select 5 x £100 payments, and it'll create a £500 deposit for you, which you'd be able to link in with the banking screen.

 

View solution in original post

Anonymous
Not applicable

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi James

 

BIG thanks to you.

 

I've just tested this on 1 month's data and as you say, as soon I change the 'bank deposit transaction' to be set as a 'No Vat' transaction it (correctly) disappears from my sales reports.

 

There was also one Journal Entry that incorrectly appeared on the sales report. The first two lines on the screenshot below were how it was originally entered. I then added two new lines, but leaving the VAT field empty. I deleted the first two lines and saved, and this has also correctly disappeared from the sales reports.

 Screenshot.jpg

I will double check our accountants agree, but it all looks good to me, so thanks again.

 

 

With regards to your two questions:
Question 1

 

When I was evaluating Quickbooks, I checked out the Bank Deposit feature via the web app. We would have run it as follows:

 

1. Create invoice.


2. Receive payment into Undeposited Funds - selecting whether payment was made by card, cheque or cash

 

3. At the end of each day, go into Bank Deposit, Select all the payments made by Card, and push them into the bank account (so they match the single payment that will come in from  Barclaycard)

 

4. Every two or three days, go to the actual bank, select all the payments that were cash or cheque, check that the actual cash/cheques they have in their wallet total the same amount, pay them into the bank and click save on Quickbooks to move them from Undeposited Funds to the Bank Account

 

5. Then, as they do now, check their bank statement for BACS transactions (which aren't always perfectly phrased, ie customer may not include our invoice number, or the name of the payee may be slightly different, but the franchisee will know who the payment is from, even though lots of payments are for very similar/identical amounts), mark the invoice paid in Quickbooks (I'm thinking to initially to undeposited funds, as if they start switching between Undeposited Funds and the bank account, things could end up in the wrong place).

 

Yes, they could try to 'match BACS payments' from within Quickbooks, but it's hard to make a mistake if they go to their actual bank account, looks at the payments that have come in for the day, go into Quickbooks, find the individual invoices and mark them paid. The match feature would be great if the invoice totals were different, but when they're as similar as ours, it feels like they'd end up incorrectly matching payments.

 

6. Then we could get a book keeper to reconcile the bank feed to what Quickbooks shows. In theory the daily card payments from Barclaycard should match, but in reality, they will occasionally incorrectly mark a payment down as card, when it was paid another way, so the book keeper will have to go through the credit card transactions to work out where the mistake has been made and correct it. Deposits of cash & cheques should match, providing they haven't take £10 out of the cash, spent it on a new screwdriver, and not topped up the cash when paying it into the bank. BACS payments can be matched by the book keeper, but as mentioned before, if the customer hasn't put an invoice reference in, it would be easier for the franchisee to know which invoice relates to which payment.

 

 

The alternative - Issue invoice, mark paid, job done!

As much as we would prefer customers to always pay by card (leaving commercial customers to pay by BACS), we don't feel we can reject offers of payment by cash, cheque or BACS yet. We ask customers to include the invoice number on their payment, but some will forget (making matching tricky when payment amounts are similar)

 

I did think about varying the price charged to customers, so the first invoice would be £90 (the agreed price), the next £89.99, then £89.98, £89.97 etc until we got to something like £89.75 when we would revert to £90. That would make it easier to match payments that come in via BACS.

 

After much deliberation, we decided to say 'if the invoice has been marked paid, the business has received the money'. 

 

We could go through the long double checking process, but the cost vs benefit wasn't worth it. It's really unlikely a franchisee would make a mistake and mark an invoice paid when it hadn't been. If that happened, it's possible that a customer wouldn't pay the bill (because we weren't chasing them), but most customers are honest!

 

What this does mean, is if the bank account is short by £100 or £500 at year end, it will be put down to drawings to the Director (franchisee) as if they had spent it personally. They franchisees are happy with this as the alternative is lots more admin + costs. Also, the reality is that there are occasions when they need to pay £10 or £30 cash for something (business or personal), and they take it from undeposited customer cash. I tell them not to, but occasionally it still happens. This way, if it's personal, it's put down to them as the bank account will be short. If it's business and they remember to send a receipt to the accountants, it's covered. I estimate it would cost about £3,000/year in franchisees time/book keeping to double check all the payments going through the system and I don't see they will get a return on that time/expenditure!

 

 

Question 2

 

I don't think I understand this question!

 

Are you saying 'even though we aren't going to match the payments from Undeposited Funds to the bank feed', it's worth pushing the records from 'Undeposited Funds' to the bank account?

 

Thanks again for your time. I really do appreciate this.

QuickBooks Team

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi James,

 

It's not a problem; We seem to come across each other right at the end of my Shift >_< I'll give you a full response tomorrow.]

 

The first section though, including the Journal - What you've done with the Journal matches the advice i'd have given you on how to fix it, i'm confident your accountant will be happy with it.

 

I'll catch up the rest of our conversation tomorrow James, Thanks again :)

QuickBooks Team

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi @Anonymous,

 

I completely understand making the flip between the cost effectiveness of using the systems features, and managing it when you've got payments coming through Cards, Cash & BACS. The system is designed to be able to account for payments that are made using £10 out of the cash Kitty before you've deposited it, however i can completely understand the additional cost, time and risk of a mistake when trying to track these.

 

I can also completely understand the frustration that is caused when a reference number is not there, I presume that if the £100 or £500 was out, you'd just do this through your end of year adjustments? Like you said, if the receipts are being sent to the accountant it'll be business cost, and if there's no receipt then it'll be personal, can't disagree with the logic.

 

Q2

Because you're using the Undeposited Funds Contra account to help with ensuring you can reconcile the bank account, you don't specifically need to push the transactions to the bank account - You've got your match in the transaction to undeposited funds Contra.  My advice would have been to go through the process of pushing the deposited funds transactions to the bank account; but because there may be discrepancies from spending in the amounts you're depositing to the bank account, it would cause (as you rightly pointed out) more admin work, and more cost to the businesses to try to iron those out. As long as you've got the receipt and are happy with an end of year adjustment.

 

If I've got my head around your setup - My expectation is the Undeposited Funds Contra account will balance against the Undeposited Funds account come year end. If you went through the process of tracking your payments, including cash expenses and then pushed the transactions from undeposited funds to the bank account, you'd end up with a zero balance in undeposited funds at year end, instead of a balance in undeposited funds and the counter balance in the Contra Account.

 

 

There wasn't so much of a Question in the second one, and you've made a clear expectation for the reason why you use the Undeposited funds Contra Account.

 

As long as the Accountant gives the all clear about the balances and reports, then i wouldn't want to over-complicate things for you, your clientele or the employees working with you.

 

Anonymous
Not applicable

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi James

 

You’re correct. If the bank is short £100 or £500 we will adjust for that at year end (putting it down to being drawn by the Director/franchisee).

 

The reality is it will only be short if the franchisee has spent customer cash personally or paid cash for something and forgotten to send the receipt to the accountants.

 

All franchisees are aware that they should always bank all customer cash (and not take £50 out to spend personally just because they can’t get to a cashpoint) and should pay every single bill by card or electronically (to save cash payments being missed because they lose the receipt). As such, if we do need to make adjustments at year end, and put it down to the franchisee’s personal spending, they understand it’s because they’re breaking the rules!

 

It sounds like we’re on the same page. I’ve almost finished correcting the various accounts, and I’ll be following up with the accountants next week to agree how banking needs to be processed so we prevent this in future.

 

Thanks again for the superb help you have provided. 

 

Kind regards

 

James

 

 

 

 

QuickBooks Team

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi JamesRead,

 

I'm really happy I've been able to help you with this one, i've found learning as much as i have about your business to be really enlightening. I've found this insight into your business very interesting.

 

Please do not hesitate to get in contact with us if you have any other questions, or come across anything you want to raise to us.

 

Thank you for your post.

 

Kind Regards

JamesC^

Not applicable

Re: Some advice re Undeposited Funds & Bank Feeds.

I think your 'Accountants' need to go the Qbooks courses - they are excellent.

 

HOWEVER

Qbooks undeposited funds 'feature' is a deeply teeedious 'feature' that at the least punters should be able to turn off, as it causes havoc most of the time in Qbooks records.

Imagine if it didn't exist what would your guys have to do? Ans:- post the entries correctly

QuickBooks Team

Re: Some advice re Undeposited Funds & Bank Feeds.

Hi James.

 

As you have stated when you have received payment on the invoices they have been allocated to the undeposited funds. When the amounts appear on the banking page, possibly a number of invoices relate to one deposit, you will have to create a bank deposit and select all the relevant transactions that relate to the deposit appearing on your banking screen, you will then be able to match the the deposit to the amount appearing on the banking screen.

 

If you add the amount showing on the banking/for review page without doing the above it will double your income on your report.

 

We would be grateful if you can PM us if you require further assistance.

 

We look forward to hearing from you.

 

Regards

Established Community Backer ***

Re: Some advice re Undeposited Funds & Bank Feeds.

You need to Stop Using Undeposited Funds, when you have a mix of Too Many People doing too many things.

 

The Payments processed should be sent directly to a Bank account for internal holding; name it "Funds In Transit."

 

Then, the External accountants also stop using anything for banking download except "Transfer" from Funds in Transit to Operating Checking.

 

It really is that easy to stop this chaotic mess. If you are not going to use the Functions properly and do so much manual work, then simplify the data flow.