How is the supplier risk score calculated?

It is the risk priority number: severity times occurrence times detection, each rated 1 to 10, so it runs from 1 to 1,000. A severity of 6, occurrence of 4 and detection of 3 gives an RPN of 72, a criticality (severity times occurrence) of 24 and 28 points under a threshold of 100. That matches the Supplier Risk Score calculator.

Why is there a severity floor as well as an RPN threshold?

Because RPN is a product, a severe risk can score low. A food-contact declaration that has lapsed might rate severity 9, occurrence 2 and detection 2, an RPN of only 36, yet it can stop shipments. With a severity floor of 9, that risk is marked Act whatever its RPN. Both values are your own settings on Setup.

What RPN threshold should we use?

There is no universal value. Set it so the Act list is a workload your team can actually carry, then review it. Many teams start by acting on the top 10 to 20 percent of scores plus anything at the severity floor. In the example, a threshold of 100 and a floor of 9 put 7 of the 26 risks on the Act list, 6 of them still open.

Does this follow the AIAG-VDA FMEA action priority method?

No. The AIAG-VDA FMEA Handbook replaces RPN with action-priority tables for design and process FMEA, and those tables are copyrighted, so they are not reproduced. This register uses RPN with a severity floor, which suits supplier risk ranking. If your customer requires action priority for an FMEA, use the handbook.

How is action status worked out?

Against the as-of date on Setup, not today's date. With an as-of date of 12 October and a 14-day window, an action due on 5 October is Overdue, one due on 20 October is Due soon and one due on 15 December is In progress. Typing a closed date makes it Complete and the risk Closed.

What does the spend with an Act risk show?

The total annual spend, from the supplier list on Setup, with every supplier that has at least one open risk marked Act. In the example four of the eight suppliers carry an open Act risk, so the tile shows 4,580,000 of the 8,580,000 on Setup, 53% of annual spend. It tells leadership how much of the supply base the open risks touch.

Planning and Supply Chain

Supplier Risk Assessment Excel Template

Score every supplier risk for severity, occurrence and detection, see which ones to act on, and track each mitigation action to closure on a live dashboard.

Overview

This supplier risk assessment workbook is a working risk register for supplier quality engineers, buyers and supply-chain managers. It ships with an example register for a fictional conveyor maker: eight suppliers, 26 risks across quality, delivery, single sourcing, finance, trade and tariffs, natural hazards, cyber and compliance, and the actions taken on them, so the dashboard is populated the moment you open it.

Each risk is rated 1 to 10 for severity, occurrence and detection. The workbook multiplies them into a risk priority number (RPN), the same formula as the Supplier Risk Score calculator on this site, and also shows criticality (severity times occurrence) and the distance from your threshold. Because a multiplied score can hide a severe failure behind low occurrence and good detection, a risk is marked Act when its RPN reaches your threshold or its severity reaches your floor. Every Act risk needs an action, an owner and a due date, and the action status is judged against an as-of date you set, so the workbook never depends on the computer clock.

In practice, score new risks as they appear and review the register monthly or quarterly. The top-ten list and chart show where to spend audit days and engineering time, the severity by occurrence matrix shows how much risk sits in the danger corner, and the spend tile translates the register into money leadership understands. Closed risks stay in the register as a record of what was done.

What this template includes

Setup with your RPN action threshold, a severity floor, an as-of date for action status, a due-soon window, a plain-words rating guide for the three 1-10 scales, and the supplier list with annual spend and a single-source flag
Risk Register table: one row per supplier risk with category, description, severity, occurrence and detection ratings
Per-row RPN, criticality, distance from your threshold, a Priority word (Act, Monitor or Closed) and the rule that triggered it
Mitigation action, owner, due and closed dates, with an action status word (Complete, Overdue, Due soon, In progress) judged against your as-of date
Check column that names problems in words: supplier not on the list, ratings outside 1 to 10, an Act risk with no action, owner or due date, duplicate IDs
Dashboard KPI tiles: open risks to act on, overdue actions, highest open RPN and the annual spend with suppliers that carry an Act risk
Two native charts: the top 10 open risks by RPN and open risks by category split into Act and Monitor
Top-ten table with owner, due date and status, a severity by occurrence count matrix, and summary tables by supplier and by category
Method and Verify sheets: every formula in words, and a self-check against an independent reference including the Supplier Risk Score calculator's example
A CSV with live formulas of the Risk Register for any spreadsheet app; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it for the quarterly supplier risk review, to decide which suppliers to audit or dual-source, to show leadership the spend exposed to risks you have to act on, and to chase mitigation actions until they are closed.

How to use it

On Setup, agree the RPN threshold and severity floor with your team, set the as-of date, read the rating guide, and replace the example suppliers with your own, with annual spend and single-source flags.
On Risk Register, add one row per supplier risk with its category and description, and rate severity, occurrence and detection from 1 to 10.
For every row marked Act, type the mitigation action, owner and due date; type the closed date when the action is done.
Fix anything the Check column flags.
Update the as-of date before each review and open the Dashboard for the risks to act on, the overdue actions and the spend exposed.

Frequently Asked Questions

How is the supplier risk score calculated?: It is the risk priority number: severity times occurrence times detection, each rated 1 to 10, so it runs from 1 to 1,000. A severity of 6, occurrence of 4 and detection of 3 gives an RPN of 72, a criticality (severity times occurrence) of 24 and 28 points under a threshold of 100. That matches the Supplier Risk Score calculator.
Why is there a severity floor as well as an RPN threshold?: Because RPN is a product, a severe risk can score low. A food-contact declaration that has lapsed might rate severity 9, occurrence 2 and detection 2, an RPN of only 36, yet it can stop shipments. With a severity floor of 9, that risk is marked Act whatever its RPN. Both values are your own settings on Setup.
What RPN threshold should we use?: There is no universal value. Set it so the Act list is a workload your team can actually carry, then review it. Many teams start by acting on the top 10 to 20 percent of scores plus anything at the severity floor. In the example, a threshold of 100 and a floor of 9 put 7 of the 26 risks on the Act list, 6 of them still open.
Does this follow the AIAG-VDA FMEA action priority method?: No. The AIAG-VDA FMEA Handbook replaces RPN with action-priority tables for design and process FMEA, and those tables are copyrighted, so they are not reproduced. This register uses RPN with a severity floor, which suits supplier risk ranking. If your customer requires action priority for an FMEA, use the handbook.
How is action status worked out?: Against the as-of date on Setup, not today's date. With an as-of date of 12 October and a 14-day window, an action due on 5 October is Overdue, one due on 20 October is Due soon and one due on 15 December is In progress. Typing a closed date makes it Complete and the risk Closed.
What does the spend with an Act risk show?: The total annual spend, from the supplier list on Setup, with every supplier that has at least one open risk marked Act. In the example four of the eight suppliers carry an open Act risk, so the tile shows 4,580,000 of the 8,580,000 on Setup, 53% of annual spend. It tells leadership how much of the supply base the open risks touch.