Get a handle on your inventory valuation

Christmas is just around the corner, the New Year follows shortly after, and before we know it, it is January. For all of you whose calendar year and financial year follow the same months, January usually offers the task of creating an overview of the inventory value. We usually also notice this in tracezilla support, where we receive many questions concerning inventory valuation every January. This year, we have decided to write a guide for you that gathers the most important knowledge about inventory valuation in tracezilla and points you to our various tutorials on the topic. We hope that this guide will make the upcoming valuation easier to approach - and of course it is also for those of you who need to calculate the inventory value at any other time of the year, or do so every month.

Inventory valuation in tracezilla

With tracezilla, the calculation of your inventory value is automated and optimised, because information about inventory levels, costs, and cost prices is collected in one place and updated in real time. This reduces time consumption and the risk of manual errors - but remember that the calculations are only as good as the data you have made available to the system.

Report tabs

The inventory valuation report can be found under Financials > Inventory Valuation. The report consists of six tabs with data. The tabs Summary, Movements, Accrual, By Lot and By SKU contain the total inventory value calculated using two different methods (see more below), while the tabs Inbound and Outbound show background data for the tab Summary, Movements. If you have an integration to an accounting system, it is the inventory value in the tab Summery, Movements that can be transferred to the accounting system, and it is therefore this tab you should base your work on. The six tabs can individually be retrieved in a print-friendly version and downloaded. Soon it will also be possible to download all six tabs in one combined file. You can read more about the individual tabs here.

Report filters

On the left side of the report you will find a filter section. Here you can filter by a single SKU as well as SKU category and SKU tags, just as you can generate the report for a specific movement type (e.g. purchase, return, or sale) or a specific partner location. Please note that the filters may result in different values in the different tabs depending on how the inventory value is calculated. Read about the report’s two calculation methods below.

Expected vs. actual cost prices If you have activated the extension actual cost pricing, you will be able to choose whether the inventory valuation should be calculated based on actual or expected cost prices. Actual cost prices should only be chosen if you continuously import expenses into tracezilla and match them with your orders. If you do not have a systematic registration of actual cost prices, the inventory value will typically be very incomplete if actual cost prices are selected for the valuation.

Beginning of month and cut-off month The inventory value is only calculated for whole, closed months. If no period is specified in the filter, the report is based on the most recently closed month. You can define the period you want to view the inventory valuation for. The selected month in the Beginning of month-field determines which opening value is shown in the tab Summary, Movements, as well as how much data is included in the tabs Accrual, Inbound and Outbound. The cut-off month determines which month the inventory value is calculated for. The selected cut-off month should always match the cut-off month on which the inventory value is calculated, as the cost price of non-traceable items will typically change over time. At the top of the filter section you can see which cut-off month the inventory value has been calculated for. If you want to look at another month, you should therefore recalculate the report with this cut-off month - but be aware that this is a large calculation which, depending on the amount of data in your account, can take 10-15 minutes to complete, and that it is not possible to save new data in the system during this period.

Calculation methods

Inventory value is calculated using two different methods;

(1) As a calculation based on the total value of inventory inflow and outflow.

(2) As the sum of the value of all individual lots where the value of each lot is calculated as cost price x quantity. This calculation method is also shown per SKU where the value of each lot of every individual SKU is summed.

Calculation method (1) is shown in the tabs Summary, Movements and Accrual (where the value is distributed across different phases, e.g. in transit from supplier, in stock, etc.), Inbound and Outbound. Calculation method (2) is shown in the tabs By Lot and By SKU. Because these are two different calculation methods there may be small discrepancies in the total inventory value between the methods. Larger discrepancies, however, indicate registration errors. These will typically be errors in dates that lead to the value of a lot to be counted both as a raw material and as a produced lot.

Read more about how tracezilla calculates cost price here.

What affects inventory value

Order dates

It is not the status of an order or a lot that determines whether the value of a specific lot is included in the inventory value. Instead, it is the order dates that are decisive.

If a posting date appears on an order, this will be decisive for the inventory value. On purchase orders or production orders, a posting date can be added when the order is edited. On sales orders, a posting date is added when the order is invoiced - regardless of whether the goods have been delivered or not. The posting date is set to the same date as the first invoice date.

If there is no posting date on an order, the delivery date on the order will be decisive for the inventory value. This means that if an order has not been received, the order’s value will still be included in the inventory value if the delivery date falls within the calculated period. Orders in draft status are not included in the calculations.

Note that when inventory value is calculated based on actual cost prices, it is the invoice dates of the expenses that define when a lot is included in the inventory value. The value of inventory inflow will never be counted as inventory outflow before the inventory inflow has taken place. If an invoice for an expense is received after an inventory outflow has taken place, this inventory outflow will be posted in the period in which the expense has been posted.

Changes to orders in closed months

Inventory value is recalculated every night. The latest recalculation time appears at the top of the filter section. If changes have been made to orders that belong to the period for which the inventory value is calculated, the report must be recalculated before the changes are included.

A change can for example be (the list is not exhaustive):

Crediting of a sales order
Return of goods (both purchase and sale)
Change of delivery date or addition of a posting date on a purchase order
Change of dates on a sales order
Addition of a line, change in quantity, or removal of a line
An inbound order has had a budget entry added
An quantaty adjustment has been added or removed to a lot
A price on an inbound order has been corrected

Good advice to ensure correct inventory value in tracezilla

Inventory valuation is complex, and the inventory value is affected by a number of actions in tracezilla. Below we have described a number of good practices that ensure you the most accurate inventory value as well as the possibility to clarify discrepancies/adjustment entries in relation to your accounting system, if you transfer your inventory value from tracezilla to the accounting system - something that can otherwise be a time-consuming process, and which tracezilla support cannot do for you.

(1) Update all orders (including return orders) with dates in the period for which the inventory value is to be calculated. That is, if an order is actually completed, it must also be received/delivered/invoiced in tracezilla. If an order is not closed, the order must reflect reality in terms of both dates and location of goods, as the latter is essential for correct stock-taking:

Purchase orders: Move the delivery date to the expected delivery date if the goods have not been delivered as expected.
Production orders: Issue raw materials to production if the items have been picked.
Warehouse orders: Mark items as picked on warehouse orders
Sales orders: Mark items as picked on sales orders or register items as delivered

(2) Ensure that all inbound orders have had the relevant costs registered - i.e. that there are correct prices on purchase orders and that any additional costs such as freight, customs, etc. are registered. The same applies to production orders, where production costs may be registered.

(3) Do stock-taking - and preferably follow these recommendations:

Count the SKUs as separate lots, as the cost price of individual lots may vary.
Count the entire stock at once and avoid movements (purchase/production/sale/return/warehouse transfers) during this period, if possible.
Register the stock-taking in tracezilla immediately.
Be aware that the effect of a stock-taking on the inventory value can be seen in the month in which the stock-taking is registered - once the month is closed. Therefore, if possible do the stock-taking at the end of the month for which you subsequently want to calculate the inventory value.
Read more about stock-taking here and here.

(4) Recalculate the inventory valuation report if you have made changes to orders registered in the period you wish to calculate inventory valuation on the same day that you want to calculate inventory value.

(5) Validate your data. For example, download the tab By Lot as an xlsx file. In the file, you can easily add a filter to the columns. In column H (Quantity), you can look for negative numbers - this means either that there are open orders that should be updated or that registration of purchase orders are missing (for non-traceable SKUs only). In column I or J (Valuation/Per unit), you can look for negative values, value = 0, or too low value:

Negative value: negative inventory
Value = 0: Missing registration of purchase price and possibly relevant budget entries on the relevant inbound orders (very common on import purchase orders created during go live)
Too low value: Incomplete registration of either purchase price and/or budget entries

(6) Download the report in xlsx format (all tabs or as a minimum Summary, Movements and By Lot), name the file with the valuation date, and save it for later use. Do this every time you calculate and use your inventory value.

(7) Keep a change log for changes made to orders that date-wise belong to periods where inventory value has been calculated. This makes it easier for you to find causes of discrepancies/adjustment entries later on.

(8) Be aware that sales price and cost price are not the same. The inventory value of your saleable goods is not the value you can sell the goods for, but the costs incurred to get the goods into stock.

(9) Other reasons for discrepancies/adjustments entries in relation to your accounting system may be:

A large part of the inventory is registered without traceability. The cost prices for non-traceable SKUs is calculated as an average price based on historical orders, and the average will typically change from month to month.
Missing or changed information in the integration setup to the accounting system.

Good advice for transferring inventory value to the accounting system

Once inventory value has been calculated in tracezilla, the inventory adjustment entries as described can be transferred to your accounting system as a posting journal, if there is an active standard integration between the two systems. Many of our customers use e-conomic as their accounting system, and e-conomic is therefore used as the point of reference in the advice below. If you integrate to a different accounting system, please contact our support for guidance.

Ensure that the integration setup is correct and that all relevant accounts are filled in under point 7 of the setup. Read our guide to e-conomic integration here.
The accounts used for the different quantity adjustment of the stock must be tracezilla-specific and must not be used for other data, as tracezilla may otherwise overwrite this data when adjustment entries are transferred.
Transferred posting journals that are not to be posted after all should be deleted in the accounting system not to interfere with later data. Read more about transferring inventory adjustment entries here.