Learn About Audit Logs, Discrepancy Reports and Data Import
Learn about audit logs, discrepancy reports & data import in the QuickBooks PowerHour, presented by QuickBooks ProAdvisors’® Hector Garcia and Michelle Long.
Michelle: Welcome, everybody, to another QuickBooks Power Hour. We are glad to have to joining us today for QuickBooks at year end part three. My name is Michelle Long, and I've got Hector Garcia with me today. Just a quick introduction, for those that don't know me, I am Michelle again. I'm a CPA. I have my own practice here in Kansas City, Missouri, where it's pretty cold today. I'm an author of several different books in Intuit academy courses and all of that. The LinkedIn group is a great resource for you when you have questions after the Power Hour. Throughout busy season, if you have questions, it's a great place to post your questions as well as a new Facebook group that we have for QB Power Hour. If you're more of a Facebook person, please join us on the QB Power Hour group. There's a link, or if you search for QB Power Hour, you should be able to find us there.
I'm proud to say I'm an international speaker for Intuit. Like I said, I just got back from Edmonton, Canada yesterday, up there teaching them about QuickBooks Online. I'm thrilled that I've got the chance to go over to London and down to Australia in addition to all across the US and Canada, teaching people about QuickBooks Online. I'll be talking about the online version today, and we've got Hector who's going to be talking about the desktop version. Hector, do you want to introduce yourself?
Hector: Yes, hi, my name is Hector Garcia. I work and live in Miami, Florida. I teach QuickBooks almost exclusively for a living, however, I do do some traditional tax work and stuff like that. We're kind of buried in work right now. We do some CPE type of training for other CPA firms, and I administered the CPE for this webinar. Perfect, the agenda for today is to talk about QB Power Hour. This is, I believe, our ninth or tenth episode.
We, in a nutshell, talk about 50% QuickBooks Online topics, 50% QuickBooks Desktop topics. Every once in a while, we bring in a third party, either a programmer or a vendor or somebody in the industry, to talk about things that you can do outside of quickbooks combined with QuickBooks and things like that. Today we don't have any third-party people, so it's just going to be Michelle and I. We'll talk about CPE process. Let me talk about topics.
Michelle is going to talk about different ways to clean up your chart of accounts and your lists using merge and sub accounts. This is a very common year end side topic. She'll talk about writing off invoices and kind of how it works and the caveats behind that. She'll talk a little bit about the auto log and what kind of information you see in there and working with a closing date password and talking about those topics here.
I'll be talking about undeposited funds report, sort of a quick way to look at a report that people really need to see. Working with a chart of accounts: we'll talk about some clean up tricks that I do when cleaning up the QuickBooks file at year end. Then I'll talk about some tips about entering large amounts of data. It's kind of a common theme, where people ask about how do I do 12 months worth of data and that sort of thing.
The webinar series is every two weeks. The next webinar is going to be February 19th. We usually record them and put them either in my YouTube page or Michelle's YouTube page. They're posed in Qbpowerhour.com, so make sure that if you miss something, if you're not able to attend, or if something was too fast or too technical, sometimes I speak fast, I apologize for that, go back and watch those recordings. The survey link down there helps us to understand how we're doing, understand our audience. We definitely want to make Power Hour for you, for the viewers and the listeners. We want to know what you want to learn about QuickBooks, and our job is to put together topics that we think are pertinent and popular. Do you want to pass the slide for the CPE process, Michelle, please?
We issue CPEs in QB Power Hour. You must attend the webinar live. The recordings don't earn CPE. At least we wont' issue a certificate for that. You must be logged in for at least 50 minutes, and the CPE password will be provided at some point in the middle of the webinar. Near the end we're going to put the polling question. You must have the answer correctly, and the CPE certificate will be emailed in about three weeks. We actually already emailed all of the certificates up to the very last webinar, so we are up to date with issuing those CPE certificates. Can you pass the other slide, please, Michelle?
Michelle: Yep, OK, and I'm going to talk about the upcoming events and announcements that we have. First of all, if you go to Intuitacademy.com, there's a lot of great webinars that are available out there. You can of course get some certification training that's going on and some other webinars. There's also a great webinar series that's going to be starting at the end of ours, actually. Ron Baker is going to be talking about value pricing, and it's a four-part webinar series. If we can, we're going to get done maybe a few minutes early or right at the top of the hour. You all can log in if you'd like to to listen in on Ron Baker's webinar this afternoon.
I also would like to invite you to the award winning Ultimate Accounting vCon. This is a virtual conference that's coming up on May 7th and 8th that Sandy Smith Leyva and I offer. It is a conference that you can attend from your own home, and it's also got a lot of great content. We've got hundreds of people already registered and would love to have you join us for that. If you want more information, go to accountingvcon.com, and the prices are really unbelievably low right now. If you want to join us for that, please check us out.
Also Hector and I plan on going to QB Connect 2015 again in San Jose on November 2nd and 4th. We'd love to see you out there, so if you will go to this URL: QBCon2015, you can register to get more information, as it becomes available, as to the speakers and the sessions and all that stuff. You can register and find out more information there as well. We'd love to see you at some of these upcoming events, and now let's go ahead and start talking about QuickBooks Online at year end. I'm going to cover the online aspect. Some of these things do apply to Desktop and some don't.
Let me go ahead, and I'm going to toggle into QBO. Let's see if I can get the right QBO here, because I have several of them. Goodness, now look what I did. Hold on a second. Don't look at that either, stop. I'm sorry, I am so tired. I'm sleep deprived from traveling. The good news is, I'm going to be here on most of the webinars for the next several months, because I'm not traveling during [inaudible 00:06:55] season. That's the good news.
Let's talk about when you're working in QuickBooks Online, and you need to clean up some of your lists. For example, if you have customers or vendors or suppliers that you need to merge, you can merge those things just like you did in the desktop version. Hector's going to talk more about this in the next QB Desktop. A lot of this stuff is the same, but one thing that is different when you're working with QuickBooks online that I wanted to point out to you is, if I scroll down here, you'll see I have a phone expense right here which is set up as an expense account. I have a telephone expense which is also an expense account. If I wanted to merge these two accounts, what you can do is you decide which one you want to keep. Let's say I want to keep phone expense, so I need to edit telephone expense. I'll come over here to the far right and click on edit. This is going to pop it up.
You know you have to change the name, so it matches exactly. What's different is you have to also look at the detail type, because both of them were an expense account, but in QuickBooks Online, and this is the part that's different from the desktop version, in QuickBooks Online we have a detailed type of account. You'll notice one of these is office and general admin, but the other one that I'm trying to merge it into is under utility. If I don't change the detail type, watch what happens when I get click save. I get an error message that says that there's a different detail type using the thing. You have to change not just the name, so that they match exactly. You also have to change the detail type, so it matches exactly the account that you want to merge it into. Then when I click save, it'll say this might affect your reporting and stuff, because it's going to merge these two together as if they were always one. Be very, very careful. Just like in the desktop version, there is no undo. It will take all the transactions from the telephone expense and merge it into phone expense. There is no undo. You'd have to manually redo those. It says the name is already being used. Do you want to merge them? You say yes.
That will merge the two accounts. Just like in the desktop version, it will merge those. I wanted to point out to you what was different is the fact that you need to make sure the detail type of account is the same, so that you can merge them. Hector's going to talk more about cleaning up your lists and things like that a little bit later.
Another thing that's real important at year end is when we want to go in and write off our invoices. We have a cool tool that's available for us to write-off invoices. For me, I'm using the new QBOA, so I have this little brief case right here. If you're in the old QBOA, which you should be updated soon, we'll talk more about that a little bit later, under the gear right over here to the left of settings is where you'll see the accountant tools. You'll see write-off invoices. In the older version of QBOA, it's going to be right up here to the left of the settings. In the brand new QBOA that's just being rolled out, so not everybody has it right now, as a matter of fact not very many people have it, you'll see the accountant in green. That's how you're going to know you've got the new one.
When you go into write-off invoices, this is the thing. Whether you're using the desktop version, or you're using QuickBooks Online, let's say I want to go out here, and I want to write off all these invoices. I'm going to do it as of today. Let's just see what we get. This tool is great to identify invoices that are a certain age, invoices that are through a certain date, and the balance maybe less that $500 or $5,000, whatever it is you want to search for, this is a really great tool. You've got this in the desktop version as well. What I'm saying is going to be true for both QuickBooks Online and QuickBooks Desktop.
If your client or you, if you have sales taxes that are involved, do not just use this write-off-invoices tool, because it doesn't adjust your sales taxes. What's important is this took can help you identify invoices that need to be written off based on the age and the amount. You could do a print screen to print this off, but I don't recommend that you just write this off. What it does is, in QuickBooks Online, it's going to create a discount on that invoice, and it won't adjust the sales tax payable. How do you adjust sales tax payable? A lot of you will have clients that have sales tax payable.
For example, I've got an invoice here for a bad customer. Let's go in and take a look at that invoice, and let's determine what's on that invoice. I'm going to go over here to customers, and I'm going to go to a bad customer. They're a bad customer, because they didn't pay us. We have to write off an invoice. If I want to go see what I have for this customer, let's go in and take a look at the invoice here. Here we have one that's overdue, and we probably want to write it off. I'm going to go ahead and open that invoice. My internet's being a little slow today. Notice I have two items here. I have the first line, $500 is taxable, and the second line for the service is not taxable. This is where it's important to identify the taxable portion and the non-taxable portion.
What you need to do, I'm going to open my items list here in a new tab, because I want to keep the invoice open. I can show you over here on your products and services list or on your items list in the desktop version, you need to set up two items that we're going to use when we write things off. See down here, how I have one write off taxable and one write off non-taxable. You can choose what account you want to write those off to, like bad debt expense, write-off invoices, whatever name you want to give it. The key is one of these is taxable. See how one is taxable and one is not taxable.
When you're ready to write of that invoice, so we're going to write off this invoice with $500 taxable and 1,000 non taxable. Let's go back here and let's duplicate and have another tab open. We actually need to enter a credit, and we're going to use those two items, the taxable and non taxable items. I'm going to click on the plus up here on the top. Boy, my internet's decided to be slow today. I'm going to click on the plus, and we want to enter a credit for this customer. Under credit memo, we want to enter a credit memo for this customer when it comes up. It was a bad customer. I can choose the date. Maybe I want to write this off as a Pro 31-20-14. You can choose your date, and then we have write offs taxable. Remember, we have $500, not 5,000. On the next line we have write off non taxable, and it was $1,000. I can put the right quantities in there and everything if I wanted to.
Notice, when I do this credit memo, this sales tax will be adjusted right here just like on my invoice. I had $40 going into sales tax payable. I need to do a credit memo, so I have $40 adjusting my sales tax payable. When you just use the write-off invoice tool, it doesn't adjust sales taxes payable. You can use that tool to help you do your filtering to see which invoices need to be written off, but if you have sales taxes involved, you need to adjust the sales taxes as well. You also need to know what the laws are for your state, because different states have different rules. I'm not going to advise you on that, because there's all these different states and different rules and different requirements, so you need to determine what applies to you. Just be aware that that write-off invoices tool will not adjust the sales tax payable for you.
You need to do a credit memo, so we want to do a credit memo for two items, one that's taxable, one that's not taxable, so you can allocate it. Then this will adjust your sales tax payable for you. That will work in both the desktop version and QuickBooks Online. That's true for both of those, so I hope that was clear and understandable. You create a write-off item, one for taxable for non taxable, and then you write off that invoice and split it according to what's taxable and what's not taxable. Another really cool tool that we have at year end is when we reclassify transactions. Again, you have this tool in the desktop version as well. It's under the accountant menu under client data reviews. It allows us to find and fix errors quickly and efficiently instead of doing each one one at a time.
I can come in here. Let's say I'm reviewing the fuel expense. Let's say all of these gas expenses went to the wrong account. I can select all of them, and then down here in the bottom right corner, let's reclassify under cost of goods sold just to keep it easy here. I'm going to reclassify these three from fuel expense into cost of goods sold. I don't have to open each on of them separately. I can select the, quickly classify and presto magic, it will automatically put them up here in cost of goods sold for me. You'll see this went from 405 to 572, and there's my three oil and gas. You'll see how easy that is.
The reason I did that is I wanted to show you this really cool report that we have. This is way better than the desktop version. It's the audit log, so it's under your reports. You also have a shortcut for it on the gear. This audit log report, it's amazing. The desktop audit trail will only show you transactions that were changed the QBO audit log will show you when somebody's logged in and logged out. They'll show you here, I added a credit memo, I edited an expense, I edited those three expenses for oil and gas. It will show you I merged an account. When I added new accounts before we started, I set those accounts up. I created that invoice for a bad customer before we started. It will show you every little thing. I even changed some settings. It shows you everything. Furthermore, I can filter it by user, so I can see everything a certain user did on a certain day if I wanted to, by user, by date. You can see all of these things, so depending on what you're looking at, you can see everything.
Best of all, let's say I wanted to see this cash expense that I just edited. I click over here to the far right, if I click view, you'll see I added it a little bit before we got started, and I edited it up here. I guess somebody else edited it, but you'll see it highlights what changed. The cost of goods sold account is what changed. Maybe I want to know what was it before. If I click on compare, this is where I just love it, you'll see on the left column here when it was first added, and on the right column you'll see when it was edited. See, we have added here and edited here. I can scroll down, and I can see it was a fuel expense. Now it's cost of goods sold.
Don't you love it when the client makes changes, and you're like, "What was it before?" This will very easily allow us to see what it was before, and I can even click on show the changes only. Up here on the top, on the top right, where it says show changes only, I can quickly see what changed, what was it before and what was it later. This audit log can be a huge help for you at year end, when you're trying to figure out what happened, what the client did, just various things like that. I think that's going love it. I see a couple questions here about can the client see the audit log also. Yes, they can.
Also, another one that's really helpful for us that we can see at a glance but the client can't, and it's created from the audit logs, but under the accountant tools we will see a voided and deleted transactions report. This voided and deleted transactions report is very helpful. Maybe I need to delete something really quick. Let's go over here. I have this invoice up; that's the one I wrote off, isn't it? Oh well, let's delete it anyway. Click on more. To delete something you click on more down on the bottom. That's also where you see your transaction journal, your control Y, where you see the journal entry. I'm just going to go ahead and delete this one. Let's hit yes, so let's delete that invoice. There we go, it's gone. Back over here I was going to the ... Let's refresh this; now there should be another one out here. The voided and deleted transactions report, I'm going to refresh it, because I just deleted on.
Let's see this come up, and I'll show you, this report is really great to see when clients delete things. Here you can see I deleted this invoice. There's no undelete button, but I can go out here and view this, and I can go out there and see what it was to begin with. If I click on added, see how I can see what this was, so they can recreate it. You can help your clients find deleted transactions and reenter them. There's no way to quickly undo something. The other thing that the voided and deleted transactions report is good for is to monitor the employees. If you've got somebody who's voiding and deleting a lot of transactions, maybe they're a new employee, and they need some more training, because they're making lots of mistakes, and they have to void and delete thins all the time. Maybe you have employees that are stealing from the company. They enter an invoice, and then when the money comes in, they pocket the money and delete the invoice. Monitoring the activity on the voided and deleted transactions report is a good idea to watch out for either employees or staff or whoever, people that need additional training, or is there some theft going on? This is a good way to track because it tracks every single thing that people with do.
One of the other things I just want to make sure you know is undoing a bank reconciliation. When we're logged in as the accountant user, we can undo the bank reconciliation. You go into the reconcile window, and this is where I see a lot of people looking for it, under the accountant tab. They're looking for undo in here. You see reconcile, but you don't see undo. What I'm going to do is I'm going to go ahead and force this really quickly, so I can show you. This is where we're going to be able to undo the bank reconciliation. I'm just going to put a month end, and let's put a balance in here, one, two, three, four, and I'll show you what the client does sometimes. Let's make that go away.
They will force the bank rec. Let's select a few things out here, select a few things over here, and let's go ahead and finish now. It says, "You're off. Do you want us to fix it?" Yeah, let's do that; that's a great idea. Let's go ahead and reconcile, so I force the bank rec. Your clients do that, right? The good news is you will see in the change of columns if they changed a transaction that was previously reconciled. In the adjustments column you will see if they forced the bank reconciliation, even if they changed that. I had a client that knew if I saw reconciliation discrepancies on the P and L that he was going to get a call from me. He changed that transaction, so it posted to office supplies. He thought I wouldn't find it, but this will still show up in here telling you that they forced the bank reconciliation.
If you point to that line, you'll see over to the right, undo. That's where you can undo the bank reconciliation. If I had six of these here, I could undo six months ago, and it will undo all six reconciliations as one time. You only will see that if you're logged in as the accountant user. You're not going to see it in the normal QBO login, so your client can't do that.
One more thing that I wanted to make sure that I point out to you is if you go into your company settings, you're going to want to set a closing gate password just like you do with the desktop version. Some people will do this on a monthly basis after they reconcile the month. Some people do it at the end of the year, but under the company setting on the advanced tab, this is where we can set a closing gate password. I can put my closing gate in there, let's do 12-31-2014. I can choose here. Do I want to allow them to do changes after they get a warning? In other words, they get a warning message, "You probably shouldn't do this," and then go ahead and let them do that, or do you want to require a password? Give them a warning and require a password. That's the one that I recommend, and I recommend the password be something like $500. If they have to make a change, and they mess up year end, it's going to cost them at least 500 bucks. I didn't type in the thing. Maybe I did. Use a good password, so it stops them.
The thing is, it's kind of a soft close in the sense that you can make adjustments prior to the closing date. If you have the password, you can enter that. That' something very important to know. Cathy, thank you. She said, "My love for Michelle is increasing by the day. I guess I haven't been keeping up to date on the QBO updates." Good, Cathy. Speaking of keeping up to date, there's something very important that I want to share with you all. I'm going to go into another user down here, so you'll see I've got my happy face user down on the bottom, and I've got this other lady user. What I'm in now is I'm in a different user in Chrome, so I can have a separate company open in QuickBooks Online. In here I wanted to point something out to you that's very, very important.
In the existing QBOA, this is the existing QBOA that we have now, you have a my company. If your QBOA doesn't look like this, up at the top there's a little try it now button up here. There's three versions. Version one, if you still have version one, look at the top and click on try it now. This is version two, where you have my company. You as an accounting professional get a subscription, and you can convert your own company, your firm's books into QBO. Version two here, in this version of QBOA, I can convert my QB Desktop into my company. With the new version of QBO that's rolling out in phases, so you probably don't have this yet, my company has become your books. This is your firm's records here, and it's your books.
What's very important to know, and I'm trying to double-double check this, but I'm 90% sure, you can't convert from the desktop into your books in the new version of QBOA. You can only import your lists, your customers, your vendors or suppliers, your [inaudible 00:27:03] accounts, products and services. Only lists can be imported into your books in the brand new QBOA that's going to be rolling out. If you still have the old version of QBOA, and you haven't started using my company yet, if you want to convert your desktop data to QBO, you should do it ASAP. Do it now before they update you to the new QBOA, otherwise you're not going to be able to import all that.
Hector's going to talk to you about some cool tools, about importing transactions and things like that, so there maybe some alternatives for you, but it's also going to be more work for you. Just be aware, if you haven't done it yet, and you want to use that my company, you might want to go ahead and do that sooner rather than later, before you get converted to the new QBOA. I see that we're at 11:28. I'm going to see if Hector has a couple questions, and then I will try to answer some questions.
Hector: Hi, Michelle.
Michelle: Yes, Hector, go ahead.
Hector: There was tons of questions and comments around write offs, so I wanted to kind of give you an opportunity to stave your comments in some of these. The first kind of question was when you created the credit memo for the write off, you didn't tell it anywhere which invoice you are writing off. I think you left out the fact that then you have to go to receive the payment and match that credit memo to the invoice, correct?
Michelle: You're right, I forgot that piece. Yes, you should. Actually, what you can do even is actually on that invoice you can click to create a memo for it. You're right. See how it say unapplied? I should go apply that credit memo to that invoice, and I can't now, because I deleted the invoice when I was showing that. You're right; I should have applied that to the invoice, and I didn't show that. That's a good catch, whoever caught that.
Hector: The other comment was or question was, "What account was affected by this?" I think you have not gone deep into it, but the items that you created called write off taxable and write off non taxable, these items would point to the bad debt account, to the write off account. I think that just wasn't mentioned, so I wanted to clarify that.
Michelle: Yes, you set those up. When you set up those new items, you can map them to whatever accounts you want to, bad debt expense, write off expense or write offs. Some people, if it's a cash-based business, some people will set up a write off account as a other expense, so that it's down at the very bottom. If you're cash basis, you're not going to deduct that, because you never recognize the income. If you choose which account you're going to be using when you set up these new items, you choose which account you want to map them to.
Hector: The other comment was, when you look at the bad debt account, and you're going through the bad debt account, and you're trying to identify all those expenses, the bad debt expenses you wrote off, people were asking, how do I know what invoice was written off? How do I know the invoice was written off? I can answer that if you can tell me.
Michelle: Sure, go ahead.
Hector: The user would have to go in the credit memo, and they would have to copy and paste or just type in the invoice number in the description field of that credit memo if you wanted that to show up in a transaction detail report when looking at that.
Michelle: Right, usually you can go ahead and put in there invoice number whatever it is to have that trail. If you link them together when you write it off, that's going to help link those two together as well. One thing, Hector, that people can do that will help a lot to see and play around with it is go into that sample company and play around with it. Create and invoice and write it off, just like I just did. That will help you to see how that works. In QBOA, I'm logged into QBOA, if you click on that question mark, see where it says QuickBooks Online sample company? Go in there and play around with it and look at the reports, create the transactions and click on more at the bottom of that transaction in transaction journal. You'll see the journal entries, and you'll see how it's working and things like that. That's a good way to really understand it better.
The other thing is that in the QuickBooks Online advanced certification training materials that is covered in, I think, troubleshooting or special transactions. I think it was module three, special transactions. It's covered in the training material, so anyone that's a pro advisor can access those training materials, module three or section three.
Hector: No more questions, I guess the power needs to go back to me now.
Michelle: OK, I will transfer it back to you. I will answer some questions in the questions log. I will try to do that. Hector, you go ahead and take it for a little while. I think we actually have a CPE keyword coming up next. Isn't the CPE keyword next? [crosstalk 00:32:12] I wanted to talk about it, because that's my new office.
Hector: Go ahead.
Michelle: The CPE keyword today is lake. I'm so excited that I'm going to be able to look out at the lake from my new office. I hope that we get ourselves moved out there in the next week or two. The keyword today is lake. I can't wait to move to the lake. I'm excited about that, and I'm glad to be home for a while, so we can actually get moved out to the new lake house. Sorry, Hector. I wanted to share that one.
Hector: That's all right, no problem. Let me put my agenda up here. Where are my QuickBooks Desktop at year end. One thing is, I've gotten comments in the last webinar, the last couple, actually, where people say, "How come you guys don't publish the exact topics and the exact bullet points of the QB Power Hour before coming in there? Every time we come in there it's kind of a surprise what we're going to cover." What I'd like to say from my perspective is Michelle and I, we're very, very, very busy. We're doing many things. This is not like a very sort of corporate lead type of webinar series. We put together topics that we think are relevant, and we use a lot of the things that happened that week to bring it forth. We look at what goes on in the LinkedIn group, and the questions are being asked. We take some of the feedback from when people log into our website or the survey forms and tell us what topics they want to see. The new Facebook group became very popular within a week. A lot of really deep discussions about things that are interesting and relevant to the viewers.
Sometimes we actually finish polishing the topics up a day or two before. These are things that Michelle and I know, and they're sort of in the back of our head, but we don't know exactly what we're going to present until we know what people want. Unfortunately it is kind of a surprise sometimes. I know some people appreciate that, because they will come into the webinar no matter what. We're going to try somehow to be a little bit more proactive in putting those topics together. Michelle, do you want to say anything about that, or would you take my response as our official response?
Michelle: Yeah, the one thing I will say is that we will try to do a better job of getting scheduled out, at least maybe the next couple of webinars, for the next two or three that are upcoming or something like that. Just join us every time, because we'll have great information every webinar.
Hector: Can you see my screen? Can I get started? Michelle, can you just give me some feedback if you can see my screen?
Michelle: Yes, Hector, we can. I'm sorry, I muted myself. We can see your screen.
Hector: No problem. The first thing I want to show you is something that they're really, really sort of quick hits tips. A lot of people don't know this; it's sort of a simple thing. Sometimes when we're in the balance sheet, and we're trying to troubleshoot the undeposited funds account, the only way to really hone down and see what item is pending in the undeposited funds account is what I'm going to show you. Usually if you double click on undeposited funds, you're going to get a huge list like this. This one in particular that you're looking on the screen is very easy to identify, because it's their very last ones for 600. What happens if the undeposited fund itself, and this could be several, this could be hundreds of undeposited funds, is not sitting there on the very last line. It makes it a little bit difficult for us to figure out exactly which items in undeposited funds haven't been deposited.
What some people do is they'll go to banking, make deposit, and then when this window pops up, they'll look at it here, but this is not a report format. For a lot of us troubleshooting QuickBooks files at year end, these accounts are absolutely humongous. There's just tons of transactions in undeposited funds, and we're having sort of a tough, tough time trying to identify what things are sitting there in undeposited funds. What I wanted to show you is no matter how big this account gets ... I'll receive a couple of payments here just to show you. I'm going to receive a couple of random payments for random amounts just to show you how we can point these out later on. I'm just going to receive a bunch of payments from customers that have not been deposited, obviously things that are going to sit in our undeposited funds account. I'm trying to sort of emulate a difficult undeposited funds account to track.
I'm looking at these undeposited funds, and I see the total value is 45,000. In the balance sheet I see 45,000, but I don't know which specific items are causing the issue. Just a quick tip, I had to frame it. We double click in there, we get a transaction detail report of undeposited funds. Then we go to the customize report menu here at the top. Then we're going to go to filters. In the filters tab, we're going to look for a filter called cleared. Typically cleared is only really used for checks that have been reconciled, but in this particular case, because it's an undeposited funds account, the programmers in QuickBooks said, "You know what? This cleared filter is also going to work if that transaction has been passed through to a deposit."
This doesn't mean it's been cleared in the bank yet, so just because it says cleared, it doesn't mean it cleared the bank. It just means it cleared undeposited funds. It passed through and went all the way down to deposits. By hitting clear now and hitting OK, you will have now a narrow report that specifically tells you what items are in the undeposited funds. That's kind of a quick hit report that I wanted to show you. I wanted to frame it around why this would be useful.
The other sort of common thing that happens here is that an accountant will come in and say, "You know what? I want to convert this balance sheet to cash based, because the customer made a whole bunch of errors, and there's no time to clean up the undeposited funds." What a lot of accountants do ... I'm not going to blame myself for this one, because I am an accountant, but I've never done this. I always had formal QuickBooks training. A lot of accountants without formal QuickBooks training or without the time to actually fix it, or the client doesn't want to pay what it actually takes to actually fix it, what they will do is they will just do a journal entry against the $45,000 in undeposited funds. I want to show you, if you have to do that ... not that I'm recommending it, I have to be very careful about making such a recommendation.
If I had to make a journal entry against undeposited funds, I want to show you the procedure that I would recommend. We'll go to make a journal entry here, and we're going to go ahead and do the journal entry for the end of the year. We're going to hit undeposited funds, and we are going to lower that, so we need to credit that account. We're going to do 45,422, there we go. The offsetting account is generally some sort of income account, because having additional undeposited funds or erroneous undeposited funds is typically going to be because the customer did the banking and put the deposit straight into income and then has the invoice straight into income [inaudible 00:39:31] and didn't follow through the procedures.
If you wanted to reverse the undeposited funds back into income, you would debit income, which will reduce income, and you would credit undeposited funds to make that balance zero. This is the first step. The first step is getting rid of the undeposited funds, quote, unquote. Undeposited funds is no longer there. I'm going to make it even further, because this is something that a lot of people don't know. Here under customized reports, it's important that you know that here on the advanced tab you can click on all. You can actually show accounts that don't have zero. Sometimes just because they have a zero ... Again, this is here under all. Sometimes when they have a zero, we forget it even exists. Sometimes it's useful to actually see even amounts with zero, sometimes for chart of accounts cleanup, which we'll talk about in a second. For different purposes you may want to see the zero accounts in there. One of the nice things about it is even with that zero, I can now double click on that, and it will take me to my transaction detail report, which will show me how that ended up at zero.
That's sort of two reporting tips I wanted to give you right off the bat. What is the problem now? The problem that I cost is when I go do a bank deposit, I'm going to see all my client deposits there plus some random negative number causing an issue. The way to fix it is you select them all, obviously everything up to that December 31st period including that negative amount. This will actually create a deposit that will net down to zero, so your bank will not be affected. If you want to create some other bank account called clearing accounts or something like that, so you don't even see it touch the bank, you can do that. Long story short, what you want to do is you want to adjust your deposited funds and then bring it in with an equal negative amount. You want to follow throw the deposit. Once you follow through the deposit, when your client ever goes back to make a deposit, they don't see those annoying things sit there. Those are two sort of quick clean up [inaudible 00:41:30] that we do for year end whenever we don't have the time to actually clean those up one by one.
Michelle, I'm going to switch over to my bank account for a second, because I want to show a technique about downloading data. Do you want to ask me some questions while I switch over? Do you have any questions that you think people want?
Michelle: Sure, a couple things. Somebody was asking about the advanced certification. I just want to go ahead and let people know, the QuickBooks Online advanced certification training materials are available that you guys can access out there now. The exam itself, we are revising the questions. It should be available in a couple of weeks, so just sit tight. If you subscribe to me on Facebook or my blog or LinkedIn or whatever, I will post and make sure to let everybody know when the exam itself is available. Right now the training materials are there. You can log in and access that, but the exam will be available later. For those of you that may not be a member of the QuickBooks Pro Advisor program, you can join and get the certification, the training and all of that free for QuickBooks Online. If you go to proadvisor.intuit.com, you can sign up and access QuickBooks Online training and certification for free. If you want the desktop, you want QuickBooks Pro Premier, if you want to get QuickBooks Accountant and QuickBooks Enterprise Accountant, if you want the QuickBooks Desktop training and certification and support, you need to pay money for that aspect, but the online version is free. Hopefully that helps.
Francis is asking, "Is it the same for candidates?" Yes, there is a cloud Pro Advisor program in Canada as well. Again, you access the training and certification for free, however, the advance certification is probably going to take longer to be available in Canada versus in the US. It's not easy; it's different than the desktop.
Hector: Michelle, I'm ready now, by the way.
Michelle: OK, go ahead.
Hector: I wanted to show you something that I was kind of experimenting with a couple of days ago. When I go download my .QBO and my web connect file, I always see this option here for QuickBooks IIF. Because of my experience in QuickBooks and stuff like that, I've always been kind of afraid of working with and IIF file. I said, "You know what? Let me download this QuickBooks IIF and see what the heck it does." Basically I want to show you a file that I downloaded. I went to my bank, I went to QuickBooks IIF, I set up a brand new blank file. I'm just going to switch over the blank file.
I wanted to show you, because I thought this was one of the most bizarre things that I've seen in a very long time. Why that option to download IIF is even there is perplexing. I want to show you the [grout 00:44:25] of that. I'd love to get some comments on whether or not you are as perplexed as I am about this. Basically I downloaded IIF file. I went to file, utilities, import IIF file, and then I went and I grabbed my IIF file I downloaded from the bank. I hit open, and I went through all the cycles, hit yes, OK, I'm sure. Then when I went into my profit and loss, because I was really wondering what happens if I take a whole year's worth of data and download it to IIF. Everything downloaded, but nothing came in my profit and loss. I'm saying, "What the heck is this?" Then I go into my customer center, and then I see no customers were created. Then I go into my vendor center, and I see a whole bunch of crazy vendors created here. Basically it took every single name of transaction and created us a vendor.
On top of that, it went into my other names list, and it also put a whole bunch of other names in there. It created two sort of crazy things. The first thing I wanted to show you was my first instinct was that's easy. I can go into other name list. I can go into activities. I can go to change other name types. By the way, a lot of people don't know this is even hidden in here. Then I can come in here, and I can actually just check on all of these and switch them all to vendors. This is kind of the one way to kind of start cleaning this process up. Even with that, the strangest thing happened.
I pull up the balance sheet, and basically what downloading an IIF from the bank does, it basically creates every single transaction as a separate bank account. At the end it will net out to zero, because it will balance. This is completely 100% crazy and unmanageable. If ever you ever want to experiment with this type of work, I would say if you download a bank IIF, I would be very, very, very careful about that.
The other piece I want to show you, because I wanted to frame something I wanted to talk to you about, one of my specialties is importing data into QuickBooks or doing data imports. If you go to my blog, which is qbblog.com, you're going to see a lot of things that I write about importing data, different techniques, different tools, third party tools. I've had a lot of emails, a lot of chatter saying, "Hector, when do you use it?" I wanted to kind of give you guys a run down of the different tools I use and how I compare them.
Batch enter transactions, which was introduced in 2013, this is only an account in an enterprise. It's an amazing tool, one of the best things I've ever seen. I will try to do a demo if I have time. IIF, it's very fast, but the problem is it's error prone. As you saw, some of the results are unpredictable. There's not error correction for duplicates, no error correction for if something's broken in the IIF. If you want to figure out Excel data and convert it to IIF, you will need a third party tool.
One of the preferred choices is bank feeds. Bank feeds will still require most major banks to have either the direct connect feature, which you pay a fee, and usually you're limited to about 90 days of data. Most banks will do a free download of a "OSX file," which is a .QBO extension. It works with both QBO and Desktop, but what happens when I don't have bank feeds, which is a preferred choice? I have basically different data in different ways.
There's three major tools out in the market that people usually talk about, and in the very last QB Power Hour webinar, I did a demonstration of the third one, QB Convert Pro. We had a lot of buzz. A lot of people emailed me, and they were asking me ... This sounds great. Some people were thanking me for introducing them to the tool. The tool is absolutely amazing, but I wanted to kind of break them down. Many people were asking comparative questions.
Let's start with ScanWriter. ScanWriter is one of the real popular programs. They sponsored an even maybe six or eight episodes ago with a demonstration of them. ScanWriter allows you to take scanned paper documents. The client gives you a paper bank statement, you scan it. The software itself recognizes the information inside of it and has its own interface to load data into QuickBooks. It only works with QuickBooks Desktop, but in my understanding, they've developing, and it's very soon to come out, a QBO version. Transaction Pro Importer is very popular software that is mostly for importing data that's already in Excel or CSV format, but mostly for Excel format. Transaction Pro Importer will push information into QuickBooks, but it doesn't send it to bank feeds, which sometimes is the best of both worlds.
That's why I recommend this third tool called 2qbo Convert Pro, which will take a PDF, bank statement, not the scanned version. The scanned version you have to use ScanWriter. The client gives you a PDF bank statement, email it to you, USB, whatever, you can actually take it to this program, and it will convert the data into CSV. With the CSV program I can do stuff like batch enter. I'm going to pull up batch enter. I know I've covered batch enter in the past, but I want to pull up batch enter, so we understand exactly what we're talking about. If I'm able to get data in CSV format, in Excel format for my client, I can copy and past it straight into here. If I don't have it in Excel format for my client, but I have a downloaded PDF, I could use a program like 2qbo Convert Pro and convert it from PDF into Excel. In Excel I can make some changes and then copy and paste it through batch enter.
What I like about it is if you don't like batch enter, and you prefer a bank feed, you can take that same PDF downloaded statement or a CSV Excel file and convert it to a .qbo. A .qbo can be imported into QuickBooks Online, QuickBooks Desktop, and it's a great option also for people using PayPal, for example. PayPal allows you to download into CSV. It doesn't allow you to do .qbo, but it allows you csv. You can take it to .csv, clean it up, convert it to .qbo and then brink it into QuickBooks through bank feeds. Bank feeds is the preferred choice. Why is bank feeds the preferred choice? I will tell you.
I will tell you for most people, I will compare head to head batch enter and bank feed. Batch enter will require you to have the data in Excel. It will require you to have the data exported from the bank in Excel. Not all banks give us that information. Batch enter, you must select the expense account. The system will not automatically select an expense account the way it does with bank feeds with bank rules. If you copy and paste the data, you're going to repeat some vendors or potentially repeat some vendors. I wanted to show you a demonstration of what that looks like, so I'm going to open a CSV file that I downloaded from a bank. What you're seeing here on the screen is a CSV file I downloaded from a bank. I got this straight from Chase.
If I was to bring this data in here, and let's say for example, I was to copy and paste it. I want to show you what the potential issue is with pasting data from CSV. When I go paste the payee data, I may have a whole bunch of vendors are essentially the same thing, or a bunch of payees are the same thing. Because of the coding, because of the last couple of numbers or the store number, it's going to keep creating more vendors for me. That's sort of the drawback of doing that. Let me go back to my slide here.
With bank feeds using bank rules, the system will take similar names and put them all together. I actually like both options. People ask me my opinion, batch enter versus bank feeds. I like both options. They do present a good solution, but batch enter is for somebody that has Excel skills and can clean things up in Excel before bringing them into QuickBooks. Batch enter really doesn't require that much Excel skills, plus you can teach bank feeds to a client very, very easy. That's why I like the option of doing bank feeds instead of batch enter.
We actually demoed the product of 2qbo last week, but here was a lot of people asking, they don't understand how the concept it. I made a video to YouTube explaining the process of what it does exactly. I asked the developer to extend the coupon code, because I wanted the viewers to get some deal. A lot of people will buy a tool if it's presented in this sort of environment, so if you go to that link, that will take you straight to a YouTube video. It's like 15 minutes and I walk you through what the program does. If you have bank statements in PDF format given to you from your client, that they downloaded on the bank, and you don't want to enter this information by had, you can run a program called 2qbo Convert Pro, convert it to a .qbo and bring it in as bank fees.
I'm going to go back and put the slide of comparing these three tools here together, and I'll ask Michelle. Michelle, are there any questions that people want to put up, so we can also put the polling question? Michelle, can you help me put some questions up or answer some questions?
Michelle: Yeah, there's lots of questions there. I was just getting ready to type to people; I am typing as fast as I can trying to answer all your questions, but I just can't keep up. One of the questions was what does IIF stand for, first of all?
Hector: IIF: Intuit interexchange format. That was created back in 1998 or '99. Because of the errors that they started creating, Intuit came up with something called the SDK, the software development kit that came out in 2002. The SDK allowed developers like Transaction Pro and ScanWriter and many other developers to push data into QuickBooks without using this IIF format, which is flawed and unsupported by Intuit since 2007.
Michelle: Then somebody else said they wanted to know. Does the 2qbo Convert Pro work for credit card statements as well?
Hector: Yes, anything that's a PDF statement, you'll do it, and the developer, I've been working with him hand and hand for six months. When I discovered this tool three years ago, I was completely amazed by it. I didn't want anybody to know, because this was my edge. As a consultant I was an expert in importing data. I just kept helping him push and push, and then he committed that if a major bank or a bank in the US has a statement that doesn't work, he will go in the program, and he will fix it, and he will make that statement work. Yes, the answer is yes, it will work.
Michelle: The poll question, how long have we had that up?
Hector: A minute.
Michelle: If you need a continuing education credit, please go ahead and answer that poll quickly, so we can get back to some of the ... Some people wanted the coupon code that they wanted to write down and things like that. Somebody typed in the poll question. You need to answer the poll question.
Hector: The poll question has been up for a minute and a half. We took it out already.
Michelle: The poll question is gone now. If you didn't answer it, I'm sorry. It looked like we had a big number of you that did answer the poll question. Yeah, we put that down now. Hector has got that code again, the QBPA for QB Power Hour 40.
Hector: A couple things here I wanted to just show before we wrap it up, this is for the people that were asking for that coupon code. When we talked about importing and different ways to import, some people were asking, "What about Transaction Pro?" We've heard about it. We know about it. I actually want to bring Transaction Pro, the developers themselves, in QB Power Hour to do a demonstration. I'll ask the crowd if you're intrigued about the possibility of bringing transaction pro into QB Power Hour to do a demonstration. I'm just going to put a polling question up.
While people put the polling question up, Michelle, I just want to remind people that right after this webinar is over, which actually, it's over, this is the last thing. We already put the polling question up. After the webinar is over, run into intuitacademy.com and register for Ron Baker's webinar, because it's absolutely fantastic. It's about fixed pricing. Michelle, you want to say something about that?
Michelle: Yeah, I just wanted to say the Ron Baker webinars are going to be great. It's a great resource for you, but also in just a second on the last slide, we're going to give you the link to the Facebook group and the LinkedIn group. Some of the questions that we didn't have time to answer today, go post those questions on the Facebook group, or go post them in the LinkedIn group. We can continue to try to help answer those as well as a lot of other people who are members of those groups can help answer those questions as well. I'm sorry I didn't get to as many questions as I would like to, but I just can't type that fast.
Hector: I'm pretty good at answering questions in the page, especially the Facebook page, because it goes straight into my phone. Join the Facebook group and ask questions there. We have people that answer questions for free all the time. It's a great resource, us as a group is a great resource.
Michelle: Yes, and some people said that there's other tools other than Transaction Pro Importer, and you're right. There are other tools that are available out there. That's just the one that I think maybe has been around the longest and some of us are more familiar with. You're right, there are some other tools out there. If you just Google it, you'll find some more tools as well.
Hector: Yeah, suggest the tools. Go into the groups and say, "I use this tool, and it's great for this." If there's a lot of interest, we'll bring it in.
Michelle: Yes. All right, we've got over 80% that have answered this poll question, and it's been open for two minutes as well. Let's go ahead. Somebody keeps saying, "Are you going to address the poll question?" The poll question for the CPE keyword was up for two minutes. I don't think we can relaunch a poll question once it's been done.
Hector: No, we can't [inaudible 00:58:56] rules say that they come up [inaudible 00:59:00], and they have to be there for at least a minute. Unfortunately, because people have to be paying attention through the whole hour in order to get, we only can put it up for a minute, and that's it, so unfortunately [crosstalk 00:59:11].
Michelle: If you missed it today, I'm sorry, but we offer CPE for all of these webinars, as well as if you go to the Intuit academy. You can get lots of free CPE there as well. If you missed it today, I'm sorry, but there's other ways that you can get some free CPE in there as well. We apologize for those of you that missed that. Intuitacademy.com, there's a lot of those out there. Intuitacademy.com. Somebody else says you snooze and you loose on CPE. You're right. You snooze, you loose. I'm sorry. Hector, I don't have anything else today. Thank you everybody for joining us for another QuickBooks Power Hour. I hope that you will join us again in the future. Anything else, Hector?
Hector: That's it, thank you very much.
Michelle: All right, thank you, everybody. See you later.