Added inventory cost correction workflow

[lubos]

In the latest version (25.2.19) when you go to Inventory Items tab, you can see Average cost column.

If your average costs seem to be wrong, it’s because your total costs under Total cost column are incorrect.

There is now new button named Inventory Cost Correction Worksheet in the bottom-right corner under Inventory Items tab.

Clicking on this button will reveal all inventory items which seem to have discrepancies in Total cost.

If inventory item has discrepancy, then it means its costs need to be recalculated. You can do it by clicking on Discrepancy figure.

This will take you to the ledger of inventory item where you will see Recalculate button under Unit Cost column.

When you click Recalculate button, Manager will recalculate unit costs for inventory item and saves the results in the database. Recalculation brings discrepancy to zero so it will disappear from Inventory Cost Correction Worksheet screen. If you recalculate all your inventory items, then Inventory Cost Correction Worksheet screen should become empty.

Unfortunately, at this time recalculation needs to be done for each inventory item separately but I’m working on a report that will allow to do this all at once.

[shahabb]

@lubos This new Recalculation algorithm cannot handle negative inventory.

Old Method

After Recalculating

The ledger of Item A is overvalued because the Cost of Sales is valued at zero.

[Kriala]

Hello, how can I use Inventory Cost Correction Worksheet? Can I do batch correction and how?

[Burhania]

Hi @lubos, I tried to use the correction worksheet, how ever, I encountered:-

1. Some items can’t be recalculated, they remain silent - through small in number ( i.e. 90 items accept recalculate where 6 items are nonresponsive in my real case).
2. This is a very fatigue job of calculating one by one for a hundreds of items in a single company let alone others.
3. Retained Earning A/c is fluctuating with these calculations.

Dear @Lubos, I would highly recommend that we go back to earlier version of the program where the system was stable when it comes to Inventory management for both Merchandising as well as Manufacturing organizations. Our bookkeeping clients lost trust both in the software and in us - because they see changes in their financials (specifically - Cogs & Retained earnings ) in the morning when they come office which is different than what they left yesterday.
I think the solution is simple - make the system perpetual-based system where the system calculates itself for all inventory movements, rather than making manual re-calculate button, and WAC as our costing method ( do not bother FIFO or LIFO) - this is the basics for any accounting software and make this functionality stable.

Thanks

[lubos]

@Burhania

Some items can’t be recalculated, they remain silent - through small in number ( i.e. 90 items accept recalculate where 6 items are nonresponsive in my real case).

Could you show ledger for those 6 inventory items or give me access in some way?

@Burhania

This is a very fatigue job of calculating one by one for a hundreds of items in a single company let alone others.

I’m working on a report that will do it with just single click.

@Burhania

Retained Earning A/c is fluctuating with these calculations.

That’s because recalculation is changing your cost of sales which feeds into retained earnings. However, upcoming report will be for the period. This is to ensure that you won’t recalculate past historical periods if they are already closed (and locked).

@Burhania

I would highly recommend that we go back to earlier version of the program where the system was stable

I keep getting this a lot. However the new inventory approach is storing unit costs in the database which means future versions won’t break your historical balances. The worst thing is that when you upgrade to newer version, your historical inventory cost of sales figures change because these costs were calculated by the software on demand. The new inventory approach is turning your cost of sales figures into data so you will get the same balances regardless of the version you are using. Previously cost of sales figures were calculated on demand by algorithm which means merely using different version could produce different cost of sales figures.

This was a big problem. Someone reports some bug in inventory costing. Now I’m facing hard choice. Fix the bug but then many businesses who didn’t even realize they were affected by the same bug have their cost of sales figures slightly changed. Or I can do nothing and keep telling people forever “it can’t be fixed because it’s too late”. Not to mention, there are some feature requests in ideas category that can’t move forward until this is addressed for the same reason.

@Burhania

Our bookkeeping clients lost trust both in the software and in us - because they see changes in their financials

The transition has been extremely rough. I agree. The big issue is that the initial design I had in mind (periodic inventory) felt apart for businesses with complex inventory requirements. So I was going in wrong direction for a while and as a result everyone was suffering.

It will take some time to restore trust. I’m confident the trust will be restored in upcoming months when businesses see progress on topics in ideas category.

@Burhania

I think the solution is simple - make the system perpetual-based system where the system calculates itself for all inventory movements, rather than making manual re-calculate button

The inventory is now perpetual (costs are now calculated separately on every sales, production order, inventory write-off etc). This is why you can see Cost of sales for invoice and we will be able to do fancy things like showing projected gross profit margin on individual orders etc.

As for making it 100% automatic, I was considering this but it’s just not viable. You absolutely do not want your figures to be recalculated automatically if the software notices they are not correct. There needs to be manual trigger so that software doesn’t recalculate your cost of sales in closed periods.

You might say - we have Lock Date to prevent this. Sure. But once in a while, lock date is sometime lifted. You would not expect your cost of sales to be recalculated for all your past periods just because you have removed Lock Date for a few minutes.

The way I’m thinking about this is that when you are viewing financial statements such as balance sheet, Manager can tell you.

• You have 3 unreconciled bank accounts (Reconcile button)
• You have 8 inventory items with inaccurate cost of sales (Recalculate button).
• etc.

So recalculate button can be eventually so subtle, you just click it to get the yellow notice to go away. But it needs to be clicked. And if you did something you didn’t intend to do (e.g. clicking Recalculate on historical balance sheet when lock date was lifted temporarily). You should be able to undo it using History. If these things are 100% automatic, they can happen when you do not intend them to happen and there is no way to undo them because well, undoing them would immediately trigger correction. So that’s why it cannot be 100% automatic but we can make it so subtle that it will seem like automatic because you won’t be thinking about it.

[eko]

@lubos

The big issue is that the initial design I had in mind (periodic inventory) felt apart for businesses with complex inventory requirements.

Yes!

@lubos

The inventory is now perpetual (costs are now calculated separately on every sales, production order, inventory write-off etc).

No! You are not implementing it as it should be because it is not instant and requires users to click recalculate buttons or similar. So it is manual and therefore still periodic.

This is proven by your statement:

@lubos

As for making it 100% automatic, I was considering this but it’s just not viable

But it was before, no issues then!

Despite your efforts you changed something that worked but only for average and not for FIFO/FIFO and therefore you need to strongly consider if it is a good move to go manual just because of this.

[Patch]

@lubos

As for making it 100% automatic, I was considering this but it’s just not viable. You absolutely do not want your figures to be recalculated automatically if the software notices they are not correct. There needs to be manual trigger so that software doesn’t recalculate your cost of sales in closed periods.

The best of both worlds would be automatic calculation (and storage) of new transaction data based on continuously updated costing data and chosen method (FIFO, LIFO, running average).

Then should any correction of prior data be required, this would always be manual via an appropriate worksheet.

As for the performance implications, I’m not convinced loss of program functionality for all due to a minority of users having very big inventory lists is optimal. Alternatively the new inventory price could be calculated in background after a price effecting transaction occurs (so the calculations do not delay user response times).

[Christopher_Esmeres]

I have clients with hundreds of SKUs and been using for years with no problem, it’s just this update that created chaos among them.

I firmly believed based on my more than 20 years in computerized accounting x perpetual inventory (automatic) it’s more robust and reliable compared to the current that needs human intervention. Yes i agree that it could create problem with COS by the time that inventory cost is messed up also assuming that the lock date is lifted. BUT, inventory costing is date sensitive, esp the weighted average method, as long as you don’t post inventory transactions on prior dates, PNL and Balance Sheet data will always be accurate.

I suggest that we perform 2 locking mechanism, one that is already existing (Lock Date) feature, and the other one is LOCK PERIOD of which this is a secondary control to prevent mistake on date. The system is locking the whole period may it be calendar year of fiscal year. Second line of defense. Let alone performance issues since never encountered it even with my big clients with hundreds of transactions daily for several years.

[Burhania]

@lubos

Could you show ledger for those 6 inventory items or give me access in some way?

This one Item which is non-responsive. The red line is to draw your attention where i suspect some thing is wrong and shows an unknown transaction debiting $4025 as a closing balance.
All other remaining items are the same - internal unknown transaction exists.

[shahabb]

That last entry is system generated, and it happens when the balance amount of item goes into Credit (negative) then system create that entry to clear the negative balance amount too.
I think this is happening because of negative inventory. I believe when the algorithm is updated to handle negative inventory this issue will go away by itself.

[lubos]

@shahabb

This new Recalculation algorithm cannot handle negative inventory.

The latest version (25.2.21) adds support for negative inventory when using Recalculate button.

[shahabb]

Thanks. Its better now.

[sonicgroup]

@lubos

There is now new button named Inventory Cost Correction Worksheet in the bottom-right corner under Inventory Items tab.

A big gift of ease from @lubos . I must appreciate this work around and a good idea that will save a lot of time. Before, for clicking each recalculate button would take almost 10 working hour, a tedious job. Now, with this update, the system listed just 20 Inventory items with flaws and it took just 15 mins to get it done. A great time saver and a great idea, i must commend @lubos for this brilliant innovative work around.

[Burhania]

Is there a batch update for the Cost correction worksheet ? or only the Negative Qty is resolved - plz advice me

[lubos]

Batch Recalculate button has been added Inventory Cost Correction screen.

Added ability to batch recalculate inventory costs

In the latest version (25.2.25) you can now batch recalculate your inventory costs. This is done on Inventory Cost Correction screen which you can access by going to Inventory Items tab, then clicking Inventory Cost Correction button in bottom-right corner. [image] Inventory Cost Correction screen shows all your inventory items and calculates Discrepancy. If there is discrepancy, then you will see discrepancy amount and checkbox. Selecting the first checkbox will select all items so you don’t…

[Wafra_Farms]

@lubos
Balances are still affected retrospectively when using recalculation or cost correction.

[Ealfardan]

That could be because you haven’t a Lock date in place, do you?

[Sweey]

No, this is because the entry is sometimes not calculated correctly. The correct calculation is done after the recalculation which affects the historical balances. I recently noticed that the recalculation is recalculating using the periodic inventory method. This is still a problem for us.

[Ealfardan]

The only way to make an inventory adjustment fully adherent to perpetual method would involve editing the historical data. This would breach the “cut-off” principle of accounting and create more hassle.

The idea behind this adjustment is to allow for swift correction of inventory valuation without going out of your way to restate everything is to make a periodic adjustment to your perpetual records. This is by far what is considered best practice by most – if not all – auditors and public accountants.

Now, addressing specific points that you raised:

@Sweey

The correct calculation is done after the recalculation which affects the historical balances.

I’m not sure whether this describes the current process or is a proposal for change.

In case the current process is afecting your historical records in locked periods then that would be a bug so please demonstrate with screenshots how we can replicate this behavior.

In case you propose changing historical figures to correct inventory, I think I just described why it’s better not to do that.

@Sweey

I recently noticed that the recalculation is recalculating using the periodic inventory method. This is still a problem for us.

Ideally, both periodic and perpetual methods would result in more or less the same figures except for when user input errors exist in which case you are faced with the following trade-off:

1. Correct historical records and bear the large cost of having to restate each an every transaction and possibly audited financials

Or

2. Make a single correction in the current period to revalue the balance sheet figures to their correct value and have the current profit and loss bear the entire correction.

In any case, the only difference this will have is when looking at the costs on Sales Invoice level. But if you consider that both perpetual and periodic inventory costing are nothing but glorified estimates of costs, then how can anyone objectively tell if one method of cost estimation is superior to the other?

From my pov, method 2 is the superior method since constant restatement of financial records is a poor accounting practice and also indicates that the underlying issue more often than not is wrong user inputs in which case we are dealing with GIGO.

Having said that, I’m not dismissing your case as irrelevant, you can still demonstrate your situation and show us the variances you are getting so we can learn more about it.

[Sweey]

I don’t know what you’re talking about. I didn’t suggest anything. You clearly didn’t understand anything I said. You talk about accounting as if it were a new science. The bigger problem is that most of what you’re saying is incorrect. I’m sorry, I don’t think I can argue with you.