GL Inventory Accounts Total vs Stock Status Report Totals Comparison


(Mike Abell) #1

Each month during month end duties, we run a comparison of our GL Inventory Accounts Totals versus our Stock Status Report totals to make sure they tie. On the first day of each month, we run a capture and post up to the prior month end and then run a stock status as of the last day of the prior month to compare. There is always a discrepancy between the two (usually no more than a $1K) that we usually just fix by a journal entry to make them tie. However, we have now been asked to try and see if we can figure out what exactly is causing them not to match so I’m wondering if anyone knows of an easy way to “reverse engineer” or some other method of comparing the “detail” to figure this out. Any help would be greatly appreciated. Thanks…


(Josh Owings) #2

What is your costing method? Need this info to possibly begin an explanation. I would also recommend running the last day of the month at end of day regardless of COS/WIP processing being complete.

Josh Owings


(Mike Abell) #3

We use Average costing.

Best regards

Mike Abell
IT Manager

Flexial Corporation
a company of BOA Group
1483 Gould Drive, Cookeville, TN 38506, USA

Phone: +1 931 432 8408
Fax: +1 931 432 1889
Mail: mabell@flexial.com
Website: http://www.flexial.comhttp://www.flexial.com/
http://www.boagroup.comhttp://www.boagroup.com/


(Joe J) #4

FIRST - u need to be sure GL Control accounts and ALL part trans are hitting the correct accounts!
A method… dig thru GL CONTROLS and audit/confirm
B method… extract all PART TRANS-history records and pick-up accounts listed!

And now for the THURSDAY GIVE-AWAY… put together over 11+ years using EPICOR there are two/similar lists as I once shared my list in exchange for another…

Additionally, some content may be a bit outdated, depending on the release you are on!

Second - there are GL re-balance utilities that exist on E-9 and E-10… RUN THEM!!!

STOCK STATUS - to - GL Comparison
Detailed Summary

What to look for when the Stock Status Report does not tie out to quantities or dollars on other reports, GL accounts, etc. What may help reconcile.

  1. Big item to watch for on all reports involved for is the “As of Date” or date ranges. Make sure they match exactly. Any transactions dated outside/after/before the dates or ranges will or may not be reflected in any totals.
    Inserting dates way out into the future may help catch transactions with incorrect dates.
    Accuracy and timeliness of labor/material transactions against jobs can also improve report.

  2. System Management > Utilities > Database Conversions. Running the 6430 may help synchronize tables so all transactions are included properly. May clear up some corruption.
    ‘Execution of this utility’ should be accomplished in the TEST environment as the reserved quantities will be zero’ed out! Replicate PRODUCTION to TEST to ensure the results of the process are understood/acceptable prior to performing the process in the PRODUCTION environment. Proper review in a non-production environment must be accomplished!

  3. Each parts costing method, costing changes, accuracy and timing of transactions can result comparative variance per GL and Stock Status. Standard cost changes to a part, with jobs in process…. Or jobs that were released w/o complete costs, as well as average cost parts with transactions not being performed in a timely manner will result in variance.
    For dollar amount mismatches, it might be possible that part master costs for the parts have changed?
    Costing changes should be properly/best managed without excess. With regard to Job processing/cost, the estimated cost on a JOB (at the time the job is created) needs to be ‘clean’ or as accurate as possible! If a job is created with only 85% of the cost loaded on a MOM, then the costs are revised, the EST & ACT (estimated and Actual) will be different, as well as the expected standard cost based on std-cost rolls and when (timing) they occur in reference to the material movement off a job. This usually occurs in a std-cost environment when skeleton MOM (Method of Manufacture) structure is in place and Job’s are released. (MOM = BOM [Bill of Material] + BOO [Bill of Operations])
    Coordination and management of updates to std-cost and updating the JOB/MOM configuration are dependent or associated events! More-so…. In an extended tree-hierarchy of a top level finished good with a number of sub-assemblies (that may not be completely defined when the top-level job is created could result in sub-assembly or component cost absorption/incorporation to the upper level at zero-cost, then the sub-assemblies become fully defined per costing, but the std-cost roll and required management of job update are not attended to.
    Additionally, purchased parts… especially new purchased parts w/o any cost (last cost) when rolled up to a std-cost will understate std-cost. Parts are procured, received, and issued…. with cost, but the part-moved off the job is processed at the lesser standard.
    Why? Impact of timing and disassociation of processes?
    Transactions recorded to the GL Inventory accounts were done at whatever the cost was at the time the transaction was created, which was probably different than the current part master cost used for the Stock Status Report valuation. < oops!>
    The stock status values on-hand qty only by the current part master cost. No history is kept as to cost change (FIFO is an exception). Transactions recorded in the GL Inventory accounts were done at whatever the cost was at the time the transaction was created, which was probably different than the current part master cost used for the Stock Status Report valuation. Std-cost roll AFTER SHIPMENT, but before processing Capture COS/WIP!!!

  4. Issues as to GL account association/Set-up as per part or part class being incorrect!
    For some part master parts, perhaps GL control set-up may use or in the past have used a Part Class with the Inventory Account set to an expense account rather than an Inventory account. When the capture and post to GL was done, this expense account would be updated instead, leading to discrepancies when trying to compare a GL Inventory Account balance to the Stock Status Report value.
    PUR-STK transactions which are updating the Stock Status and/or other Inventory reports may be going to expense accounts in GL rather than GL Inventory accounts. If these accounts match now, maybe at some point in the past they didn’t match.

  5. Also possible: Part(s) have no part class, then the system looks for accounts in the Control Accounts — in company configuration where master GL Controls reside (don’t forget to AUDIT THESE!— where perhaps the Inventory account is once again set to an expense account? Or something outside of Inventory?

  6. NOTE: STOCK STATUS REPORT does not show: non-nettable bin inventory; Inspection; DMR; and RMA Inventory based on the execution of business processes within the ERP platform.
    If you have the setting “MOVE COSTS TO DMR” the costs won’t be on the WIP report!!!
    Additionally, as previously noted standard cost processing (entry #3) will impact inventory valuation, as well as the impact of out-of-order transaction processing!

EX: PO’s were received to non-nettable bins on their receipts, which does not affect / increase on-hand qty.
Therefore, any items still sitting in these non-nettable bins are not included in the on-hand qty for the stock status report calculations. But the initial receipt entered as to receipt entry creates the normal PUR-STK transaction for the full dollar amounts of Qty Received X PO Cost. This debits Inventory and credits AP Clearing in the GL after the Capture WIP / Post process is run.
This has “BIG” potential for creating disparities.

  1. Manual GL entries made to the Inventory accounts will not be reflected on the Stock Status Report totals.

  2. To find other accounts that may hold Inv Transactions, run the Inventory/WIP reconciliation report and choose transaction types that have STK and QTY in them. Then look at what GL Accounts are affected by these
    transactions.

  3. Look on the Inventory WIP Reconciliation Report, if PUR-UKN transactions are going to Raw Material accounts it could create a “BIG” difference, since there are no corresponding parts on the Stock Status report to match these.

PROBLEM DESCRIPTION:

The Stock Status Report does not tie out to the General Ledger.

RESPONSE:

The Stock Status Report and General Ledger may not tie out due to the following situations:

  1. Rounding variances.

  2. Journal entries are made to the general ledger that do not correspond with the transactions posted from the inventory interface.

  3. Version 3.00 and higher does not post RMA transactions to the general ledger.
    Transactions created from the RMA transaction occur once the disposal has occurred.

  4. Version 3.00 and higher does not post a variance between purchased parts standard cost and purchase order receipt unit costs to the general ledger.

  5. Parts going to negative on-hand quantity where the average unit cost is different that the eventual purchase order receipt unit cost. THIS can be a killer all over the place! WHY? because there can NEVER be negative value! So when EPICOR moves the part, the valuation is (by default) ZERO! Until qty > 0… then the average will become wonky (thanks Canada for that word!)

  6. Non-nettable bins are not included on the Stock Status Report.

  7. Transactions back dated to prior months after closing a month. The Stock Status Report run at the time the inventory transactions were posted to the general ledger through the Capture COS/WIP Activity function will be different when run after back dated transactions are entered even though it is run with the same “As of” date.
    Management of Fiscal Period Close and Earliest apply date!

  8. The Stock Status Report uses the “As of” date to determine the on-hand quantities. The unit cost (average, last or standard) used in the report is the unit cost at the moment the Stock Status Report is run.

  9. When the Capture COS/WIP Activity function is run and posted a second time for a period, the new posted transactions will hit the general ledger but the Stock Status Report Report run at the time of the original “month-end” Capture and Post will not reflect these additional transactions. Also, rerunning the Stock Status Report with the period ending “As of” date will not report period ending costs as stated in number 7 above.

  10. The stock status does not contain future transactions, those dated after the As of Date referred to above.


(Mike Abell) #5

Joe, thank you so much for this VERY detailed answer….

Best regards

Mike Abell
IT Manager

Flexial Corporation
a company of BOA Group
1483 Gould Drive, Cookeville, TN 38506, USA

Phone: +1 931 432 8408
Fax: +1 931 432 1889
Mail: mabell@flexial.com
Website: http://www.flexial.comhttp://www.flexial.com/
http://www.boagroup.comhttp://www.boagroup.com/


(Mark Damen) #6

I have another big one to state - if you have Part A with Part Class 123 and put it into inventory it will hit the GL account associated with the GL Control code attached to Part Class 123 (typically, if you haven’t specified a GL Control code at part level too). At this point, if you were able to run the Stock Status report and Chart Tracker for this part (and this part only, which you can’t …!) then you would see it balance.

Now, somebody comes along and decides to change either the Part Class that the Part belongs to, or the GL Account for the GL Control Code attached to Part class. There will be NO JOURNAL made to GL, the dollars stay in the same account that they went to originally. BUT, if you run stock status again you may be expecting it to be in Raw Materials but it’s not a Finished Good.


(John Kayton) #7

Hey Mike,

What GL accounts did you use to balance the accounts?