Supplier Offer Assessment Tool

2022 · Snowflake · Google Sheets · Google Apps Script

Impact

• Used for >2000 negotiation iterations per year; time to analyze a supplier offer reduced from roughly 1 hour to under 5 minutes, a >90% reduction
• Margins improved over the years of direct supplier negotiations, but the tool's contribution is hard to isolate: selling prices and procurement input costs both shifted during the same period, and there is no control group; the measurable contribution is the negotiation decisions it directly informed
• Gave the procurement team a grounded, data-backed view of where they stood in every supplier negotiation, replacing gut feel with actual category benchmarks

Business Problem

The company had been buying all products through a joint buying cooperative. When they transitioned to negotiating directly with suppliers, they had no baseline: no visibility into what margins looked like under the old contract, and no system to track how prices and margins evolved as the direct relationships matured.

The need was twofold: margin insights on historical contract prices to establish what normal looked like, and ongoing competitor price tracking to benchmark each negotiation against what the market was actually charging. This was not a one-off project but a continuous effort: the tooling was extended and automated over time as the team's analytical needs grew and the volume of negotiations increased.

Solution Design

Three components working together:

• Templated price offer tracking: structured Excel templates sent to suppliers for capturing incoming price offers in a consistent, comparable format
• Competitor price tracking: web-scraped competitor prices loaded into Snowflake, covering different price lines across multiple competitors
• Margin analysis: compare margins over time, per supplier, per product, per category, against competitor price benchmarks

Reports were produced in Google Sheets and exported to PowerPoint slides for validation and sharing with buyers. The export was triggered via an Apps Script button inside the sheet. Over time, more steps in the data preparation and template validation pipeline were automated, reducing manual effort and making the weekly reporting cycle faster and more reliable.

Technical Challenges

No existing baseline. The company had been buying through a cooperative, so there was no internal history of direct supplier prices or category-level margin data. The first task was reconstructing a baseline from cooperative contract data, which required normalizing product descriptions across suppliers who used different naming conventions for the same items.

Competitor scraper maintenance. Reliable competitor price coverage meant dealing with layout changes, inconsistent product structures across competitors' ranges, and scrapers that broke silently. Scraper health had to be monitored actively to catch gaps before they propagated into the margin calculations.

Supplier template compliance. The Excel templates only produced comparable data if suppliers filled them consistently. Real submissions had missing fields, alternate layouts, and free-text overrides that required preprocessing before the data could feed into margin calculations.

Status

• Delivered in 2022 as part of the company's transition to direct supplier negotiations
• Covered multiple competitors and supplier relationships across product categories
• Google Sheets reports shared with buyers via PowerPoint exports, generated with an Apps Script button inside the sheet

Next Steps

• Commodity price index integration: link supplier cost structures to public commodity and gas indices so the margin view reflects underlying input cost movements, not just selling price changes; see the Commodity & Gas Index Mapping project