AR: Lockbox

Overview

WinTeam's Accounts Receivable Lockbox program was designed to keep payments (cash posting) current, and to assist with daily deposits by providing an interface from a bank's records to WinTeam. To use Lockbox, users must have the screen security. User(s) that have this security may access the Lockbox screen from the WinTeam Accounts Receivable menu.

Key Functionality

Click the Import from File button to navigate to the place on your computer/network where you have saved a text (.csv) file from your bank. Select the file and click Open to import the file data into WinTeam.

There are two formats for the import file—one includes adjustments and the other does not. To Use the file that includes adjustments, you must enable the Lockbox | IncludeAdjustmentOnImport | Yes custom setting. This custom setting includes Adjustment Type, Discount Amount, and Job Number in the import file.

Document:

You can download the following documents to see an example of a populated .csv file:

Warning:  You may work on only one lockbox file at a time. If you attempt to import more than one file at a time, the system will discard the original lockbox file. Once you have started working with a lockbox file, do not exit out of the AR: Lockbox window. The system will delete the file you have imported and all of the changes you have made. If you need to start over, you may re-import the original text file.

Required Fields Additional Information
Time HH:MM (hour and minute)
Date Deposit date of the check
Check Amount Actual check amount
Net Invoice Amount The amount that the check is paying for on this invoice
Check The actual check number of the deposit
Optional Fields Additional Information
Num (Number) Unique identifying number for this transaction
Env Num (Envelope Number) Envelope identifying number assigned by the bank
Envelope Envelope identifying number that the check came in
Transaction Bank's EES initials and the date or other descriptor for this transaction
Lockbox Number of the lockbox assigned to the customer by the bank
ABA/RT Transit routing number
Account Num (Account Number) Bank account number
Invoice Page Invoice page identifier assigned by the bank
Check Image DEPRECATED
Envelope Image DEPRECATED
Invoice Image DEPRECATED
Batch Batch number for all the deposits—if the batch number is greater than 200 then the check is marked as an EFT (electronic funds transfer)
Batch Item The identifying number for the batch
Remitter Name Customer name from the check
Check Num (Check Number) The check number on the check

Note:  Not required but should be included to identify payment records
Invoice Number The invoice number that the check is paying for
Adjustment Type Identifying number for the adjustment type:
  • 1 = Discount
  • 2 = Write Off
  • 3 = Finance Charge
  • 4 = Revenue Adjustment

The dollar amount of the adjustment must be contained in the Discount Amount field

Note:  To include this field in the import file you must enable the setting Lockbox | IncludeAdjustmentOnImport | Yes custom setting
Discount Amount The dollar amount of the Adjustment Type
  • Write Off and Discount amounts must be positive values
  • Finance Charge amounts must be negative values indicated by parenthesis—for example, ($1.11)
  • Revenue Adjustment amounts can be either positive or negative values

Note:  To include this field in the import file you must enable the setting Lockbox | IncludeAdjustmentOnImport | Yes custom setting
Job Number The identifying number for the job
  • Job Number is required when the Adjustment Type is 2 (Write Off) or 4 (Revenue Adjustment)
  • Job Number can be blank when the Adjustment Type is 1 (Discount) or 3 (Finance Charge) when the Job Number is left blank, the system uses the default job number from Company Setup

Note:  To include this field in the import file you must enable the setting Lockbox | IncludeAdjustmentOnImport | Yes custom setting

When you are ready to update or update/print, enter the G/L Cash Account to which you want this deposit recorded. Notice that the Deposit Date has been imported from the text file.

This area displays the Customer Number, Check #, Check Amount and other information from the lockbox file to be updated to WinTeam. The Customer Number may be blank if the system is unable to determine the account to which this check should be posted. If the invoice(s) being paid exist in a particular customer account in WinTeam, that is the customer number that will be credited with the payment.

 

Items and order validated:

  1. System imports data from the .csv file.
  2. Systems tries to determine the Customer Number to apply the check to by first looking to see if it can find an Invoice # “match”. Since an invoice number can only be used once, this is a good way of verifying what customer should be credited for payment. It is possible that a single check could be applied to more than one Customer #. When this happens, you will notice that the Imported Text: area will show a customer name of something like Split From ID ‘8’ (or whatever main ID number was assigned to the main check record that was imported). This is indicating that the check was actually split between more than one customer number. It will apply the appropriate amount to each customer.
  3. If the system can not determine the customer # from the invoice numbers being paid (no match), it will then try and determine the customer number by the Customer Name recorded in the Lock Box File.
  4. If it STILL can not determine the customer number, it will display the check record but with no Customer Number. You will have to fill in the customer number for this record yourself before exporting to WinTeam.

Steps and the order taken:

  1. Makes sure that the G/L # is not blank.
  2. Makes sure that the Deposit Date is not blank.
  3. Checks to be sure that each check record has a Customer Number assigned to it. If it does not, you will have to indicate the customer number the check should be recorded under by using the Customer Number Drop Down and selecting the customer it should apply to. Once the customer number is entered, you will then have to find the correct invoices that this payment should be applied to. There is a nice Invoice Drop Down in the Invoices Pay by Check section that should supply you with all information you need. If the check amount can not be fully applied, the system will calculate the remaining amount left as Unapplied Cash later in this process.
  4. Insures that the Check Number field is not blank (under actual information) and not EFT (if it is an EFT no check number is required).
  5. Insures that the Check Amount is not blank.
  6. Checks to be sure there are no blank invoice numbers or blank Invoice amounts.
  7. Checks to be sure the Invoice numbers being applied exist for that customer.
  8. Checks to be sure the Sum of the Details (invoices being applied) does not exceed the check amount.

    9. Note:  This step will not be checked if you are using the AllowShortPays custom setting.

  9. Checks to be sure the Invoices being applied are not already paid.
  10. Checks to see if the check amounts are greater than the invoice amounts.
  11. If the check amounts are greater than the invoice amounts, it will compute unapplied cash. This credit amount displays as a negative value.
  12. If there are checks listed with a $0.00 check amount, the system will prompt you to review these checks.
  13. If there are checks that result in unapplied cash, the system will prompt you to verify if it should be updated to WinTeam.

Blank Customer Numbers

Sometimes the system may not be able to relate a record that is in the .csv file to a specific customer. This can happen when the invoices being paid by a check are not identifiable to any customer in the WinTeam database. In this case, review the import file, add the customer numbers to the appropriate record(s) and then re-import the .csv file.

Warning:  During the same entry session, you should not attempt to update the same lock box information more than once. Doing this, will create duplicate entries.

Use the Preview button to display a report to review the status of the .csv file records. If there are records listed in the "Records NOT being imported into WinTeam" section, review those records and make corrections to the .csv file and then use the Preview button again to review. Once everything has been validated, use the Update or Update/Print buttons to update the information into WinTeam. The lockbox file information will be imported to the AP: Payments area in WinTeam.

Use the Preview button to display the Lockbox Cash Posting Report.

Custom Settings

Section

Item

Value

Description
Lockbox AllowShortPays Yes This setting allows invoices from the file to update even if it is a short (partial) payment. The notes column displays "Partial Payment" and shows remaining amount due on the invoice.
Lockbox AllowOverPays Yes This setting allows invoices from the file to update even if the payment amount exceeds the invoice amount. The notes field displays the term "overpayment". The overpayment is displayed on the "Unapplied Cash being imported into WinTeam" section of the report.
Lockbox CombineSameInvoices Yes This custom setting allows for more than one payment to be applied to the same invoice. With this setting the payments are combined into one row.
Lockbox CreateBatch Yes This setting creates a temporary batch ID number.

Note:  The temporary batch ID number is not saved or locked down. Users cannot see it in Show all Batches or in the AR Payment wizard, also it is not under the Payment- Update to GL Batches criteria for selection.
Lockbox IncludeAdjustmentOnImport Yes This setting adds the following columns to the .csv lockbox import file:
  • Adjustment Type
  • Discount Amount
  • Job Number
If the imported file contains adjustment values that are greater than the limit set in AR Defaults error message is given for each record with an error.