Plays from YouTube (privacy-enhanced mode) only after you press play. Privacy
Excel vs Estimating Software
Which estimating mistakes does a spreadsheet hide, and which ones does estimating software catch?
We estimate the same garage job in Excel and OpenConstructionERP, using the same quantities, unit rates and 15% markup. Five deliberately planted spreadsheet errors let us test what each workflow catches and what still needs a human check.
You will produce
One garage estimate run in a spreadsheet and in the app side by side, with the five spreadsheet errors that made it about 13% too high.
Transcript
0:00 Same job, two programs
Same garage, same prices, two programs. The spreadsheet says $48,000. There are five mistakes hiding in it, and nothing on the sheet looks wrong. This is for anyone who prices work in a spreadsheet, a small contractor, a remodeler, an estimator starting out, and wonders if software is worth the switch. I'll run one small estimate both ways, side by side. By the end you'll know which mistakes a spreadsheet hides, which ones software catches, which ones nothing catches, and how to bring your own sheet into a free app.
0:35 BOQ, unit rate and markup
First, a few words you'll keep hearing. An estimate is usually laid out as a bill of quantities, or BOQ for short. That's one line for each piece of work, with a description, a quantity, a unit like square feet, a price per unit, and a total for the line. That price per unit is called the unit rate. It's built from what one unit costs you in material, labor and equipment. For this slab, $7.50 a square foot is $4 of concrete and mesh, $3 of labor, and 50 cents of equipment. Those are example prices, not a quote. Quantity times unit rate gives you the line total. Add up all the lines and you have your cost. Then comes markup, a percentage on top of cost that pays for running the business and leaves you a profit. In this example it's 15 percent.
1:29 The garage job
The job is a detached two-car garage, 24 feet by 24. 12 lines, from digging for the footings to the electrical. On the left is the spreadsheet, on the right the app, and both get exactly the same lines and the same prices.
1:46 Where spreadsheets win
Spreadsheets have earned their place. They're already on your computer, everybody knows them, and you can lay out a job any way you like. A 2013 survey of about 200 construction professionals found that for smaller companies, simple tools like spreadsheets were satisfactory for estimating.
2:04 How the spreadsheet works
Here's how the sheet works. Each line multiplies quantity by rate. A sum at the bottom adds up the lines, the markup row adds 15 percent, and the grand total sits under that. Every one of those cells is a formula, but on screen you only ever see the answer. That's the weak spot. A number typed over a formula looks exactly like the formula's answer, and a sum that stops one row short still shows a believable figure. In audits of real business spreadsheets from 1997 on, 91 percent of 54 sheets had significant errors. Those were sheets of all kinds, not estimates, and each audit counted errors its own way. We're also not great at spotting our own. In one experiment, students who built a small spreadsheet put the chance of an error in their own work at about 18 percent. 86 percent of them had made one.
2:59 Five planted mistakes
So I put five ordinary mistakes into this sheet on purpose, the kind anyone makes late in the day. The stopwatch up top only times the computer, from a click to the result, measured while filming. It doesn't time me, so it says nothing about how long either way takes a person.
3:19 Import the sheet into the app
Now the app. In the sidebar, under Estimating, open Import / Export. Pick United States and the schedule of values format. Choose the project and its bid schedule, which is what the app calls an estimate, then choose the spreadsheet file. There's nothing to retype. The app reads the column headings, item, code, description, quantity, unit and rate, and reports back: 12 positions imported, 3 rows skipped. Those 3 are the subtotal, the markup and the grand total. They have no description, so the app doesn't read them as work. It also refused one row and said why: position 8 already exists. I'd copied the shingles row to start a new line and never changed it, so the sheet paid for the roof twice. The app kept the first copy and told me about the second.
4:09 The app recomputes every line
Open the bid schedule. The app doesn't take any totals from your sheet. It multiplies quantity by rate on every line itself and adds up every line it was given: $41,371. The sheet's subtotal is 41,992. Only $621 apart, and three of the mistakes sit in that small gap. The first one: the electrical line went into the row below the range the sum adds up, so the sheet never counted its $2,448. A sum doesn't stretch by itself when you add a line under it. The second: the slab total isn't a formula at all. Somebody typed 3,840, which was right when the slab was 512 square feet. The quantity changed to 576, the total didn't, and $480 went missing. Add the shingles counted twice, and those three mistakes almost cancel out. A close total proves nothing.
5:10 What the checks find
Now the checks. In the bid schedule toolbar, click Validate, then Run Validation. The app runs 139 rule checks over the lines. The score is 99 percent, with 3 warnings. A high score only means most checks passed. The warnings are what matter. The first warning: the rate on position 10 is more than 5 times the median, the middle value when you line up all the rates. $97.50 a foot for gutters should be $9.75. A slipped decimal turned a $585 line into $5,850. The second: position 6, the wall insulation, has no unit rate, so in the sheet it quietly came to zero. The third flags the garage door, and the door is fine. It's the only item priced by the piece, so its rate sits far above the per-foot prices around it. That's how to read these checks. They point at a line, and you decide.
6:06 Fixing it in a few clicks
Fixing them takes a few clicks: the gutter rate back to $9.75, and $1.40 a square foot for the insulation. The 15 percent goes in the markups panel under the lines, not in a line of its own. The grand total now reads $42,758. The sheet said 48,290, about 13 percent high. That's how you lose a bid you should have won, and other mistakes could just as easily have made it low.
6:34 What nothing catches
Now the honest limit. Leave a line out completely, say the concrete apron in front of the garage door, and neither program flags it. A check can only look at the lines that are there. In that 2013 survey, overlooked costs were the most common cause of estimating errors.
6:51 Which one to use
So which should you use? For a small job you know well, a careful spreadsheet does fine. Lock the formula cells, add a check cell that works out the total a second way, and sort your rates to spot the odd one out. Software earns its keep when estimates get longer, more people touch them, or old sheets get reused. Then checks that run every time beat checks you have to remember. One important note. Prices and quantities in cost databases and benchmarks are approximate, a guide only. Check every price and every quantity carefully yourself. We can't be responsible for errors.
7:31 Checks for every estimate
Whatever you use, run these on every estimate. Check that every line is quantity times rate. Check that the sum covers every line. Look for item numbers that show up twice. Look for blank or zero rates. Sort the rates and question the outliers. And walk the drawings one more time for anything you left out. If you want to try this with your own spreadsheet, OpenConstructionERP is free and open source, and it imports a sheet like this one in a few clicks. The install video is right here. It walks you through setup, all the way to your first estimate.
