Case Study • Job Quoting • Field Assessment Tool
Paper sheets. Manual math. Gone. Consistent quotes, every time.
A complete field assessment workbook to price storm and water damage, featuring a centralized pricing engine, admin-delineated action items, and an auto-generated estimate, delivered in March 2024.
The Challenge: Two representatives walk into two comparable water-damaged basements. Do they come out with the same price?
Before this project, not necessarily. Field reps recorded every affected room and material on a paper worksheet, then priced the job by hand back at the office against an informal rate sheet. Adding to the potential for differences, damage category and after-hours status impacted the rate and required action — distinctions a paper form had no way to enforce.
The bottom line: No instant feedback for the customer and a real risk the same job could be priced differently by two reps — or the same rep on different days.
Structured
One hidden pricing sheet cross-references every material, assessment, and rate — so every entry resolves to a single answer.
Automated
Protected Room sheets pre-fill fields as soon as a material is selected and automatically prep for the next entry.
Tailored
One button clones an entire Room tab — automation included — so a rep can add as many rooms as the job needs.
How I Built It
What data is needed?
Primary Data
Room-by-Room Entries
Material, quantity, and assessment, logged room by room during the site visit — nothing else required from the rep in the field.
Metadata
Pricing & Rules
A materials list, a pricing table by assessment and category, and dropdown lists for rooms and units.
Where does it come from?
Metadata Lists
Hidden Admin Sheets
Lists and Prices sheets, hidden from the rep, hold the rate and required action for every material-and-assessment combination.
Structure
Job Summary Tab
Customer info and the job’s Category/After Hours flags filter every downstream dropdown to only valid combinations.
Primary Data
Guided Entry
Selecting a material auto-fills its unit and assessment. An event macro adds the next blank row automatically.
Tailored
One-Click Cloning
A button duplicates the Room tab, automation included, so the workbook grows to fit the job’s needs exactly.
What do you need to do with it?
Instant Output
Automatic, Detailed Estimates
A fully formatted report renders automatically — on site — with room-by-room material selections, quantities, and action items.
Custom Output
Standardized Estimates
Per-room and total estimates roll up as a controlled range, not false precision, using a variance percentage set by management.
Reports
Print-Ready
Header info, disclaimers, and job notes print on every report without manual formatting or manual retyping.
Fig 1 — Room input sheet, cloneable to create additional rooms with the button at the top.
Fig 2 — The top of an auto-generated report — header information, room-by-room action items, and boilerplate notes all render without manual assembly.
What Was Built
This tool was built to complete assessments entirely from dropdowns during a single site walk-through.
- A workbook a rep completes room by room, on site, with zero formula or pricing knowledge required
- A pricing engine resolving the correct rate and action automatically — consistent across reps and visits
- A report and estimate that render immediately, letting a representative walk a customer through real numbers on site
- A UDF-plus-recursive-LAMBDA rollup pulling every room's line items directly off each Room tab, no Power Query required
- A VBA macro laying out the full formatted report — fonts, sizes, merged cells, borders, and shading included
- An estimate built as a governed range rather than a single number, reflecting an admin-set variance percentage
A Note on Confidentiality
This project is not covered by an NDA. In the absence of documented permission to use their name, this entry withholds it and describes the client by industry only. Screenshots are real, with the company’s name and logo replaced by The New Excel’s own branding, and pricing figures withheld or replaced with placeholder values. The system architecture and methodology described are accurate.