Added "Inventory Costing Calculation Worksheet"

[lubos]

The latest version (25.1.3) introduces a new report under the Reports tab called Inventory Costing Calculation Worksheet.

This report is designed to calculate the unit cost of your inventory items as of a specific date and based on a specific valuation method—currently, FIFO or WAC.

Streamlined Recalculate Button

The Recalculate button under the Inventory Items tab has also been revamped.

When you click Recalculate, Manager pulls average costs from the Inventory Costing Calculation Worksheet and saves them as Inventory Unit Costs in your database. You can view these under the Settings tab.

Key Changes in the New System

The most noticeable difference is how costs are updated and displayed:

• In the old system, inventory figures were recalculated on demand, and the Total cost field was clickable.

• In the new system, Total cost is no longer clickable.

  

Instead, if you want to see how unit costs (and total costs) are calculated, simply generate the Inventory Costing Calculation Worksheet, which provides a detailed breakdown.

Why the Changes?

Here’s the thinking behind this update:

1. Performance Improvements

Some businesses using Manager have nearly 1 million transactions, causing significant slowdowns due to constant recalculations. The new Recalculate button puts you in control of when unit costs are recalculated.

2. Data Stability

Previously, inventory costs were recalculated on demand, meaning updates to the inventory costing algorithm could retroactively change figures, even in locked periods—clearly not ideal. Now, unit costs are saved in the database, ensuring past figures remain consistent and unaffected by software updates.

3. Flexibility

With this system, you can now switch between costing methods (e.g., FIFO or WAC) easily. You can even apply different methods to different inventory items.

4. Simpler to Understand

The new approach is more intuitive. Your Inventory on hand balance is just:

Unit Costs × Qty Owned

And Cost of Goods Sold is:

Opening Inventory + Purchases - Closing Inventory

[unicornfns]

HI @lubos, My COGS is messed up again after i do the inventory costing calculation, as shown in this picture, i have restart my cloud server and the issue still there.

Shown = 37,241,574, but that’s not correct COGS, and when i clicked on it, shown like this =

I have sent you a message about this issue.

[lubos]

Drilling-down is partially broken when using custom cost of goods sold accounts. Working on it.

[unicornfns]

Even after removing the Costing Calculation & Restart the Cloud, the issue still remain the same @lubos

[Najrash]

@lubos

Drilling-down is partially broken when using custom cost of goods sold accounts. Working on it.

We look forward to your proper update on this. More grace to your strength

[ejjaadiargo]

@lubos

Drilling-down is partially broken when using custom cost of goods sold accounts. Working on it.

Please also look bugs here >>> Anomaly number in COGS account

I see the drill down consist of opening, purchase and closing. As addition for your work in drill down, please create some feature that we are still able to see the details in dropdown regard the COGS that have been recognized/used, not only opening, purchasing and closing but please include the increasing COGS from any sales (or other custom account designed to record COGS).

[shahabb]

@lubos

meaning updates to the inventory costing algorithm could retroactively change figures

@lubos The current algorithm for calculating WAC is same as the one used in automatic recalculation or it has been changed? Because the calculated cost on Production Order differs from the cost on worksheet.

[lubos]

@ejjaadiargo

As addition for your work in drill down, please create some feature that we are still able to see the details in dropdown regard the COGS that have been recognized/used, not only opening, purchasing and closing

This won’t be possible. The reason why I’m moving to COGS = Opening inventory + Purchases - Closing inventory is to make COGS fast to calculate and also making calculation immutable to changes in future upgrades so historical figures do not change. What you are asking would require to calculate costs per each transaction and this is where complexity creeps in. You can use Inventory Costing Calculation Worksheet to see how closing inventory can be calculated and in case of WAC method, it does calculate costs on each transaction. The point is decoupling these complex calculations from general ledger so if there are improvements to any inventory costing algorithm, it won’t impact historical general ledger figures.

@shahabb

The current algorithm for calculating WAC is same as the one used in automatic recalculation or it has been changed? Because the calculated cost on Production Order differs from the cost on worksheet.

I don’t think they should differ. But worksheet is as of specific date. Maybe there are some extra costs flowing to the finished goods later on due to negative inventory which are shown on production order but not on worksheet because worksheet is up to specific date?

When I make finalize the transition, I will look into how to handle this issue.

[ejjaadiargo]

@lubos

The point is decoupling these complex calculations from general ledger so if there are improvements to any inventory costing algorithm, it won’t impact historical general ledger figures.

1. so what is going on below? This picture below is coming from PnL Statement on COGS account.
   
   

Those really different to the report on Inventory Value Movement (at same period). see below (its opening balance 11.471.118,83; Purchase 52.656.500 and has no record on COGS/adjustments and of course different closing balance ):

2. How to trace the COGS if you give like that? which one we can use? All different.

3. Those calculation also different to the General Ledger Summary report (of course at same period), see below inside blue mark :
   

   
   
   Those are total debit 82.054 (this is correct), total credit 989.034 (wrong) and if we click on net movement is also messed up (see the anomaly transaction can’t be traced inside blue mark below) :
   
   

I’m really frustate for this last month regard very unstable inventory method, especially this is our closing period, it is really messed up of all inventory formula

you have to see @lubos

[Ealfardan]

@lubos

The point is decoupling these complex calculations from general ledger so if there are improvements to any inventory costing algorithm, it won’t impact historical general ledger figures.

That is great news! However, I still think that a lot has be left on the table.

Most other software calculate cost at transactional level and fill the general ledger field with static values. Later, when historical records get updated, the cost figures for these particular records get recomputed based on the current state of inventory cost records.

I know that this – in case FIFO is used – will trigger an endless chain of recalculations, but I’m sure that there could be a solution to this if we tried hard enough. I would argue that dropping FIFO altogether could be a viable solution.

One of the use cases is expense-revenue matching, customer profitability analysis, costing with Production Orders (which I believe took the greatest beating as a result of this major shift in objectives). Idealy, every batch should have its own cost known otherwise why bother using Manager to record production.

I’m starting to question whether FIFO is worth the trouble considering the number of other features being sacrificed for it.

[ejjaadiargo]

@Ealfardan

Idealy, every batch should have its own cost known otherwise why bother using Manager to record production.

Yes that’s good. Should in FIFO counted based on every single batch per item in inventory. So if user wants to use the item, the inventory unit cost determined by which the batch when procured.

Then for the WAC method, as simply as mix inventory unit cost of all procure. So the inventory unit cost determined automatically by manager based on batch (in FIFO) and based on mixing cost on procure (WAC) then maybe we don’t need feature to determine the unit cost which it makes confuse, maybe contains of subjectivity and maybe affect to messed up in inventory calculation, affect to anomaly in COGS detail, unaligned balance in several report regard to inventory and COGS (in PnL, Inventory value movement, inventory unit cost, General Ledger Summary).

But I know i’m lack of knowledge in IT Infrastructure behind manager, hopefully this problem will ended up really soon because now is January 2025, we need to do closing and reporting period for 2024 soon but all calculation are messed up for this last month and very stressful because of these.

[ejjaadiargo]

@lubos

And Cost of Goods Sold is:

Opening Inventory + Purchases - Closing Inventory

@lubos maybe this potentially to be bias or missleading. I see this condition like below :
From PnL

From Inventory Value Movement

See the closing balance, those picture are from the detail in COGS account (PnL) and from the inventory value movement. The calculation maybe correct (see the closing balance). But if only use that method (opening balance + purchase - closing balance) it potentially missleading and maybe bias, because not all inventory that went out from warehouse were COGS. For example that inventory of mine were partially used as raw material for production orders to create finished good and another part were also sold but all usage allocated to COGS account.

Is there any setting to make this right? @lubos

[basilhaddad]

This new option missed up our financial statements! is there any way to retrieve the old options and calculations?
We used to work on the system since 2018 and now when we want to prepare our financial statements, we faced a huge problem in previous years as well and all statements were missed up big time
on the other hand, write off is not recognizing assets account, i want to write off some of inventory items to be recognized as fixed assets, why this is happening suddenly ??

[ejjaadiargo]

worst condition, we are in the end of year that many business doing closing book and financial reporting but all messed up. I got frustate since Dec 24 regard this changed.

[Umeadi]

@lubos Good job. Well-done. I have a question please. Must I always click recalculate button on the inventory dashboard before my inventory unit and total cost appear? Initially I don’t have to click any button as it’s done automatically but now it appears I must click it before the costs appear.

[ejjaadiargo]

do your business use production orders?

this abnormal balance of COGS appears to my PnL whereas it comes from production order :

from PnL (negative balance) :

It comes from production order (I trace it from inventory tab that item of inventory has opening balance and purchase are 0 / zero but closing balance filled from production order so then using formula opening balance + purchase - closing balance makes negative balance) :

Do you see this @lubos ? I think this is wrong.

[basilhaddad]

This is becoming frustrating and serious problem, i am now re-checking all previous years with missed us the COGS by huge differences, this is not acceptable & no one is answering even my message
will change the system ASAP since am not feeling secure specially that we are working in a country who takes taxes and auditing in a very serious way and will affect our credibility

[ahmadmusair_3593]

The “Inventory Costing Calculation Worksheet” report menu is not visible to users with custom access. Is this intended behavior or bug? there seems no menu to enable “inventory costing calculation worksheet” report either in the user’s custom access setting

[ahmadmusair_3593]

In this post (Added "Inventory Costing Calculation Worksheet"), regarding point 2, is it necessary to create an Inventory Costing Calculation Worksheet to prevent recalculate from changing past values in reports? Or does recalculate already handle this automatically by creating an inventory unit cost entry for each item every month?

@lubos