HVAC Inventory KPI: Revenue Lost to Stockouts Explained

First-Time Fix Rate Impacted by Parts %

1. Identify the required columns in your dataset

• Confirm your dataset includes:
  • Date
  • Potential ticket revenue
  • Revenue lost (stockouts or shortages)
• Keep only what you need visible so the build stays clean.

2. Extract Month and Year from the Date column

• Create a Month field using the TEXT function:
  • Use the date cell as the input
  • Format code for month uses four M characters
• Create a Year field using the same TEXT function:
  • Use the same date cell
  • Format code for year uses four Y characters
• Once those helper fields exist, you can delete any duplicate test cells and keep the final Month and Year columns.

3. Create a unique, sequential list of Month and Year

• Select your Month and Year columns and generate a unique list so your summary table has one row per month.
• Make sure the output is sequential by date:
  • If your dataset is not sorted by date, sort it first so the month list appears in the correct order.
• Copy the unique list and paste as values using Ctrl + Shift + V.

4. Set up the summary table headers

Create columns for:

• Potential ticket revenue
• Revenue loss
• Percent
• Target

This becomes your monthly KPI table.

5. Calculate monthly Potential Ticket Revenue using SUMIFS

• Use SUMIFS to total potential ticket revenue for each month and year:
  • Sum range: Potential ticket revenue column
  • Criteria range 1: Month column
  • Criteria 1: The month in your summary row
  • Criteria range 2: Year column
  • Criteria 2: The year in your summary row
• Fill the formula down for all months.

6. Calculate monthly Revenue Loss using SUMIFS

• Repeat the exact same SUMIFS structure:
  • Only change the sum range to the Revenue lost to stockouts column
• Fill the formula down for all months.

7. Calculate the percentage

• In the Percent column:
  • Divide Revenue loss by Potential ticket revenue
• Format as percent using Ctrl + Shift + 5.
• Fill down for all months.

8. Add a target percent for comparison

• Enter a target value (example used in the video: 2%).
• Fill the target down the column so each month has the same benchmark.

9. Create a combined Month-Year label for charting

• Build a chart-friendly date label by combining:
  • Month
  • Year
• Use a simple concatenation approach so the x-axis displays clearly.

10. Build the combo chart with secondary axis

• Select the Month-Year label plus:
  • Revenue loss
  • Percent
  • Target
• Insert chart:
  • Insert → Recommended Charts → All Charts → Combo
• Set chart types and axes:
  • Revenue loss as columns on the primary axis
  • Percent as a line on the secondary axis
  • Target as a line on the secondary axis
• Confirm the secondary axis is enabled so the percent and target are visible.

11. Clean up the chart formatting

• Make the target line thinner so it does not dominate the chart.
• Adjust styling so the KPI line and target line are easy to distinguish.
• Add data labels only to the line series:
  • Click the line, then add labels for that series only
  • Avoid labeling everything so the chart stays readable
• If labels are hard to see:
  • Use the fill option on the label background to increase contrast.

12. Add totals for annual rollup

• At the bottom of your table:
  • Sum Potential ticket revenue
  • Sum Revenue loss
• Calculate overall percent:
  • Total revenue loss divided by total potential ticket revenue
• This gives you the full-period KPI number for the dataset.

13. Final check before reuse

• The hardest part is not the formulas, it is the data structure:
  • Make sure your stockout or shortage events are actually being captured consistently in your system
  • If the inputs are incomplete, the KPI will underreport the true impact