Importing those voluminous monthly credit card transactions

Are you like many customers dreading the recording of the American Express or some other credit card bill every month?  Turning those pages and pages of charges into either a journal entry or accounts payable (AP) transaction can be a time consuming and frustrating task.  Generally one of two scenarios takes place:

  1. Assign a general ledger (GL) account to each expenditure then total the debits and credits for each GL account and create a journal entry. This method is convenient and quick, records all of the expenses to the GL but gives you no insight into vendor spend or individual amounts of the transactions.
  2. Assign a GL account to each expenditure then total the debits and credits for each GL account and create an AP transaction, which is posted to the credit card vendor. This is also quick, provides just a bit more detail and allows you to apply the payment via the purchasing module but also gives you very little insight into vendor spend or individual amounts of the transactions.

You could do one of two things to give you more detail about your credit card expenditures each month:

  1. Integrate each line item as a separate transaction, using Integration Manager, and post to each individual vendor.  This would give you the detail you are looking for and an accurate vendor spend. Of course, the downside is that you’d have to setup vendor records for all transactions that don’t currently have a vendor on file.  And assigning vendor IDs to that list of transactions may be a daunting task.  This is more work on the front end but will result in better purchasing analysis in the long run.
  2. Integrate each line item into one AP transaction to the credit card vendor, using Integration Manager, utilizing the distribution reference field to record where the expenditure was made.  The transaction description from the credit card vendor could be integrated to the distribution reference field.  This won’t give you accurate vendor spend but it will give you the transaction detail (rather than the lump sum spent in each GL account) and some reference to where the expense was incurred.

 

recording1

 

recording2

Of course this is so much easier if you can get an Excel, *.csv or *.txt file from your credit card vendor each month to paste into your credit card transaction template.  The import file should look something like this:

recording3

The good news is if you choose option one above, GP2016 has a new feature where you can pay open invoices by credit card the same way you create a computer check batch.  You will need to setup the credit card and assign the credit card vendor to the credit card as well as the appropriate default accounts.  When you post your credit card payment batch the individual vendor invoices show as being paid by the credit card.  The credit card vendor will show one open invoice for the total amount of all invoices selected – to be paid by either check or manual payment.

recording4

Keep up to date with the workings of Microsoft Dynamics ERP by subscribing to our Dynamics Community News publication.  If you have questions,  contact our Dynamics experts 855-437-7202.

by Tracy Jo Dance for RSM