What's New
Quick Links
syncing QuickBooks bank downloads to invoices
This is a rather targeted post which will mainly be of interest to independent contractors using QuickBooks. And probably only people who have no accounting background like myself and are stymied when they discover QuickBooks is rather primitive. QB is surprisingly incapable of doing some very basic things without being prodded along.
The issue is the duplicate deposits that show up when you 1) invoice customers and then click on 'receive payments' you have recorded income in the 'undeposited funds' account 2) when you download your bank records another deposit shows up. So you then have two records for that 'income'. This of course screws up your summary Profit & Loss statement. What to do? It should be simple. It is harder than it should be, but easy enough once you figure out how to treat QB like the primitive program it is (at least primitive in this area).
I worked out these answers after reading a few dozen posts on the QuickBooks Online Community.
Here is what I do:
1) create invoices for every bill I send
2) when the customer pays click on 'receive payment'. This puts it into the 'undeposited funds' account (semi-secret account).
3) When I download my bank statement from Bank of America I associate those deposits with an income account (the field on the second row and third column of the bank record in the register)..
4) I then click on those deposits in the bank account records, click on 'edit transaction', and then click on the 'payments' button (with the green dollar icon) at the top ......this lets you bring into this record money from the undeposited funds account. Check off the right records from the right customers and click 'OK'.
Now the details for that record will show two deposits in the same amount - one is from your bank account download and one from your 'undeposited funds acct'.
5) Now change the amount of the original record to 0.00. You are doing this so you don't have double the income in QB.
6) In the memo field I write m.d.e. - matched deposit entry followed by the number # for that paid invoice or invoices. That makes it trivial to go back later and figure out what came from where if you need to.
Done! Rinse and repeat for each customer payment showing up in your bank's register.
It also helps to understand the definition of 'undeposited funds'. Basically that account represents the paid invoices which your company has received but you've not yet deposited in the bank - hence 'undeposited'. Money flows through accounts in QuickBooks, but when you do automated downloads the same money shows up in two places. This might seem obvious to anyone with some accounting knowledge but I found it extremely confusing. I was probably equally confused because I develop software and I simply could not bring myself to believe that QuickBooks was so primitive that I had to manually edit payments.
In theory you might be able to use the QB 'matching' right when you pull the bank records in and that match window pops up, but this is unreliable because the amounts might not be perfect and if you haven't already created an invoice and clicked 'receive payment' when you download the bank records you cannot go back and do this.
QuickBooks has some nice features, but it's rather primitive in some ways as well. For instance when using the above method it only shows lump sums for undeposited funds from a particular client, you cannot split those across bank records. In these cases I just pull the lump sum into the bank records and go zero out the other downloaded bank deposit records while adding a comment to the memo field of the matching invoice numbers and former amount.
I hope this saves someone the time it took me to figure out. If you know a better method please let me know.
Labels:
Archives
|
|
February 2008 |
|
||||
|---|---|---|---|---|---|---|
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |
| 1 | 2 | |||||
| 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 17 | 18 | 19 | 20 | 21 | 22 | 23 |
| 24 | 25 | 26 | 27 | 28 | 29 | |

1 Comment
comments.show.hideFeb 15, 2008
Guy Fraser
We gave up on off-the-shelf accounts software - it just didn't come close to meeting our business needs. We tried all the usual products like Sage, QuickBooks, etc., but found that they all suffered from similar problems:
We eventually decided just to create our own database - a bunch of MySQL tables and a Ruby on Rails front end. Part of that required similar issues to what you experienced - being able to get the matching right. However, things are a lot easier when you can just go in and edit the software to do exactly what you need it to do.