Case Study
One email in, tonight's loading call out: rebuilding a fuel distributor's daily price book.
- Industry
- Fuel Distribution
- Operation size
- Multi-plant regional distributor
- Engagement
- Ongoing on-site work: pricing
Anonymized: Client name withheld; no approval for named publication is on file. Supplier, system, site and staff details are generalized. The work, the checks and the current status are reported as they stand.
By Jacob Johnston | Published | Updated
Summary
Every day the owner of a Colorado fuel distributor emails a price book, built from the ERP's evening pricing report, that dispatch uses to decide where loads come from. The source prices were available, but the layout and comparisons hid the answer. TNDS turned that same email into a zoned, freight-aware price book and dashboard that updates from the email, and checked it value for value against the old spreadsheet math before anyone relied on it. It is in live review with the owner and general manager, with a five-business-day reliability window before cutover.
What the operation looked like before
The price book came out of the ERP's evening pricing report as a spreadsheet with two zone tabs, and dispatch used it to pick a loading terminal for each zone. Half the report was posted rack prices that dispatch never uses. Stale and placeholder rows sat next to live ones. The bold 'best' price ignored freight and ignored whether the terminal actually had product to load, and live pickup points were buried in a 300-row tab of unassigned locations. Nobody needed new numbers. They needed the night's decision on one screen.
What was actually broken
Half the report was noise
Posted rack prices filled half of every report, and dispatch had no use for them.
Best did not mean loadable
The 'best' flag ignored freight and availability. One bolded best price sat on a supplier line that usually had no product at the terminal.
A formula pointed at the wrong row
One best-price comparison referenced the wrong row, so the report compared against the wrong number. Nobody could see it in a tab that size.
Live data hidden in the clutter
Live pickup points sat in a 300-row unassigned tab, next to placeholder and stale rows that looked just as real.
Freight was a guess, or missing
Freight changes which terminal wins, but the report left it out. The first build had to start from an industry per-mile estimate until the company's own freight lanes were loaded.
What TNDS did
The rules came first, out of the weekly operations meeting with the owner and the general manager. The build came second, and the checks came before anyone was asked to trust it.
Step 1
Decide the rules with the people who use the book
Transport lifting only. The evening ERP report stays the source. Drop the posted half. One sheet split by zone, plus a cross-zone view for the long haul. Backup terminals go on the dispatch trip note. An earlier-in-the-day book was considered and turned down: it would have gained about 46 minutes and forced discounts to be kept in two places.
Step 2
Build it twice, same rules
An Excel prototype and a live Google Sheets build ran on the same pricing rules, so each could check the other. The live build matched 1,684 values against the spreadsheet math with zero mismatches before it went in front of the client.
Step 3
Let the email do the data entry
A script picks up the owner's price book email and updates the workbook and the dashboard. In the first observed run the dashboard was current about five minutes after the email arrived, with nobody touching it.
Step 4
Put freight and availability into the answer
Freight moved from an industry estimate to the company's own lanes, 77 per fuel. Terminals that rarely have product start as Unavailable, terminals that need a nightly call start as Call to confirm, and a special-circumstance terminal is off by default. Dispatch's own flags always beat the book.
Step 5
Hand it over without surprises
Client accounts are read-only by default. Header notes are in plain words. A 19-slide walkthrough covers the nightly routine, and user and developer manuals, a cutover and rollback record, and a run board of 38 tracked tasks go with it. Cutover waits for five business days of on-time updates.
Tools used
Tools the operator already pays for, plus free public data. Nothing that needs a new license or a vendor contract to keep running.
- Google Sheets as the price book and history
- Google Apps Script for email intake, the pricing engine and freight lookups
- An HTML dashboard: a page per zone, head to head, cross-zone and a nightly calls page
- The company's own freight lanes in place of industry estimates
- Public EIA diesel price history and National Weather Service forecasts for context
Where it stands
- In the first observed run, the owner's price email became a zoned, freight-aware price book with no re-keying; the dashboard was current about five minutes after the email arrived.
- 1,684 values matched the old spreadsheet math with zero mismatches; 317 baseline and 379 feature checks pass
- Two errors in the old report were found and fixed before anyone relied on the new one
- Every estimate is labeled as an estimate, and dispatch's flags override the book
- Live review with the owner and general manager is under way; cutover follows five business days of on-time updates
- Next-day load planning was parked on purpose, so the price book could ship first
Why it was built this way
The source prices were available; the comparisons and layout needed work. The job was to build the decision a dispatcher makes at night, not a prettier copy of the report. Rules agreed first, two builds checking each other, every estimate labeled, and a dated go-live that depends on observed reliability rather than a demo.
If your operation looks like this
Start with an Operations Assessment. TNDS works with your people to understand the operation, then provides a written priority order and a quote for the fix work.
More from True North
These case studies describe client engagement work. Pipeline Punks is our public workshop and publications hub. Our GitHub organization introduces the practice and its public development standards.