Reconcile Cash Reconciliation Spreadsheet

Contents

The cash reconciliation spreadsheet should be reconciled each day for the current month and for a final reconciliation on the 1^st business day of the previous month. The spreadsheet should match NetSuite and the bank. Each month has a detail tab that lists all of the payments and a pivot table tab.

Daily Reconciliation

The spreadsheet should be updated with the post date as payments are processed and reconciled at least once per day.

NetSuite Reconciliation

Once payments are processed, enter the current month’s amounts in the NS column on the current month’s pivot table tab from the Customer Payment by Date saved search
- Run saved search
  - Quick Links -> Customer Payments by Date
  - Update filter for the same date range as the dates on pivot tab of the spreadsheet (column A)

Enter the amount(s) from the Sum of Total column on the saved search into the NS column on the spreadsheet

Confirm the total from the saved search matches the spreadsheet total (cell G31)

Refresh pivot tables
- Select any cell in the 1^st pivot table and select Refresh

Column I of the spreadsheet will show an X when the day matches
If there is not an X, research each day
- Use the Customer Payment by Date saved search and pivot table to determine the method(s) that doesn’t match

Run the Customer Payments by Payment # saved search
- - Quick Links -> Customer Payments by Payment #
Filter saved search for the date and payment method

Filter the detail tab of spreadsheet for the same post date and the type (aka method)
- Match payments from saved search to each line

- Determine the payments that do not match

Drill down into the Customer

Open the payment(s) and customer refund(s)

Match payment(s) and customer refund(s) to remittance provided by the customer
- Correct the error
  - For more complicated errors refer to: Resolving Remittance Errors and Issues.docx

Bank Reconciliation

Refresh pivot tables
- Select any cell in the 2^nd pivot table and select Refresh

Column O of the spreadsheet will show an X when the day matches

If there is not an X, research each day
- Open the incorrect day’s Previous Day Composite report saved from bank
- Check to see if a line from the bank report matches the difference amount
- If there is no match, filter the detail tab of spreadsheet for the same bank date
- Match payments from bank report to each line
Correct the spreadsheet for the incorrect line
If every line is correct, the error may be on the Cash Activity spreadsheet. Let the Accounting Manager know about the difference

End of Month Reconciliation

Add undeposited credit card transactions

Filter detail tab by the Credit Card type

Add lines for Cybersource amounts showing on Customer Payments by Date saved search and not on the detail tab
- Should be 1 or 2 business days
- Leave the bank date blank

Confirm pivot tables match

On pivot tab, ensure all lines have an X in columns I and O
If there isn’t an X in a row of column I (post date), follow the steps from the daily reconciliation
- Starting on page 3
If there isn’t an X in a row of column O (bank date), follow the steps from the daily reconciliation
- Page 6

Confirm all payments are processed/deposited

Filter by Post Date for only blank cells

Process any payments not posted
Filter by Bank Date for only blank cells
Follow up with customer(s) on any payments posted but not deposited in bank
- All for 2 business days for credit card payments to be deposited

Create New Tabs

On the 1^st or 2^nd business day of the month, new tabs needs to be created for payments for the new month.

Open the current year’s spreadsheet
- S:\Accounting\Accounts Receivable\Team Folder\YYYY Cash Reconciliation Spreadsheet
Select both tabs (detail tab and pivot tab) from the previous month by holding down tab
Right click on one of the selected tabs
Select Move or Copy

Select the Create a copy checkbox
Double click on each tab to change the name to the new month

On the detail tab, delete all of the rows with the Bank Date and Post Date from the previous month
- Do no delete the rows that have a blank Bank date or Post Date
- Do no delete the rows that have a Bank date or Post Date for the current month

On the Pivot tab, there are 2 pivot table that need to be updated
Select any cell in the 1^st pivot table
Select Analyze
Select Change Data Source

Select the newly-created detail tab for current month
The rows in the new tab will be selected

Select OK
Update pivot table filter
- Select Post Date filter
- Hover over Date Filters option
- Select Between…

Update date filter selection to current month

Select any cell in the 2^nd pivot table
Select Analyze
Select Change Data Source

Select the newly-created detail tab for current month
The rows in the new tab will be selected
Select OK
Update pivot table filter
- Select Post Date filter
- Hover over Date Filters option
- Select Between…