Reconciling Consigned Inventory

Consignment is when you provide a store or a distributor with one or more of your products, and you don't collect on those products until they are sold. More information on consignment is found here: Consignment Retailers or Consignment Wholesalers/Distributors

If you use the system described in the above links (by using "C" and "T" sales codes), AnyOrder can track the amount of inventory that you have on consignment.

Sales codes are entered in the "S" column in the Item Area. The "S" sales code , above, means regular sale. A "C" sales code is used to indicated the item has been placed on Consignment. A "T" code means the item has been removed from Consignment.

To find out how much inventory you have on consignment, select PRODUCT TOOLS >> INVENTORY FUNCTIONS. On the Inventory Options Screen, click on either "Current Inventory" or "Choose Start and End Dates." That's all describe in detail here: Sales Activity and Inventory Report. A report appears and consignment activity is found in the lower half of this report as shown below:

The Sales Activity and Inventory Report shows all consigned activity. Thus, if you have more than one location where items are placed on consignment, it shows the consignment activity for all of those locations combined.

If you want to show activity for one customer, you'll need to use the Special Report function. To do that, start at the Inventory Functions Screen (PRODUCT TOOLS >> INVENTORY FUNCTIONS). Place a checkmark beside "Customer" in "Special Inventory Report" area. Then run the report. Now you'll see consignment activity for one customer only.

When you run a report for a one customer, you will be able to see how many consigned items have been sold. However, you will not be able to see what the starting and ending consignment levels are. Because of the complexities involved, AnyOrder doesn't track starting and ending levels vis a vis customer. However, it possible to find out the number of consigned items left at a particular consignment location.

If you have Level 6 or higher, you have it easy. Starting and ending consignment levels can be obtained by using PRODUCT TOOLS >> PRODUCT REPORTS and selecting the "Consignment" report option.

If you don't have Level 6 or higher, you'll need to run an inventory report for a customer which includes all invoices in your file. For example, let's say you want the ending consignment level for "ABC Consignment Store." Let's also say you started using AnyOrder on 01/01/08. To run the report, you would select PRODUCT TOOLS >> INVENTORY FUNCTIONS. Place a checkmark beside "Customer." Then click on "Choose Start and End Dates." Enter 01/01/08 as the "Start Date. Enter today's date as the "End Date." Place a checkmark beside "Remove Non-applicables." Then click on "OK." A report will be created. Since the report covers all activity, you can obtain the ending consigned inventory for ABC Consignment Store.

One of the things you may need to do periodically is reconcile AnyOrder's consigned inventory levels. In other words, you may need to update AnyOrder's counts of consigned items so that they match with the actual physical counts that you've taken.

1. First run a "Sales Activity and Inventory Report" for each customer. Use the procedure described above by running a report for all the invoices since you started using AnyOrder. (Level 6 -7 users, all you need to do is to use PRODUCT TOOLS >> PRODUCT REPORTS and select the "Consignment" report option. This will give you a report for all customers.)

2. Take a physical count of the inventory that you have in your warehouse. Plus, take physical counts of how many consigned items are found at each consigned location. (In some cases, particularly with distributors, you'll need to rely on what they report to you.) You don't necessarily have to do it at the end of the year. But it is easier if you can time it for the end of the year. In any case, once you obtain the counts, you should run through the procedure below before issuing any more new invoices.

3. When you've finished the above two steps, create a spreadsheet with the following columns:

The Difference Between Customer A's Physical Counts and AnyOrder's (In other words the Physical Count minus AnyOrder's Level).

Finally, the sum of the Consigned Physical Counts plus Local Warehouse Inventory.

4. When the above spreadsheet is finished, you're ready to reconcile. Let's set up an example. Let's say you have a consignment customer named "National Distribution." You sell to them a product named "Fishing Basics."

For this example, we'll just use the one product (Fishing Basics) and the one customer (National Distribution), but you'll want to include all of your products on consignment and all of your consignment customers. Here's how a portion of the spreadsheet would look:

5. With the above spreadsheet completed, you can now make consigned inventory adjustments for each customer. Using the example above, make out an invoice to National Distributor. The invoice is used to reconcile the customer's levels in AnyOrder so they match the physical count.

On the invoice, you would enter the product "Fishing Basics" in the Item Area. For quantity, you would enter "5" (the difference between the physical count and AnyOrder.

If it's a negative number (the level is greater in AnyOrder than the customer), then use the "T" sales code. From AnyOrder's perspective, that means you are taking away books from the customer (which has the effect of bringing AnyOrder's counts in line with the actual physical count).

If the difference is a positive number, use the "C" sales code. This has the effect of adding books to the customer's consignment inventory.

A. One is to make the correction directly in the Inventory File. Find out which Inventory File is being used for a "Current Inventory" (PRODUCT TOOLS >> INVENTORY FUNCTIONS and select "More Options" and click on the "Inventory File Info button. Then go back to Inventory Functions, select "Manually Update Inventory, and select the current inventory file. Look for "Fishing Basics." You'll need to adjust the Total Inventory Available figure so that when you run a "Current Inventory" the Ending Inventory level matches the "Total" from the spreadsheet. If you have returns and sales since the first of the year, that will take some manipulations.

B. The second way and by far the easiest of the two is to select PRODUCT TOOLS >> INVENTORY RECONCILIATION. Then:

Now enter the inventory amounts from the "Total" column from the spreadsheet above. So we would enter "125" from the above spreadsheet." Then when you've entered all of the counts for the other products (if any), click on "Enter Values in Inventory File"

B. Click on "Manually Update Inventory" and select the current inventory file. PRODUCT TOOLS >> INVENTORY FUNCTIONS and select "More Options" and click on the "Inventory File Info button.

D. Now you'll be changing "Inventory at Consignment Sites." This is the only field you'll be changing. Make it the same as the "Total Consigned" column in the above spreadsheet. In this case, we would enter "25." (Note: there may be a complication here. If there's been consignment activity since the first of the year, you may need to take that into account to get the figures to balance. It's because of this one adjustment that it's easier to do the reconciliation at the end of the end of the year.)

8. You can now run a "Current Inventory" and your figures and AnyOrder's will be coordinated. Note that we are running an inventory based on current inventory file. You wouldn't be able to run inventories for previous year because we've only corrected the current inventory file. You can do previous years, of course, but you would need counts for each of the previous years - and, in most cases, that wouldn't be necessary. It's usually just the current year that needs to be reconciled.