The Vendor Scorecard

What you’re building

Three weeks after you sent out the generator RFP, four vendors have responded. Caterpillar, Cummins, MTU, and Kohler each sent back a different PDF with pricing buried on page 12, lead times on page 3 (or was it page 7?), and warranty terms written by lawyers who are paid by the word. Your VP wants a recommendation by Thursday.

You know exactly how this goes if you do it the old way. Two days of building a comparison spreadsheet in Excel, manually extracting numbers from four different document formats, color-coding cells that nobody else understands, arguing with yourself about whether Cummins’ lower price offsets their longer lead time, and then writing a memo that basically says “they’re all pretty close but I like Caterpillar.” Your VP nods, asks “why not the cheapest one?”, and you say something about lead times that you can’t actually back up with math because it was a gut call. We’ve all been there.

Or you could dump the bid data into a CSV, run one command, and get a scored comparison matrix with a defensible recommendation memo in 15 seconds. The kind of memo where, when your VP asks “why not the cheapest one?”, you point at the weighted scoring methodology and say “because math.”

That’s what you’re building now.

By the end of this lesson you’ll have a Node.js CLI tool that reads vendor bid data from CSV, applies weighted scoring across five criteria, generates a side-by-side comparison matrix, and outputs a recommendation memo that doesn’t sound like it was written by someone who just picked their favorite vendor and reverse-engineered a justification. Because that’s what the old way always looked like, and everybody knew it.

The showcase

Here’s what this tool does, and honestly, it’s the kind of thing that makes you wonder why you ever did this in Excel:

• Input: A CSV file with the stuff that actually matters — vendor name, unit price, total price, lead time (weeks), warranty (years), past project count, and technical compliance score (0-100). No formatting, no pivot tables, no conditional formatting that breaks every time someone opens it on a Mac.
• Weights: You decide what matters most. --weights "price:30,lead:20,technical:30,experience:10,warranty:10" or let the defaults ride. Tight schedule? Crank lead time to 40%. Cost-sensitive build? Push price to 50%. The tool doesn’t care about your priorities — it just enforces them consistently.
• Scoring: Normalizes everything to a 0-100 scale so you can actually compare dollars to weeks to years. Lower price = higher score, because this tool isn’t an idiot.
• Comparison matrix: Side-by-side table with raw values and normalized scores for each criterion, for each vendor. The kind of table that makes your VP think you spent two days on this.
• Recommendation memo: A real narrative — executive summary, scoring methodology, analysis of the winner, analysis of the runner-up, risk flags, and a conditional recommendation. Written like a procurement manager would write it, not like a data dump.
• Output: Markdown memo + JSON with full scoring data + CSV summary. Three formats because the memo goes to your VP, the JSON feeds your next tool, and the CSV goes into your procurement tracker.

The prompt

Open your terminal, navigate to a project folder, start your AI CLI tool, and paste this prompt:

Build a Node.js CLI tool called vendor-scorecard that evaluates vendor bids
for data center MEP equipment procurement. It parses bid data from CSV, applies
weighted multi-criteria scoring, and generates a recommendation memo.

PROJECT STRUCTURE:
vendor-scorecard/
  package.json
  src/
    index.js          (CLI entry point using Commander.js)
    csv-parser.js     (CSV reading and validation)
    scorer.js         (weighted scoring engine)
    normalizer.js     (data normalization — lower price = higher score, etc.)
    memo-writer.js    (recommendation memo generator)
    formatter.js      (Markdown table and output formatting)
  sample-data/
    generator-bids.csv (sample bid data for testing)

REQUIREMENTS:

1. CLI INTERFACE (src/index.js)
   - Usage: node src/index.js --input <csv-file> --project <name> --type <equipment>
   - Options:
     --input <path> (required — path to bids CSV)
     --project <code> (e.g., "SV9")
     --type <equipment> (e.g., "generator", "chiller")
     --weights <string> (criteria weights, e.g., "price:30,lead:20,technical:30,experience:10,warranty:10")
     --output <path> (output directory, default: ./output)
     --top <n> (highlight top N vendors, default: 3)
   - Default weights: price 30%, technical 30%, lead time 20%, experience 10%, warranty 10%

2. CSV FORMAT (sample-data/generator-bids.csv)
   Create a sample CSV with these columns and 4 sample vendors:
   vendor_name, unit_price, total_price, quantity, lead_time_weeks,
   warranty_years, past_projects, technical_score, notes

   Sample data (realistic data center 2500kW generator pricing):
   - Caterpillar: $895,000/unit, 36 weeks, 2yr warranty, 45 past projects, 92 technical
   - Cummins: $845,000/unit, 42 weeks, 2yr warranty, 38 past projects, 88 technical
   - MTU: $980,000/unit, 30 weeks, 3yr warranty, 22 past projects, 95 technical
   - Kohler: $870,000/unit, 38 weeks, 2yr warranty, 30 past projects, 85 technical

3. SCORING ENGINE (src/scorer.js + src/normalizer.js)
   - Normalize each criterion to 0-100 scale:
     * Price: lowest price = 100, highest = 0 (linear interpolation)
     * Lead time: shortest = 100, longest = 0
     * Technical score: use raw value (already 0-100)
     * Past projects: highest = 100, lowest = 0
     * Warranty: longest = 100, shortest = 0
   - Apply weights to normalized scores
   - Calculate weighted total for each vendor
   - Rank vendors by weighted total (highest = best)
   - Flag any vendor whose lead time exceeds project timeline (if --deadline provided)

4. COMPARISON MATRIX OUTPUT
   - Markdown table showing:
     * Row per vendor, columns for each criterion
     * Raw value and (normalized score) side by side
     * Weighted total in final column
     * Sorted by rank (best first)
   - Below the table: a "Key Differentiators" section calling out the most
     significant gaps between #1 and #2 ranked vendors

5. RECOMMENDATION MEMO (src/memo-writer.js)
   Generate a professional memo with:
   - Header: To/From/Date/Subject
   - Executive summary (2-3 sentences: recommended vendor and why)
   - Scoring methodology (weights used, what each criterion measures)
   - Full comparison matrix
   - Top vendor analysis (strengths, risks, why they won)
   - Runner-up analysis (what would change the outcome)
   - Risk flags (any vendors with lead time > 20 weeks, pricing outliers > 15% from mean)
   - Recommendation with conditions (e.g., "recommend Caterpillar, contingent on
     confirming 18-week lead time in writing")

DEPENDENCIES: commander, csv-parse, chalk

SAMPLE RUN:
node src/index.js --input sample-data/generator-bids.csv --project SV9 \
  --type generator --weights "price:25,lead:25,technical:30,experience:10,warranty:10"

What you get

After the LLM finishes — about a minute, roughly the amount of time it takes to explain to the PM why you can’t just “pick the cheapest one” — you’ll have a working scoring engine with sample data ready to test.

Fire it up

cd vendor-scorecard
npm install
node src/index.js --input sample-data/generator-bids.csv --project SV9 --type generator

You should see a ranked comparison matrix printed to your terminal and a recommendation memo written to output/. Open it up. That memo has an executive summary, a methodology section, a comparison matrix, vendor analysis, risk flags, and a conditional recommendation. It took 15 seconds to generate and it looks like you spent two days on it. Nobody needs to know you didn’t.

If something is off

These are the usual suspects. Each one is a quick follow-up prompt.

How it works (the 2-minute version)

Here’s what’s actually happening when you run this thing, because “it does math” isn’t a satisfying explanation and you should know what you’re handing to your VP.

1. CSV parsing reads the bid file and makes sure it has the columns it needs. Each row becomes a vendor object with actual numbers, not the string “eight hundred and ninety-five thousand” — it handles the type conversion so you don’t have to fight Excel’s formatting opinions.
2. Normalization is the part that makes the whole thing work. You can’t compare $895,000 to 36 weeks to 2 years — those are three completely different units. So the normalizer converts everything to a 0-100 scale. For price and lead time, lowest value gets 100 because less is better (duh). For technical score, experience, and warranty, highest gets 100. Now you’re comparing apples to apples even though the original data was apples to construction timelines to dollars.
3. Weighted scoring is where your judgment comes in. You set the weights — how much does price matter vs. lead time vs. technical compliance? — and the engine multiplies each normalized score by its weight and adds them up. A vendor who’s cheapest but slowest will score differently depending on whether you weighted price at 50% or 15%. The math is transparent, the weights are your call, and it’s all written down in the memo. No more gut-feel decisions you can’t defend.
4. The memo writer takes all that scored data and turns it into something your VP will actually read. Executive summary, methodology, comparison matrix, winner analysis, runner-up analysis, risk flags, and a conditional recommendation. It reads like a human wrote it because the template was designed by someone who’s written a hundred of these. Except this one took 15 seconds instead of a day.

Customize it

You’ve got the base tool working. Now let’s make it smarter. These customizations are the difference between “a tool I built once” and “the tool I can’t imagine working without.”

Add sensitivity analysis

This is the one that wins arguments. When your VP says “but what if price matters more?”, you run this and show them the answer in 10 seconds instead of reworking the spreadsheet for an hour.

Add a --sensitivity flag that runs the scoring three times with different weight
profiles: "price-focused" (price:50, others distributed), "schedule-focused"
(lead:50, others distributed), and "quality-focused" (technical:50, others distributed).
Show whether the winner changes under different priorities. Output a sensitivity
matrix showing which vendor wins under each profile.

Add bid gap analysis

The “what would it take” feature. When the runner-up’s sales rep calls to ask why they lost, you can tell them exactly what would have changed the outcome. Or when your VP asks “how close was it?”, you have a real answer.

Add a --gap-analysis flag that calculates the delta between each vendor and the
top-ranked vendor for each criterion. Output a "what would it take" section showing
what each non-winning vendor would need to change to overtake the leader. For example:
"Cummins would need to reduce lead time from 22 to 16 weeks OR reduce price by 12%
to overtake Caterpillar."

Try it yourself

1. Open your CLI tool in an empty folder.
2. Paste the prompt. Watch a scoring engine materialize in about a minute.
3. Run it against the sample data. Look at the comparison matrix and recommendation memo.
4. Now change the weights. Run --weights "price:50,lead:10,technical:20,experience:10,warranty:10" and see if the winner changes. This is the moment where you realize the old Excel spreadsheet was just confirming your existing bias.
5. Edit the sample CSV — change Cummins’ lead time from 42 weeks to 28 weeks. Run it again. Watch the rankings shift. This is what “data-driven procurement decisions” actually looks like, not the version your VP says in meetings.
6. Build a CSV from your actual current bid evaluation. Real vendor names, real numbers. Run it. Then compare the tool’s recommendation to what you would have recommended on gut alone. If they match, great — your instincts are solid and now you have the math to prove it. If they don’t, even better — you just caught a bias you didn’t know you had.

Key takeaways

• Weighted scoring replaces gut feel with math — and the math is transparent. When your VP asks “why not the cheapest?”, you don’t fumble through a defense. You point at the weights and the scores. The methodology section of the memo does the arguing for you.
• Normalization is the unsung hero — you can’t compare $895,000 to 36 weeks to 2 years. Normalization puts everything on the same 0-100 scale so the comparison is actually fair. Without it, you’re just eyeballing numbers in a spreadsheet and calling it analysis.
• The memo is the deliverable, not the spreadsheet — the scoring engine is the brains, but the recommendation memo is what leadership sees. A formatted memo with methodology, analysis, and risk flags lands differently than a forwarded Excel file with “see highlighted row.”
• CSV input means this tool plays nice with everything — export from Excel, from your procurement portal, from whatever cursed system your company uses. As long as it’s a CSV with the right columns, this tool doesn’t care where the data came from.

What’s next

The RFPs went out, the bids came back, you scored them, and your VP approved the recommendation. Now comes the part nobody warns you about: the vendor sends a purchase order confirmation with a submittal schedule, and suddenly you’re tracking shop drawings, O&M manuals, and factory test reports across four projects and twelve equipment categories. In a spreadsheet. That you share via email. That three people have different versions of.

In the next lesson, you’ll build The Submittal Tracker — a tool that tracks the lifecycle of every submittal across multiple projects, flags overdue items before they become problems, and generates the weekly status report that your PM asks for every Monday at 9am. The bids from this lesson become purchase orders, and those POs kick off the submittal process that the next tool manages. The pipeline is starting to connect.