Reconciling Inventories to the General Ledger

1
Reconciling Inventories to the General Ledger

• Session 20100
• Presented by Dan Murphy, Reliasys
• Charles Chamberlain, Cleveland Golf

2
Overview of Integrity Issues

• Setup / Configuration (e.g. AAI errors, line
  types, etc.)
• Manual Data Corrections (SQL, etc.)
• Receipts Routing
• Cost Changes / Overrides
• Abnormal Program Termination
• Modifications / Custom Programs
• Upgrades / Data Conversions

3
Process Goals

• A clear set of procedures for analyzing Inventory
  activity
• Timely inventory review at month end
• No unexplained variances

4
Implementation Strategy

• Long Term
• Process / Procedures
• Setup Issues
• Short Term
• Establish Base Reconciliation
• Identify causes
• Re-establish Database Integrities

5
The Solution

• The Theory
• The Principles
• The Process
• The Procedures

6
The Theory

• The objective of our procedures is to
  demonstrate that the inventory we have recorded
  in the General Ledger is the same as the
  inventory we actually own.
• In order to accomplish this, we use the
  following tools
• Perpetual Inventory
• - Retains the current balances of all the items
  in our inventory
• Sub-Ledgers
• - Records all activity that changes the current
  balance reflected in our perpetual inventory
• General Ledgers
• - Records the financial activity from the
  inventory sub-ledgers
• Physical Inventories
• - Performs regularly scheduled cycle counts
  and/or physical inventories to insure the
  perpetual is an accurate reflection of our
  inventory balances.

7
In ERP Terms
8
The Principles

• In order for our process to be effective, we
  must understand the principles upon which we
  build the foundation of our process. Our
  procedures will be designed such that we will be
  able to perform two key steps when analyzing the
  data in the inventory system.
• Recreate the Perpetual
• Group Inventory Activity into Daily Buckets

9
The Principles

• To insure our procedures can accomplish this, we
• need to establish a set of ground rules, or
  Maxims.
• It is important to understand these maxims have
  not
• been developed to be broken, which is why we
  prefer
• NOT to call them rules. The maxims are well
• established principles, which we must assume are
• followed otherwise, the tools we provide to help
  with
• this process will not work effectively.

10
The Principles

• Always record your activity while you are doing
  the transaction.
• Always use the current days date as your
  transaction date.
• Always make sure your GL date is the same as your
  transaction date.
• Always post all your inventory transactions to
  the GL daily.
• Never use your inventory accounts to record your
  reconciling journal entries

11
The Process

• Reconciling your inventory is a five step
  process
• Costing Integrity
• CARDEX to GL Integrity
• CARDEX to Perpetual Integrity
• GL to Perpetual Reconciliation
• Non-perpetual Inventories

12
Costing Integrities

• In this process step, we review the
• system for two costing problems
• Parts with no costs (R30543 Cost Component /
  Ledger Integrity)
• Standard Cost Integrities (R30801 Costing
  Exception Report)

13
CARDEX to GL Integrities

• In this process step, we review the system for
  CARDEX
• activity which does not agree with the GL
  activity
• Missing GL Activity (R41543 Item Ledger /
  Account Integrity
• CARDEX Activity does not match GL Activity
  (R41543 Item Ledger / Account Integrity)
• GL Activity with no CARDEX (Custom Solution)

14
CARDEX to Perpetual Inventory

• In this process step, we review the system for
• integrity issues in the Perpetual Inventory
• Negative quantities on hand (R41544 Item
  Balance / Ledger Integrity)
• CARDEX balances that do not agree to the
  Perpetual Inventory (R41544 Item Balance /
  Ledger Integrity)

15
GL to Perpetual Reconciliation
16
GL to Perpetual Reconciliation

• R41590 Inventory Valuation Analysis
• R09321 Transaction Journal

17
Defining the Procedures

• R30801 Costing Exception Reports
• R30543 Cost Component / Ledger Integrity
• R41543 Item Ledger / Account Integrity
• R41544 Item Balance / Ledger Integrity
• R41590 Inventory Valuation Analysis
• R09321 Transaction Journal

18
Non-Perpetual Inventories

• Not all inventory is recorded in the Perpetual.
  These
• inventories will have to be reconciled
  separately.
• Work In Process
• All materials issued to an open shop floor work
  order will not be in the perpetual table but
  would be considered inventory. Separate
  procedures are required to reconcile this.
• Receipts Routing
• All materials entered into the receipts routing
  process will not be in the perpetual table. If
  your company recognizes this as inventory, then a
  separate set of procedures are required to
  reconcile this.

19
The Procedures

• Non-Perpetual Inventories
• Work in Process
• The Perpetual report for this process is R31811
  Completed Work Order Valuation. Run this
  report with the variance flag set to a value of
  1.
• The General Ledger report for this process is
  R09422 G/L with Subledger Totals. Run this
  report by G/L account and set to ITD (Inception
  to Date).

20
The Procedures

• Non-Perpetual Inventories
• Receipts Routing
• Unfortunately, there is no canned solution for
  this simple reconciliation. However, only two
  simple reports need to be developed. The tables
  are linked by using the PO Number.
• The F0911 has a field called PO which is needed
  to summarize the GL account activity.
• The F43092 stores the PO Number in the data item
  DOCO which is needed to summarize the Quantity
  at Operation for all operations which are not
  Receipt to Stock operations.

21
Initializing your Data

• Establish data integrity between the three
  inventory tables.
• Identify issues which corrupt the integrity
• Work with you to develop policies and procedures
  to maintain data integrity

22
Questions?

• Contact Information
• Dan Murphy [phone] dan_murphy_at_reliasys
  .com
• Larry Mcbride [phone]
  larry_mcbride_at_reliasys.com