How to Set up a Three-way Reconciliation to Track and Bill Credit Card Expenses for Multiple Cardholders

Do you need to track and reconcile hundreds of credit card transaction each month for multiple cardholders? Do you bill your credit card expenses out to your clients? If you answered yes to both of these questions, then you have probably considered a three-way reconciliation, perhaps without even being aware of it.  But wait- is a three-way recon even possible?

Three-Way Credit Card Reconciliations ARE Possible!

Baystate Consulting’s Transaction Pro Importer (TPI) is a godsend for QuickBooks users, no matter which way you cut it. There are so many things that this utility enables and empowers you to do in QuickBooks. Recently, we figured out how we could leverage TPI to assist with reconciling our corporate cardholder accounts three-ways. What’s that, you say? A three-way recon?  Yep, that’s right. THREE ways.  Count ‘em:
1.  Cardholder-to-expense report
2.  Expense report-to-bank
3.  Unreported expenses-to-Credit Card Clearing
TPI helps with two out of three of these reconciliations. Read on to find out how. . .

Our Challenge

We are a strictly-services consulting firm.  Our challenge was that we had approximately 70 corporate cardholders charging $100-$200k/month of (mostly) travel expenses, approximately 10% of which were prepaid for future trips.  We pass these expenses through to our clients as trips are taken, so the prepaid travel expenses (which are typically the most expensive) are captured in our Credit Card Clearing account and carried over each month until the trip is taken and we can recognize the expenditure as we bill it out to our clients.  Our Credit Card Clearing account reconciliation was backlogged 9 months due to the tedium involved in tracking down specifically what the unreported transactions were.  We were spending over 15 hours a month including massaging the transactional detail for all of our users into a proprietary format so we could begin the reconciliation!  It is worth noting that we don’t need to track our corporate credit card balance because we pay our credit card in full every month.

Solution Part One – Expense Reporting System

First, we needed a way to check that our cardholders were getting all their expenses entered and not missing something that we could bill to our clients.  We solved this problem by purchasing a PSA (Professional Services Automation) system. It is cloud-based and has a mobile app that our users can login to and post their time and expenses from anywhere on the road.  We went with a NetSuite product called OpenAir, but there are a number of solutions out there under other names as well. Just google the afore-mentioned “PSA,” “time-and-expense management,” “Practice Management” or “Expense Management” and dozens of solutions will pop up. Concur is a big player in this space, and many corporate card programs even provide an online reporting tool for cardholders with their more robust product offerings.  We gained so much productivity with this implementation that we are now checking reported expenses against credit card activity on our bank website merely one week after they are reported.  Reconciliation target number one: Cardholder-to-expense report - Check!

Solution Part Two - Import the expense reports and Credit card activity to QuickBooks credit card accounts

Each month we export the reported transactions from our PSA to .csv and then use TPI to import them in fully-detailed format to our Quickbooks credit card accounts.  We do a standard Credit Card Charges import with TPI and the amount of detail we are able to capture utilizing our PSA’s Integration Manager really makes this whole process just work. Once the charges are populated, we can download the bank transactions and bring them in as opposing entries within each credit card account. We messed around with all the Web banking download options but ended up doing a simple .csv or Excel download for the following reasons:
•  Our credit card provider is Bank of America (BofA) and apparently, as of August 2015 the agreement between Intuit and BofA ended, so Direct Connect banking downloads are no longer an option.
•  Web Connect downloads were still available but would have to be done individually by cardholder. For us that was 70+ downloads each cycle.
•  Once you have the Web Connect file, you either match or add the transactions from the bank, but this is not a reconciliation.  You still have to go through the reconciliation process again, even though you have essentially reconciled the bank transactions once already. In our use case, this was a huge duplication of effort.
•  All that the Web Connect import accomplished was putting the non-reported transactions into the register, but that would have resulted in the transactions being double-entered when the cardholder finally reported the expense, because it would come through in the import from our PSA system.

So how could we easily see what credit card charges were not reported - in detail - so that we knew what we were hitting in our Credit Card Clearing account each month?  TPI made it possible to take our credit card statement download (a single file, instead of 70 files) and import it to Quickbooks as Credit Card Credits, essentially zeroing out the employee credit card sub-accounts except for the unreported transactions each month.  Reconciliation target number two: Expense report-to-bank – Check. 

Solution Part 3 - Reconcile Credit Card Clearing

The combination of setting up your Chart of Accounts, imports and reconciliation processes as outlined above then makes it easy to see exactly what transactions have gone unreported and whose cards they were charged on. Periodically, we audit the cardholder accounts and are able to follow up with our employees about specific transactions using the detail we captured from the bank side of the credit card import.

Here’s How the Accounting Works

Our employee credit card accounts are set up as sub-accounts to the main corporate credit card account:

Credit Card Clearing is set up as a Liability account:

1.  Employee charges a travel expense to company credit card:

     a.  Imported to QuickBooks via TPI Credit Card Charges import from PSA:

          i.  Debits expense account

         ii.  Credits employee credit card account in QuickBooks

2.  Employee charges a prepaid expense (such as airfare) to company credit card

     a.  Imported to QuickBooks via TPI Credit Card Credits import from Bank (not yet reported by employee):

          i.  Debits employee credit card account in Quickbooks

          ii.  Credits Credit Card Clearing account

3.  Accounting reconciles Employee Credit Card account to a zero balance at month-end:

      a.  Any transactions that came into the register from the bank import with no match are simply carried over until the employee reports the expense.

4.  Employee takes the trip and reports the prepaid expense:

     a.  Imported to QB via TPI Credit Card Charges import from PSA:

          i.  Debits expense account

         ii.  Credits employee credit card account in QuickBooks

        iii.  This will create the register entry that offsets the Bank entry, on the right side of the reconciliation screen.

Putting It All Together On The Balance Sheet

This is how it all looks on the Balance Sheet (Disclaimer: Balances are not real, nor do they all foot, in this sample)

Reconciliation target number three: Unreported transactions-to-Credit Card Clearing - Check.

Why Small Business Accounting needs Both Xero and QBO

So it's Friday night after a long, particularly demanding work week but I have been watching some comments floating around out there coming from the QuickbooksConnect event attenders last week that have raised some concerns.  

The tenor of the comments were writing Xero off as a market contender for Small Business Accounting here in the US.  Here are my concerns:

Our community very much needs a direct competitor against Intuit, who has been slumberingly lethargic for way too long.  Xero's coming on to the market scene here in the US has made Intuit stand up and take notice in a good way and we are all benefiting from that right now.  (If Xero hadn't come to town, would there have been an QuickbooksConnect this year?  I wonder. . .)  When starting my practice, I quickly embraced the ease of online accounting software, starting with Quickbooks Online in 2010.  I realize that makes me a newcomer compared to many of you, but my newness also affords me a fresh perspective.  As a ProAdvisor, I respect the functionality in Quickbooks desktop and even though I know enough to probably make Quickbooks desktop version 'stand on it's head,' I quickly got bored with it because it affords zero flexibility when trying to collaborate with other stakeholders in my clients' financial success - one of the core values of my practice.  I was an advocate for Quickbooks Online when most of my colleagues were hating on it and participated in the "experience design" focus groups for the Harmony development.

Then Xero came along.  After flying up and meeting the company in San Francisco 2 years ago, I was so energized and excited about another online software that I could deploy to service my clientele.  I was and continue to be totally psyched about what Xero has to offer small businesses. 

When recommending accounting software solutions to my clients, I think it's very important to remain completely vendor-neutral. Both platforms have strengths and weaknesses that need to be considered.  For example, Xero is a good fit for clients that are starting from zero accounting records and you have to completely reconstruct a set of accounts because you can pre-code everything in Excel and upload it - with a zing! (Ah, the satisfaction of watching the transaction count increment before your eyes with a confirmation message that hundreds of transactions have imported and are all reconciled - there's nothing like it!!)  But I did have one client who was pretty disengaged with her accounting and just wanted to 'dabble' occasionally with recoding miscellaneous small transactions, so we actually moved her back to Quickbooks desktop (and I happily transitioned that client on to another accounting firm) because the register view shows the account much more transparently than what happens with Xero.  And then there's payroll solutions.  I will admit that I had 2 Xero payroll clients this year and next year I'm taking them both off and putting them into payroll solutions that integrate with Xero. Intuit Online Payroll is the most robust payroll service for serving small business needs and the integration with QBO is beautiful, I have to say.

But let's talk about Inventory for a moment.  While Quickbooks never has really claimed it's inventory functionality as a selling point, the third party inventory solutions for Quickbooks desktop are costly, unwieldy and hard both to implement and maintain.  Xero doesn't do inventory either, but that's where the ecosystem comes in.  Right now there are at least three (3) vetted, robust inventory solutions that work with Xero and more are in development.  A quick check online in the QBO apps just now shows 3 companies, two of whom I have never even heard of.  Quick note on that topic - the legacy inventory app provider for Xero (who I am moving one of my inventory clients off of in 2015 because the newer players work better) just announced that they are focusing on developing for QBO and so not putting the resources into their Xero functionality.  To me, that doesn't inspire confidence - more failure to develop the main solution completely before moving to supporting a second platform.

Which is my main gripe with QBO - Intuit continues to fail to develop it's solution to completion, from a functional standpoint.  Since 2010, there have been two persistent, basic functionality issues: 1.  The reporting doesn't pick up memo detail from the memo field that shows in the main register view.  So to ensure that you get the fullest detail in reports (which Intuit software does excel at - detailed reports), you have to OPEN EACH TRANSACTION you are coding (how many clicks is that - 2? 3?) and put the detail in the description field down in the distribution lines of the transaction.  2. You can't round and group time billing transactions in QBO like you can in Desktop..  Which is why, for my business, I am still in Desktop and have to pay $100+ in extra fees per month for a hosting solution (so dinosaur! so 1990's!) so that my employees can log their hours into our company file as well.  If QBO wants to make friends with bookkeepers, why haven't they figured out how to do this yet so that we could all move our own company books into QBO and add weight to our recommending it to clients?  Especially should this be the case since their target demographic for the software is service-based businesses.

And don't even get me started on the failures of Harmony to deliver efficient functionality.  When I use it, I always feel like I'm fighting Interface: fancy icons, dropdowns and screen transitions - more like a Powerpoint presentation than an accounting solution, with just a touch of social media (the timelines - which I never even look at). I've signed up for the QBO Accountant Beta, so we'll see what that experience brings to light.

I must say I'm having a difficult time understanding why some are saying that 'the Xero work has dried up.' Could you be losing touch with what's going on out there? Here in Southern California, my phone has not stopped ringing from business owners looking for support in Xero and an interesting demographic is coming to light:  The calls I get for QBO help usually come from small business owners who are more naive when it comes to accounting.  They want to be led.  The people who come to me about Xero are young, globally inclined, savvy and have more innovative business models than the other side.  In Xero we are doing things like multi-currency, multiple warehouses in different countries, multiple e-commerce platforms and payment methods, franchising, consolidated financials, vendor self-service where they login and post their own bills, etc.  This is where I want to be: keeping my finger on the pulse of what drives entrepreneurs and innovators and leveraging the cloud and the global market to serve their needs.

And here's a question I would like to pose to Michelle Long: Since you "value your independence highly" and "are not paid to promote Intuit" but are no longer endorsing Xero, I would like to know just what other non-Intuit general ledger accounting solutions are you recommending for small businesses?

I don't know what is leading to Xero's current downslide in the US.  Whatever it is, I sincerely hope they figure it out.  Our industry needs another option than just Intuit.

From Quickbooks Online to Xero - Why We Chose to Convert Some of Our Clients

'OK' - Xero's version of the Easy Button

KS Bookkeeping & Technical Services has been a Quickbooks ProAdvisor since 2009 and in 2010, we started moving several of our clients over to Quickbooks Online.  We chose to work with Quickbooks Online because it was an easier way to stay on the same page with clients while doing their month-end or quarterly bookkeeping - that was a big priority for us.  But we began to see that Quickbooks Online caused almost as many problems as it solved for us in working with our clients.*  Then this Spring one of our technology advisors recommended Xero as another online bookkeeping option for our clients.  So in May we decided to go up to Xero’s offices in San Francisco and take a closer look.  We attended the Xero Cloud event and heard two CPA’s speak about how implementing Xero really helped them service their clients better by getting their tax returns filed on time, cash managed, and their clients excited about logging in to their accounting system and actually participating in the day-to-day accounting!  That got us motivated and we identified a handful of our Quickbooks Online clients for conversion to Xero.  We have finished conversion on several and have been using the converted files as demos to incent our slower-to-adopt clients to move on over as well.  In fact, we met with one of our clients last week – he wanted us to show him how to do coding in Quickbooks Online so that he could do some of the bookkeeping himself.  Groaning inwardly, we agreed, but also decided that we were going to give him a peek at Xero and show him how, with a little bit of our guidance from behind-the-scenes, he could get a much more accurate result by continuing to use our services through Xero.  The plan was to first do a coding tutorial in Quickbooks Online and then show him Xero and let him compare the two.  Well when we told him the schedule, he wanted to skip right to Xero and see the interface.  He was so impressed with the Dashboard which shows him exactly how much is in his bank accounts, the ability to communicate with us from right within Xero (via the Discuss tab on each transaction), and the ease of approving the behind-the-scenes coding (just click on ‘OK’ – how easy is that?  Kind of like the Easy Button. . . ) that he actually “came back to the table” and wants to continue working with us.

The 'Discuss' tab is on every bank transaction

We wanted to share this experience with our readers - not to bash Quickbooks Online - which we are continuing to use in supporting some of our clients, but just to share our happiness that there is another alternative out there, something more that we can offer to accommodate our clients’ bookkeeping personalities.  Stay tuned: in our next posting we will share some factors that help us determine that Xero is a better fit for our clients.

* [Note: To Intuit’s credit, we see that they have been improving the functionality in QB Online a bit more frequently of late.]