Trying to figure out why some jobs go well and others don't can
be a nightmare. Often there are so many factors involved, it's
impossible to get a clear read. Even answering simple questions
can be tough: Are additions or kitchens more profitable? Why
does one lead carpenter seem to produce more than another? Why
do some jobs always run over on labor while others don't?
With all the day-to-day responsibilities of running a company,
who has time to track the kind of data needed to answer these
questions? And so it is that many builders rarely know for sure
why certain things happen.
A few years back, our company needed a system for tracking and
reporting man-hours worked on individual jobs. At the time, we
were using a report we would pull from QuickBooks after all the
weekly timecards had been entered into the computer. We would
present this report at our weekly production meeting, but it
always seemed to raise more questions about each job than it
answered. For one thing, the report told us only how many hours
we had spent thus far on a given job; how much time was left in
the bid — and whether that was enough to finish the job
— remained a mystery.
To get a more in-depth look at our jobs, we created an Excel
spreadsheet that compared the total man-hours budgeted with a
revised estimate of how many hours we thought the work would
take. We got the revised estimate by adding the hours spent to
date (taken from our weekly QuickBooks report) to the number of
hours we thought it would take to complete the remaining work.
This gave us a clear idea as to whether we would hit the labor
Each week I would add any new jobs and change orders that had
been sold, update the actual hours used, and revise the
projected hours needed to finish. Within a few minutes of
entering the data, I had an updated report I could take to the
The spreadsheet was a big improvement, and it worked for a
while. But because we were selling roughly a job a week, my
database was getting very large and unwieldy. The problem was
that every job remained on the list — even the ones we
had completed, which we kept for the valuable historical
information they contained.
Over time, using pivot tables has
enabled us to make intelligent choices based on real
data, not gut feelings.
A New Approach
One day, Randy Foster, one of our company's owners, mentioned
that I could create pivot tables in Excel that would allow me
to manipulate the data any way I wanted. Randy wasn't sure how
to set them up, but if pivot tables would make my report easier
to read, I was determined to find out how to make them. My
guide was a book called Sams Teach Yourself Microsoft Office
Excel in 24 Hours ($22.50, written by Trudi Reisner,
www.samspublishing.com). After about two hours of working my
way through this book, I could create a pivot table in less
than a minute.
Right away, I added a new column — Status — to my
database, and started using four words to describe each job:
PRE for jobs sold but not yet under construction, OPEN for jobs
under construction, DONE for jobs recently completed, and COM
for jobs that had been completely closed out. I was now able to
get a report that showed only open jobs with the particular
data I wanted. I could also have Excel do the math from the
pivot table and tell me how the company as a whole was doing on
actual hours vs. hours bid. (This one bit of data allowed us to
fine-tune our bids to the point where, companywide, we stay
consistently within 2 percent of total hours bid.)
And, once a week, I could now get a report that was accurate
and truly useful. We were able to spot jobs that were heading
south and make proactive adjustments to head off trouble.
A Better Spreadsheet
A few months later, I developed a new spreadsheet designed to
take full advantage of pivot tables. I wanted to be able to
compare as much information about completed jobs as possible. I
started by titling each column of the spreadsheet according to
the information it contained: Job Type, Salesman, Lead
Carpenter, Job Name, Total Man-Hours to Complete, Gross
Revenue, Cost of Goods Sold, and Gross Profit. With the pivot
table, I could now compare different kinds of job types. We
soon discovered, for example, that we were repeatedly
underbidding bathrooms. Why this was happening wasn't
immediately clear, but we were still able to add a percentage
to each bathroom, which has increased the overall profitability
of this job category.
We also can compare lead carpenters, to see which ones produce
the highest overall gross profit — or, more important, if
any consistently produce a low profit margin. With this ability
to look at real facts, we can spot our problems and work on
them. In the case of a lead carpenter whose jobs may be lagging
in profitability, for instance, we can work with him to
discover why the problem exists and how to overcome it. We've
also found that showing crew members actual data really
motivates them to improve.
We've found it too time-consuming to go backward and try to
create databases from past job history. Now, when we sense a
need to mine new data in some area of our business, we start
entering it from that day forward to build the database. Over
time, this has enabled us to make intelligent choices based on
real data, not gut feelings.
In short, pivot tables have become a truly important tool for
our company. They provide us with one of the best ways we know
to distinguish what is real from what we only think is
is a project developer for the Artisans
Group, an Olympia, Wash., remodeling company.
How To Create a Pivot Table
Step 1The most important part of creating a
pivot table is to get as much relevant information as possible
into your database. Give some thought to what you would like to
analyze. In the example shown here, which is the database we
use to examine gross profit margin, we added as many fields as
we thought we would want reports on. Keep in mind that you can
always insert more columns and increase the size of the
Step 2Now you are ready to input data. You have
two options: One is to simply start filling in the cells one at
a time. The other is to highlight the first cell of the title
row, click on Data at the top of the toolbar, then click on
Form. A window will appear; click New and you can input your
data, one entry at a time.
Step 3Creating the pivot table is easy.
Highlight your entire database, even the area that has no
information yet. This way, when new data is entered, the pivot
table will read it. Next, click Data on the toolbar, then Pivot
Table and Pivot Chart Report. A window will appear. Select both
Microsoft Office Excel list or database and Pivot Table, then
click Next.Step 4The next screen asks where you want the
data to come from. Because you've already highlighted the
entire database, Excel will fill in the range for you. This
area is known as an array. Click Next.