Inform is a perpetual inventory system and maintains a constantly updated stock value based on the physical movements in and out of various warehouses.
- Physically, each item maintains a cost that is updated any time a new stock receipt or return is processed. At the time of adjustment, transfer or sale, the system applies the current cost of each item to the transaction.
- Financially, purchases and vendor returns are booked directly to the Inventory Asset G/L account. The intent is to use the exact same costs as were applied when receiving the Purchase Order. When a sale is posted, the system records a debit to Cost of Goods Sold offset by a credit to the Inventory Asset Account based on the item’s current cost value.
Each item maintains a moving weighted average based on the incoming stream of Purchase Order costs. Each time a new shipment is bought or returned, the cost is updated. This is why it is important to use as accurate of a cost as possible when purchasing and receiving merchandise. You can go to File > Product > Master: Setup to view an item's C2 Weighted Average Cost. The system is capable of maintaining nine separate costs for each item. This discussion uses C2 Average Cost as the example.
Tip: DDI recommends confirming all vendors pricing to reduce instances of retroactive cost changes.
The system maintains a physical value based on the sum of all individual item values. This value can be determined by going to Inventory > Inventory Value Report, then clicking OK to choose all items, and then specify warehouse. This moving weighted average cost follows the item around the system at time of purchase, transfer and sale. The corresponding financial value in the Inventory G/L accounts is segregated by warehouse. A regular reconciliation process is required to keep the physical and financial values in balance.
Each inventory process is progressive and depends on successful completion of previous step.
- Creating a PO has no effect on system quantities or G/L transactions.
- Receiving a PO has an immediate effect on the physical value and C2 cost.
- Entering an A/P invoice updates the G/L balance. For this to happen normally, the invoice must either be entered directly from the stock receipts screen, or the PO must be linked in Accounting > Accounts Payable > Invoice Entry.
A PO is considered un-invoiced during the period of time between stock receipt (when the physical value changes) and invoice entry (when the financial value changes). It is critical to maintain an accurate value of un-invoiced items because it is used in the inventory reconciliation.
To view current items, go to Purchasing > Un-Invoiced PO Report and choose a date range that starts on the first day of Go-Live month.
If a vendor has already been paid, but the PO is still considered un-invoiced, enter a zero dollar A/P invoice and link the PO. The vendor will not be paid again and the PO will be removed from un-invoiced status.
Users of Inform Version 17 and higher may elect an automatic accrual of the value of Un-invoiced PO’s at the time of the End of Month Close.
The purpose of this account is to filter transactions that do not meet the system criteria as inventory.
- For Purchases: An Accounts Payable invoice entered to a merchandise vendor requires a valid link to a PO. If the PO is missing, the system will replace the Inventory G/L account with the Non-Inventory account.
- For Sales: Placing a Consumable item or SP item with a cost on a Sales Order will cause the system to replace the Inventory account with Non-Inventory.
- For Adjustments: If the status of an item is changed to or from Consumable, and there is an associated cost, the system will use the Non-Inventory account accordingly.
A physical warehouse represents quantities on hand at an operating location of the business. Multi-location operations generally have one physical warehouse for each branch. There may also be warehouses set up for a showroom or repair operation.
A virtual warehouse is a suspense location for “temporary” items and applies universally to all branches of the organization.
- The function of the Drop Ship Warehouse is to serve as suspense for items moved directly from vendor to customer. Ultimately, there should be a zero value at the end of each month to indicate that all purchases and linked sales were posted.
- The function of the Returned Goods Warehouse is as a temporary repository for items designated “Return to Vendor” on return sales orders. To avoid placing the item back in saleable stock, it remains here as a positive quantity until further action is taken to send it back.
A consignment warehouse represents other merchandise scenarios in which ownership is not necessarily by the company.
- Vendor Consignment: the vendor owns the material until the company sells it.
- Customer Consignment: the company owns the material until the customer sells it.
As part of every End of Month Close, an Inventory Value by Warehouse Report is automatically archived in the Attachments area of the EOM Closing Journals menu. The Excel export does not contain a reconciliation summary page. The report may also be produced manually to provide a real-time snapshot.
- Go to Inventory > Inventory Value Report.
- On the Product Selection Criteria pop-up, click OK to choose all items without filtering.
- A second window opens. Select a single warehouse or leave the field blank to choose all warehouses. (note that all may not include Drop Ship)
- The Cost Column defaults to the costing method.
- From the Detail or Summary list, choose Detail to display a line for each item or Summary to display a total only.
- Click Run.
- Run the report to the screen and proceed to the final page.
Considerations when running an Inventory Value Report
- Report requests for “all warehouses” include Returned Goods but do not include the Drop Ship Warehouse unless a Company Master flag is enabled. To include Drop Ship quantities, go to File > Company > Master: Purchasing/Inventory and set Include Drop Ship Warehouse in Inventory Value for All Warehouses to Y.
- Users of FIFO and Landed FIFO must capture each warehouse separately for accuracy.
- The Invoice Average Cost Variance has been replaced in Version 17 with real time posting of differences between purchase and selling cost.
Refer to the sample report for an explanation of each field on the Inventory Value Report Reconciliation Summary.
Note that if you maintain multiple product-line-specific Inventory G/L accounts the Reconciliation Summary Page will display the balance of the default account and yield an overstated difference. In addition, if you are on Version 17 and above, and use C2 as G/L cost, Invoice Average Cost Variance will no longer appear.
The information contained on the Reconciliation Summary forms the foundation of an end of month reconciliation. It is helpful to use a spreadsheet to perform the calculations and show each month’s results side by side to assess whether the variance is changing.
Refer to the example below for a reconciliation template. Note that users of version 17 and above will not need an invoice average cost variance because it posts in real time. Version 17+ users electing to accrue un-invoiced PO’s will not need to include them either.
|Inventory Reconciliation||Month 1||Month 2||Month 3|
|G/L Inventory Value||762,530.31||802,222.55||764,878.39|
|Invoice Average Cost Variance||275.69||125.07||1.47|
|Adjusted G/L Inventory Value||887,836.30||902,837.14||894,911.78|
|Negative Inventory Value||-6.72||0.00||0.00|
|Total Inventory Value||905,995.62||932,045.00||923,375.21|
|Change from Prior Period||-18,159.32||-11,048.54||744.43|
|Diffference as a % of Total Inventory Value||-2.0%||-3.1%||-3.1%|
Following are some common reasons for variances between physical stock value and G/L balance:
Opening Values. At the time of conversion to the Inform system the physical product quantities on hand were represented by an opening journal entry. Check to ensure that the journal entry matched the inventory value. If a portion of the material on hand was un-invoiced, and the full value was brought over by journal entry, then the entry of A/P invoices for the un-vouchered portion would in effect duplicate the value on the books.
Evolution of Difference. Trace the difference back as far as possible to determine when it started and whether there are continuing changes. DDI recommends running a valuation at the same time each day to pinpoint changes.
Unwarranted Journal Entries. Avoid making journal entries that are not backed up with an actual change in physical value. The exceptions would be making a monthly accrual to post the value of un-invoiced material to a period or making a year-end entry to settle any differences.
Carrying Excessive Un-Invoiced PO’s. If there are un-invoiced PO’s that have already been paid, they need to be cleared off the report by linking to a zero dollar A/P invoice.
Purchase Cost Variance. When entering a vendor invoice for payment, it is required that the value of the linked receipt match the amount posted to the Inventory G/L account. Large differences mandate a correction to the PO and stock receipt before saving the invoice. Lesser amounts should be recorded into a Purchase Cost Variance G/L account to avoid affecting the Inventory G/L adversely.
Invoice Average Cost Variance. For users of C2 Average Cost only, this is an internal calculation made by the system when an item sells before its sell cost updates and there has been a change in the cost of the most recent stock receipt. The value on the Inventory Reconciliation Summary should be journalized once per month, as it resets itself at every EOM close.
Unit of Measure Changes. If a unit of measure is changed or deleted for items on open sales orders or transfers, the system will apply the quantity (based on the deleted UOM) and the cost (based on the new UOM). This can cause the system to deplete the correct physical quantity, but use a highly skewed value to reduce inventory at the time of sale or transfer, thereby creating a difference.
Product Merge of Dissimilar Items. Use the merge function to combine items with identical costs and unit of measure. Otherwise the physical characteristics of the new item will not carry the same value as the sum of the individual characteristics.
Use of Drop Ship Warehouse. If the drop ship warehouse and corresponding G/L account do not maintain zero balances at the end of each month, it is indicative of an incorrect or incomplete process.
Use of Consumable Items. To avoid creating unnecessary entries to the non-inventory account, consider using Product Line G/L to post any consumable costs in and out of the same cost of goods account. Otherwise, set the cost of the consumable to zero.
Voiding Sales Orders. If more than a few days have passed since the original invoice entry, process the reversal as a Sales Return to avoid any inconsistent value changes between physical and financial value. Voids apply the same cost as the original. If other transactions in the interim caused the C2 Average Cost of the item(s) to change, the sales journal cost amount will not match the value of stock put back.
Clearing the Non-Inventory Account. The account should be reviewed monthly to reallocate balances into the correct accounts. Purchase items normally require reallocation to regular inventory, vendor rebates, or some other vendor related cost. Sales items usually generate an erroneous entry to cost of goods that needs to be reversed.
Posting Freight Incorrectly. Systems using a Landed Average or Landed FIFO cost for valuation must post freight-in costs directly to the inventory G/L account to ensure that the financial transaction picks up the full cost of the product. Systems using any other cost should post freight-in costs to a cost of goods account named accordingly.
Non-Inventory G/L Account. Review the account on a monthly basis and reallocate any items to the correct accounts as needed. Avoid simply reallocating balances to Inventory.
Accounts Payable entries represent merchandise vendor invoices missing a valid link to the PO. Some of these invoices, especially credit amounts, may be legitimate non-inventory items such as vendor rebates.
- The proper journal entry for a vendor rebate is:
Debit Non-Inventory / Credit Vendor Rebate Income (if posting flow is turned off)
Debit Non-Inventory / Credit Rebates Receivable (if posting flow is turned on)
Sales Journal entries represent sales of SP or consumable items with a cost. In general, the cost of a consumable is picked up elsewhere, as in the case of labor or freight.
- To avoid double booking costs, the entry would generally be:
Debit Non-Inventory / Credit Cost of Goods Sold
Direct Ship Inventory G/L Account. Review the account to ensure that purchase debits and sales credits offset each other. All transactions should be completed before the EOM close in terms of the vendor and customer billings.
- If there is a debit balance in the account, the corresponding Inventory Value by Warehouse should show a positive value. This situation will occur if the purchase portion of the transaction is processed before the sale.
- If there is a credit balance in the account, the Inventory Value by Warehouse will display a negative balance. This scenario happens when the customer is billed before the vendor stock receipt and invoice are entered.