Accounting Finance:AMEX POS Reconciliation

Overview

All AMEX store sales figures are to be reconciled against the deposits to our CIBC account on a monthly basis. All variances should be accounted for and reported immediately to ensure that we are not missing any sales for each shareholder. To do the reconciliation you will need the following:

1. CIBC Monthly Statement which Nancy will download in both .pdf and excel versions in CIBC AMEX Statements
2. AMEX Monthly Statement and access online to confirm submission and settlements totals
3. FINAR006 Print POS summary report
4. GL 100100 Summary Report

Contacts

• Chase Relationship Manager - Oliver Manram T:416.940.6170, F: 416.940.6026 Oliver Manram
• Chase Helpdesk: T: 1-800.265.5158
• Toronto Customer Service: Tor-CanadaCustomerService@chasepaymentech.ca
• American Express Customer Service: 1-800-268-9877

CAP/Bank account numbers

• AMEX Cap Number 9324777847
• Bank account required when calling AMEX Customer Service #00900-38-52814

Running ASW POS Report

1. At the ASW main screen click System Management
2. Click on Query Manager and then Run a query request
3. Click FINAR006 Print POS summary report
4. Put the correct date range (example 080117 to 083117)
5. Customer range will always be 00000 - 99999
6. Change printer type to LP05 and once report is printed, keep the summary and take only the stores that show AMEX sales

Logging into AMEX Website

1. Open your supported web browser
2. Log on to https://www.americanexpress.com/canada/en/merchant/home/merchant_c.shtml
3. User ID is unipharm and password is test123
4. Click See All Payments
5. Make sure you put the correct date range

AMEX Reconciliation Details Worksheet

1. Click on the link to go to the details worksheet AMEX Bank Rec Jun17-Jan18 Details
2. Copy the previous worksheet and rename the tab with the month you are working on (example AMEX-SEP2017)
3. While on the AMEX website, download the monthly statement by clicking the icon on the upper left side (see screenshot below for details)
4. Report will open in Excel. Copy and paste it to your Amex reconciliation details worksheet in column AA. Cut and paste all merchant numbers from submission location to the cell to the right of 'Total. Embedded Vlookup formulas will find the merchant numbers and pull the data into the template. It is important all merchant numbers are moved besides the 'Total' cell; otherwise, the data will not be pulled.
5. Using the POS report generated from ASW, fill in the yellow cells on the worksheet
6. Change the date and manipulate all formula to make your worksheet work.
7. Any discrepancies should be included in your monthly reconciliation sheet AMEX Bank Rec Jun17-Jan18

GL 100100 Summary Report

1. At the ASW main screen click Financials and then Financial Accounting
2. Click Analysis Tasks and then G/L Analysis Tasks
3. Click Print General Ledger, hit enter
4. Put the correct period and GL number you wish to print and change the printer queue to LP05 (see screenshot below)