Supplier coverage and response dashboard workbook
Compare two supplier-review snapshots without letting the population or the weighting change unnoticed. This workbook calculates issued-request, substantive-reply and usable-evidence coverage for one frozen supplier cohort. It shows both supplier counts and a separately declared fixed weighting, with the decision references needed to explain the figures.
The XLSX contains a blank dashboard and cohort, a separate fictional dashboard and cohort, and instructions. Each cohort has twenty supported supplier rows. The matching five-page PDF is a static, zoomable sheet reference. It does not recalculate. Use the workbook for calculations and retain the reviewed source records outside it.
Download the editable supplier coverage and response dashboard workbook (XLSX).
Download the static supplier coverage and response dashboard reference (PDF).
Freeze the supplier question before comparing snapshots
Choose one common request scope and one supplier counting unit. Enter the programme, controlled cohort reference, two review dates, expected supplier count, rule reference and reporting owner. Confirm the cohort only after reconciling its business identities and completeness. The spreadsheet checks the entered fields; it cannot establish that the supplier list is complete.
One row represents one supplier for this selected request scope. A supplier with several questionnaire exchanges still occupies one row. Keep its individual topic-period requests in the response tracker and record the supplier-level decision here. Do not paste request rows into the cohort and then label their count unique suppliers.
US EPA’s Supply Chain Guidance describes GHG information pilots involving a subset of suppliers. Keeping the selected cohort explicit here is an original design choice, not an EPA dashboard requirement. No coverage target is imported from that guidance.
Both snapshots use exactly the same suppliers, request definition and weights. If the actual programme adds a supplier or changes its question, prepare a separately reviewed edition and explain the change. This file does not lock history, detect an overwritten weight or reconstruct a previous state automatically. Controlled version history remains necessary.
Use four states with an explicit response rule
Not requested means no request has been issued. Awaiting reply means issued without a substantive response. Reply with gap means a substantive response exists but the evidence has not been accepted as usable for the entire selected request. Usable evidence means that the referenced human review has accepted it for that scope.
These are local administrative conventions supplied with the workbook. An acknowledgement alone does not count as a substantive reply. An explanation that information is unavailable can count as Reply with gap. Neither silence nor that explanation establishes poor sustainability performance, misconduct or non-compliance.
Record an earlier and later decision reference for every supplier, including those not contacted. Give open work a specific action and owner. A usable entry still needs its retained review reference. The dashboard cannot authenticate a document, decide whether its scope is sufficient or turn an administrative label into independent assurance.
Keep the fixed weighting separate from the supplier count
Declare one weight basis, unit and period, then supply that weight for every supplier. The fictional example uses purchases in INR lakh. A real file could use a different supported, consistently defined basis. The workbook does not convert currencies, reconcile purchasing records, estimate emissions or validate the relevance of the selected weighting.
A zero-weight supplier remains in the supplier-count denominator. A missing weight is different: count results can remain available, but all weighted amounts and percentages are withheld until the weights are complete and valid. When every weight is zero, the amounts are zero and weighted percentages show n.a. rather than an invented rate.
Read the denominators carefully. Every coverage percentage uses the whole frozen cohort or its whole declared weight, including suppliers not yet contacted. The dashboard does not calculate a response rate only among invitees. Its percentage changes are percentage points, calculated before display rounding; count and amount changes are ordinary differences.
Replay a case where the two measures move apart
All suppliers, weights, records and decisions in the example are invented. Maple’s cohort COH1 contains A01 to A06 at reviews on 24 September and 8 October 2026. Record W1 fixes their purchase weights at 50, 20, 15, 10, 5 and 0 INR lakh, respectively. The total is 100 INR lakh at both reviews.
At the earlier review, A01 has usable evidence and A03 has a reply with a gap. A02 and A05 await replies. A04 and A06 have not been requested. Four suppliers have been issued requests, two have replied and one has usable evidence. The two replies represent 65 of the 100 weight units.
At the later review, A02 and A03 have usable evidence. A04 has replied that data are unavailable, so it remains a reply with a gap. A05 still awaits a reply and A06 remains unrequested. A01 also moves to Reply with gap because review R07 identifies evidence for the wrong site. That is a recorded correction, not a newly measured environmental result.
Substantive replies rise from two of six suppliers to four of six, or 33.3% to 66.7%. Their fixed-weight coverage rises from 65% to 95%. These figures describe the collection record. They do not establish that the replies contain complete quantities, that the suppliers improved or that a particular reminder caused the change.
Usable-evidence coverage tells a different story. It rises from one of six suppliers to two of six, or 16.7% to 33.3% by count. Its weighted coverage falls from 50% to 35%. A01’s weight of 50 exceeds the combined 20 and 15 carried by the newly usable A02 and A03.
Both calculations are correct under the stated fixed-cohort rules. The useful next step is to resolve A01’s site mismatch and the other open questions, not to select whichever percentage looks better. A01 remains a substantive respondent, which is why its weight still enters reply coverage while leaving usable-evidence coverage.
Test missing values and corrections before relying on the file
Clear A01’s weight in a disposable copy. The supplier counts should stay visible and weighted outputs should be withheld. Enter zero instead and the weighted denominator changes: zero is an actual input assertion. Restore the supported W1 value after the test. Do not use zero to make an incomplete schedule appear complete.
Give A02 the same business ID as A01. The duplicate-ID check should withhold the aggregate outputs, including weighted ones. Restore the original ID. The check detects repeated entered identifiers without regard to case; it does not discover two different supplier codes that belong to the same legal business.
Change A01’s later state back to Usable evidence in the test copy. Later usable weight becomes 85 of 100 and the supplier count becomes three of six. Those calculations demonstrate that the formulas respond to edits. In real use, the state must change only when the retained review supports it; the formula does not close the wrong-site question.
Respect the tested editing contract
Paste literal values only and retain the original General base formats. The workbook rejects input formulas, booleans, text numbers, invalid states and unsupported numerical values. Weights range from zero to one billion with at most two decimals. Dates must be whole numeric spreadsheet serials from 1 January 2000 through 1 January 2100, with the earlier date no later than the later date.
Use the twenty designated rows, keep the formula columns intact and retain the exact state labels. The cohort tables can be filtered, but filtered-out suppliers still enter every summary formula. Rows beyond the supported range are not included automatically. A larger cohort needs a redesigned and retested workbook, not a silent extension of the table.
Fresh headless LibreOffice recalculation and reopening test the delivered formulas and controlled input changes. Microsoft Excel and desktop interaction have not been tested. The authoring engine cannot evaluate ISFORMULA, so its initial diagnostic previews are not evidence of numerical correctness. The delivered workbook and PDF use the separately recalculated results.
Keep the cohort reference, request definition, fixed weights, dated decisions and output edition together. Use the supplier response tracker for individual exchanges and the supplier evidence request checklist when designing the underlying request. The response-rate guide helps diagnose why a request has stalled; this dashboard preserves the aggregate view and its denominators.
Source: US EPA, Supply Chain Guidance, How to Engage Suppliers. Checked 15 October 2026.
Version 1.0. Original Nitium Advisory workbook, formulas, diagram and fictional case. No source questionnaire, official scoring system or recommended coverage threshold is reproduced. The files contain no macros, external connections or automatic transmissions. They supply no legal determination, supplier certification or independent assurance.
Comments