Difference between revisions of "Inventory Reconciliation"

From GivenHansco Keystone Help
Jump to navigation Jump to search
 
(7 intermediate revisions by the same user not shown)
Line 27: Line 27:
  
 
===Products===
 
===Products===
In addition to the Sales G/L and Cost of Sales G/L you will need:
+
In addition to the Sales G/L you will need:
 +
*Cost of Sales G/L
 
*Inventory G/L
 
*Inventory G/L
 
*A/P Clearing G/L
 
*A/P Clearing G/L
Line 96: Line 97:
 
| C || Standard Cost || . || . ||  || Standard || Created in product maintenance when std. cost changes. Used to determine prior values of std. cost.l
 
| 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 or Previous Last Cost Record
+
| 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_GL_IN || Standard || Standard || Item is produced without any components.
+
| 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.
+
| 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) || APCLEAR_GL(C) || Actual || Actual || The AP Clearing account amount from purchase receipts should wash against the AP Clearing amount from AP Vouchering.
+
| 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.
 
| 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) || COSTOFSALES_GL (D) || Standard || Running Avg. || Record costs of Stocked Items.
+
| 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.
+
| 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!
 
Do not backdate before last Reconciled Transaction!
 
|-
 
|-
| V || Production Rcpt Out || INVENTORY_GL (C) || PRODUCTION_GL_OUT || Standard || Running Avg. || Represents the reduction of inventory to the components during production.
+
| 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).
 
|-
 
|-
| W || Production Rcpt In || INVENTORY_GL (D) || PRODUCTION_GL_IN || Production Cost / Qty In || Production Cost / Qty In || Increase of inventory to the assembly product during production.
+
| || 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.
 
|-
 
|-
| X || Transfer Out || INVENTORY_GL || AP_CLEAR_GL || Standard || Running Avg. || Inventory Reduction from Transfer Out.  Linked to Y record.
+
| 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.
 
|-
 
|-
| 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.
+
| X || Transfer Out || Inventory GL || AP Clear GL || Standard || Running Avg. || Inventory Reduction from Transfer Out.  Linked to Y record.
|}
 
 
 
<!--
 
 
 
{| border=1
 
|| . || Description        || INVENTORY || OFFSET || Comment
 
|-
 
|| B || Balance || . || . || 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|| . || . || Created in product maintenance when std. cost changes.  Used to determine prior values of std. cost.l
 
|-
 
|| L || Last Cost|| . || . || Last Cost Record created by Reconcile.  (Starting 2.7.28)
 
|-
 
|| D || Production Receipts (No Components) || INVENTORY_GL ||  PRODUCTION_GL_IN  ||  (Starting 2.9.5)  When an item is produced without any components.
 
|-
 
|| M || Miscellaneous || INVENTORY_GL || Manually Entered || Useful for unrecorded COGS, Inventory Adjustments and lost amounts.  Uses current running AVG Cost.
 
|-
 
|| R || Purchase Receipt<BR>(unlinked to AP/PO) || INVENTORY_GL (D)|| '''APCLEAR_GL'''(C) || Uses manually entered cost. Impacts Last and  Avg. Cost. The AP Clearing account amount from purchase receipts should wash against the AP Clearing amount from AP Vouchering.
 
|-
 
|| I || PO Inventory Receipt<BR>(linked to AP/PO) || . || . || Uses manually entered cost. Impacts Last and  Avg. Cost. No G/L generated... related G/L created when linked to invoice and invoice posted.  For Stocked items this is a Debit to Inventory, Credit to A/P.
 
|-
 
|| S || Sale || INVENTORY_GL (C)|| '''COSTOFSALES_GL''' (D) || Record costs of Stocked Items.  Uses current running avg cost.
 
|-
 
|| P || Physical || INVENTORY_GL || Manually Entered || 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.<BR>
 
Do not backdate before last Reconciled Transaction.
 
|-
 
|| V || Production Rcpt Out || INVENTORY_GL (C) || PRODUCTION_GL_OUT ||  (Starting 2.7.28) Represents the reduction of inventory to the components during production.
 
|-
 
  || W || Production Rcpt In || INVENTORY_GL (D) || PRODUCTION_GL_IN ||  (Starting 2.7.28) Represents the increase of inventory to the assembly product during production
 
|-
 
|| X || Transfer Out || INVENTORY_GL || AP_CLEAR_GL|| Linked to Y record.  Uses current running avg. cost.
 
|-
 
|| Y || Transfer In || INVENTORY_GL || AP_CLEAR_GL|| Cost comes from X record.  Can not reconcile until X record is reconciled.
 
 
|-
 
|-
 +
| 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===
 
===Resulting Output===
Line 193: Line 160:
  
 
==See Also==
 
==See Also==
[[Inventory Reconciliation Report Queries]]
+
:[[Inventory Reconciliation Report Queries]]
 +
:[[Inventory: Understanding Average Cost]]

Latest revision as of 18:03, 26 October 2023

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