How to Calculate Revenue Lost to Stockouts % in Excel

Revenue Lost to Stockouts % in Excel

1. Confirm the Required Columns in Your Dataset

• Locate the columns you’ll use for this KPI:
  • Date
  • Potential Ticket Revenue
  • Revenue Lost to Stockouts
• Keep the dataset in a structured table format so formulas reference cleanly.

2. Extract Month and Year From the Date Column

• Create a Month field using the TEXT function:
  • Use the Date column as the input
  • Format as month name using mmmm
• Create a Year field using the same TEXT function:
  • Use the Date column as the input
  • Format as year using yyyy
• If you already created these fields, you can delete the temporary examples and keep the final Month and Year columns.

3. Build a Unique Month and Year List for Monthly Reporting

• Highlight the Month and Year range you want to summarize by.
• Use UNIQUE() to generate the unique list.
• Make sure the list is sequential by date:
  • If your dataset is not sorted by date, sort it first.
• Copy the unique list and paste values using Ctrl + Shift + V.

4. Add Your Summary Table Headers

• Create columns in your summary table for:
  • Potential Ticket Revenue
  • Revenue Lost to Stockouts
  • Percentage
  • Target

5. Calculate Monthly Potential Ticket Revenue With SUMIFS

• Use SUMIFS() to sum Potential Ticket Revenue by:
  • Month equals the Month in your summary table row
  • Year equals the Year in your summary table row
• Fill the formula down for all month-year rows.

6. Calculate Monthly Revenue Lost to Stockouts With SUMIFS

• Use the same SUMIFS() structure as above, but sum the Revenue Lost to Stockouts column instead.
• Use the same Month and Year criteria ranges.
• Fill the formula down.

7. Calculate Revenue Lost to Stockouts %

• In the Percentage column, divide:
  • Revenue Lost to Stockouts ÷ Potential Ticket Revenue
• Format as a percent using Ctrl + Shift + 5.
• Fill down for all rows.

8. Add a Target %

• In the Target column, enter your benchmark target (example used in the video: 2%).
• Fill the same target down the column so it charts as a line.

9. Create a Display Date Label for Charting

• Create a helper “Date” label for the x-axis by combining:
  • Month cell + Year cell
• This becomes your clean x-axis label for the chart.

10. Build the Combo Chart

• Select these fields for the chart:
  • Date label column
  • Revenue Lost to Stockouts (for columns)
  • Percentage (for line)
  • Target (for line)
• Insert a Combo chart:
  • Revenue Lost to Stockouts as a Column series
  • Percentage as a Line series
  • Target as a Line series
• Set Percentage and Target to the Secondary Axis so they display correctly.

11. Format the Chart for Readability

• Rename the chart title (example: “Revenue Lost to Stockouts %”).
• Adjust line thickness so the chart is easier to read.
• Add data labels to the Percentage line only:
  • Click the line
  • Add data labels for that series only to reduce clutter
• If labels are hard to see, add a fill color behind the label text using the font fill option.

12. Calculate the Overall Total %

• At the bottom, calculate totals:
  • Sum total Potential Ticket Revenue
  • Sum total Revenue Lost to Stockouts
• Compute the overall percent:
  • Total Lost ÷ Total Potential
• This gives the overall loss percentage across the entire period.