In our experience one of the big things small businesses miss when managing their bookkeeping and accounting processes is, effectively integrating their bookkeeping tools.
They may well have advanced software such as QuickBooks Online or Desktop but have rarely successfully integrated it with related applications and services. Examples of this include payment gateways, payroll, and tax calculation tools. They can also include software applications such as Shopify and Amazon ecommerce, Square Point of Sale Software, Concur expense reporting software, and Clockify time tracking software.
QuickBooks Integrations Are Often a Big Miss
The result is inaccurate data, and inaccurate QuickBooks bookkeeping. Oftentimes a lot of additional work is created. More staff than they would need if their accounting processes were properly integrated and streamlined. We see this often also resulting in the late and overpaying of taxes as well as poor business decisions being made as they are based on an inaccurate understanding of revenue and profitability.
QuickBooks Integrations – Our Experience and What We Often Encounter
Talk about some problems that I typically see that we can solve. One thing that I’ve, that I’ve noticed that clients can have multiple third-party applications. For example, they’ll have ADP for their payroll, they’ll have Avalara for the sales tax calculation and, and tax reporting am Amazon online store sales. And they use Shopify merchant service payment processor. And a lot of times these clients have no knowledge of how to incorporate the data into their accounting software. From these third-party applications, we assist clients with integrating those third-party applications.
Let’s start from the beginning. When you get calls from people that lead to needing to help them with those types of, so I guess we’d call it software integration with QuickBooks issues, typically what do they perceive they need when they first start talking to you? What would be some examples of that?
Well, in cases with these particular people, for instance, they had ADP payroll, but they weren’t integrating that at all with QuickBooks. They didn’t even know they could just man manually entering this stuff. And he was doing it incorrectly.
When they first talked to you, they were interested in your services, but what was their perception of their problem? Why did they want somebody to happen with the bookkeeping? What was their starting point?
I Can’t See Where I Am Financially
That I’ve got my accounting in two different softwares, so I can’t honestly see where I am financially. I have no clue where I am, what the health of the business is.
He was using QuickBooks Online and Sage he had up from January to May in QuickBooks online. And he had from May to up till the time that I took him over in August was in Sage. And he said, I really wanna go to QuickBooks Desktop and have everything all in one accounting software.
So what, what was the process that got from that point to the point where you were the, then also helping them with some other software integration? How did that play out? What were the steps? What problem did you solve first and where did that lead to?
When Tax Is Not ‘Calculated’ It’s A Problem
The first problem was his sales tax calculation. They weren’t calculating sales tax, they were just, they were charging 9.75% sales tax to every customer invoice, which was incorrect because they didn’t have a way to calculate accurately the sales tax.
So what, why would calculating it 9.75 be incorrect?
Because not everybody in California is 9.75. It’s, it’s all over the place. I mean some people are 8.75, some are 9 2 5. He was just using his current location sales tax rate, which was incorrect. So that was the first thing I noticed. We needed to get him set up with Avalara. So he is accurately calculating sales tax for California. He was overpaying tax. That’s true. But it wasn’t coming out of his pocket, so to speak, because he was charging the client.
So the net of it then is that he was overpaying tax, but it wasn’t costing him anything because he was overcharging the client in some instances.
Even though he was overcharging the client, he was seeing no benefit from that other than his price was higher than it needed to be.
Uhhuh <affirmative>. And the problem with that is if he were ever audited, look what he’s done. He’s charged all the wrong sales tax and paid California the wrong sales tax based on those sales. Well that matters. California would, would just come down on him like a ton of bricks.
Avalara Calculates Tax Correctly And Keeps Those Calculations Up To Date
And the bottom line then is that Avalara will just take care of all those calculations automatically.
Right. It automatically calculates that and correctly and does the tax returns correctly based on the ship two location. And not only that, the other advantage with Avalara is by doing what he’s doing and he’s selling everywhere, Florida, Georgia, all these different places, he wouldn’t know if he was nearing that nexus level for a state. And every state has something different. I think Georgia says if you sell a dollar here, you have Nexus
The, the example that you just gave where if you just, if you sell a dollars worth, you’re gonna be liable for sales tax. Where mm-hmm. <Affirmative>,
I believe, I’d have to look at it. It’s been a while since I’ve looked at it, but I think it was Georgia. I think Georgia is one of those states that they don’t care. There is no threshold if you sell anything in their state, you have to set up an employer account and you’re required to collect and remit sales tax.
The Amount You Sell, Can On Its Own, Create Nexus
So what you’re saying then is that some states you reach Nexus purely based on the amount of revenue you do in the state, not necessarily having actual people or offices in that state.
Huh. Okay, I didn’t know that.
Yeah, every state has sales tax Nexus. If you sell a certain amount and you’ve brought that into our state, then they have a threshold of what that is. And then you are now required to obtain a, a seller’s permit and have an account set up and then collect a remit sales tax.
So I thought that Nexus was based on having presence in the state. And you’re saying that’s not necessarily the case? Yeah, not anymore. Used to be. But they’ve cottoned on. So what you did there then is you started with helping the client to get on top of their books because they were keeping their books on two separate software applications and you help them to migrate and have one software application.
<Affirmative> and what was that?
That was QuickBooks Desktop.
What you said they had is they had QuickBooks Online and Sage. Why not use either Sage or QuickBooks Online? Why did you go to a separate system of QuickBooks Desktop?
QuickBooks Desktop Is Better for Job Costing
QuickBooks Online really isn’t the best software to use for somebody that does what he does as a contractor. It’s not ideal. Job costing and report functionality are the two main things that is not great if you’re a contractor and you try to use QBO.
Right. QuickBooks online. Okay. Got it. Mm-Hmm. <affirmative>. So you solved that problem, but then you ran into that secondary issue of, well, the sales tax needs to be comprehended as well.
Right. When I found out they were using job numbers and the sales team was actually the one sending out the invoices to the clients, then I look to see, well, how are you calculating sales tax job nemesis? I wanna do that.
So you’re following, you’re following the process and seeing how they’re doing things and realizing there’s smarter ways of doing it that Right. Connect to do it accurately. Going back to our, our subject today, which is the integration aspect of the, the, the help that the clients need. What, what was the nature of the work or the challenge associated with, you’ve got QuickBooks desktop, you’ve got Avalara, press the button and it all works. Or is it not as simple as that?
No, it’s not as simple as that. You have to go set up the account with Avalara and you have to set up your tax return, tell ’em what type of return you need to file with California. So you have to know what tax return you need to file with California and set all of that up in Avalara. And then you have to then bring it in and install it on the server that QuickBooks desktop is on and then do that integration with QuickBooks and Avalara.
So give us a sense of the complexity of that, the skill level needed to be able to get all that to work. And you know, how mu how much work is it, is it like half an hour’s work and for somebody that knows QuickBooks or what’s the skill level need to be able to pull that off and how much work is it roughly?
It took about a week. You, you also have to work with the Avalara engineers to do that and it does take a pretty high level of skill to be able to do that. Avalara will not set that up for you.
So in your experience where you have talked to other bookkeepers, maybe you’ve interviewed them for a job or you’ve talked to ’em for other reasons, how often would you say it’s likely that you’re gonna find a bookkeeper that is able to do that kind of work?
You’re not, most of the bookkeepers in the other accountants I’ve talked to don’t, doesn’t even know what Avalara is.
Huh. And given that Avalara is the market-leading tax application, especially associated with QuickBooks, it’s surprising to me that the way you characterize it, that so few bookkeepers are aware of it or can do
It in QuickBooks. You can go in and you can set up sales tax. QuickBooks will calculate sales tax. You just tell it what sales tax rate you wanna use and it’ll just calculate it. You know, I’m gonna use 7.25%. That’s what it is. Okay. Ohio’s tax rate is 7.25%. So anything I sell in Ohio, cuz I have Nexus here, I’m gonna make it 7.25%. And a lot of people probably just use what, what QuickBooks has available to ’em, not realizing that you’re also not calculating it. If you do sales outside of Ohio, do you know what the threshold is for every state in the United States? Do you know what each state’s threshold is? Are you, are you tracking that Avalara does more than just calculate that sales tax?
If you’re in QuickBooks, it’s just the flat one, flat rate. But if you gotta Avalara it knows every district, every city.
So in summary then, what you’re saying is that in some states you can get away with just knowing the flat tax rate for that state and it stay the same wherever anybody is. But if somebody is in California or in a state where it’s not that simple or doing business across multiple states, then that’s where Avalara really adds value.
Yeah, that’s correct. Some of these people just don’t know what they don’t know. If you’re selling to other multiple states, yeah, you don’t have Nexus there. You’re, you’re correct about that, but at some point you could.
So that’s Avalara. Was there other integration or the other software that you’ve helped to integrate?
Integrating ADP PayRoll And Why
Yeah, ADP payroll for your financial reports, you obviously wanna see officer compensation. How much was that? When you look at a profit and loss, you wanna be able to see what’s the officer compensation, what is the payroll tax expenses, IRA deductions, health insurance, the health and dental and medical and all that stuff that they do with their employees. And none of that was being tracked and there was no good data for that. So ADP is a major payroll company. So they had something called a general ledger that you could go in and create a general ledger. What are the accounts that are being used in pay in ADP and what accounts do I want that same information to come into QuickBooks. You create that general ledger file and then every time they run payroll, I go create a, a ledger file that I can bring into QuickBooks every time they run payroll.
So I, I create it, I export it and then I go copy it on to the server and import it into QuickBooks and it comes in and brings all of the information and puts it exactly where I need it to go. That took me a, a couple days to get that all set up and then I tested it so that took a couple days. And that again is gonna be a software setup. It’s not so much accounting it, it did have a little bit of accounting into it because you had to go look at the accounting file and say, where do I want to see these things? Where do I wanna see this payroll information and how do I wanna see it on a profit and loss? How would the owner want to see this on the profit and loss? And then go set up your chart of accounts in the accounting system to be able to bring that stuff in. So that’s the accounting piece of it. The rest of it was all basically software driven. It’s not real likely that you’re gonna have somebody that just does accounting that is going to have that skillset as well.
So if a typical bookkeeper was faced with that issue, what would their reaction typically be? What, what would they do if they, you know, would they basically have no idea what to do or what, you know, how would they handle it?
I think probably with all of these things like ADP or Avalara, whatever, they would probably rely on their IT department to kind of work these things out for them. You know, to go set these things up, these accounts up and get ’em all set up. And probably most owners would too. They, they would just rely on their IT department to go assuming
They’ve got an IT department of course,
Which is the problem he had cuz there’s no IT department number one and the bookkeeper they’ve got didn’t know how to integrate those two programs. So the problem was they were disparate and the information going to QuickBooks wasn’t right. So that’s where someone like Linda, her knowledge of being able to do integration actually helped the client a lot because she was able to get that integration and get everything going into QuickBooks. No bookkeepers couldn’t do it cuz the one they had couldn’t do it.
Knowing What Can Be Integrated Is Part Of the Battle
So it strikes me part of the added value as well is just knowing that there’s integration to be done in the first place,
Right? And that, that’s where I think we, we have an advantage with some of these clients because a lot of clients are unaware of what business tools and applications that are out there that could really greatly enhance their accounting process and their financial reporting. And they’re just limited to only what their, their accounting software can do for them out of lack of knowledge or lack of skilled employees who can set it up and integrate it for ’em.
So who was doing the bookkeeping for them previously?
Internal full-time bookkeeper.
So in that particular case they had an internal full-time bookkeeper, but even though they had a full-time bookkeeper, they weren’t able to do these things. You are still working with them to get on top of the books if they had all these problems when you started. The thing that jumps out at me is to ask the question, well why isn’t the bookkeeper that they’ve got sort these things out? If they’re a bookkeeper, surely, they would know how to do all this.
Right? And that’s what I’m saying, it’s either for just either lack of knowledge that there are other applications and tools that you can use to, to make your life easier. You’re doing a lot of manual work that you don’t need to do. If there’s an integration out there, then go get it and integrate it.
So did you take over from that bookkeeper or do you still, are they still there? What’s the setup?
Working With In-House Bookkeepers
I didn’t take over for that bookkeeper. I work in conjunction with that bookkeeper to do the more advanced level of things. Like when they have things like that, then I set those up and get those integrated and then I work with his team to train his team on how to log that stuff in QuickBooks. I oversee what they’re doing, and I do the advanced things.
Do you have another example but with a different integration with a different software?
Shopify and QuickBooks Integration
Yeah. And that’s with another client that used Shopify. They sell clothing, they have an online store and they used Shopify as their merchant service provider.
When I first got them, their books were just kind of a mess. They didn’t have anything much in there and what they did have in there was incorrect and they didn’t know how to log the sales that were occurring inside Shopify. How do they get those inside to QuickBooks? And they’re using QuickBooks online.
So what I did with them is I went into Shopify and looked and seen how their sales were being calculated inside there. And do they have an integration with QuickBooks online? Because there’s a lot more integrations with QuickBooks online than there is desktop. And sure enough they had an integration. So I work to set that up to integrate with QuickBooks online so that when sales occur they automatically come across and go into QuickBooks Online and log that for him.
He doesn’t have to log any of that manually anymore. That was kind of on the level of Avalara. That one, the Shopify was a little difficult. That took me a week. Well I think most bookkeepers, if they didn’t have those skills, they would be kind of terrified to try that. He said that now I wanna sell on Amazon and we’re starting that. So I need to have you look at how to set up Amazon so that we know how to log that and bring those sales into QuickBooks online. That’s gonna be very similar. It’s, it’s gonna be similar to Shopify’s challenging. It’s a little challenging to set that up with Amazon. I have another client that had, was using Square. He has a bookstore, a local bookstore, and he use a square in his store. Square does not integrate. I don’t even think it even integrates with QuickBooks.
Square PointOf Sale Software Does Not Integrate With QuickBooks
Online Square just doesn’t integrate period. So that was very, very challenging. That one you have to go into Square and design a report that gives you what you need and then you download that into Excel and then even then you have to manipulate that file and Excel to allow for when he, people would use a gift card or they’d purchase a gift card and he offered tons of discounts. Square didn’t deal with that very well. When you created customer invoice, you have to say this sale was $500, I had $48 in discounts. They bought a gift card and a gift card is basically now a liability for that company. You, you did make a sale, but it’s a liability because that person hasn’t used it yet. So all of that had to be set up in QuickBooks. And how do you log this properly to show it where it needs to be for the sale for that day and have it match up to what Square has including the sales tax. Cuz it has to go in QuickBooks. If you don’t put it in QuickBooks, you don’t know how much sales tax you owe. And that’s where he would use QuickBooks to determine what’s my sales tax liability. And then he would have to go file his, his sales tax return and make those payments. So sales tax payments, that probably took me about three weeks. That was probably one of the most difficult things I’d worked on. Any
Concur Expense Reporting Software and QuickBooks Integration
Concur? Yeah, I did that. The reason that, that he asked me to set that up for him is that, you know, he had 10 99 contractors that are out in the field working on these jobs and he wasn’t entirely sure that that’s where they were. You know, they, they were turning in time that I, I worked on this job on for five hours on this day and on Tuesday I worked seven hours at this job and he wasn’t entirely sure that what they, they were turning into him that they were working at these jobs that they really were. So one of his main reasons for looking for a software that would track time was also a software that would track GPS and where were they? If they were gonna turn in these five hours at this job, he could look at the GPS and see if they were at that location. That took about two weeks. Concur is a, a very robust software. There was still some things, I mean he’s still kind of reached out to me several times and he’s still doing that to this day. He’s still doing that.
But associated with that particular integration, he’s still,
But so when he is reaching out more on that integration for what exactly what’s the nature of the problem that he needs help with?
Just different problems that they have you know, with the software. You know, and and they also, that’s also where his employees would submit their expense reports. They would submit their expense reports and then you know, they need to be approved and then it also pays you, you can pay the employee and it pays them from there. If you approve it and say, okay, yeah, I wanna set it for payment, it also does that same thing and it sets it for payment.
Right. So he is reaching out to you on an ongoing basis for that, but for what exactly?
There are just various problems. I’ve got an expense that I approved and it’s not showing up in here or just different problems that he has.
Any other examples you can think of?
Clockify Time Tracking Software and QuickBooks Integration
I just set up Clockify that was so needed because they have their, they are manufacturing, they are pr close to 4 million a year. They have 13 employees. They had all of their employees will handwrite their time sheets just on paper. They would handwrite their hours each day. One of the challenges was they work on different jobs and everything needs to be job costed for them. So somebody can write down, I worked four hours on this job and two hours on that job. Part of the problem was you can’t read their handwriting. They can’t add, so if they work 10-hour days, that should all add up to 10 hours. You can’t say that you worked eight hours on this job and you worked four hours on this one and your total for the day is 10 hours. Okay, what’s wrong? So I was constantly, constantly having to go back and ask these people to fix their time sheets and tell me what you really worked and what job was this really applied to.
So I thought, okay, we need to come up with a better system for two reasons. One is I had asked them repeatedly to have somebody over there look at those time sheets before they handed ’em to me so that I wasn’t doing all of that and having to hand them back and they just weren’t doing that. They were still giving them to me. They still had tons of errors in almost every single one. So I thought, okay, this needs to be automated. The other thing that made it so time-consuming was that, well let’s say in that week’s time on Monday they worked for Eaton. On Wednesday they worked for Eaton and Friday they worked another hour for Eaton. Well, okay, when I’m putting that in QuickBooks and I’m logging and I’m, I’m entering their payroll, I’m just entering the total hours they had for the entire two-week period for Eaton. I had to manually from that manual timesheet, I had to manually on a calculator add up one hour here, two hours here. I’d have to look through the whole two-week time period and look for all the Eatons and look for all, you know, I, I’d have to add all of those jobs up manually.
So it so costing the client about nine hours every two weeks of unnecessary cost.
Yep. So the solution is I found a software, the Clockify software where the people go in and I enter the jobs in there. I enter all of their current jobs into that software. So when somebody wants to, you know, say, okay, on Monday I work this many hours, I’ve got it set where they have to enter a job or they can’t enter their hours. So if they say they worked pick your job and you worked how many hours and you worked on, on that job, that’s what they’re being paid for and that’s the job it’s being assigned to.
So that’s a clock five me. That’s the, that’s the application you’re talking about? Yeah. Did that eliminate the whole nine hours?
Yes it did. So now I just go pull the reports. What did somebody work?
So about a, about a week of work and again, same sort of thing in terms of the skill level needed. You’ve gotta be an IT-ish kind of person that can handle integrating software.
Which is typically not the skillset the average bookkeeper has.
Got it. Alright, so this is great stuff Linda.
QuickBooks Integrations – Is Your Business Optimized?
Do you have an in-house bookkeeper with the skills needed to identify opportunities to save time and money by integrating your accounting software and processes? Are you sure you have accurate data in your bookkeeping systems?
Do you use QuickBooks Desktop or QuickBooks Online but have a disparate payment gateway, expense, and or time-tracking software? Do you have payroll and or tax calculation software that is not integrated with the rest of your systems?
Bring your QuickBooks Integrations up to date by working with Columbus Bookkeeping & Accounts Services.
Accounting Firms – What Are The Costs