where is the stock status report taking the unit cost from? We are trying to use it to reconcile our inventory balances but I am not convinced it is a good tool.
For instance, I have a part with a unit cost of $3.86 up until 12/31/2018 and a new cost of $ 3.40 after a revaluation on 1/1/2019. But If I run the report with a “As Of Date” of 12/31/2018, it takes the cost of 1/1/2019 in stead of the one on 12/31/2018.
You are correct. The Stock Status report WILL give you quantities by date but not costs. There is no rhyme or reason as to where the costs come from - ESPECIALLY if using average costs. I engaged Epicor on this and the conclusion was the report was working as designed. It’s a very popular issue so I sent a note to Staci’s group about adding the explanation to the Help and Documentation but I do not see any notice in 10.2.300.8’s help.
What some users do is to copy off the PartCost table daily and then use that to determine the costs on that day. At least I think that’s how I understood them getting around this.
Something to keep in mind about average costing. Average costing is an accounting practice that is trying to estimate your current value of your inventory, not giving you the average price you paid for your stuff from now back to when you started using Epicor. So for some examples.
If I have 0 on hand, and I receive 1 at $100, the average cost is now $100. I can say that with complete confidence without even knowing what your history or previous average cost was on that part, because the average cost is the calculated like this. ((Received Cost x Rcvd Qty) + (Prior cost x Prior QOH)) / (Rcvd Qty + Prior QOH). Notice the prior qty on hand in there, if 0 means that the prior cost is irrelevant. The goal of average costing is to make the value of your inventory the most accurate, and if you had none, and you bought 1 for $100 you inventory is $100. It doesn’t matter that you bough the last batch of the for $25. It has no bearing on your current inventory valuation.
If you have inventory on hand already, it’s basically a weighted average of what you paid for the items. If you had 1 at $10, and you bought 10 at $100, the average isn’t $55 because you have more of the $100 ones. ((10010)+(101))/(10+1)=$91.81. The calculation doesn’t care about any previous costs either. Also Once you hit 0 on hand, it doesn’t matter what you had in the past, your average cost is basically now the same as last cost and a clean slate. For calculating inventory valuations, it makes sense to do it this way.
So this makes sense in accounting world when it comes to inventory valuations. But, If you are trying to see what something is going to cost you, and you just got gouged for the last 2 you bought because you needed them rushed, or your salesman blackmailed your supplier after a night of debauchery and you got it for half price, it might not be the best number to evaluate future costs. You are probably better off actually averaging the purchase price of the parts that you bought, and if the price is steadily changing, plotting the change.
But back to the question at hand about getting a cost on a particular day, If you want to see the cost on a certain day, you should be able to use the part tran table and query a transaction for that part number that is the last transaction that was earlier than the day in question and take the unit cost of that transaction.
My 2 cents anyways.
I scheduled the SSR to run every day at 12:01, emailing me the results. Then I have an archive of the QOH and cost, for each day.
It doesn’t resolve the issues with the effects of back dated transactions on QOH and costs for that date. But at least I have something to start with.