Warranty and Callback Costs
as a Percentage of Revenue in Excel

Warranty and Callback Cost as a Percentage of Revenue

1. Set up the Months and Column Headers

• In your summary table, type Month and then enter January.
  
• Drag January down until you reach December.
  
• Copy the headers you’ll be calculating from the dataset:
  
  • Invoice Amount (Revenue)
    
  • Warranty Parts Cost
    
  • Warranty Labor Cost
    
  • Callback Labor Cost
    
  • Callback Parts Cost
    
• Use Ctrl to multi-select those header cells in the dataset, copy, then paste them into your model.

2. Calculate Monthly Revenue Using SUMIFS

• In the Revenue row (Invoice Amount), use SUMIFS to sum invoice amounts by month.
  
• Structure:
  
  • Sum range: Invoice Amount column
    
  • Criteria range: Month column
    
  • Criteria: The month in your table (January, February, etc.)
    
• Fill the formula down for all months.
  
• Format as currency and center align:
  
  • Currency: Ctrl + Shift + 4
    
  • Center: Alt + H + A + C
    
• If calculations were turned off, re-enable Excel calculations so formulas update.

3. Calculate Monthly Warranty and Callback Cost Columns

• Use the same SUMIFS formula pattern for each cost column, changing only the sum range:
  
  • Warranty Parts Cost uses its column (example mentioned: T)
    
  • Warranty Labor Cost uses the next column (example mentioned: U)
    
  • Callback Labor Cost uses the next column (example mentioned: V)
    
  • Callback Parts Cost uses the next column (example mentioned: W)
    
• Fast way to do this:
  
  • Copy the Revenue SUMIFS formula
    
  • Paste it into the next cost row
    
  • Replace the invoice column reference with the correct cost column reference
    
• Use Format Painter to keep formatting consistent across rows.
  
• Use proper absolute and mixed references so the month reference changes down the column but stays correct as you copy across:
  
  • Use F4 to lock references
    
  • Lock the month column when needed (add $ before the column letter)

4. Create Total Warranty Cost, Total Callback Cost, and Total Cost

• Add calculated rows:
  
  • Total Warranty Cost = Warranty Parts + Warranty Labor
    
  • Total Callback Cost = Callback Labor + Callback Parts
    
  • Total Cost = Total Warranty Cost + Total Callback Cost
    
• Copy each formula down through December.

5. Calculate Percent of Revenue

• Add a row for Percent of Revenue:
  
  • Percent of Revenue = Total Cost ÷ Revenue (Invoice Amount)
    
• Format as percentage:
  
  • Ctrl + Shift + 5
    
• Fill down for all months.

6. Build a Column-by-Column Heatmap

• Apply conditional formatting carefully so totals do not distort the color scale.
  
• Do not apply the color scale across the entire block at once, because Total Cost will always dominate.
  
• Instead:
  
  • Highlight one column at a time (example: January values for each row you want to compare)
    
  • Apply Conditional Formatting → Color Scales
    
    
  • Choose a scale where red indicates high (since high cost % is bad)
    
• Use Format Painter (double-click it) to quickly apply the same formatting to the other month columns.

7. Add a Benchmark Row

• Add a benchmark value (example shown: 1.5%).
  
• In the benchmark row across months, reference the first benchmark cell so the line stays consistent:
  
  • Each month’s benchmark cell equals the benchmark cell above/left (copy across).
    
• This makes it easy to change one benchmark value and update the entire benchmark line.

8. Create the Combo Chart With a Secondary Axis

• Select the following while holding Ctrl:
  
  • Month labels
    
  • Revenue (Invoice Amount)
    
  • Percent of Revenue
    
• Insert a combo chart:
  
  • Insert → Recommended Charts, or
    
  • All Charts → Combo
    
• Set Percent of Revenue to Secondary Axis so the percentage line is readable.
  
• Rename the chart title to:
  
  • Warranty and Callback Cost as a Percent of Revenue
    
• Add the benchmark line:
  
  • Copy the benchmark row
    
  • Click the chart
    
  • Paste (Ctrl + V) to add it as a new series
    
• Format benchmark series:
  
  • Change to a gray solid line (optional: dashed)

9. Add Data Labels to the Percent Line

• Click the percent line in the chart.
  
• Turn on Data Labels.
  
• If labels feel crowded, manually move a few labels to reduce overlap.

10. Add Sparklines to Show Trend by Row

• Click the sparkline cell location.
  
• Go to Insert → Sparklines → Line.
  
• Select the range for the row you want to trend (monthly values).
  
• Turn on high and low markers if desired.
  
• Drag across to create sparklines for the other rows.

11. Build the Technician Table Using UNIQUE

• Create a list of technicians:
  
  • Use UNIQUE() on the Technician column.
    
  • Quick select trick: click the first cell then Ctrl + Shift + Down Arrow
    
• Copy and paste values (Ctrl + Shift + V) and sort alphabetically.

12. Calculate Jobs, Claims, and Claim Rate by Technician

• Set up columns:
  
  • Jobs
    
  • Claims
    
  • Claim Rate
    
  • Benchmark
    
• Jobs cannot be a simple COUNTIF on technician name because callback rows would double count.
  
• Use COUNTIFS with an extra condition that distinguishes original jobs vs callbacks (the video’s fix was switching from COUNTIF to COUNTIFS and filtering where a specific field is blank).
  
• Claims:
  
  • Copy the Jobs COUNTIFS formula
    
  • Change the second condition to count non-blanks using:
    
    • “<>” inside quotes
      
• Claim Rate:
  
  • Claims ÷ Jobs
    
  • Format as percentage

13. Highlight Technicians Above a Benchmark Using Conditional Formatting

• Put a benchmark rate in a cell off to the side (example shown: 17%).
  
• Highlight the technician table rows.
  
• Go to Conditional Formatting → New Rule → Use a formula
  
• Use a rule like:
  
  • “Claim Rate cell is greater than Benchmark cell”
    
• Lock the benchmark reference with $ so changing the benchmark updates the highlighted rows automatically.