Difference between revisions of "Accounting Finance:AMEX POS Reconciliation"

From uniWIKI
Jump to navigation Jump to search
m
 
(26 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
==Overview==
  
==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.
 +
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:
  +
#CIBC Monthly Statement which Nancy will download in both .pdf and excel versions in {{unc|\\SUPERSERVER\Accounting\common\AR\Banking\Amex\Reconciliation\CIBC AMEX Statements|CIBC AMEX Statements}}
  +
# AMEX Monthly Statement and access online to confirm submission and settlements totals
  +
# FINAR006 Print POS summary report
  +
# GL 100100 Summary Report
   
 
==Contacts==
  
==Contacts==
Line 9: Line 13:
   
 
==CAP/Bank account numbers==
  
==CAP/Bank account numbers==
−
*AMEX Cap Number 9324777847
 +
*AMEX Cap Number '''9324777847'''
−
*Bank account required when calling AMEX Customer Service #00900-38-52814
 +
*Bank account required when calling AMEX Customer Service '''#00900-38-52814'''
  +
  +
==Running ASW POS Report==
  +
# At the ASW main screen click '''System Management'''
  +
# Click on '''Query Manager''' and then '''Run a query request'''
  +
# Click '''FINAR006 Print POS summary report'''
  ⚫
# Put the correct date range (example 080117 to 083117)
  ⚫
# Customer range will always be 00000 - 99999
  ⚫
# 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==
  
==Logging into AMEX Website==
Line 23: Line 35:
 
# Copy the previous worksheet and rename the tab with the month you are working on (example AMEX-SEP2017)
  
# Copy the previous worksheet and rename the tab with the month you are working on (example AMEX-SEP2017)
 
# While on the AMEX website, download the monthly statement by clicking the icon on the upper left side (see screenshot below for details)
  
# While on the AMEX website, download the monthly statement by clicking the icon on the upper left side (see screenshot below for details)
−
# Report will open in Excel. Copy and paste it to your Amex reconciliation details worksheet in column AA.
 +
# 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.
−
# Using the POS report generated from ASW, fill in the yellow cells on the worksheet
 +
# Link the Total Discount and Total Charges cells to the corresponding cells from the AMEX report in the summary section at the top.
  +
# Using the POS report generated from ASW, fill in the yellow columns on the worksheet under '''General Ledger, Manual Entry'''.
−
# Change the date and manipulate all formula's to make your worksheet work.
  
−
# Any discrepancies should be included in your monthly reconciliation sheet {{unc|\\SUPERSERVER\Accounting\common\AR\Banking\Amex\Reconciliation|AMEX Bank Rec Jun17-Jan18}}
 +
# Keep a note of the discrepancies. Leave yourself an indicator. Any discrepancies should be included in your monthly reconciliation sheet {{unc|\\SUPERSERVER\Accounting\common\AR\Banking\Amex\Reconciliation|AMEX Bank Rec Jun17-Jan18}}
   
 
[[File:Amex Download Report.JPG|400px]]
  
[[File:Amex Download Report.JPG|400px]]
   
−
==Running ASW POS Report==
 +
==GL 100100 Summary Report==
−
# At the ASW main screen click '''System Management'''
 +
# At the ASW main screen click '''Financials''' and then '''Financial Accounting'''
−
# Click on '''Query Manager''' and then '''Run a query request'''
 +
# Click '''Analysis Tasks''' and then '''G/L Analysis Tasks'''
−
# Click '''FINAR006 Print POS summary report'''
+
# Click '''Print General Ledger''', hit enter
  +
# Put the correct period and GL number you wish to print and change the printer queue to '''LP05''' (see screenshot below)
⚫
# Put the correct date range (example 080117 to 083117)
  
  +
# Use the data from this printout for the AMEX Reconciliation report
⚫
# Customer range will always be 00000 - 99999
 
  +
⚫
# Change printer type to '''LP05''' and once report it printed, keep the summary and take only the stores that show AMEX sales
  
  +
[[File:GL Printing 100100.JPG|400px]]
  +
  +
==CIBC AMEX Bank Statement & Visa/MC Transfers==
  +
#Print out the AMEX Bank statement from the ''''CIBC Bank Statements''' folder
  +
#Use the data from the printout for the Reconciliation
  +
  +
  +
  +
#Locate the file '''Visa & MC Transfers Feb17-Jan18'''{{unc|\\SUPERSERVER\Accounting\common\Bank Reconciliations\Visa-MC Transfers\Visa & MC Transfers}} and print out the corresponding month
  +
#Use the data from the printout for the Reconciliation
  +
  +
Compare the data from the two printouts and note any discrepancies.
  +
  +
==AMEX Reconciliation Worksheet Procedures==
  +
#Change the dates to the corresponding month
  +
#Enter the ending balances from the G/L and the Bank Statement into associated cells
  +
#Copy over the list of stores following the existing format along with its associated discount charges. This will total up to the discount charges for the month. Leave a note to Nancy to move this sum to G/L 220154
  +
#Below the Discount charges Sum, note any discrepancies. A good rule of thumb is to include any non-timing issue related differences between the '''CIBC AMEX printout''' and the '''Visa & MC Transfers''' printout. As well, include discrepancies arising from ''future'' timing differences. Look to the G/L printout for any discrepancies that might help balance the Bank Balance to the Adjusted G/L Balance
  +
#Fill in the balances, deposits and transfer amount in the yellow cells below. Past Adjustments can be found on the G/L Printout, using the sum of credits excluding amount for Total Discount Charges.
  +
#By the '''Reconciliation''' cells, enter the value of the prior month adjustment under ''Bank -> Current adjustments''. Enter the value of the current month adjustment under ''G/L -> Current Adjustments''. Enter Past adjustments under ''G/L -> Past Adjustments''
  +
#After confirming all amounts under '''discount charges''', create a manual invoice for each store, using item number 02081271. Record the invoice number under the associated column and send manual invoices to each store.
  +
#Attach all printouts and send to Nancy for signature
   
 
[[Category:Workflows-Finance]]
  
[[Category:Workflows-Finance]]

Latest revision as of 13:25, 9 November 2017

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

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. Link the Total Discount and Total Charges cells to the corresponding cells from the AMEX report in the summary section at the top.
  6. Using the POS report generated from ASW, fill in the yellow columns on the worksheet under General Ledger, Manual Entry.
  7. Keep a note of the discrepancies. Leave yourself an indicator. Any discrepancies should be included in your monthly reconciliation sheet AMEX Bank Rec Jun17-Jan18

Amex Download Report.JPG

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)
  5. Use the data from this printout for the AMEX Reconciliation report

GL Printing 100100.JPG

CIBC AMEX Bank Statement & Visa/MC Transfers

  1. Print out the AMEX Bank statement from the 'CIBC Bank Statements folder
  2. Use the data from the printout for the Reconciliation


  1. Locate the file Visa & MC Transfers Feb17-Jan18[1] and print out the corresponding month
  2. Use the data from the printout for the Reconciliation
  Compare the data from the two printouts and note any discrepancies.

AMEX Reconciliation Worksheet Procedures

  1. Change the dates to the corresponding month
  2. Enter the ending balances from the G/L and the Bank Statement into associated cells
  3. Copy over the list of stores following the existing format along with its associated discount charges. This will total up to the discount charges for the month. Leave a note to Nancy to move this sum to G/L 220154
  4. Below the Discount charges Sum, note any discrepancies. A good rule of thumb is to include any non-timing issue related differences between the CIBC AMEX printout and the Visa & MC Transfers printout. As well, include discrepancies arising from future timing differences. Look to the G/L printout for any discrepancies that might help balance the Bank Balance to the Adjusted G/L Balance
  5. Fill in the balances, deposits and transfer amount in the yellow cells below. Past Adjustments can be found on the G/L Printout, using the sum of credits excluding amount for Total Discount Charges.
  6. By the Reconciliation cells, enter the value of the prior month adjustment under Bank -> Current adjustments. Enter the value of the current month adjustment under G/L -> Current Adjustments. Enter Past adjustments under G/L -> Past Adjustments
  7. After confirming all amounts under discount charges, create a manual invoice for each store, using item number 02081271. Record the invoice number under the associated column and send manual invoices to each store.
  8. Attach all printouts and send to Nancy for signature
Retrieved from "https://owl.unipharm.com/mediawiki/index.php?title=Accounting_Finance:AMEX_POS_Reconciliation&oldid=9837"