The Vendor Scorecard
Last reviewed
BeginnerWhat you'll learn
~30 min- Parse multi-vendor bid CSVs into structured comparison data
- Implement weighted scoring with configurable criteria weights
- Generate side-by-side comparison matrices with normalized scores
- Output a recommendation memo with scoring justification for leadership review
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.
Scoring vendor bids is like leveling drop-day options. Price matters — but if one store actually has Stagg (availability), you’re not settling for Weller SR at a closer store just to save the drive. And past experience with a vendor is like knowing which ABC stores lie to your face about what’s in the back. This tool lets you set the weights so the math matches your priorities, and the memo gives you something defensible when your VP asks “why not the cheapest one?”
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.
CSV input → normalization → weighted multi-criteria scoring → ranked output + narrative summary. This pattern works for any decision-analysis task: office space comparisons, software vendor evaluations, contractor selection, even comparing job offers. The techniques transfer anywhere you need to make a defensible choice from structured data.
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.
Before you start
If your agent is still open from the previous lesson, exit that session first (/exit or Ctrl+D). Create a clean folder for this lesson and open it:
mkdir -p ~/ofci-playbook/02-vendor-scorecard && cd ~/ofci-playbook/02-vendor-scorecardIn Windows PowerShell:
mkdir ~/ofci-playbook/02-vendor-scorecard; cd ~/ofci-playbook/02-vendor-scorecardOr open the folder directly in your desktop agent app. Start your agent the way Workbench Setup showed.
Switch to plan mode
Switch to Plan mode inside your session (Shift+Tab, /plan, or the mode picker in your desktop app) so the agent clarifies your scoring criteria and proposes a design before writing files.
The conversation
Say what you want
Start with a short plain-English request naming the tool and outcome:
I buy data center equipment. Help me build a tool called vendor-scorecard that scores vendor bids from a CSV across weighted criteria and writes a recommendation memo. Ask me what you need first.Answer its questions
The agent will ask how bids are structured, weighted, and formatted. Answer in plain English:
| If it asks | You might say |
|---|---|
| Which criteria and default weights should it score? | Price 30%, technical 30%, lead time 20%, experience 10%, warranty 10%, with a —weights flag to override. |
| How should each criterion be normalized to 0-100? | Lower price and lead time score higher; higher technical score, past projects, and warranty score higher. |
| Do you have a bids CSV, or should I include sample data? | Create sample-data/generator-bids.csv with four sample generator bids from Caterpillar, Cummins, MTU, and Kohler. |
| What should the recommendation memo include? | Executive summary, methodology, comparison matrix, winner and runner-up analysis, deadline and price-outlier risk flags, and conditions. |
| Which command-line options should it support? | Use whatever standard command-line tools are simplest, with options for —input, —project, —type, —weights, —deadline, and —output. |
ℹReference notes to hand the agent (or check against)click to expand
When the agent creates sample-data/generator-bids.csv, use columns vendor_name, unit_price, total_price, quantity, lead_time_weeks, warranty_years, past_projects, technical_score, notes with these illustrative sample figures (not real quotes from these vendors):
- Caterpillar: $895,000/unit, 36 weeks, 2-year warranty, 45 past projects, 92 technical
- Cummins: $845,000/unit, 42 weeks, 2-year warranty, 38 past projects, 88 technical
- MTU: $980,000/unit, 30 weeks, 3-year warranty, 22 past projects, 95 technical
- Kohler: $870,000/unit, 38 weeks, 2-year warranty, 30 past projects, 85 technical
Note on dependencies and risk flags: chalk@4 works with require() (chalk 5 and later are ES-module-only). For risk flags in the memo, flag any vendor whose lead time exceeds --deadline and any pricing outlier more than 15% from the mean (both are placeholder thresholds you can adjust per project).
Read the plan
Before approving the agent’s plan, check four things:
- Direction of normalization: Price and lead time invert the scale (lowest price = 100, shortest lead time = 100), while technical score (already 0-100), past projects, and warranty score highest = 100.
- Configurable weights: Default weights (price 30%, technical 30%, lead time 20%, experience 10%, warranty 10%) can be overridden on the command line via
--weights. - Side-by-side matrix and differentiators: The comparison table shows raw values alongside normalized scores and weighted totals, sorted best first, with key gaps between #1 and #2 called out.
- Complete recommendation memo: The memo includes a To/From/Date/Subject header, executive summary, scoring methodology, comparison matrix, top vendor and runner-up analysis, risk flags, and a conditional recommendation.
If the plan gets a rule backwards, steer it before approving:
Invert normalization for price and lead time so the lowest price and shortest lead time score 100.Once the plan covers all four checks, approve it and let the agent build the project. When it finishes, ask it to save a Git checkpoint.
Shifting priority to lead time (for example, 25% price and 25% lead time) changes the outcome when schedules are tight, because the cheapest generator that arrives 6 weeks late costs more than one that arrives on time. Writing the weights into the run and the memo makes your priority transparent to your VP.
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
Ask your agent to install what is needed, score the sample generator bids, and show you the recommendation memo:
Install what the tool needs, run vendor-scorecard on sample-data/generator-bids.csv for project SV9, and show me the recommendation memo.If you prefer running the commands in a terminal yourself:
cd vendor-scorecardnpm installnode src/index.js --input sample-data/generator-bids.csv --project SV9 --type generatorYou should see a ranked comparison matrix 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.
| Problem | Follow-up prompt |
|---|---|
| CSV parsing fails with “Invalid Record Length” | The CSV reader is failing because some vendors have commas inside their quoted notes field. Handle quoted CSV fields that contain commas cleanly. |
| All vendors get the same normalized score | The normalizer is not differentiating vendors. For price and lead time, the lowest value should score 100 and the highest should score 0 with linear interpolation between. Check that the min/max calculation is using the correct direction for each criterion. |
| Memo output is raw JSON instead of formatted text | The recommendation memo is outputting raw JSON scoring data instead of a readable document. Format the memo into narrative paragraphs with headers, a Markdown comparison matrix table, and a concluding recommendation section. |
When Things Go Wrong
Use the Symptom → Evidence → Request pattern: describe what you see, paste the error, then ask for a fix.
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.
- 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.
- 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.
- 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.
- 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 scoring under three profiles: price-focused (price 50%), schedule-focused (lead 50%), and quality-focused (technical 50%), then prints a matrix showing which vendor wins each.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 each vendor's delta behind the winner and prints a "what would it take" summary showing the price or lead-time change needed to overtake first place.Try it yourself
- Open your agent in a clean lesson folder and switch to plan mode.
- Describe the scorecard tool, answer the agent’s questions, and approve the plan.
- Run it against the sample data. Look at the comparison matrix and recommendation memo.
- 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. - 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.
- When you’re ready to score real vendor bids, check your data rules from Workbench Setup first. Close your AI agent session (
/exitorCtrl+D) before touching live NDA bid numbers so real vendor quotes never enter the AI conversation, then run the finishedvendor-scorecardtool locally on your machine against your bid CSV (or use anonymized vendor names and dummy pricing if you are still iterating inside the agent). Compare the weighted result to what you would have recommended on gut alone — if they match, you have the math to back it up; if they don’t, you just caught a bias before your VP did.
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.
You're evaluating bids for UPS systems. Vendor A quotes $420,000 with an 18-week lead time. Vendor B quotes $395,000 with a 26-week lead time. Your project timeline requires equipment on site by week 20. You set weights to price:25, lead:35, technical:25, experience:10, warranty:5. Which vendor likely scores higher, and why?
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.