How to Reconcile Credit Card Statements

How to Reconcile Credit Card Statements

Description

In the middle of each month (around the 15th), ish receives two credit card statements spanning the previous 30 days. The charges outlined in these statements need to be categorized in the Credit Card Reconciliation Excel spreadsheet and sent to Brian Daly from Bottom Line Solutions along with a copy of all employee expense reports.

Steps

Receive the statements

Your journey begins with two pdfs…

  1. Receive the credit card statements from ish.
    1.  Around the 15th of the month, expect an email from ish with two pdfs of credit card statements.
    2. The first pdf will be the credit card statement from any/all chase credit card. (Examples found HERE.)
    3. The second pdf will be the credit card statement from ish and Chris’s Ink credit cards. (Examples found HERE.)
    4. If ish doesn’t reach out by the 15th, feel free to send him a reminder.

 

Populate the data

Before anything else, you’ll want to turn these pdfs into Excel documents. NOTE: This is currently the biggest bottleneck in the system. To streamline the system, the statements could be exported into Excels at the start or sr4 could seek software support that syncs Chase with QuickBooks.

  1. Export each pdf to an Excel spreadsheet
    1. In Adobe Acrobat select File>>Export to>>Spreadsheet>>Microsoft Excel Workbook
  2. Copy the charges from each spreadsheet to the Credit Card Reconciliation spreadsheet
    1. Line by line, copy and paste the charges from each statement spreadsheet into the tab titled “DATA” in the Credit Card Reconciliation spreadsheet (found HERE).
      1. Copy the posting date into column A
      2. Copy the description into column B
      3. Copy the amount into column C
    2. As you copy and paste, populate columns D – F with the pertinent data.
      1. Column D, Export Date – this is the date that the credit card statement was exported
      2. Column E, Person – this is the person who owns each card.
      3. Column F, Card – this is where you identify the card as Ink or Chase.

 

Categorize the data

Now that the line items are in Excel, you get to categorize them.

  1. Categorize each line item
    1. Use the dropdown field in column G (Account Name) to categorize each charge.
      1. Descriptions of each category can be found of any of the month tabs.
      2. If the amount is a negative value, leave the category blank.
      3. TIP: Sort the entire sheet alphabetically by column B (Description) before you begin. This helps to see similar charges next to one another and you can easily copy and paste past Account Names.
    2. Ensure that reimbursable project costs are in Harvest.
      1. As you categorize charges as “Reimbursable Project Cost,” be sure to check the expense report in Harvest to ensure that they've been loaded properly.

 

Create the month tab

Now that the line items are categorized, you’ve just got to craft a tab with this month’s data.

  1. Copy a "Month" and a "Month Ink" tab to the end of the Excel.
    1. Right click on the “September” tab and select “Move or Copy.”
    2. Select “(move to end).”
    3. Click “Create a copy.”
    4. Select “ok.”
    5. Rename the tab with the name of the current month.
    6. Repeat steps 1 – 5 for the “September Ink” tab.
  2. Update the Month tab’s date range.
    1. On the current month’s tabs, change cells B2 and B3 with the start and end dates for this statement.
    2. NOTE: cell B3 must match the export date on the DATA tab.
    3. Once these cells are updated, the tabs will use formulas to automatically populate the categorized amounts.

 

Save and send

You’re almost done…

  1. Save the Excel with today’s date.
  2. Scan a copy of all employee expense reports from the past month.
  3. Send the spreadsheet and expense reports to Brian at Bottom Line Solutions.