Corrective action tracker: spreadsheet columns and formulas

A corrective action tracker for supplier SCARs works as a spreadsheet with one row per SCAR and a due date and a status for each 8D section. Sort it by days late every week and you will see which supplier owes you what. The tool above takes your own open SCAR list and shows the same view, with every late section first, without sending anything to your suppliers.

What columns should a SCAR tracker have?

One row per SCAR, with a due date and a received flag for each section you chase. That is what lets you see a supplier who sent containment but not root cause.

If your 8D has other sections, add them. If a customer requires different terms, put their dates in the due columns.

What formulas make it show what is late?

Three formulas do the work. Assume row 2 is the first SCAR, issue date is in F, the D4 due date is in J and the D4 received flag is in K.

Due date from the issue date and a number of days (here D4 at 10 days for normal priority):

=F2+10

Days late for D4, blank if it has been received or is not yet due:

=IF(OR(K2="Yes",J2=""),"",MAX(0,TODAY()-J2))

Status for D4:

=IF(K2="Yes","Received",IF(J2="","",IF(J2<TODAY(),"Late",IF(J2-TODAY()<=2,"Due soon","Open"))))

Repeat for D3, D5/D6 and D7. Add a final column that takes the largest days-late figure across the four, and sort on it. Format the date columns as dates; dates pasted as text break every formula above.

What due dates should the tracker use?

Count each section from the issue date, by priority. The defaults SCAR Chase uses for a normal-priority SCAR are 1 day for D3, 10 for D4, 20 for D5/D6 and 30 for D7. High priority is 1, 5, 10 and 20; low is 2, 15, 30 and 45. Your customer's contract can override all of these, so write theirs in the due columns when they apply. Our corrective action request template guide has a filled-in example.

What is a good weekly routine for a SCAR tracker?

Fifteen minutes on the same morning each week keeps the list honest:

1. Sort by days late, largest first.
2. For every late section, email the supplier contact and name the section and its date.
3. For anything still late after the second reminder, call and copy their manager.
4. Enter anything received last week, then read it before you mark it accepted.
5. Close any SCAR whose sections you have all accepted, and put the date in.
6. Look at the suppliers with the most late sections. Those are your calls.

How do you keep the tracker audit-ready?

Never overwrite a date. If a supplier gets an extension, add a note with who agreed and when, and keep the original due date in its own column so the history shows how late the section really was. Save the supplier's response file next to the row, or in a folder named for the SCAR number, and enter the date you accepted each section. An auditor sampling one SCAR wants the request, the answers and the dates in one place, and a tracker that edits history makes that impossible.

When does a spreadsheet stop being enough?

It stops when the routine slips. A tracker does not email anybody, so the chasing is still your time, and the reminders go out only on the weeks you remember. Suppliers who never reply are also the ones nobody notices until the audit. The signs are a column of "Late" nobody has acted on, SCARs closed by "see email", and a supplier history that exists only in your inbox.

SCAR Chase reads a spreadsheet laid out like this one. Import your open SCAR list as a CSV and it shows every late section, matching headers such as SCAR number, supplier, issue date and the due and received columns for each section, and then it emails the suppliers when you press Start chasing. Nothing is emailed until you do. Our corrective action tracking software guide has a checklist for comparing tools.

FAQ

Is a corrective action tracker the same as a CAPA tracker?

They overlap. A CAPA tracker usually covers internal corrective and preventive actions across departments. A supplier SCAR tracker follows requests you sent to outside companies and, so, what they owe you and by when.

Can I track SCARs in Google Sheets?

Yes. The formulas above work in Google Sheets as written. Share it view-only with anyone who needs to see it, and keep one person responsible for editing.

What should the tracker keep for an audit?

For each SCAR: the request as sent, the dates, the supplier's response and your acceptance of it, and the closed date. Keep records for as long as your quality manual or your customers require.