How to Calculate Inventory as a Percentage
of Revenue in Excel

Learn how to track whether inventory is growing faster than revenue, and what that means for efficiency and cash flow. In this lesson, you’ll build a clear month-by-month view and a heat map by part category so you can quickly spot where inventory is getting heavy and where it’s staying under control.

Download the Excel file used in this tutorial:

Download

Inventory as % of Revenue by Month and Part Category

1. Calculate Average Inventory in the Dataset

  • Add a new column called Average Inventory.
  • Use the AVERAGE function to calculate the average of:
    • Beginning Inventory
    • Ending Inventory
  • Fill the calculation down for the entire dataset.
  • This becomes the value used in all further calculations.

2. Create a Unique List of Month-End Dates

  • On your reporting sheet, use the UNIQUE function on the Month-End column.
  • Sort the results from smallest to largest using SORT.
  • Add a header labeled Month.
  • Copy the list and paste values using Ctrl + Shift + V.

3. Create a Unique List of Part Categories

  • Use the UNIQUE function on the Part Category column.
  • Sort alphabetically using SORT.
  • Use TRANSPOSE to move the list across the top of the matrix.
  • Paste values to remove formulas.

4. Calculate Total Average Inventory by Part and Month

  • In the first cell of the matrix, use SUMIFS.
  • Sum the Average Inventory column.
  • Apply two criteria:
    • Month-End equals the month in the left column.
    • Part Category equals the category in the top header.
  • This gives you the numerator for the percentage.

5. Retrieve Monthly Revenue

  • Revenue is stored once per month, not by part category.
  • Use VLOOKUP (or XLOOKUP) to return:
    • Total Revenue
    • For the selected Month-End date
  • Use exact match mode.
  • This gives you the denominator.

6. Calculate Inventory as % of Revenue

  • Divide the SUMIFS result by the revenue lookup result.
  • Format as percentage using Ctrl + Shift + 5.

7. Lock Cell References for Copying

  • Use F4 to lock references appropriately:
    • Lock the month column so it does not shift when copying across.
    • Lock the part category row so it does not shift when copying down.
  • Copy the formula across the entire matrix.
  • If structured tables prevent dragging, copy and paste across instead.

8. Create Overall by Month

  • At the bottom of each month column, use SUM to total all part-category percentages for that month.
  • Format as percentage.

9. Create Overall by Part Category

  • First, calculate total revenue for the entire dataset:
    • Use VLOOKUP to pull revenue for each month.
    • Use SUM to total those values.
    • Lock the total revenue cell using F4.
  • Then calculate total average inventory by part category:
    • Use SUMIFS.
    • Filter only by Part Category (do not filter by month).
  • Divide total inventory by total revenue.
  • Format as percentage.

10. Calculate Overall for the Entire Dataset

  • Use SUM on the entire Average Inventory column.
  • Divide by the total revenue for the dataset.
  • Format as percentage.

11. Apply Conditional Formatting (Heat Map)

  • Highlight the matrix.
  • Go to Home → Conditional Formatting → Color Scales.
  • If one color scale distorts comparisons:
    • Apply conditional formatting column by column instead.
    • Use Format Painter to replicate formatting.
  • Apply separate color scales to:
    • Overall by Month
    • Overall by Part Category

This completes the Inventory as % of Revenue heat map and summary totals exactly as shown in the video.

Inventory as a Percentage of Revenue (Excel Heat Map)

Q1. What does “Inventory as % of Revenue” mean?
 Inventory as % of Revenue shows how “inventory-heavy” your business is relative to sales. It helps you see whether inventory levels are staying balanced or growing faster than revenue, which can signal inefficiency and higher cash needs.

Q2. Why is Inventory as % of Revenue an important inventory KPI?
 Because revenue can rise while inventory rises even faster. This KPI helps you identify when growth is becoming more expensive to support, tying up cash in parts and increasing the risk of overstock or slow-moving inventory.

Q3. What will the heat map help me see?
 A heat map makes it easy to spot patterns at a glance, like which part categories are consistently consuming a larger share of revenue and which months are driving the biggest inventory load.

Q4. Can I use this to compare performance by month and by part category?
 Yes. You’ll get both views: a month-by-month perspective to track trends over time, and a part-category view to identify which types of parts are contributing most to inventory pressure.

Q5. Does a higher percentage always mean something is wrong?
 Not always. Some categories are naturally expensive, so the percentage may be higher by default. The value comes from spotting changes over time, comparing categories, and identifying where inventory is becoming disproportionate to revenue.

Q6. Do I need a specific system to do this, or can I use my own data?
 You can use your own data from any system (ServiceTitan, ERP, inventory tools, spreadsheets). As long as you have month-end timing, part category, and inventory values, you can recreate the analysis shown in the video.

Q7. Is there a file or dataset I can use to follow along?
 Yes. You can download the practice file linked below the video. If you can’t find the link, you can request it using the email shown in the lesson.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Join Now

Menu

Databoards

Analysis & Development

© 2025 All Rights Reserved.

Facebook Twitter Youtube