Truck Stock Accuracy Dashboard in Excel
(By Tech and Item Category)

Truck Stock Accuracy Dashboard in Excel

1. Build a True/False Accuracy Flag in the Dataset

1. Identify the two fields you are comparing: Expected On Hand vs Actual Count.
   
2. Create a new column to flag whether the counts match.
   
3. Use a direct equality check formula (no IF statement needed):
   
   • Enter: = [Expected Cell] = [Actual Cell]
     
4. Fill the formula down so every row returns TRUE or FALSE.

2. Create a Unique List of Technicians

1. Create a header called Technician in your summary area.
   
2. Use the UNIQUE() function on the Technician column in the table (Technician is in Column D).
   
3. If your data is in an Excel table, do not select the full worksheet column. Select the table’s column range instead.
   
4. Copy the unique list and paste values using Ctrl + Shift + V.

3. Calculate Accurate Audits, Total Audits, and Accuracy Percentage by Technician

Create three headers next to Technician:

• Accurate Audits
  
• Audits
  
• Accuracy Percentage
  

1. Accurate Audits (count TRUE results for each technician)
   
   • Use COUNTIFS() with two criteria:
     
     • Criteria 1: the True/False column equals TRUE
       
     • Criteria 2: Technician equals the technician name in your summary list
       
2. Audits (count total audits for each technician)
   
   • Use COUNTIF() or COUNTIFS() counting how many times the technician appears in the dataset.
     
3. Accuracy Percentage
   
   • Divide Accurate Audits by Total Audits.
     
   • Format as percentage using Ctrl + Shift + 5.
     
   • Fill formulas down for all technicians.

4. Create the Truck Stock Accuracy by Technician Chart

1. Select the Technician names and the Accuracy Percentage column.
   
   • Hold Ctrl to select non-adjacent ranges if needed.
     
2. Go to Insert → Recommended Charts.
   
3. Choose a Clustered Bar Chart.
   
4. Rename the chart title to something like:
   
   • Truck Stock Accuracy by Technician
     
5. Sort the summary table by Accuracy Percentage so the chart orders the bars the way you want:
   
   • If you want the smallest accuracy at the top, you may need to sort the data in the opposite direction because of how bar charts display order.
     
6. Optional formatting:
   
   • Add data labels using the chart “+” (Chart Elements) → Data Labels.
     
   • Change label decimals: click labels → Format Data Labels → Number → Percentage → set decimals.
     
   • Adjust axis bounds if you want a tighter view (example: start at 0.75 instead of 0).

5. Create a Unique List of Item Categories

1. Create a header called Item Category in a new summary area.
   
2. Use the UNIQUE() function on the Item Category column (Item Category is in Column I).
   
3. Copy the list and paste values using Ctrl + Shift + V.

6. Build the Variance Table by Item Category

In this second table, you’ll create:

• Accurate Audits
  
• Audits
  
• Variance Rate
  
• Target Variance
  

1. Accurate Audits (count TRUE by item category)
   
   • Use COUNTIFS() with:
     
     • True/False column = TRUE
       
     • Item Category column = the category in the summary list
       
2. Audits (total audits per category)
   
   • Use COUNTIF() or COUNTIFS() to count how many times each category appears.
     
3. Variance Rate (percent of FALSE audits)
   
   • Compute FALSE count as:
     
     • Total Audits minus Accurate Audits
       
   • Divide by Total Audits:
     
     • (Audits – Accurate Audits) / Audits
       
   • Format as percentage using Ctrl + Shift + 5.
     
   • Fill down.
     
4. Target Variance
   
   • Enter a benchmark (example used: 10%) and reference it so it stays consistent.
     
   • If you want it dynamic down the column, reference the benchmark cell and fill down.

7. Create the Variance Chart with a Target Line

1. Select Item Category, then hold Ctrl and also select:
   
   • Variance Rate
     
   • Target Variance
     
2. Go to Insert → Recommended Charts.
   
3. If Excel does not suggest the right one, go to:
   
   • All Charts → Combo
     
4. Set chart types:
   
   • Variance Rate = Clustered Column
     
   • Target Variance = Line
     
5. Do not use a secondary axis for the target line (keep it on the same axis).
   
6. Rename the chart title to:
   
   • Truck Stock Accuracy by Item Category
     
7. Optional cleanup:
   
   • If percent labels appear on both the columns and the line and you only want one, click the unwanted labels and press Delete.
     
   • Resize and adjust fonts to fit the dashboard layout.