Balancing Inventory Evaluation to GL Control Account

• The extended value reported on the Evaluation report is calculated using the average cost of a product at the time the report was run. If the report is backdated and the average costs have changed since the report "as of" date, then the evaluation report is not reporting the true inventory value for that date. For example, if the value of a product was $10.00 on May 31 and a new receipt on June 1 changed that product's average cost to $11.00 then an evaluation printed on June 2 but backdated to May 31 would report the May 31st quantity at $11.00 cost. (when technically the value of inventory was $10.00 on May 31)

• Check GL45 for any journal entries made to the Inventory control account, which only impact the GL and not the evaluation.

• Check distribution entries posted as a result of inventory adjustments (posted by IC51 per GL accounts defined in program IM44). There should be a record defined in IM44 for each Whse/GL Code/Adjustment Code combination; check that the correct GL account is specified for Inventory.

• Check distribution entries posted as a result of customer invoicing (posted by IN44 per GL accounts defined in program CC00-IN2). There should be a record defined in CC00-IN2 for each Sales Office/GL Code/Customer Type combination; check that the correct GL account is specified for Inventory.

• If Receipts Accrual is turned ON, PO receiving is booking the debit to Inventory accounts and credit to Receipts Accrual account, as specified in program CC00-PO2. Check that the correct GL account is specified for Inventory. Check what GL accounts are being updated when the corresponding AP invoice is being posted (i.e., is the Inventory control account being used again?). We would not expect GL postings with Source Code=AP if Receipts Accrual is ON. Also check what account is being offset when a receipt is cancelled via AP36.

• If Receipts Accrual is turned ON and Landing Factors are included in the cost of the product, check that the Inventory account is NOT assigned in any of the Landing Factor records maintained in Maintain Purchasing Tables (PO09/Landing Factors). For example:

  	Debit	Credit
  Receive 1 unit @ 1000.00 + 30.00 Freight. Expected GL distribution is:
  Inventory	1030.00
  Receipts Accrual		1000.00
  Freight Absorbed		30.00
  If the Inventory account is assigned to the Freight Landing Factor in PO09, then the GL distribution will look like this:
  Inventory (Net of 1030.00DR and 30.00CR)	1000.00
  Receipts Accrual		1000.00
  >>Inventory Evaluation=1030.00 but the Inventory GL account only =1000.00; an out of balance between the Inventory Evaluation and the GL now exists.

• In program IN02, check for any TAT codes that define an override Inventory and/or Cost of Sales control accounts (i.e., consignments, internal invoices, credits for defective mdse, short shipments, etc.). If Update COS=Y and Update Inventory=N and there are no override GL accounts specified, then IN43 will update COS and Inventory control accounts per the CC00-IN2 set-up but Quantity On Hand will not be updated. (IE: Inventory GL control account is updated but not the evaluation).

• If Receipts Accrual is turned OFF, no General Ledger interface occurs out of PO64. The evaluation increases when stock is received via PO60/63 but the Inventory control account is not increased until the AP invoice is posted. This accrual must be accounted for manually.

• If a new product is invoiced before the product has an average cost (which could happen if a new product is physically received, shipped, invoiced and then received through the PO system) then the invoice will credit Inventory at $0.00 but it will be debited with the landed cost when the receipt is finally posted. Further if an existing product is shipped & invoiced prior to receiving (and the stock went negative) then the Inventory account is reduced by the average cost at the time of invoicing, but when received will be increased by actual cost.

• Average Cost for Warehouse Transfers:
  • If QOH in the receiving warehouse is positive before the transfer, a new average cost is calculated based on the value of existing stock and the value of the transferred stock. This preserves the balance between the Evaluation and the GL.

  • If QOH in the receiving warehouse is zero before the transfer, the average cost in the receiving warehouse is set equal to the average cost of the sending warehouse. Inventory goes down in the sending warehouse and up in the receiving warehouse by the same value in both the Evaluation and in the GL.

  • If the QOH in the receiving warehouse is negative, the average cost in the receiving warehouse is SET equal to the average cost of the sending warehouse. The GL will be debited/credited using the average cost of the sending warehouse, having no net effect on the GL but the Inventory Evaluation will go up or down depending on the change to the Average Cost. This will create an out of balance between the Evaluation and the GL. This is depicted in the simple example below:

  Whse		Evaluation	G/L
  	Opening Picture:
  01	100 @ 5.00	500	500+
  02	15- @ 0.00 (goods sold before received)	0	0
  	Totals:	500	500+
  	Transfer 10 units @ 5.00 from WH01 to WH02
  01	90 @ 5.00	450	50-
  02	5- @ 5.00 <<Avg Cost Changed	25-	50+
  	Totals:	425	500+

  Note: For Stock Receipts, if QOH in the receiving warehouse is negative, the average cost in the receiving warehouse is SET to the landed cost of the receipt and the same type of discrepancy depicted above for transfers could arise for receipts.

• Cost overrides: If cost is overridden on an invoice detail line, then the invoicing cycle (IN43/44) will credit the Inventory GL control account using the override cost but the reduction in inventory is reflected on the evaluation using the average cost. For example, evaluation before sale: 2 @ 1000.00 = 2000.00 Sell 1 unit but override the cost to be 1100.00. Inventory will be credited for 1100.00, leaving a balance in the GL = 900.00 but the evaluation will be 1 @ 1000.00 = 1000.00. The result is a 100.00 discrepancy between the Evaluation and the Inventory control account.
  • Note: New functionality related to Cost Override Variances was introduced in Enterprise 6.0. For an overview and examples of this functionality, click here.

• When stock is put back into inventory because a customer returned it and the cost on the credit note is different than the current average cost (which happens if the cost is manually overridden on the credit note detail line or it is picked up from Invoice History if an autocredit was processed) then a costing discrepancy will arise. QOH will be increased for the return but the cost is NOT re-averaged. However, the override cost is used to book the increase to inventory in the GL. For example:
  • Assume you start off with 2 units @ 100.00. GL and Evaluation = 200.00.
  • Then you sell one unit. GL and Evaluation = 100.00.
  • Then you receive 1 more unit at 150.00. Now GL and Evaluation = 250.00; average cost now = 125.00.
  • The unit that was sold at cost of 100.00 is returned via autocredit (autocredits pick up the cost of the original sale from Invoice History). Inventory is increased in the GL by 100.00 so now GL = 350.00. QOH is increased by 1 unit but cost is not re-averaged. Now Valuation = 375.00 (3 units @ 125.00). There is a discrepancy of 25.00.

  Note: New functionality related to Cost Override Variances was introduced in Enterprise 6.0. For an overview and examples of this functionality, click here.

• A Class Product (Product Type=CP) should never have an average cost; it is a generic product that is not purchased or received. For costing purposes, the 'Minimum GM%' assigned to the product in IM13 is used to calculate an estimated cost for the sale which is then treated as a Cost Override. For an overview and examples of the Cost Override Variance functionality, click here .

• If assembled sets are purchased pre-packed, that product will have an average cost set as a by product of PO receiving. If program IM62 is subsequently run to reset costs for assembled sets equal to the sum of the component costs, then the Inventory control account would have been updated with a different value than what would be reported by IC81.

• Consigned Inventory:
  • When goods are transferred to a consignee, Inventory is credited at the current average cost.
  • An average cost is also maintained in the Consignment file (ie, if you consign a product out twice and the inventory average cost changes between those transactions, then the consignment cost will be an average of the incoming inventory costs)
  • If the goods are returned, they will be brought back at the current average cost in the consignment file.
  • If consigned stock is included on the Inventory Evaluation, you cannot backdate the report because the consigned quantities are as at the printing of the report--consignment movement is not backed out. The reported consigned value will not reconcile to a backdated GL balance if there has been consignment movement.

• Recosting a receipt, stock receipts, inventory adjustments, physical count variances, warehouse transfers and invoicing all update the Inventory control account(s). If any of these registers could not find the appropriate Inventory account to DR/CR, then an "XYZ" GL account would have been generated. Any balance in an "XYZ" account needs to be transferred (via journal entry) to the appropriate Inventory account. Check the assignment of control accounts in these programs:
  • CC00/IN2 (posted from IN44, IC56)
  • CC00/PO2 (posted from PO64, PO66)
  • IM44 (posted from IC51 for inventory adjustments)
  • IM44-Code P (posted from PI19 for physical count variances)
  • IN02 (posted from IN44 for special TAT codes)

• In the following programs, Quantity On Hand is updated in the posting program and the corresponding GL is updated when the register is printed. Be sure all registers are printed before trying to reconcile:

  QOH/Cost Updated:	GL Updated:
  IN43-Invoice Scheduling	IN44-Invoice Register
  PO60/63-Receiving	PO64-Receiving Register
  PO65-Revise Landing Costs	PO66-Cost Adjustment Register
  IC50-Inventory Adjustment	IC51-Adjustment Register
  IC55-Receive In-Transists	IC56-Receipt Audit Trail

• If a receipt is processed and the purchase price is wrong, then the sequence in which you correct this costing error could result in a discrepancy between Inventory Control account and the evaluation. For example:

The Correct sequence should be: