Hi everyone,
Can anyone explain what is going on here ? Why do I have negative and positive insufficient quantities.
these are qty on hand transactions:
I discovered this while creating a custom report and saw that I have sales with zero cost for some inventory items.
I have Inventory Automatic Revaluation enabled by default.
Any help is appreciated.
Another way to look at it is when drilling down on inventory sales from the summery page for this item.
I have 18 sales and returns registered:
Then when checking inventory cost for same Item I see only 5 transactions:
Thats because program is only considering quantity available for credit note which you have sold. Even though you have 269 qty on hand/owned (through Journal Entry) initially but you have sold only 12.
First credit note is fine as its less than 12 (11) for second your credit note quantity becomes more than the total qty you sold till the date 10/05/2022 and this behaviour goes on until sold qty balances credit note qty which in your case is not happening thats why you have still insufficient qty.
This should be changed as this can cause issues if you are migrating from another accounting software, @lubos can you look into this?
There is no merit to this. @BawarYassin is already several years a member and should familiarize himself with the changes introduced by @Lubos to better deal with inventory. A simple search but also the new guides tell it all.
@shahabb Exactly! When you have over 300 products in a factory is not uncommon to have declining products that will eventually be discontinued. And this is an example of one.
@eko Iâm aware of the current changes of the program. But in case Iâm missing something kindly link the guide mentioning a solution for this issue because I could not find any.
By the way I tried the new Periodic Revaluation but the result was the same.
Perhaps @lubos could look into this.
edit: typo
I doubt that as using new inventory periodic revaluation method it wont let you click on that total cost figure. All you got is average cost value which you can define yourself or copy from revaluation worksheet.
So it wont be an issue using new revaluation method.
True, it wont let me click on the total but the total link opened in another tab still works and refreshes.
Regardless, I tried below method as well
When using Periodic Revaluation you must disable automatic revaluation.
I have it disabled of course.
As I still have access to the old cost total link I refreshed the tab and the insufficient qty is still there:
What do you expect? A message?
That page belongs to Automatic revaluation, and you have opened it another tab then disabled automatic revaluation to see if it magically vanishes or not.
I donât know why are you guys are avoiding the elephant in the room here. I only mentioned this for more clarification. Forget about that page.
Why there is no cost for these sales? You said because I have more credit notes than sales.
Then how to fix this ? clearly journal entries do not work.
It gets corrected when sales qty becomes more than the credit qty and i already mentioned in my first reply that this needs to be changed and that Journal Entry should be taken into account in credit scenario.
Further if you want to solve it right away use Periodic revaluation that help in most edge cases.
The developer put automatic revaluation as an obsolete feture just sees the future with periodic revaluation. I already posted my disagreement to move that direction for a perpetual accounting system this seems odd. I understand the benefit that this will allow for more costing systems beyond the current average costing but more effort could be put in automatic revaluation using various costing systems rather than going the manual way, Anyway @BawarYassin if you want it solved now then follow the advise by @shahabb
Dear @shahabb I am using Periodic Revaluation in my post here above as you can see there is only a single entry on 28/09/2024. I still dont have cost records for all the occurred sales.
That single entry is showing total cost of that item which your business has realized in a specific period. It doesnât show per cost per sale transaction but shows overall cost of an item which also includes the difference in prices. Let say you have defined 3000-unit price for an item, but you got a better price and purchased it for 2900 then the remaining 100 will be credited to the inventory-cost account of that item and still if you want to value it on Avg cost basis then go to revaluation worksheet which calculates values on Avg basis and create a new revaluation worksheet from there.
To make it easy for you that value is equal to
Cost of item in a period = Opening Stock value + Purchases value - Closing Stock value.
The Opening/Closing stock value = Qty owned * Unit cost (Which you have to define using inventory revaluation in settings)
and this above formula is used by many businesses which i have seen. Only If @lubos can show opening and closing stock values on the inventory cost account details to make it easy for users who are confused.
Thank you for the detailed explanation. What I understand now is that we can only get a correct PL report if credit notes qty is not higher than sales qty in that period. (Which I hope is not true but I will validate all this in test business soon.)
Thatâs the issue with periodic inventory, you will not have a dynamic costing and you canât match the COS to Sales on the invoice level.
I hope I still remain on track with this topic @BawarYassin, so please stop me if I stray too far.
I do remember that at one point, there used to be a Prefill â Cost field in Inventory Items and a cost field in invoice lines at some point.
I donât know why the developer has given up on that course, I thought it was a step in the right direction and combines the best in both of Perpetual & Periodic Inventory methods.
This field could be used to assign standard cost for inventory items so that the expense and revenues are matched on line level. This field could be Batch updated every time a revaluation is made so that Inventory Items Prefill â Cost field is kept up-to-date periodically and later invoices reflect the most recent standard cost.
The field could be kept hidden on invoices so as to keep it simple and not to overwhelm users.
Thank you for your input, indeed this is a problem with periodic inventory. Based on my P/L statement I have to believe that even by using periodic revaluation you cannot fix the issue of âbacksalesâ being higher than âsalesâ because the system introduces the âbacksalesâ into the database differently (unfortunately I still could not make time to validate this but I will post here as soon as I could).
I still hope @lubos would add cost at line level for credit notes to solve this issue (but that is just wishful thinking).
I definitely need to find a solution which combines benefits of automatic perpetual and periodic revaluation.
I donât think thatâs the right approach. First issue is that when business is selling items before purchasing them, then we are in negative inventory territory and cost of sales transaction should not take effect. It should take effect once inventory is purchased but then cost of sales transaction is at different date (this really confuses people).
But even if we get past the confusion, what if you purchase item and sell them. And the cost on sale transaction is different from purchase price. Which figure should we use. From accounting point of view it should be purchase price but then when do you draw the line when to use purchase price and when to use manually entered cost price on sale transaction. The point is, you run into problems and solving these problems just makes everything more complicated.
I think the issue with periodic revaluation is that it âfeelsâ too manual. If Manager would prompt you to revaluate your inventory every time you drill-down into your inventory, then perhaps it wouldnât feel like work.
Periodic revaluation will have still trouble figuring out cost of items that have been produced out of negative inventory (we donât know the cost) but periodic revaluation could make an educated guess. Either way, @BawarYassin under periodic revaluation Insufficent Qty is not an issue. I will need to improve this part.
But first thing that will need to happen, I need to somehow convince everyone that periodic revaluation is the way to go. Most people do not agree. I think they donât agree because periodic revaluation seems like extra work while automatic perpetual revaluation was - well - automatic.
This is what i was thinking it would make things easier. But instead of inventory make it on Purchase invoices. Either a prompt or an automatic valuation entry for items that are in included in Purchase Invoice.
This way a user can change valuation later like going from FIFO to Avg costing without affecting previous transactions.
Or
Instead of going for easy route implement valuation of Goods Receipts that would solve plenty of negative inventory issues.
And make both valuation methods optional.
