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.