How to Track Stockout Rate % in Excel
(Monthly Trend + Target Line)

Monthly Stockout Rate % Calculation Process

1. Start with the raw dataset columns you need

• Confirm your dataset includes Date, Demand, and Fulfilled.
• The core idea in the sheet is that when Demand does not equal Fulfilled, you have a shortage.

2. Create an End-of-Month column for grouping

• Add a new column called EO Month.
• Use the EOMONTH function on the Date field:
  • EOMONTH(DateCell, 0)
• Use 0 to return the end of the same month.
• Format the result as a date if it shows as a number.

3. Create a Stockout Units column at the job level

• Add a new column called Stockout Units.
• Calculate stockout units as:
  • Demand – Fulfilled
• Use brackets if your sheet requires it based on the way the columns are referenced.
• Fill the formula down the entire dataset so every row has a stockout unit value.

4. Generate a unique month list for the summary table

• In your summary area, create a Month column.
• Use UNIQUE() on the EO Month column to create the monthly list.
• Copy the results and paste values using Ctrl + Shift + V.
• Center the month column and format it as a date.

5. Build the monthly totals with SUMIFS

Create these columns in your summary table:

• Demand
• Fulfilled
• Stockout Units
• Stockout Rate %
• Target %

Then calculate each one month-by-month:

Demand by Month

• Use SUMIFS() to sum the Demand column where EO Month equals the month in your summary row.

Fulfilled by Month

• Use SUMIFS() to sum the Fulfilled column where EO Month equals the month in your summary row.

Stockout Units by Month

• Either:
  • Subtract Fulfilled from Demand in the summary table, or
  • Use SUMIFS() on the Stockout Units column using the same EO Month condition.

6. Calculate Stockout Rate %

• In the Stockout Rate % column:
  • Stockout Units ÷ Demand
• Fill down the column.
• Format as a percentage using Ctrl + Shift + 5.

7. Add a Target % column

• Add a Target % column (example target used in the video: 2.5%).
• Fill the same target value down all months so it can be charted.

8. Create the combo chart with a secondary axis

• Highlight Month, then hold Ctrl and also select:
  • Stockout Units
  • Stockout Rate %
  • Target %
• Go to Insert → Recommended Charts.
• If it does not automatically build correctly:
  • Go to All Charts → Combo
  • Set:
    • Stockout Units as a column series
    • Stockout Rate % as a line series
    • Target % as a line series
  • Turn on Secondary Axis for the percentage lines so they are not crushed by the units scale.
    
    

9. Clean up the chart formatting

• Make the target line visually lighter so it does not dominate:
  • Change it to a neutral color
  • Make it thinner
  • Optionally make it dotted
• Add labels only to the Stockout Rate % line:
  • Click the line so the data points are selected
  • Add Data Labels
• If labels are hard to see, adjust label fill to improve contrast.

10. Final check

• Your monthly summary should now show:
  • Demand totals
  • Fulfilled totals
  • Stockout Units
  • Stockout Rate %
  • Target %
• Your chart should display:
  • Stockout units as columns
  • Stockout rate and target as lines on the secondary axis