Dynamics GP Lockbox Processing and Write Off Functionality

This document is help in using the lockbox functionality in Dynamics GP.  Though the term lockbox is meant to represent a service provided by banks it can be used with any file representing monies from your customers.  

This definition should not need adjusting as we will manipulate our file to match it.  In this definition, we have made the following assumptions:

11)      The file will be a tab delimited file.

22)      The format type is single line, which means that the check information only takes one line.

33)      The decimal places in the file are 2.

44)      We want the invoices paid to be specific.  If there is not a match it is imported unapplied.

55)      The first record, the header, is ignored as its just text.

Other than for showing how we are changing a data file sent by your customer you should not need to go into the Lockbox Maintenance or make changes.

Data File

The data files sent to you by your customers can vary greatly.  We only need certain information from the file.  There are:

11)      Total check amount

22)      Check number

33)      Customer number

44)      Paid date

55)      Amount paid (per invoice)

66)      Invoice to apply to

77)      Row Indicator

As an example, see the screen shot below.

 

Depending on your customer you most likely will not have the check number, your customer number for them, or the row indicator.  You will add these to your file.  

The first line of your file is the header line.  The names and spelling are not important but they should stay in this order.  The first line of data is the check information.  The first column row 2 should be the check amount.  The next column is the check number.  If you have one, add it and copy down to all records.  The third column should be your customer number.  Again, enter it and copy down to all records.  The fourth column is the paid date.  This may be in your file.  If not, enter it and copy down to all records.  For the check record leave the amount paid and invoice number blank.  The last column you will enter the number 6.  This is what is called a row indicator.  6 means that it is the check information.  4 means it is invoice information.

For the detail of what is being paid you will start with row 3.  You will have already populated the check number and customer number in the above step.  The columns for Amount paid and Invoice number should be in the file you have been provided.  Copy them into these two columns.  Lastly enter the number 4 for the row indicator and copy down to the last record.

You now have the file in the order it needs to be in.  Now you will need to save it as a tab separated file.  See the image below.

 

 

  

Running Lockbox Processing

To run the lockbox processing go to the Sales Series, Transactions – Lock box entry.  You then pull up the windows below.  Do a lookup on Lockbox ID and find the Lockbox ID.  You will then need to find the file you created previously by browsing out to your computer.  The only other item required is a batch ID.  You can type in any alphanumeric characters.  When you tab off it will ask if you want to create the batch.  After creating the batch you will return to this window.  Click on transactions.

 

This will then import the transactions from your file, and like the normal cash receipts entry, will apply to each of the invoices in your file. You then be presented with the following screen.  It could have the green checkmark, which means it is fully applied, or it may have an exclamation mark for not fully applied.  If it has a red X you may have the wrong customer ID in your file.

When complete simply check the create batch and a cash receipts batch will be created.  You can then edit applied information or post to update your customer record.  This information will also go to your checkbook as a receipt.  You will then create a deposit to update your checkbook balance.

 

Taking care of Discounts and Writeoff’s

To address taking care of discounts taken on the invoices you just paid you can use the Write Off Documents feature in Dynamics GP.  You will select this from the Sales-Routines-Write Off Documents.  You will keep it using the type Underpayments.  You can filter on one customer.  Put in a write off limit.  You may want to look at your source file for the largest discount and enter it here.  You will also need to make sure that you allow write offs on the customer card under options.  That supersedes this window and without it marked with unlimited or a dollar amount, nothing will be done on this window.  After making these choices you can click on the Preview button.