|
(pressing HOME will start a new search)
|
|
Using Powerful Spreadsheet Application Tools to Increase the Efficiency and Effectiveness of Estimating
- Jay P. Christofferson
- Brigham Young University
- Provo, Utah
There are many tools that small-to-medium-size residential contractors can use to estimate more effectively and efficiently. While most of these builders have computers with spreadsheet software, many do not make time to use these tools effectively or simply do not know how to use them. Builders have indicated that they want more training on estimating. This paper discusses methods of setting up spreadsheet estimates and the use of powerful functions that can increase the capability and automation of Excel spreadsheets. Data Validation allows a user to quickly select items from a list. The VLookup function returns information from a database based on a specific input. For example, if a user enters a specific door model, its corresponding unit price will be returned. Using these two functions together can significantly decrease the time it takes to complete an estimate.
Key Words: Construction, Estimating, Computer Spreadsheets, Effectiveness, Efficiency
Introduction
According to NAHB (1997), 93 percent of builders have at least one computer. Computers have become powerful tools for those who use them. The problem is that many builders either don’t take the time to use this technology or they don’t know how. Many large construction companies use estimating programs and integrated systems that cost thousands or even tens of thousands of dollars to purchase – more money than most small-to-medium size builders can afford. However, there are inexpensive ways to do computer estimating. One way is to use computerized spreadsheets that have the power of programs costing thousands of dollars.
The benefits of having computer spreadsheets are;
 | they are inexpensive |
| they are easy to use |
| they can be customized to your style of doing business |
| and they are very powerful. |
In 1992, the National Association of Home Builders completed the "Builder Computerization Study" (NAHB, 1992). The purpose of the study was "to find the level of computerization among [residential] builders and to find the extent to which computers are used in various operations in the construction industry." A random sampling of five thousand members of NAHB was made. The study had a response rate of 21 percent (n=1050).
The 1994 "Builder Computer Study" repeated the 1992 survey (NAHB, 1994). The 1994 study also had a 21 percent response rate (n=5000). A comparison of computer usage between the years 1992 and 1994 is shown in figure 1.
Of note is the high usage among builders of word processing, accounting, and spreadsheet software. Only 40 percent of the builders used estimating software during 1992. The reported figure for estimating actually decreased by 1 percent when the survey was repeated in 1994.
|
Figure 1. Comparison of 1992 and 1994 Builder Computer Software Usage. |
A follow-up survey was made in 1997 (Carmichael, 1997). This "Member Computer Survey" was not as in-depth as the 1992 or the 1994 study. There were 612 responses to surveys distributed through the local Home Builder Associations. Builders were asked to identify all areas of operation that were currently computerized (figure 2). This study showed that word processor usage among builders was 88 percent; up 10 percent from the 1994 study. According to this study, the use of spreadsheets increased by 9 percent and ranked as the second most used software by builders. Although the use of estimating software by builders increased, its use remained relatively low at 46 percent of the builders that responded.
|
Figure 2. 1997 NAHB Builder Survey of Computer Usage |
In the 1997 survey, builders were asked what kind of technical seminar they needed. The highest percentage of any category listed by builders was estimating at 47 percent of the respondents. Project management training was the next highest category listed (36 percent).
It has been shown that many builders have a spreadsheet program on their computer already. Computer spreadsheets can be as simple or as complex as needed. They can take the drudgery out of doing estimates and will make estimating and other office tasks more efficient. Although many builders can set up basic cost breakdown summary sheets and use basic formulas, most do not make use of the spreadsheet tools that provide the real power in creating spreadsheet estimates. Here are some helpful methods that can turn basic spreadsheets into powerful tools to accomplish estimates quickly and accurately.
Setting up a Summary Sheet
The summary sheet is an overview of all the costs of construction broken down into major work categories such as excavating, framing materials, framing labor, etc. The summary sheet is similar to the Cost Summary Breakdown sheets that banks and mortgage companies give out with their construction loans. Many summary sheets are divided into project overhead and hard costs. They are usually organized according to the sequence of construction but may be organized into the 16 divisions of the CSI structure.
Creating Detail Sheets
Detail sheets are pages that contain the actual quantities and unit prices for each of the construction categories. Formulas can easily be entered to perform the calculations that typically take up so much of a builder’s time in the estimating process. Once the detail sheets are created, an estimator need only enter the quantities for the different items. The totals on the detail sheets can be linked to update the summary sheet automatically.
Information Lookup Methods for Estimating
Figure 3 shows a typical take-off item that would be found on a detail sheet. The line item includes the item description, quantity, unit of measure, unit price, and extended or total price. Additional speed can be obtained in the take-off process by selecting an item from a list simply by clicking on the item and then having the computer look up the unit of measure and also the unit price. With this type of estimating program, the only data entry necessary would be quantities of items. Well-developed spreadsheets will save builders much of the time necessary to put together an estimate.
|
Figure 3. Typical Take-off Item |
Following are two methods that can help an estimator spend less time entering spreadsheet data.
Data Validation
Picking items from a list is a quick and easy way to select the items desired to take off. A database containing a list of options can be entered into an Excel spreadsheet as a list. The database can be entered into any worksheet of the workbook. The range of cells containing the list of materials should be given a name (i.e. the named range "ExtDoorList" figure 5).
Next, select the cell where the description the take-off item will be (cell B2, figure 3). Click on the Data menu item at the top of the screen, and select Validation (Excel 97). The Data Validation menu screen pops up (Figure 4). Under Allow, click on the down arrow and select List from the available options.
|
Figure 4. Data Validation Dialog Box |
Under Source, enter the name of the list that contains the selection options. Place the cursor in the source box and click.
Go to the Insert menu item on the main tool bar and click.
Click Name, Paste. A list of named cell ranges will pop up.
Select the desired name by double clicking on it and the name will then be entered into the source box with the "=" sign in front of it.
Click OK to complete the Data Validation.
The down arrow for the pop-down-list button will appear when cell B2 is selected (figure 5). The list of items display when the down arrow is clicked with the mouse. Click on the preferred option and the description will automatically be entered into cell B2. Figure 5 shows cell B2 with the validation list of options active and the Stanley K1-6-Panel door selected. The figure also shows the "Database/Material & Price List." This is a table with a list of the exterior door take-off options, their units of measure, and their prices. This database, "ExtDoorDB" (cells B18-D18), would typically be located in another worksheet or in another workbook. It is shown here for demonstration purposes only. The named range of cells, "ExtDoorList," is highlighted (cells B13 through B18).
Creating A Vlookup
VLookup, meaning vertical lookup, is a most powerful and useful function (see also the Lookup and Hlookup functions). This function allows a user to look up a value based on some input value. An estimator would want the spreadsheet to look up the unit of measure and the unit price depending on which selection is entered into cell B2 (the Lookup_value). When the "Stanley K1-6-Panel" door is selected in cell B2, the program looks up the corresponding unit of measure ("EA") and unit price ("$171.00"), then automatically puts these values in D2 and E2 respectively. The Total Price is automatically calculated based on the formula in cell F2 ("=C2*E2").
|
Figure 5. Selecting From a List Using Data Validation |
To create a VLookup, the database or table that holds the descriptions, units of measure, and unit prices for the exterior doors should be named (figure 5). In our example we named the exterior door database "ExtDoorDB" (cells B13-D18). Next, select the cell where VLookup will input the Unit Price (cell E2, figure 7) and click the Function Wizard (fx) on the Standard Menu Bar and choose the "VLookup" function from the "Lookup and Reference" category.
The VLookup dialog box is displayed (Figure 6). In the space next to Lookup_value, enter the cell reference (B2). The value stored in cell B2 is used as an index. This value is then matched to an item with the same name in the database.
The Table_array refers to the database or table where the door information is stored (i.e."ExtDoorDB"). The Table_array reference, "ExtDoorDB," can be inserted by clicking Insert/Name/Paste/"ExtDoorDB".
There are three columns in the "ExtDoorDB" database. The Col_index_number refers to the column in the database or table which contains the information to return. If the unit price of the door was needed, the Col_index_number would be 3 or if the unit of measure for the door, then the Col_index_number would be 2.
Enter "False" in Range_lookup. The "False" value in Range_lookup requires that the Lookup_value, "Stanley K1- 6-Panel" in cell B2, exactly matches an item, "Stanley K1-6-Panel", in the list of database options (cells B9-B14).
When the VLookup dialog box is completed, click OK. If the VLookup formula is correct, the Unit Price will be entered in cell E2 (figure 7). To get the unit of measure value into cell D2, enter "=VLOOKUP(B2,ExtDoorDB,2,FALSE)" into cell D2.
|
Figure 6. The VLookup dialog box |
|
Figure 7. Using the VLookup Function to Select Information From a Database or Table |
Conclusion
The possibilities for automating and controlling your estimating spreadsheets are unlimited. By applying these two Excel features to worksheets throughout the workbook, estimating templates can be created that will make generating estimates fast and easy. Making a list of take-off items is simple using Data Validation. Units of measure and unit prices are automatically input through the VLookup function. The estimating effort, then, is centered on taking off quantities. The time spent estimating can be substantially reduced through effective use of these powerful tools.
References
Carmichael, J. (1997). 1997 Member Computer Study. Information Services Division, National Association of Home Builders.
NAHB. (1992). 1992 Builder Computerization Study. National Association of Home Builders.
NAHB. (1994). 1994 Builder Computer Study. National Association of Home Builders.
