Efficient Job Pricing With Excel

Figure 1. Name a worksheet by right-clicking the tab, selecting Rename, and typing in a descriptive title.

Figure 2. Type category headings in the first row of the Inventory List worksheet, leaving two columns blank after each. In the first column below each heading, enter the list of materials. The second column will be filled with costs; the third column will be left blank, as a visual break between categories.

Figure 3. The greatest amount of up-front work is compiling the costs of individual materials. To simplify estimating later on, the author also bundles items into commonly used assemblies; here he has priced out the components of a cedar railing assembly and transferred just the total cost to the list.

Figure 4. On the Labor Unit Costs worksheet, break down labor components as finely as possible for a more accurate final estimate. Price tasks by the square foot, linear foot, or unit, depending on what works best.

Figure 5. Name lists by highlighting the item names, then clicking Insert > Name > Define. In the dialog box that appears (above), enter the list name and click OK.

Figure 5. Name lists by highlighting the item names, then clicking Insert > Name > Define. In the dialog box that appears (above), enter the list name and click OK.

Figure 6. This fleshed-out Working Estimate worksheet has space for client information on top, and beneath it columns for listing tasks, materials and labor units, notes, quantities, costs, and extended prices.

Figure 7. Break down a typical deck job into categories and enter those in column A on the Working Estimate worksheet. Within each category, list individual components.

Figure 8. Pull-down menus that reference the Inventory List worksheet speed estimating tremendously.

Figure 9. Making a pull-down menu is easy. Click on the cell where you want the menu to appear. Click Data > Validation (top), which brings up a dialog box (above). Under “Allow,” click List. Under “Source,” type the exact category name from the inventory sheet, preceded by =. Click OK.

Figure 9. Making a pull-down menu is easy. Click on the cell where you want the menu to appear. Click Data > Validation (top), which brings up a dialog box (above). Under “Allow,” click List. Under “Source,” type the exact category name from the inventory sheet, preceded by =. Click OK.

Figure 10. To make a price appear in cell E52 when an item is selected in cell B52, use the VLOOKUP function. First, click cell E52. Select Insert > Function to bring up a dialog box. Select the category Lookup and Reference and click VLOOKUP. Click OK.

Figure 11. Once you’ve selected VLOOKUP, a “Function Arguments” dialog box will pop up. You’ll need to fill in four fields. First, click in the top box, “Lookup_value,” and click on cell B52. Second, click next to “Table_array,” go to the Inventory sheet, and select cells A3 to B53. Third, type 2 for “Col_index_num.” Fourth, type False for “Range_lookup.” Click OK.

Join the Discussion

Please read our Content Guidelines before posting

Close X