Stock Value Report

Owned by Luna Hoang
Last updated: Feb 19, 2024
11 min read

I/ Overview

To access the Stock Value Report:
1 - On the Left Menu, click on Report

2 - Go to Omni-channel Reports > Inventory Reports > Stock Value
Alternative Path: Go to Omni-channel Reports > Report Dashboard > Overview > Inventory
Reports > Stock Value
The Stock Value Report page is displayed with the default view as below:

image-20240219-073757.png

This report allows you to look into details of current stock value data, by each warehouse or the
whole system, so that you can evaluate the cost of inventory vs. their potential sales.
Main metrics:

  • Quantity On-hand: the real quantity of a product physically presented in Warehouse(s)/
    Source(s).

  • Cost (Moving Average Cost) (also MAC): A moving average (unit) cost is an inventory
    costing method wherein after each goods acquisition, the average unit cost of the item is
    recomputed. This helps properly count the product quantity & compute the new selling
    price.

  • Price: The price at which the product is currently sold. It does not include tax, discount, etc.

  • Stock Value: The total value of the product in the warehouse (Quantity in Source x MAC)

  • Potential Revenue: The total potential selling value (Quantity in Source x Price)

  • Potential Profit: The potential revenue minus stock value.

  • Potential Profit Margin: The potential profit calculated in percentage.

Note: Please note that some report columns require specific pre-conditions to display.

 

II/ Summary Data


The Summary Data block is displayed on top, giving you a quick overview of important statistics in the Stock Value Report.

image-20240219-074218.png

 

Four key metrics include:
• Total Quantity On-hand
• Stock Value
• Potential Revenue
• Potential Profit
All numbers in the Summary Data block are sum totals of their relative columns in the current report view. If you apply any filters, the total numbers are also updated accordingly.

III/ Filter Report Data


To narrow down what is shown in the analytics and the inventory list, you can use the Filter function.
1 – Click on the [Filter] button
2 – Key in details in the following filter fields:

Field Name

Type

Description

Field Name

Type

Description

SKU

Input

  • Pre-condition: SKU column is visible in the data grid

  • Input Format: String

  • Default Value: Blank (inactive filter)

  • Action: When SKU filter is applied, look up products that have SKU containing the search query entered

{Custom attribute}

Input

  • Pre-condition:

+ Custom attribute in Report Settings is enabled
+ {Custom attribute} column is visible in the data grid

  • Input Format: String

  • Default Value: Blank (inactive filter)

  • Action: When

{Custom attribute} filter is applied, look up products that have the custom attribute's value containing the search query entered

Name

Input

  • Pre-condition: Name column is visible in the data grid

  • Input Format: String

  • Default Value: Blank (inactive filter)

  • Action: When Name filter is applied, look up products that have Product Name containing the search query entered

Category

Multi-select

  • Pre-condition: Category column is visible in the data grid

  • Input Format: Tick on the checkboxes and click

[Done] button

  • Default Value: Blank (inactive filter)

  • Action: When Category filter is applied, look up products that belong to

one of the categories selected

{Supplier}

Dropdown select

  • Pre-condition:

+ the Purchase Management module has been installed
+ Supplier column is visible in the data grid

  • Dropdown Options: the list of enabled suppliers (in

Purchase Management > Suppliers > Manage Suppliers> View Supplier > Product)

  • Default Value: All Suppliers (inactive filter)

  • Action: When Supplier filter is applied, look up products that are provided by the supplier selected (in

Catalog > Products > View Product > Suppliers). If “Blank” option is selected, return the list of non-supplier products.

Warehouse

Dropdown select

  • Pre-condition: Warehouse column is visible in the data grid

  • Dropdown Options: the list of enabled warehouse (in

Inventory Management > Stock Listing > Warehouses)

  • Default Value: All Warehouses (inactive filter)

  • Action: When Warehouse filter is applied,

+ look up products that are stocked in the warehouse selected (Check in Inventory Management > Stock Listing > Warehouses > Stock On-hand or Catalog > Products > View Product > Advanced Inventory > Warehouse)
+ only display Qty On-hand in the warehouse selected (not the whole system), which will make revenue & profit data update accordingly

Qty On-hand

Input

  • Pre-condition: Qty On-hand column is visible in the data grid

  • Input Format: Decimal number range

  • Default Value: Blank (inactive filter)

  • Action: When Qty On-hand filter is applied, look up products that have Qty in Warehouse no less than the 'from' value, and no more than the 'to' value

Cost (Moving Average Cost)

Input

  • Pre-condition: Cost (MAC) column is visible in the data grid

  • Input Format: Decimal number range

  • Action: When Cost (MAC) filter is applied, look up products that have unit cost in MAC method no less than the 'from' value, and no more than the 'to' value

Stock Value

Input

  • Pre-condition: Stock Value column is visible in the data grid

  • Input Format: Decimal number range

  • Default Value: Blank (inactive filter)

  • Action: When Stock Value filter is applied, look up products that have stock value no less than the 'from' value, and no more than the 'to' value

Price

Input

  • Pre-condition: Price column is visible in the data grid

  • Input Format: Decimal number range

  • Default Value: Blank (inactive filter)

  • Action: When Price filter is applied, look up products that have unit price no less than the 'from' value, and no more than the 'to' value

Potential Revenue

Input

  • Pre-condition: Potential Revenue column is visible in the data grid

  • Input Format: Decimal number range

  • Default Value: Blank (inactive filter)

  • Action: When Potential Revenue filter is applied, look up products that have the expected revenue no less than the 'from' value, and no more than the 'to' value

Potential Profit

Input

  • Pre-condition: Potential Profit column is visible in the data grid

  • Input Format: Decimal number range

  • Default Value: Blank (inactive filter)

  • Action: When Potential Profit filter is applied, look up products that have the expected profit no less than the 'from' value, and no more than the 'to' value

Profit Margin (%)

Input

  • Pre-condition: Profit Margin (%) column is visible in the data grid

 

  • Input Format: Decimal number range

  • Default Value: Blank (inactive filter)

  • Action: When Profit Margin (%) filter is applied, look up products that have the profit margin ratio no less than the 'from' value, and no more than the 'to' value

3 – Click on the [Apply Filter] button

The system will re-generate the Report Grid & Summary Data of the products that match ALL conditions of active filters.

VI/ How To Create Custom Visible Column

If you think that some information in the report is unnecessary, you can choose to hide specific columns from the report. 

1 – Click on the [Columns] button 

2 – Untick the fields that you don’t want to show on the report. 

The changes will be applied instantly to the Report Data Grid & Filter Fields without any further action.

You also can drag & drop to change the column's position as you want to customize your own report view.

V/ How To Create Custom View

One of the most convenient features of the Stock Value Report page is the ability to customize the report view and save what is frequently used. Thus, you can quickly access your favorite data in one click instead of filtering and sorting everything from scratch. 

To create a new view: 

  1. –  Customize the current report view as your desire, such as:

  • Filter data

  • Choose visible columns 

  • Change columns position

  • Change sort order 

  • Change the number of records shown per page

2 – Click on the [Default View] button 

3 – Click on the [Save View As...] button 

4 – Enter the new view's name 

5  – Click on the [->] button to save

Next time you just simply select that view from the list and all the same filters will be applied again.

To edit an existing view:

1 - Click on the [Default View] button (or the current selected view)

2 - Click on the view that needs editing

3 - Apply new filters, choose visible columns and change columns’ positions as desire

4 - Click on the toggle button of the current view again

5 - Click on the [Pen] button of the current view

6 - Edit its name as needed

7 - Click on the [->] button to save changes

To delete an existing view:

1 - Click on the [Default View] button (or the current selected view)

2 - Click on the view that needs to be removed

3 - Click on the [Trash] button to delete it

VI/ How To Export Report Data

To export Stock Value Report data:

1 – On the Stock Value Report page, click on the [Export] toggle button 

2 - Click on the [Export] button in the collapsible form

The system will generate a CSV file from the current report view (active filters, visible columns, column position & sort order) and export it for you to download instantly.

VII/ List of Stock Value Report Metric

 

Field Name

Type

Description

Field Name

Type

Description

SKU

String

The unique identification code of each product with a global scope.

Values are retrieved from SKU attribute in Catalog product 

{Custom attribute}

String

The custom product attribute that could be used as an ID number for products instead of SKU (E.g. supplier barcode, product EAN code…).
The column title is the attribute name.

Pre-conditions: The custom product attribute

  • is created by admin user

  • requires unique value within the context of the scope setting

  • and can be enabled in Omni-channel Report's Settings. (Refer to section 5 for more details).

Values & Column name are retrieved from {Custom attribute} attribute in Catalog product 

Name

String

The name of product.

Values are retrieved from Name attribute in Catalog product 

Category

String

List all categories that a product belong to, separated by line break

Values are retrieved from Category attribute in Catalog product 

{Supplier}

String

List all suppliers in the Purchase Management module that provide a product, separated by line break

Pre-conditions: The 'Supplier' column is only displayed if the Purchase Management module has been installed.

Values are retrieved from Suppliers data in Catalog product
If product is not assigned to any supplier yet, its 'supplier' value is blank.

Warehouse

String

List all warehouses in the Inventory Management module that a product belong to, separated by line break

Values are retrieved from Advanced Inventory >Warehouse Stocks >Stocks Listing in Catalog product 

Qty On-hand

Decimal number

Determine the quantity of a product that physically exist in:

  • the whole system (if Filter by Warehouse is not active)

  • a specific warehouse (if Filter by Warehouse is active)

 

It's the [Qty in Warehouse] in the Inventory Management module
Formula: 

Qty On-hand (Qty in Warehouse) = Available Qty. + Qty to Ship

 

Available Qty = Qty in Magento Catalog, which is the number of items left to sell

Qty. to Ship = the number of items allocated in sales orders but not shipped yet

Cost (Moving Average Cost)

Decimal number

Determine the unit cost of a product using the Moving Average Cost (MAC) method. A moving average (unit) cost is an inventory costing method wherein after each goods acquisition, the average unit cost of the item is recomputed. This helps properly count the product quantity & compute the new selling price.

Formula: 

  • If Purchase Management module is not installed

 MAC = Unit Cost defined by Magento's “Cost” attribute in Catalog product.

 

  • If Purchase Management module is installed, MAC indexes will be re-calculated every time new items are received and transferred to warehouse(s) from PO.

MAC = [Total Stock Value after Purchase] / [Total Qty. On-Hand after Purchase]
= ([Total Stock Value before Purchase] + [Total Cost of Items Received from PO]) / [Total Qty On-Hand after Purchase]

 In which:

Total Stock Value before Purchase = [Current Qty. On-hand in All Warehouses] * [Current MAC]

 

Alternative Use Case 1:

Pre-conditions: 

  • No previous POs that have Qty Received & transferred > 0 (Items are received from Purchase Order for the 1st time) 

  • Cost attribute in Catalog Product has a specified value 

Formula : 

Total Stock Value before PO = [Current Qty On-hand in All Warehouses] * [Cost (product attribute in Catalog)]

 

Alternative Use Case 2:

Pre-conditions: 

  • No previous POs that have Qty Received & transferred > 0 (Items are received from Purchase Order for the 1st time) 

  • Cost attribute in Catalog Product has NO specified value (blank)

Formula: 

Total Stock Value before PO = [Current Qty On-hand in All Warehouses] * [Landed Cost (from PO just received & transferred items)]

 

 

Total Cost of Items Received from PO = [Landed Cost] * [Qty. Received & Transferred]

 

Landed Cost = [Purchase Cost] + [Tax] – [Discount] + [Shipping Cost] 

Note: Landed Cost in PO whether includes Shipping Cost, tax & discount or not depending on Inventory Costing Methods Settings

 

Total Qty On-Hand after Purchase = Current Qty On-hand in All Warehouses + Qty. Received & Transferred from Purchase Order

 

Items that will not be accounted in MAC include:

  • items purchased but not received 

  • items received but not transferred 

  • items returned to supplier

Stock Value

Decimal number

Determine the total inventory cost of a product in:

  • the whole system (if Filter by Warehouse is not active)

  • a specific Warehouse (if Filter by Warehouse is active)

Formula: Stock Value = Qty On-hand * Cost (MAC)

Price

Decimal number

The selling price of a product

Values are retrieved from Price attribute in Catalog product 

Potential Revenue

Decimal number

Determine the total expected sales of a product if all items in:

  • the whole system (if Filter by Warehouse is not active)

  • a specific warehouse (if Filter by Warehouse is active)

are sold at the normal selling price.

Formula: Potential Revenue = Qty On-hand * Price

Potential Profit

Decimal number

Determine the total expected profit of a product if all items in:

  • the whole system (if Filter by Warehouse is not active)

  • a specific warehouse (if Filter by Warehouse is active)

sold at the normal retail price.

Formula: Potential Profit = Potential Revenue - Stock Value

Profit Margin (%)

Decimal number

Determine the expected profitability ratio of a product
Formula: 

Profit Margin (%) = Potential Profit/ Potential Revenue *100%
= (Price - MAC)/ Price *100%