In my template, column A (labeled "Avg. cost/hr") holds a
formula that divides total cost by total hours. In its simplest
form, the formula would look like this:
The problem with this formula, and with others that use
division, is that any time column E is empty or contains a
zero, the formula automatically considers this a
"divide-by-zero" error. Instead of calculating a number, the
formula puts the message #DIV/0! (or ERR! in some versions) in
column A. When clients see this — and I know plenty of
contractors who have shown spreadsheets to their clients with
ERR! all over the place — they begin to wonder about the
accuracy of the rest of the numbers.
Fortunately, there’s a simple fix using an "if-then"
statement. (In Excel, this is called the IF function.) The
formula in cell A3 looks like this:
The formula argument (the part within the parentheses) has
three parts, separated by commas: a "logical test," a "value if
true," and a "value if false" (Figure 3).
3. When a cell is left blank, a spreadsheets interprets
it as a zero. In formulas that use division, this can cause
divide-by-zero error messages. To solve the problem, use an
if-then statement. The built-in IF function has three parts,
separated by commas: a logical test, a value if true, and a
value if false. In this example, if the argument E3=0 is true,
the cell holding the formula will display a zero (or any other
text typed between the quote marks). If the argument is false,
the cell will display the result of D3 divided by E3. Without
this if-then formula, when cell E3 is zero, the spreadsheet
will display an error message.
In plain language, the if-then statement first "tests" to
see if the value of cell E3 is zero; if it is, the formula will
display "0" (or whatever message you type between the quotes);
if the value is not zero, then the formula divides D3 by E3 and
displays the result. Using this IF function, when the value in
Column E is zero, "0" appears in column A instead of
Job-Costing To estimate
accurately, you have to do some sort of job-costing. If you are
not using actual costs to update your estimating system, then
you are missing a big opportunity to become a more confident
estimator. In my company, I want to know on a day-to-day basis
where I stand on each phase of every job. If we have gone over
budget on the foundation, for example, I want to know as soon
as possible so I can work with my people in the field to make
up the overrun someplace else in the job.
You don’t need accounting software to job-cost;
instead, you can set up a spreadsheet that keeps track of all
of a job’s invoices, bills, paychecks, and so on, along
with a code to indicate which phase of work these costs belong
to. For the first four years I was in business, this was the
way I kept track of job expenses. The spreadsheet I used was
accurate, and it allowed me to have as many categories as I
liked. An abbreviated version is shown in Figure 4.
4. In this job-cost spreadsheet, column E calculates
percent complete by dividing the cost-to-date (column D) by the
budget (column C). But this calculation doesn't bear any
relationship to actual progress on the job. The solution is two
additional columns: one (G) holds a percent complete estimate;
the other (H) uses an if-then statement (yellow cell) to
determine whether the job will be over or under budget.
The formula combines two if-then statements, one inside the
other. The first handles the divide-by-zero problem; the second
calculates dollar amounts.
The simplest way to use this job-cost spreadsheet is to use
only columns A through E. The "Code" and "Category" columns are
the same as those I use for estimates, and I enter amounts into
the "Budget" column by hand from my estimate. As invoices and
timesheets come into the office, I enter total amounts in
column D. Column E holds a formula that calculates the percent
complete for each category — it simply divides the
Cost-to-Date by the Budget. Column F tells me how much money is
left in the budget for each category — it subtracts
Cost-to-Date from the Budget.
These five columns work well as a quick comparison of
estimated costs to actual costs. But the calculated percentage
in column E is based on dollars budgeted and dollars spent
— it doesn’t bear any relationship to actual
progress of the work on the site. For example, in row 5, the
spreadsheet tells me that I’ve spent about $11 more for
carpentry than I had budgeted. This is no big deal if all of
the carpentry work is done. But if there’s still some
carpentry work to be finished, I could overrun my carpentry
budget by more than $11.
statements. Columns G and H tie the spreadsheet to
what’s actually happening on the job site. I enter the
numbers in column G based on site visits and discussions with
the crew about how far each category of work has progressed on
the job. Column H holds a formula that uses these estimated
percent complete numbers to predict whether we’ll come in
under budget or run over.
The formula is intimidating at first glance, because it
"nests" one if-then statement inside another:
Everything between the first and second "IFs" is necessary
to handle the divide-by-zero problem. The seemingly complex
series of equations after the second "IF" actually perform a
couple of simple calculations. The first part
G3=100,F3 looks at the
percentage I’ve entered and if it’s 100 (meaning
that phase of the job is complete), it uses the number from
column F to show how much we went over or under budget. If the
percentage I’ve entered in column G is less than 100,
however, that means work in that category is still going on.
The second part of this formula
predicts how much money will be left for each category if work
proceeds at its current pace.
By looking at the dollar amounts in column H, I can tell
whether any category is running over budget. To make this
easier to see, I’ve set the "Format" menu on the
spreadsheet toolbar (Figure 5) to display negative numbers in
5. Negative numbers are easier to see if they are
displayed and printed in red. This setting can be changed on
the format menu, which can also be used to display dollar
signs, percentage signs, and time or date notations, among
As long as my estimate of percent complete is fairly
accurate, I can use this spreadsheet at any stage of
construction to evaluate progress and make adjustments to bring
the job back in line with my budget.
Scott Shelley owns Scott C. Shelley Construction, a
remodeling company in Petaluma, Calif.