Hello,
Does anyone have an inventory report that they run at month-end to tie out to the GL for inventory? We run the Stock Status report and it does not tie due to capturing monthly. Do you make a journal entry on a monthly basis to tie out the GL to the Stock Status?
Below is a reconciliation of our inventory GL to Stock Status.
This comes up all of the time and the Epicor response is that you cannot do it.
That being said, what costing method do you use? Do you use lot or serial tracking?
We use average costing. We do not use lot or serial tracking.
What are other companies doing to tie out inventory? Do you make a monthly journal entry to tie out to the Stock Status report? I would think that report would be the accurate value of inventory.
Like @John_Mitchell said, it’s been covered here many, many times, and there is an EpicCare KB article as well.
“Subledger” is what people call it sometimes, if that helps the searching.
But it just can’t be done. You can backdate transactions and that will always make a mess of any attempt to reconcile - though it’s nearly impossible even without that.
Just figure out what will make the auditors happy.
I’m not an accountant (sometimes forced to play one) but you should not make GL entries to your inventory accounts. Doing that to balance to the stock status will become a never ending process.
I think with AVG costing you can get closest to balance by running C&P and then the SSR right after before any new transactions are entered.
Also check to make sure none of your Misc codes are directed at your inventory accounts and that your AP dept is not making any direct invoices to those accounts as well.
If you use lot or serial tracking you can tie the parts in stock back to the GL Journal and compare the values between the GL transactions for a part and the calculated cost for the part.
I have found that MFG-VAR and ADJ-CST transactions create almost all of the variance between the GL and the inventory value for us.
This can’t be stressed enough. And it doesn’t just mean no more entries prior to that date.
SSR will use the “current” part costs. Not the cost that it was on the date selected in the SSR!
I cannot believe the Epicor cannot figure out how to create a stock report that ties to the gLs. Sage100 was able to complete this for FIFO Tiers. I have also seen SAP create a stock report that matches.
The problem with Epicor’s stock report is that the cost is always the current day’s average, so if you run a historically Stock Status report it won’t even tie to previous versions.
There is no possible way that places cannot tell production or shipping to wait around for a half a day before putting in transactions.
This is a big gap that I do hope Epicor fixes one day. Sooner the better.
Hello,
I came across this post while doing some research and was surprised to see responses suggesting that this reconciliation is impossible. It is absolutely possible if you’re using average costing, and the process isn’t overly complicated. Our company has been successfully reconciling our Inventory GLs to the Stock Status Report on a monthly basis for years using a straightforward method.
We achieve this by running the Inventory/WIP Reconciliation Report for all unposted transactions and adding that balance to our GL balance. In other words:
Posted balance (GL Report) + Unposted balance (Inventory/WIP Report) = Stock Status Report totals
The Inventory/WIP Reconciliation Report essentially simulates the Capture and Post process. If you check the system monitor while running the report, you’ll see that Epicor executes the capture and post logic in the background to infer all relevant GL transactions without actually posting them. This allows you to preview what would happen if you were to run the capture and post process. We use this report to reconcile our Received Goods, WIP, and Inventory accounts.
One challenge is the potential timing mismatch between running the Inventory/WIP Reconciliation Report, the Stock Status Report, and the GL Report, especially during business hours when transactions are actively being posted. To mitigate this, we schedule all three reports to run after hours (at midnight) when no live transactions occur. Alternatively, scheduling them during work hours could also work, as long as they run simultaneously.
To further streamline this process, we built a GL Variance App using the Epicor REST API to automate report generation for AP, AR, Received Goods, WIP, and Inventory reconciliation. The app compiles the data into a spreadsheet workbook, with sperate sheets breaking down the details for each segment and a summary sheet, enabling our controller to identify variances in just a few clicks. If anyone is interested, I’d be happy to share my code—just send me a DM.
Thanks!
I wish I could heart this like 500x. If you are making monthly entries, something is wrong.
With that said, I have heard some arguments that make sense for self reversing entries at the end of the month to deal with timing issues or wanting to book MTO inventory differently. As long as those entries reverse themselves on the 1st, they should not cause any lasting issues.
@imusial Welcome and I applaud you for reconciling your GL, but for most YMMV for other valuations. Now it can be done, but as Epicor states the stock status is not a sub ledger balancing report since it focuses on warehouses.
I would agree that doing entries to true up should not happen, most importantly understanding how GL controls and not warehouses are how Epicor decides what account a value sits in.
Fighting this issue too. Very difficult but we’re trying to at least come up with a “freeze point” at monthend and a three-way match…getting the onhand values from PartWhse (from which the SSR values are said to be derived), PartBin (recalculating based on PartUOM conversion factors) and the GL.
If we can at least get close (because there’s bound to be at least one item with a bad conversion factor to skew PartBin-based valuation LOL) then that might be enough.
And please do not post the word “auditors” again…triggers a lethal combination of anger and nausea. 
Hi Lisa,
You can tell by the replies this is a hot topic… While it doesn’t always get you the exact tie, it might be able to help you get close. I’ve set up an EOM Process Set to run the last day of the month, off hours. In it, we first run our COS/WIP Capture and then the reconciling reports. That way no transactions get in between the capture and the reports.
Best practice is to not post manual journal entries against your inventory accounts. If there is a large reconciling item where you can fix the transaction the following month (e.g., received 10000 instead of 100), then you would create a reversing entry in a manual inventory account. For example, 100000 for the automated transactions; 100001 for manual, reversing entries.
There are some other things you can do to prevent past/future dated transactions impacting your reconciliation. You can close all past and future periods. When you are ready to start transactions in that future period, open it up. You can also use Earliest Apply Date to hold a period open for month end things like cash and invoice posting but not allow inventory transactions. There are a couple of new checkmarks in Book > Validations you want to look at if you go this route. To make sure transactions happen in the proper period you would want to disable autocorrecting apply date and fiscal period closed. With those set to error, the user entering the transaction would have to pick the correct transaction date.
Jenn
That’s what we’re aiming for too…scheduled SSRs run via email, one per site. Also built an Access DB with an archive to snapshot the PartBin and PartWhse inventories on the 1st of the month shortly after 12am and email them via Excel. We’re small enough that it’s manageable - did it on our last two ERP platforms as well.
Part of the reason that the GL gets out of whack is that it is all too easy to buy a part against one GL account and then issue it from inventory against a different GL account.
For example:
I can explain some other scenarios, too.
Really, all you have to do is change a part class on a part and boom, GL is off at the very next transaction.
Or the people that make a PO for some MRO item (coffee, for example) and choose a part class that goes to inventory or R&D or anything but the right place.
Ya had to go there eh?
That is also how quick it is to fix things, so it is also part of the wip recon process to see where a transaction hits and if it is not where you want then update gl controls or make posting rule changes to move it.
True…we’ve had to play with controls/changes a little bit, things that were missing from Day1 setup. We’re still n00bs…only been live for ~2y and still see things occasionally break (i.e. an unexpected credit/return/RMA to a partclass that was missed). We’ve tried to keep things so non-customized that vanilla would seem spicy.
Mmmm… I don’t know about that. Sometimes, sure.
But let’s say this happens:
What happens to the $1,743.88 in the expense account? Nothing, unless you do either
Messing it up is a lot faster than fixing it.
Truth…there’s a learning curve for everything as far as setup goes - the more you do, the better you get at it, the faster you remember the pitfalls. We haven’t needed any datafixes recently (as I gently rap on my wooden head).