#### Efficient Job Pricing With Excel

View All 15 Photos >

Play slideshow

There are two common ways to estimate decks — stick-by-stick and by the square foot — and each has its advantages and disadvantages.

Square-foot pricing generates num­bers quickly, but it’s not precise enough for intricate deck and rail projects. Because a single component, such as a composite post-sleeve assembly, can cost well over \$100, mistakes or generalizations compound quickly, making an estimate either too low or too high. Both outcomes hurt your business: The downside of estimating too low is obvious; and estimating too high, especially in the current economy, may cost you the project.

Stick estimating, on the other hand, leaves nothing to chance and produces a number that guarantees a profitable job — but it’s insanely slow. One of the most tedious aspects is having to price out the material list over and over again, for every job. Here’s the good news: I’ve developed a Microsoft Excel–based system that eliminates a lot of the repetitive work. It’s accurate and, once you’ve set it up, nearly as fast as square-foot estimating.

Getting Started
This article assumes some basic familiarity with Excel. My system uses three Excel worksheets, or pages. The first two list detailed material and labor costs and generally don’t change over the course of a building season. The third references the first two to price out jobs using an Excel function called VLOOKUP, which stores the costs and inserts them in an estimate.

To start, open a new blank workbook in Excel. It should have three default worksheets, with tabs in the lower left corner that are labeled Sheet 1, Sheet 2, and Sheet 3 respectively. (If it doesn’t, add worksheets to the workbook using Insert > Worksheet > Blank Sheet.) Right-click on each tab, select Rename, and change the default names to Inventory List, Labor Unit Costs, and Working Estimate (Figure 1).

Setting Up Material and Labor Worksheets
To build the foundation of my system, I created categories that encompassed the deck building process and, in each one, listed relevant products and labor units. Because I use dimensional treated lumber like 2x8s and 2x10s for the framing phase of the project, building a category called Framing Lumber on the material worksheet made sense. Similarly, on the labor worksheet, I have a category called Railings that includes the labor cost for units such as installing a post.

You can have as many categories as you want, but don’t overcategorize. If you want to keep things simple at first, you could just use my categories, then make adjustments as you develop your own estimating template and begin to understand the relationship between the worksheets. For example, I deliberately put 2x8s and 2x10s in the same category, rather than making separate ones, because on the Working Estimate worksheet I have a field that specifies joist size and type, and I want all the items I might reference there to be in a single category.

Inventory List worksheet. We’ll set up the material inventory first. Click on the Inventory List worksheet and enter the categories of building materials you want to pull from (see sidebar “Inventory List Categories”) to create the Working Estimate. Allow three columns for each category: one for item names, one for costs, and one as a spacer to make the sheet easier to read. Enter the first heading in column A and then enter a heading every three columns across the top of the worksheet: Type PT Framing Lumber in column A, Decking Products in column D, Fascia/Trim Boards in column G, and so on (Figure 2). It doesn’t matter if the text overflows into the next column.

Add the names of the individual items, such as 2x4x8 MCQ, in the first, or left-hand, column underneath each heading. In the next column, you’ll eventually be entering the price you pay for each item.

It’s a good idea to leave a row blank to separate materials when transitioning from, say, cedar to pine, as whatever you put in the list is what you’ll see in the pull-down menu later on, and selecting from long lists is easier when there are visual breaks. And while lists can be as long as you like, I try to limit them to about 50 items. Any longer and the pull-downs get unwieldy. Leave about 10 blank rows at the end for additional items.

Next, assign a price to every item (Figure 3). If you have a laptop computer, the fastest way to do this is to meet with your suppliers and fill in the blanks while they look up the pricing on their computer. Don’t forget to include sales tax. Alternatively, print out your lists and have the suppliers fill in the numbers. Either way they might groan, but you can point out that this is the only time for the whole season they’ll have to look up a price for you.

Labor Unit Costs worksheet. After your Inventory List is set up, follow the same process with the Labor Unit Costs worksheet; only this time create categories and lists related to labor (see sidebar “Labor Unit Categories,” page 44). Break out the labor components of the job as finely as possible, as detail here will make your final job cost extremely accurate. I find that some things, such as framing, are best priced by the square foot, whereas others, like fascia installation, are more suited to linear-foot pricing. Footings, railing posts, and railing sections estimate most accurately as units (Figure 4).

Some considerations with labor are the height of the deck, site conditions, style of framing (flush girder or drop girder, for example), angles, and corners. I use different square-foot framing prices for decks that don’t require ladders and those that do. Laying straight decking is faster, and therefore less expensive per square foot, than laying diagonal decking, so I have separate prices for those as well.

Defining lists. Once your data is in, the next step is to define each category as a unique list within Excel. Starting with PT Framing Lumber on the Inventory List, click on the first item name in the left-hand column (6x6x8 MCQ in cell A3 in the example). Then drag your cursor down to the last cell in your list (A53 in my worksheet) to select the list of items (Figure 5). Don’t select the price columns. Click Insert > Name > Define, and in the dialog box that appears, name the list the same as the category. Repeat this process to define lists for all the categories on both the material and labor worksheets. Write down the names you use so you can reference them later.

Creating the Estimate Worksheet
With the boring lists of materials and labor completed, it’s time to build the part of the workbook that uses them to create a tangible job-cost estimate. Click on the Working Estimate tab, and at the top of the sheet, create cells for the client’s name, address, phone number, and any other info you like to attach to an estimate (Figure 6). In the row below that, label the columns, starting in column A, Item, Description, Notes, Qty, Cost, and Subtotal.

This worksheet will eventually contain all your job costs, although right now we’re building a template and you won’t populate this sheet with quantities until you’re actually doing an estimate. Think about a typical job and divide it into categories for both materials and labor (see sidebar “Categories for an Estimate”). Enter the category headings — one under another — in column A, leaving a few rows blank after each one. You can always add extra rows (highlight the cell where you want the row, then click Insert > Rows).

Within each category I list the items that many of the decks I build have in common. For example, ledgers will always need flashing, ledger bolts, joist hangers, and lumber (Figure 7). List this kind of detail in column A under each category heading. Consider things like demolition, RRP compliance, and waste disposal. The more flexibility you build into your template, the more efficient your estimating will be.

Here’s where we get to the fun part. I’ll use Joist Lumber Deck Area1, in row 52, as an example, but the steps are identical for all the items; the only thing that changes is the defined list being referenced (you did write the list names down, didn’t you?).

In cell B52, we’re going to build a pull-down menu that references the PT Framing Lumber category on the Inventory List worksheet (Figure 8).

Then in cell E52, we’ll insert the VLOOKUP function, which will automatically fill in the cost of the item that’s selected from cell B52’s pull-down menu.

To build the menu, click cell B52, next to Joist Lumber Deck Area1, and select Data > Validation (Figure 9). In the dialog box that appears, select List from the pull-down menu under “Allow.” In the “Source” box, type =PT_Framing_Lumber. This is case sensitive and needs to exactly match whatever you named your list.

Click OK to close the dialog box, and you will have a pull-down menu in the cell. To save time, use the copy and paste functions to copy this menu to other cells that will contain framing lumber.

The menu is great, but it doesn’t give us a price just yet. This is where the VLOOKUP function comes in. It takes the value of one cell (the item selected on the pull-down menu) and looks up an associated value (the item’s cost). It sounds complicated, but it’s not.

Click on cell E52 (where we want the cost). Then select Insert > Function. The “Insert Function” dialog box will appear (Figure 10). In the pull-down menu next to “Or select a category,” click on Lookup and Reference; in the box under “Select a function,” click on VLOOKUP; then click OK. (In Excel 2007, click VLOOKUP in the formula builder tool palette.)

The “Function Arguments” dialog box will appear, with four white boxes to fill in (Figure 11). Click in the top box, next to “Lookup_value,” then click on cell B52, where we just built the pull-down menu. Next click in the second box (Table_array); then go to the Inventory List worksheet and select all the values in both columns of the PT Framing Lumber category (A3:B53), including the extra rows. Enter 2 in the third box, because the price we want to look up is in the second column of cells you just selected for the “Table_array.” Finally, enter false in the “Range_lookup” field and click OK. If you haven’t yet selected an item from the pull-down menu in column B, the error message #N/A will appear in column E. Don’t worry — once you select an item, a price will appear instead.

Repeat this process to build your master workbook. If you’re using the same VLOOKUP data in another cell, you can just copy and paste; you don’t have to go through the whole dialog box process. Also, in the Excel help menu, you should read up on “Absolute Cell References,” as that concept can really save you some time.

Use Formulas
The name of the game is speed, so build formulas into the template that will do repetitive computations for you automatically. In column F, for instance, I put a simple formula (=D52*E52) to multiply the quantity in column D by the unit cost in column E to get a subtotal. I also created basic formulas at the bottom of the sheet to total up material and labor costs and to add markup, overhead, and profit to produce a final job cost.

Once your workbook is set up, you can use it as a template to create separate estimate files for your clients (just open the master workbook, click File > Save as, and enter whatever file name you want).

You can also add worksheets that reference your estimate worksheet to produce a lumber list for your supplier and a list to give to the crew that details what materials are to be used for what part of the job. Remember, this workbook contains data about all the materials and labor needed for a job, and to completely repurpose the information, all you have to do is reference it using basic formulas.

Greg DiBernardo owns Bergen Decks in Waldwick, N.J., and can be e-mailed at greg@bergendecks.com.