# Automotive Customer Scorecard Tracker Worksheet

> Source: https://netray.co/tools/automotive-customer-scorecard-tracker-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

An editable worksheet to log monthly customer scorecard results for PPM, delivery and premium freight, with gaps against each customer target.

## Category

quality

## Industry

Automotive

## Audience

- Quality manager
- Customer quality engineer
- Sales and account manager

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Customer

**Key:** customer

**Type:** text

#### Period

**Key:** period

**Type:** text

#### PPM actual

**Key:** ppm

**Type:** number

**Format:** integer

#### PPM target

**Key:** ppm_target

**Type:** number

**Format:** integer

#### PPM over target

**Key:** ppm_gap

**Type:** number

**Expr:** max(ppm - ppm_target, 0)

**Format:** integer

#### On-time delivery %

**Key:** otd

**Type:** number

**Format:** decimal

#### OTD target %

**Key:** otd_target

**Type:** number

**Format:** decimal

#### OTD shortfall pts

**Key:** otd_gap

**Type:** number

**Expr:** max(otd_target - otd, 0)

**Format:** decimal

#### Premium freight

**Key:** freight

**Type:** number

**Format:** currency

#### Status

**Key:** status

**Type:** select

**Options:**

- Green
- Yellow
- Red
- New business hold

**Rows:**

**Customer:** OEM A Plant 1

**Period:** 2026-09

**Ppm:** 18

**Ppm target:** 25

**Otd:** 99.1

**Otd target:** 98

**Freight:** 0

**Status:** Green

**Customer:** OEM B Powertrain

**Period:** 2026-09

**Ppm:** 62

**Ppm target:** 30

**Otd:** 96.4

**Otd target:** 98

**Freight:** 4800

**Status:** Red

**Customer:** Tier 1 C

**Period:** 2026-09

**Ppm:** 40

**Ppm target:** 50

**Otd:** 97.8

**Otd target:** 98

**Freight:** 1250

**Status:** Yellow

**Customer:** Tier 1 D

**Period:** 2026-09

**Ppm:** 0

**Ppm target:** 50

**Otd:** 100

**Otd target:** 99

**Freight:** 0

**Status:** Green

**Customer:** OEM E Truck

**Period:** 2026-09

**Ppm:** 120

**Ppm target:** 75

**Otd:** 94

**Otd target:** 97

**Freight:** 9300

**Status:** Red

**Totals:**

#### Total premium freight

**Key:** freight

**Agg:** sum

**Format:** currency

#### Average PPM

**Key:** ppm

**Agg:** avg

**Format:** decimal

#### Average OTD %

**Key:** otd

**Agg:** avg

**Format:** decimal

#### Total PPM over target

**Key:** ppm_gap

**Agg:** sum

**Format:** integer

**Summary:**

#### Premium freight per customer row

**Key:** freight_per_customer

**Expr:** sum_freight / count

**Format:** currency

**Notes:** Each customer defines PPM, delivery and freight in its own way, so keep the customer's published number rather than recalculating it. Use one row per customer and month. Export to Excel to build a twelve-month trend.

## Content

**Intro:** Every OEM and tier 1 customer sends a scorecard, each with its own formulas, targets and color rules. This worksheet puts them in one place so a quality manager can see which customers are over target and what premium freight is costing.

**Sections:**

#### How to use the tracker

**Body:** Enter one row per customer per month using the customer's own published numbers. Set the targets from the customer's supplier manual. The worksheet calculates how far you are over the PPM target and how many points short on delivery. Replace the sample rows with your own, add the status the customer assigned, and export to Excel to keep a twelve-month history.

#### What the numbers mean

**Body:** PPM is defective parts per million parts received by the customer, so a small shipment with one reject can produce a very high number. On-time delivery is measured against the customer's required date and window, which differs between OEMs. Premium freight is any expedited transport caused by your late or short shipment. Watch the trend and the rule that triggers escalation, since many customers move a supplier to a watch status after consecutive months off target.

**Bullets:**

- PPM: rejected parts divided by parts received, times one million
- OTD: lines delivered inside the customer window divided by lines due
- Premium freight: usually chargeable to the supplier
- Status color: follow the customer's rules, not your own

#### Common mistakes

**Body:** Mixing customer definitions into one average hides real problems. Another is waiting for the customer portal before acting: pull your own shipping and return data and compare it with the scorecard to catch errors early. Log disputes and recalculations, since scorecards are sometimes wrong. If your ERP records shipments and returns, build the same metrics there so you can reconcile with each customer before the month closes.

**Cta:** Netray can generate these customer metrics directly from your ERP shipment and return data, privately and on your own infrastructure. The worksheet is free to use without us.

**Faq:**

#### Why do customer PPM numbers differ from mine?

**Answer:** Customers count rejects at their plant, including parts rejected for reasons unrelated to your process, and sometimes use different receipt quantities. Compare your own data to theirs line by line and dispute errors promptly through the customer's portal or contact.

#### What is a good PPM target?

**Answer:** It depends on the customer and part type. Many OEMs set targets from single digits to a few hundred PPM. Your target comes from the customer supplier manual or your contract, so enter that value rather than a generic one.

#### Can I track more than one site?

**Answer:** Yes. Add the plant to the customer name column or insert separate rows per plant. Exporting to Excel lets you filter by site and compare plants over time.

#### How is premium freight handled?

**Answer:** Enter the total chargeable amount for the month. The totals row sums it so you can show leadership the real cost of delivery problems, and the cost can be used in a cost of poor quality report.

## Search terms

customer scorecard tracker, automotive supplier scorecard excel, OEM supplier scorecard template, supplier PPM tracker, on time delivery tracker automotive, premium freight tracker, customer quality rating tracker

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json