Inventory Reconciliation

Basics

The Inventory Reconciliation is designed to:

• re-sequence all unreconciled inventory transactions based on timestamp
• recalculate average cost and last costs
• create the g/l transactions that correspond with all inventory transactions
• update the cost of inventory and sales transaction and flag as reconciled.

The reconciliation process offsets purchase receipt transactions to an A/P Clearing account. As invoices for these items are vouchered, it is expected that they will be distributed to the A/P clearing account. The A/P clearing account should match the cost of unvouchered inventory and ideally at the end of posting all transactions for the month.

The inventory reconciliation process has the added advantage of improving system performance.

Soft Reconciliation

Keystone has the ability to do a "Soft" reconciliation. This is essentially the same process without general ledger output.

Soft Reconciliation improves system performance and improves cost reporting without the complexity of a full reconcile. It is useful for operations that rather make manual g/l entries for inventory transactions and for transitioning into using the full reconciliation process.

TimeStamps

One major feature of the reconciliation process, is the use of timestamps. This accommodates the fact that most of our customer post receipts for items after they are sold, leaving a negative qty on hand. With the proper use of timestamps, a much more accurate avg cost can be determined.

Setup

Products

In addition to the Sales G/L you will need:

• Cost of Sales G/L
• Inventory G/L
• A/P Clearing G/L
• Production In G/L
• Production Out G/L

Balances

Enter beginning balance transactions into Enter Inventory Balances on the Setup/Utilities menu.

For each item check:

• Date
• Time
• Bal Qty
• Unit Cost /Total Cost

NOTE on Balance Transactions: Creating balance transactions does not create a g/l transaction. It creates a starting quantity and cost for the reconciliation process.

This is different than a physical balance transaction in that physical balance transactions will create a g/l transaction and will not change the average cost. Once the reconciliation process is up and running, use physical inventory entry, or miscelaneous transactions to record new inventory count.

The effect of the Balance Transaction is to have the Reconciliation process treat all previous transactions as nonexistant.

Reconciliation Process

Prerequisites

• No product can have a qty on hand go negative
• All inventory transactions for the period must be complete

When running Inventory Reconciliation, make sure to select the correct date and time. A report will print, and if there are no errors you will be given a chance to process the reconciliation.

The most likely cause of errors is from qty on hand going negative. The most likely causes are:

• Reciepts not recorded
• Transfer from plant to plant not made
• Bad timestamp on transaction

In the case of missing transactions, simply enter the transactions and process again.

In the case of a bad timestamp, two transactions will be needed. First enter the transaction with the original qty and the correct timestamp. Then reverse it using the incorrect date originally applied.

Options

Prior Transactions

This option has been removed from version 2.5. Prior transactions will always be calculated.

Non Stocked

NOTE: This is a system option introduced in 2.0.12.0. Default to not process Sales Trx for non stocked items.

When selected, this option causes the reconciliation procedure skip g/l transactions for non-stocked items. NOTE: these will still be processed for cost and the resetting of the reconciled flag.

The Details

Sequencing Transactions

Transactions are sequenced by the timestamp.

Handling Backdated Transactions

Backdated transactions are unreconciled transactions, dated before the most recent Balance transaction.

These transactions are processed in order or transaction date, but as if they follow all previously reconciled transactions.

G/L Transactions

Trx Type	Description	INVENTORY	OFFSET	Default Cost(at time trx is created)	Reconcile Cost	Comment
B	Balance	.	.		Running Avg.	No G/L. This is the starting point for the reconcile process for each product. It is also used for calculating on-hand and current costs in several Keystone programs.
C	Standard Cost	.	.		Standard	Created in product maintenance when std. cost changes. Used to determine prior values of std. cost.l
L	Last Cost	.	.		Actual	Last Cost from Purchase Reciept or PO Inventory Receipt or Previous Last Cost Record
D	Production Receipts (No Components)	Inventory GL	Production In GL	Standard	Standard	Item is produced without any components.
M	Miscellaneous	Inventory GL	Manually Entered	Standard	Running Avg.	Useful for unrecorded COGS, Inventory Adjustments and lost amounts.
R	Purchase Receipt (unlinked to AP/PO)	Inventory GL (D)	AP Clear GL(C)	Actual	Actual	The AP Clearing account amount from purchase receipts should wash against the AP Clearing amount from AP Vouchering.
I	PO Inventory Receipt (linked to AP/PO)	.	.	Actual	Actual	Related G/L created when linked to invoice and invoice posted. For Stocked items this is a Debit to Inventory, Credit to A/P, For Non-Stocked items this is Debit to Inventory and credit to Cost of Goods Sold.
S	Sale	Inventory GL (C)	Cost Of Sales GL (D)	Standard	Running Avg.	Record costs of Stocked Items.
P	Physical	Inventory GL	Manually Entered	Standard	Running Avg.	When Reconcile crosses a P entry, it will determine the qty difference between the physical inventory record and the current running inventory calculation. The G/L entry will be based on the current running avg. cost and this calculated qty. Do not backdate before last Reconciled Transaction!
V	Production Rcpt Out (No components)	Inventory GL (C)	Production Out GL	Standard	Running Avg.	Reduction of inventory to the components during production when these component do not consist of other compenents (bottom level).
	Production Rcpt Out (Components)	Inventory GL (C)	Production Out GL	Matching Production In / Qty Out	Matching Production In / Qty Out	Reduction of inventory to the components during production when these component do consist of other compenents (Mid-Level). The Total matching Production In cost is used as the Total Production Out cost.
W	Production Rcpt In	Inventory GL (D)	Production In GL	Production Cost / Qty In	Production Cost / Qty In	Increase of inventory to the assembly product during production.
X	Transfer Out	Inventory GL	AP Clear GL	Standard	Running Avg.	Inventory Reduction from Transfer Out. Linked to Y record.
Y	Transfer In	Inventory GL	AP Clear GL	Transfer Out Cost / Qty In	Transfer Out Cost / Qty In	Inventory Increase from Transfer In. Cost comes from X record.

Resulting Output

General Ledger (glttrx)

Transactions for each transaction are created.

Sales Detail (artslsdt)

Cost is updated

Reconcile Flag is set to Y

Inventory Transaction (artprtrx)

Each transaction is updated with:

New average cost

Reconcile Flag=Y

Reconcile Session_no is set

Reconcile Trans_no is set

A new Balance record is set establishing:

New average cost

New on hand quantity

Reconciling with A/P (Non PO Method)

NOTE: This method is rarely used. A/P Reconciliation is essential when using the Inventory System option Purchase Receipts. (When using the Keystone PO system, items are linked to A/P and all purchase transactions are recorded as they hit inventory). This option does not link to A/P, but instead records items to the AP Clearing Account. This method assumes the the AP Clearing account is used for distribution account of stocked items as they are vouchered in AP.

Complete all A/P inventory postings for the month. Make sure to post g/l transactions for both the A/R system and the A/P system.

At this point the A/P Clearing account should balance to 0.

If not, you will need to determine if the problem is on the inventory side or the A/P side.

If the problem is on the A/P side make adjustments to a/p and verify the a/p clearing account has cleared.

If the problem is on the Inventory side, then you will need to fix the transactions, and then RE-RECONCILE Inventory

Re-Reconcile Inventory

To Re-Reconcile Inventory simply run Inventory Reconciliation with the same date. All unreconciled transactions before the reconciliation date will be processed in date order, following the normal rules for any transaction that proceeds a reconcile date.

See Also

Inventory Reconciliation Report Queries

Inventory: Understanding Average Cost