State RFP to a compliance matrix, then a response outline

02 · REQUEST 1

"Build a compliance matrix, one row per requirement"

We're bidding on the Illinois Treasurer's RFP 370-500-26-040 (attached). Please build a compliance matrix as an Excel workbook.

Sheet 'Matrix', one row per requirement, in this order:
1. Section III Scope of Work services III.1 to III.20
2. Section V.A Proposal Format items V.A.1 to V.A.11
3. Section V.B questions Q1 to Q45 (keep the sub-parts of Q33 i-iv and Q40 a-b inside the same row)
4. Section VI.A Mandatory Requirements VI.A.1 to VI.A.6, with VI.A.5 a-d as four separate rows

Columns: Ref (e.g. III.4, V.A.3, V.B.30, VI.A.5b) | RFP section heading | Requirement (one-line summary) | PDF page | Mandatory (Y, or 'Conditional' where the RFP says 'if any' / 'if applicable') | Page limit | Our response location | Status (leave 'Open').

Second sheet 'Key dates' with the Section IV.A schedule. Freeze and bold the header row, wrap text.

What did

• Read all four sections and wrote one row per requirement, 85 in all, in the order asked: III.1 to III.20, V.A.1 to V.A.11, the 45 questions, then VI.A with 5a to 5d as separate rows.
• Gave each row the PDF page it comes from; a requirement that runs onto the next page gets both, such as 14-15.
• Put page limits only where the RFP sets one: 3 pages for the Introduction and 5 each for the Scope of Work and Unique Capabilities.
• Marked "Conditional" the two items the RFP words as optional: Subcontractors ("if any") and the Redacted Copy.
• Added a "Key dates" sheet with the Section IV.A schedule, and froze and bolded the header row on both sheets.

We checked it against the PDF: 85 rows in the requested order with no gaps or repeats; every page number falls on the pages where that requirement is printed; page limits on exactly three rows; questions due 12:00 p.m. CT on 23 April and proposals before 1:00 p.m. CT on 6 May, as printed.

03 · REQUEST 2

"Add owners and scoring, and check Appendix B"

Good. Three changes to the same workbook - keep everything already in 'Matrix' as is.

1. Add an 'Owner' column after Status and fill every row:
   - Tom (proxy voting): III.5, III.6, III.7, Q22, Q23
   - Ana (ESG reporting and EDI): III.10, Q27, Q31 to Q40
   - Me (bid manager): all V.A rows, all VI.A rows, Q1 to Q13, Q41 to Q45
   - Priya (strategy): everything else
2. Add a sheet 'Scoring' with the evaluation factors and weights from the table in Section VI.B, plus the total.
3. Compare the Appendix B attestation list with Section III and add a note on the 'Matrix' sheet (highlighted row or cell comment) on anything that doesn't line up.

What did

• Opened the matrix it had just made and edited that same workbook.
• Added an "Owner" column after Status and filled all 85 rows by the rules given: Tom 5, Ana 12, Me 38, Priya 30.
• Added a "Scoring" sheet with the seven factors and weights of Section VI.B and the total of 115 the RFP prints.
• Compared Appendix B with Section III and flagged the gap twice: a yellow NOTE row under the matrix and a comment on III.20.
• Did not change any cell from request 1. We compared every one.

We checked it: all 85 owners follow the rules; the weights match the table on page 17; Appendix B on pages 44 and 45 does end at III.17, so III.18 to III.20 have no Yes/No line; the Key dates sheet is still there.

04 · REQUEST 3

"Draft the response outline in Word"

Now draft the response outline as a Word document so the team can start writing.

- Use the 11 items of Section V.A, in that order, as Heading 1 (Cover Letter, Table of Contents, Introduction, Scope of Work, Answers to Questions, Service Team, Subcontractors, Unique Capabilities, State Certifications and Disclosures, Cost Proposal, Redacted Copy).
- Put the page limit in the heading where V.A sets one.
- Under 'Answers to Questions', use the five V.B group headings as Heading 2 and each question as a numbered Heading 3 (Q1 to Q45, short title), with Q33 i-iv and Q40 a-b listed under their question.
- Under every heading add one placeholder line: '[Owner: <name from the matrix> - draft due TBC]'.
- Mark Cost Proposal and Redacted Copy as separate attachments, as V.A.10 and V.A.11 require.

What did

• Made a new Word file whose headings are real Heading 1, 2 and 3 styles, so Word’s Navigation pane shows the whole tree.
• Used the 11 items of Section V.A, in order, as Heading 1, with the page limit in the three headings that have one.
• Put the five question groups under "Answers to Questions" and all 45 questions under them, with the sub-parts of Q33 and Q40 listed.
• Took each owner from the matrix it had edited in request 2: Q22 is Tom’s, Q31 Ana’s, Q20 Priya’s.
• Marked the Cost Proposal and the Redacted Copy as separate attachments, as V.A.10 and V.A.11 require.

We checked it: 11 Heading 1 entries in the V.A order, five Heading 2 groups, 45 Heading 3 questions from Q1 to Q45, and every owner line matches the matrix.

Worth knowing: under Q17 the outline keeps one stray sentence from the drafting step, and from Q23 on the owner lines use a different paragraph style, so the spacing changes halfway. Both take a minute to fix in Word. ’s reply to this request listed the file but gave no summary.