RFP compliance matrix with weighted scoring template

	A	B	C	D
1	RFP compliance summary
2	Example data — replace the blue input cells with your own.
3
4	WEIGHTED COMPLIANCE	REQUIREMENTS	MUST NOT FULLY COMPLIANT	RESPONSES NOT FINAL
5	80.0%	31	3	22
6	Example: fictional SCADA upgrade RFP for Example County water utility.
7
8	Counts by compliance level
9	Compliance level	Requirements	Share of requirements
10	Full	18	58.1%
11	Partial	8	25.8%
12	Roadmap	3	9.7%
13	No	2	6.5%
14	Unknown or blank	0	0.0%
15	Total	31	100.0%
16

	A	B	C	D	E	F	G	H	I	J	K	L
1	RFP requirement matrix
2	One row per requirement (up to 200). Blue cells are inputs; weights and scores come from the Scales tab.
3
4	Req ID	RFP section	Requirement	Priority	Weight	Compliance	Score	Weighted score	Max weighted	Response summary	Owner	Status
5	REQ-001	3.1 Architecture and resilience	Primary and standby servers at two sites	Must	5	Full	1.00	5.00	5.00	Hot standby pair at each site with automatic failover	Solutions architect	Reviewed
6	REQ-002	3.1 Architecture and resilience	Failover completes within 2 minutes	Must	5	Partial	0.50	2.50	5.00	Failover measured at 3 minutes; tuning planned	Solutions architect	Draft
7	REQ-003	3.1 Architecture and resilience	Runs on the utility's existing virtualization platform	Should	3	Full	1.00	3.00	3.00	Certified for the utility's hypervisor version	Solutions architect	Final
8	REQ-004	3.1 Architecture and resilience	Container-based historian services	Could	1	Roadmap	0.25	0.25	1.00	Planned in a dated release after the first year	Product liaison	Draft
9	REQ-005	3.2 Data acquisition	Reads at least 12 PLC and RTU protocols	Must	5	Full	1.00	5.00	5.00	Native drivers for all listed protocols	Controls engineer	Final
10	REQ-006	3.2 Data acquisition	Analog points time-stamped at 1-second resolution	Must	5	Full	1.00	5.00	5.00	1-second scan and time stamp on analog points	Controls engineer	Reviewed
11	REQ-007	3.2 Data acquisition	Store-and-forward during communication loss	Should	3	Partial	0.50	1.50	3.00	Buffers 24 hours; 72 hours needs more storage	Controls engineer	Draft
12	REQ-008	3.2 Data acquisition	Historian retention of 10 years at full resolution	Should	3	Full	1.00	3.00	3.00	Retention is set by the utility's storage size	Data lead	Final
13	REQ-009	3.2 Data acquisition	Direct reads from legacy DCS panels	Could	1	No	0.00	0.00	1.00	Not offered; a vendor gateway is required	Controls engineer	Final
14	REQ-010	3.3 Alarms and events	Alarm management follows ISA-18.2 practice	Must	5	Full	1.00	5.00	5.00	Priority, shelving and suppression per ISA-18.2	Controls engineer	Reviewed
15	REQ-011	3.3 Alarms and events	Alarm and event log searchable for 90 days	Must	5	Full	1.00	5.00	5.00	90 days online, longer on the historian	Controls engineer	Reviewed
16	REQ-012	3.3 Alarms and events	Escalation rules by shift and on-call roster	Should	3	Partial	0.50	1.50	3.00	Escalation by shift; roster sync is a later phase	Solutions architect	Draft

	A	B	C
1	Priority weights and compliance scores
2	Editable tables. The Matrix tab looks up the weight for each priority and the score for each compliance level.
3
4	Priority	Weight	Weight multiplies the compliance score
5	Must	5	Must: needed to meet the requirement
6	Should	3	Should: expected; a gap needs a reason
7	Could	1	Could: useful; a gap is acceptable
8
9	Compliance	Score	Score for the response to each requirement
10	Full	1.00	Met in the standard product, as written
11	Partial	0.50	Met with a workaround, a gap or a condition
12	Roadmap	0.25	Committed in a dated release, not in the product today
13	No	0.00	Not offered

	A
1	RFP compliance matrix with weighted scoring
2	Notes: what this workbook does, how to use it and the method behind it.
3
4	What it does
5	Scores a response to a request for proposal requirement by requirement. Each requirement carries a priority and a compliance level, and the workbook turns them into weighted compliance for the whole response and for each RFP section.
6
7	How to use it
8	1. On the Scales tab, check the priority weights (Must, Should, Could) and the compliance scores (Full, Partial, Roadmap, No).
9	2. On the Matrix tab, enter each requirement's Req ID, RFP section, requirement text and priority.
10	3. Enter the compliance level, a short response summary, the owner and the review status for each requirement.
11	4. Read the weighted compliance, requirement counts and gaps on the Summary tab.
12	5. Clear every row flagged Check values, and check the RFP sections list, before the response is submitted.
13
14	Formulas and method
15	Weight = VLOOKUP of the priority on the Scales tab. Score = VLOOKUP of the compliance level on the Scales tab.
16	Weighted score = weight times score. Max weighted = weight, so a Full response earns its full weight.

What does this template do?

This matrix tracks each requirement in a request for proposal and scores the response. Each row holds the requirement, its RFP section, a priority of Must, Should or Could, a compliance level of Full, Partial, Roadmap or No, the response summary, an owner and a review status. The Scales tab sets the weight for each priority and the score for each compliance level, and both can be edited.

For each row the weight comes from a lookup on the priority and the score from a lookup on the compliance level. The weighted score is weight times score, and the maximum weighted score is the weight alone. The Summary tab divides the sum of weighted scores by the sum of maximum weighted scores to give weighted compliance, then counts requirements, Must requirements that are not fully compliant and responses that are not final. Counts by compliance level and by priority use COUNTIF and COUNTIFS, and compliance by section uses SUMIFS.

The matrix holds 200 requirements. The example is a fictional SCADA upgrade for Example County water utility, with 31 requirements. The scores describe a response on paper and do not replace the evaluation committee's review of the proposal.

What’s inside

Weight from priority (Must 5, Should 3, Could 1) and score from compliance (Full 1 to No 0)
Weighted compliance equals the sum of weighted scores divided by the sum of maximum weighted scores
Flags Must requirements that are not fully met and responses that are not final
Counts by compliance level and by priority, with compliance by RFP section
Room for 200 requirements, with a check for unknown priority or compliance values

Which tabs does the workbook have?

Tab	What it holds
Summary	Weighted compliance, requirement counts, counts by compliance level and priority, compliance by RFP section, and checks.
Matrix	One row per requirement: priority, compliance, weighted score, response summary, owner and status, for up to 200 rows.
Scales	Editable weights for the priority levels and scores for the compliance levels.
Notes	Purpose, steps, formulas used, assumptions and limits.

What formulas does this template use?

This template holds 1,087 formulas in 1,441 cells across 4 tabs, so 75% of its cells calculate. They use 11 distinct functions; the longest formula is 232 characters and 657 of them read from another tab.

Function	Uses	What it does
`IF`	2,017	one result when a test is true, another when false
`ISNUMBER`	600	true for a number
`AND`	400	true when every test is true
`IFERROR`	400	swaps an error for a fallback value
`VLOOKUP`	400	looks a value up in a column
`OR`	200	true when any test is true
`SUM`	19	adds numbers
`SUMIFS`	16	adds values meeting several conditions
`COUNTIF`	14	counts cells meeting one condition
`COUNTIFS`	14	counts cells meeting several conditions
`COUNTA`	2	counts non-empty cells

Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

On the Scales tab, check the priority weights and the compliance scores.
On the Matrix tab, enter each requirement with its Req ID, RFP section, text and priority.
Set the compliance level, a short response summary, the owner and the review status for each requirement.
Read the weighted compliance and the gaps on the Summary tab.
Clear every row flagged Check values before the response is submitted.

What is it good for?

Responding to a utility or agency request for proposal
Bid or no-bid review of an RFP against product coverage
Tracking response owners and review status before submission
Comparing two drafts of the same response by weighted score

Questions about this sheet

What score does Roadmap earn?

Roadmap scores 0.25 by default, for a capability committed in a dated release. Change the score on the Scales tab if your evaluation rules differ.

Why does a row show Check values?

Its priority or compliance is blank or does not match a value on the Scales tab. The row adds to the maximum but not to the weighted score until it is fixed.

How is compliance by section calculated?

For each section, SUMIFS adds the weighted scores and the maximum weighted scores of its rows, and the percentage is their ratio. A new section must be added to the section table on the Summary tab.

Can the matrix hold more requirements than the example?

Yes, up to 200 rows on the Matrix tab. Requirements in a section that is not listed are reported by the RFP sections check.