GMROI (Gross Margin Return On Investment) is one of the most popular metrics, commonly used in retail industry, for inventory management. Retailers are short-term investors and the “buy and hold” strategy does not work in retail industry. Instead of having the cash frozen in inventory, it is crucial to keep cash flowing to continually purchase, mark up, and sell in order to generate profits and expand the business. GMROI is a profitability metric that helps a retailer to analyse how efficiently the inventories are being converted into cash.

GMROI is defined as the gross profit a retailer makes in return for their investment in inventory.  A common formula used to calculate GMROI is to divide the gross profit by the average inventory cost.

GMROI = Gross Profit / Average Inventory Cost

Gross Profit is calculated by subtracting the COGS (Cost of Goods Sold) from the revenue:

Gross Margin = Revenue – COGS

Inventory cost is a semiadditive measure as it is not additive on the date dimension. Average Inventory Cost is calculated by dividing the sum of the inventory cost over a specified period by the total number of days of the period:

Average Inventory Cost = Sum of Inventory Cost over a Period/Number of Days of the Period 

Due to the semiadditive nature of the inventory measures, the GMROI analysis in a BI solution is normally conducted on a periodic snapshot data model, including an inventory fact table, a date dimension table, and a number of other dimension tables that are applicable to the analysis, such as vendors, stores, and products.

The snapshot below shows a sample data schema for GMROI analysis, created in Power BI. The design of the data schema can be variant, depending on the retailers’ specific business rules and LOB database design. You can download the demo pbix file here.

The Inventory table in the sample data schema records the daily snapshot of the inventory level,  the quantity of sold products, inventory cost and retail  price of the products over the vendor, store and product dimensions.

To create the Gross Profit measure, we can calculate the gross profit for each sold item and multiply it by the quantity of items sold in a day. We can then use the SUMX function to roll-up the total gross profit, depending on the evaluation context.

Gross Profit = SUMX('Inventory',
                    'Inventory'[Quantity Sold]*
                       ('Inventory'[Retail Price] - 'Inventory'[Cost]) 
                )

To create the Average Inventory Cost measure, we can sum up the inventory cost of all days in a period and divide it by the number of days in the period.

Average Inventory Cost = 
    DIVIDE(
        SUMX('Inventory',
             'Inventory'[Inventory Level]*'Inventory'[Cost]),
             COUNTROWS('Inventory')
        )

After the Gross Profit measure and the Average Inventory Cost measure are created, we can simply calculate the GMROI measure by dividing the Gross Profit measure by the Average Inventory Cost Measure.

GMROI = DIVIDE([Gross Profit], [Average Inventory Cost])

The GMROI measure can be used in different evaluation context depending on the specific requirements of the GMROI analysis. For example, we can use the measure to calculate the annual GMROI of the products from Vendor A and sold at Store X.

• Power BI/DAX/M