Inventory Measures (Bevica Power BI)
Inventory quantity, valuation, ageing, and turns measures in Bevica Power BI.
Inventory Measures (Bevica Power BI)
This summarises some of the Inventory Measures within the Bevica Power BI model.
TABLE OF CONTENTS
Measures
Time Intelligence and Inventory
Costs
Duty Liability
Inventory Ageing
Other Date Measures
Inventory Turns
Inventory Days
Average Cost
Stock Availability
Measures
Inventory Measures
Inventory Cost (Actual)
Cost Amount Actual - excluding paid reserves
Inventory Cost (All)
All Costs excluding paid reserves. Actual + Expected + Non-Inventoriable
Inventory Cost (Expected)
Cost Amount Expected - excluding paid reserves
Inventory Cost (Inventory)
Cost Amount Actual and Expected - excluding paid reserves. Does not include non-inventoriable costs.
Inventory Cost (Non Inventory)
Cost Amount Non-Inventoriable (i.e., additional charges) - excluding paid reserves
Inventory Quantity (Base)
Base Quantity (ILE Quantity) excluding paid reserves
Inventory Quantity (Case)
Calculated case quantity (ILE Quantity / Qty per UoM) - excluding paid reserves
Inventory Measures Average Cost
Current Avg. Inventory Cost (Base) - All
The average cost of a base unit of an item based on current inventory value and current inventory. This is duty agnostic - so does not account for duty status.
Dynamic Avg. Inventory Cost (Base) - All
The average cost of a base unit of an item based on current inventory value and current inventory. This is duty agnostic - so does not account for duty status. This works with date filters
Inventory Measures Inventory Days
Current Inventory (Base)
Cumulative inventory ignoring any date filters. This is inventory as of last update.
Current Inventory Cost (All)
Cumulative inventory cost (actual, expected and non-inventoriable) ignoring any date filters. This is as of last update.
Current Inventory Days - Last Sale Date
Estimate of days in stock based on Current Inventory / Sales over the last 180 days based on the last sale date of the item. Therefore if an item hasn't sold for a period of time there will still be sales in this calculation.
Current Inventory Days (180d sales)
Estimate of days in stock based on Current Inventory / Sales over the last 180 days
Current Inventory Sales 180d (Base)
Sales over the last 180 days based ignoring any date filters.
Current Inventory Sales 180d (Base) - Last Sale Date
Sales over the last 180 days based on the last sale date of the item ignoring any date filters.
Dynamic Inventory (Base)
Current inventory based on the current date filters.
Dynamic Inventory Cost (All)
Inventory cost up to the max date of the current date filters
Dynamic Inventory Days (180d sales)
Estimated days in stock based on the current date filters
Dynamic Inventory Sales 180d (Base)
Sales over the last 180 days based on the current date filters.
Inventory Measures Inventory Days Sales Last 6m
Sales Qty Base -1M
Sales in the last N months
Sales Qty Base -2M
Sales in the last N months
Sales Qty Base -3M
Sales in the last N months
Sales Qty Base -4M
Sales in the last N months
Sales Qty Base -5M
Sales in the last N months
Sales Qty Base -6M
Sales in the last N months
Sales Qty Base CM
Sales in the last N months
Inventory Measures Inventory Turns
Inventory Turnover Days
On average how many days it takes for an item to be turned
Inventory Turns per Year
The number of times the inventory turns within a year (12 months). Calculated as Costs of Goods sold over last 12 months divided by the Average Inventory Value during that period. Can be used to help identify slow and fast moving items.
Inventory Measures Inventory Ageing
Days in Stock - Avg
Average days in stock for open item ledger entries.
Days in Stock - Max
Max days in stock (oldest) for open item ledger entries
Oldest Open Inventory Date
First date of receipt for a positive item ledger entry which is still Open. This will show the first date on an aggregate (i.e., over an item) but can be split out to individual transactions with e.g., Document No.
Time Intelligence and Inventory
To achieve a current inventory valuation or quantity you will either need to not use a date filter (else you will only be analysing values within that date range) or use the Cumulative Time Intelligence filter (Time Intelligence).
Below shows the inventory quantity movements within each period
Shows the cumulative total
If we then filter to a particular date (2020-Q3 in the example below) we can see what the closing inventory quantity was (2) using the cumulative function, or the movements using no filter (1).
Costs
Please refer to this article: Inventory Costings
Duty Liability
This is an estimate based on the current duty rate of an item multiplied by the inventory. It filters out duty paid inventory (where duty has already been accrued/paid) and paid reserves.
Note that applying filters to this, such as Entry Type, will affect the calculation as it will not consider all inventory.
It should be used with a Cumulative Total filter so it considers the total of all values.
This measure is hidden by default.
Inventory Ageing
Oldest Open Inventory Date is the first date found where the item ledger entry is still open. This can be any type of entry, so could be an item journal rather than just a Purchase.
Days in Stock - Avg shows the average number of days in stock for open entries.
Days in Stock - Max shows the highest number of days in stock for all the open entries for an item.
Other Date Measures
In the Sales Measures and Purchase Measures there are additionally:
Date of First Purchase / Sale
Date of Last Purchase / Sale
Days Since Last Purchase / Sale
Inventory Turns
Inventory Turns is a standard way to measure how frequently an item turns within a year. That is how many times within a year it will be depleted or sold. It can be used to help identify or categorise slow to fast moving items and identify 'sticky' stock.
We use a year for the calculations in Bevica.
Inventory Turns per Year counts the number of times the inventory turns within a year (12 months). Calculated as Costs of Goods sold over last 12 months divided by the Average Inventory Value during that period. Can be used to help identify slow and fast moving items.
Inventory Turnover Days on average how many days it takes for an item to be turned.
Inventory Days
Measures to provide an estimated number of days remaining in stock. These are based on a simple formula:
Average Cost
This calculates an average cost for inventory based on Inventory Cost (All) / Inventory (Base).
It is duty agnostic (i.e., the cost includes both duty free and duty paid inventory and doesn't differentiate or account for duty costs). Current is as per last refresh and ignores date filters, Dynamic respects date filters (so can track historically for example).
Stock Availability
These measures are provided as a convenience to assist with forecasting. They are only ever accurate based on:
Last refresh
The default calculation will use 'Posting Date' of the open sale or purchase. This can, depending on users, sometimes be the date the order was entered.
Use of Requested Delivery Date on Sales and Expected Receipt Date on Purchases (if updated in Bevica) may provide more accuracy. To make use of these use the Date Type Filter (Open Orders) as described here: Data Model - Open Orders.
The following measures are available on the Stock Availability (Estimated) folder. They are all in base.
Current Inventory (ILE)
This enforces a cumulative total calculation on item ledger inventory base. It shows the inventory up to the max date of your filter
Current Inventory less Open Sales
Current Inventory (ILE) less open sales quantities
Current Inventory less Open Purchases
Current Inventory (ILE) plus open purchase quantities
Available Stock
The sum of all above (Inventory - Open Sales + Open Purchases)
Last updated
Was this helpful?