Overview

Use this article if you find that the Inventory Value report does not match the Inventory ledger value in either the Trial Balance or Balance Sheet. If System Five is configured to allow overselling of inventory, manual inventory adjustments or other you have workflow gaps you may experience inventory value discrepancies.

Watch our webinar on How to Balance Your Ledger & Stock Inventory

When comparing these two reports, there are a few things to consider:

1) The Inventory Value report value is derived from the value of the inventory received on purchase orders and the sale of inventory. We also want to consider that any adjustment made in the item record (stock adjustment) also affects the Inventory Value report.

2) The value of the Inventory ledger account in the Trial Balance or the Balance Sheet is derived from two components. The bills created in your system increase the value of inventory. Sales decrease the value of inventory.

Scenarios that can create inventory value discrepancies

Receiving inventory on Purchase Orders and Bill value is not the same
- Ie: PO received and Bill never created.
- Ie: PO received and the Bill is under or overpaid
- ie: PO received and the Bill is paid but does not post to the correct inventory ledger
Returning stock on an invoice and overriding the cost.
- Ie: Inventory items selected for return and cost is adjusted on the invoice return line
Bulk loading of inventory pricing can incorrectly update inventory with the wrong cost and create drift when selling or receiving. This can leave cost levels incorrectly higher or lower than their real value.
- Some industries use the same part numbers. Bulk loading an inventory file that has parts and costs associated with different inventory than you have in stock can create variances.
- ie: Receiving a case of Light bulbs at the cost of a Chandelier
Overstated inventory GL value can happen from a clerk with the security to adjust inventory
- ie: Clerk adjusts inventory quantity to avoid a future stock count adjustment usually to reflect actual stock
- This inventory adjustment will often have no corresponding bill or journal entry creating the discrepancy
- Best Practice is to review the "Manual Adjustment Report" and remove security when not necessary
Overstated inventory value from fraudulent returns of inventory
- ie: Sales being made to customers that owners may not interact with to ask about a return
- Days or weeks later the clerk returns the inventory and pockets the refunded amount
- The best practice is to count returned inventory as they occur to identify discrepancies. The point-of-sale transaction report has a "Returns" tab which can help identify inventory that should be counted.
Understated inventory value with fraudulent double sales of inventory
- ie: Sales clerk sells inventory twice on two separate transactions for the same dollar amount (often the second invoice going to friends or family for cash)
- The customer usually gets charged twice and months later when the chargeback comes through it looks like an accidental double charge and get's refunded
- The best practice is to use integrated credit card processing and investigate all credit card chargebacks against invoice payments.

How can we be sure that the two reports will reconcile?

The most likely option when working within Windward System Five can be found in the bill creation procedure itself:

You will notice that when creating a bill, to the right, there is a 'POs' tab :

By clicking on this tab, we find we now have a list of Po's ordered and received by that supplier.

In the 'select' area, we then choose which PO is relevant to the bill we are creating. We then choose the 'Add PO Items' tab . We can choose to either 'Add All Items' , or 'Add Summary'. Choosing to add all items will list all items on the PO, and the summary will summarize the PO into a single total.

Once we have made our choice, we are then taken back to the bill screen and we can see that the bill is now created. We can also see that we are posting a value to the Inventory ledger account.

By following this process, we are able to see how we can keep the Inventory Value Report (populated by the Purchase Order) and the Trial Balance Inventory ledger (populated by the bill) in reconciliation.

Because we are populating the Bill through the PO itself, we can be sure the same values are being posted to both the Inventory Value Report and the Inventory ledger account.

Another possible difference to consider are any journal entries made to the Inventory ledger outside of the PO or the Bill.

The Bill and PO Reconciliation Report

Now that we understand how the PO and the bill are linked, it is time to introduce another tool we can use to bring us closer to reconciliation between the two reports. It is called the ' Bill and Reconciliation Report'. It can be found by going to: Accounts Payable/ AP Reports in the menu.

Open the report to adjust your settings.

By choosing the settings shown here, we can view all PO's that do not have a corresponding Bill attached. Keeping in mind the idea that the PO populates the Inventory Value Report, and the Bill populates the Inventory Ledger, we can now see that if all the values reported in this report are on a PO, but not a bill, then the two reports can't possibly match.

It should be noted that the values reported for each supplier is a total of all PO's for that company, not a single PO.

Manual Adjustment Report

The manual adjustment report is another place to check on inventory adjustments made outside of purchase orders. Changes to inventory quantities can create over or understated Inventory values. The findings from this report may highlight the need to create a corresponding inventory adjustment journal entry or prompt further investigation. Consult your accountant or bookkeeper if required.