Why are my spreadsheet estimates losing money?

Spreadsheet estimates lose money in four ways: formulas that break without telling you, prices that go stale, versions that multiply, and numbers that never reach the schedule or the invoice. A spreadsheet will give you a total either way. It cannot tell you that the total left out a row.

Hidden formula errors

A widely cited review of spreadsheet audits by Raymond Panko found errors in the large majority of the spreadsheets examined. Estimating sheets fail in the same few ways:

• A sum that stops one row short. Someone inserted a line below the range.
• A typed number where a formula used to be. Pasted over months ago, and it no longer follows the quantity.
• A link to another tab or file that moved.
• A copied job with the last client's quantities still in it.

None of these produce an error message. They produce a lower price, and a lower price wins bids.

Stale prices

A material price typed into a sheet is as old as the last time someone looked it up. When prices move 10 percent in a quarter, a template from spring is a loss in fall. The same is true of labor: a wage rate without burden, or with last year's workers' compensation rate.

Version sprawl

"Estimate v3 FINAL (2)" lives on one laptop. The version that was sent to the client lives in an email. Six months later a change order needs to be priced the same way, and nobody is sure which file was the bid.

One person's knowledge

Most estimating spreadsheets are understood by exactly one person. When that person is out, bids wait. When they leave, the logic leaves with them.

The work that happens after the bid

This is the cost people miss. When the job is won, someone retypes the estimate into a schedule, then into a schedule of values, then into invoices, then into accounting. Every retyping is hours of office time and another chance to drop a line.

When a spreadsheet is still fine

Honestly: for a handful of bids a month, of the same kind, by one estimator, a careful spreadsheet works. If you stay with one, protect it.

• Lock the formula cells.
• Keep prices and rates in one table, with the date they were last checked.
• Add a check total that counts rows and flags a blank quantity.
• Save the sent version as a PDF with the date, every time.
• Have a second person look at any bid over a set amount.

What to look for when you move on

• Saved items with their cost built in. Labor at the burdened rate, materials with waste, production rates you can adjust.
• Margin visible before you send. On every line and on the total.
• One record per bid. Versions kept, with the one the client accepted marked.
• The same lines carried forward. Into the schedule, the schedule of values and the invoices, without retyping.
• A connection to your accounting, so the office enters things once.

Moving over without stopping work

Do not convert everything at once. Take your ten most used items, build them properly, and price the next real bid both ways. When the two prices disagree, find out why: it is usually something the spreadsheet had been getting wrong.

Where ConstructStream fits

In ConstructStream an estimate is built from saved products. Each product knows its phases and crew-days and its materials with waste, so every line shows labor, materials and margin before it goes out. When the client accepts, booking the job lays out the visits on the schedule from those same phases, invoices bill from the same lines, and invoices, payments and customers stay in step with QuickBooks.