Suppose you could carry an estimating tool with you that allowed you to do quick, preliminary cost estimates in the field. You would be able to easily weed out the lookers from the spenders. You could get ballpark figures for changes arising during construction, and you could quickly compare costs for different building methods. By creating an assembly-based estimating workbook in Microsoft Excel, you can have this convenient tool at your fingertips. (There is an example estimating workbook available for download here.)

Microsoft Excel has long been relied upon by people in all levels of construction for doing everything from the simplest of estimates to estimating complete, complex projects. Many people are familiar with how to use it for estimating and are comfortable with the results they get. Excel won’t do accurate estimates all on its own; for that you need an estimator who is experienced in construction and who understands that it is a process involving science and art. But once that is present, a tool like Excel takes the drudgery out of the endless calculations that have to be done.

Estimating for construction that will be built in-place is a linear process. Certain parts have to be built before others, so there is a certain order that has to be followed and that makes using assemblies a great way to go. Most people in construction know an assembly as a group of materials and processes combined to form a completed portion of a project or building.

By building an Excel estimating workbook based upon assemblies, you accomplish three things.

• You simplify the process of doing preliminary cost estimates of particular portions of projects.
• You create an estimating tool where you only need to enter some project dimensions to get a quick preliminary estimate.
• Because the assemblies rely upon the materials and processes that your company typically uses for them, you don’t have to weed through long selection lists while doing the estimate.

A building foundation represents one example of an assembly as there is a certain set of tasks that have to be accomplished to build a foundation. This article will use the example of a foundation to demonstrate the use of an assembly-based cost estimating workbook in Microsoft Excel.

Since there are many kinds of foundations, the idea here is not to create an assembly that would allow you to estimate any kind of foundation, but rather to create an assembly that would allow you to estimate the typical kind of foundation you build. The type you build is dependent upon the climate and soil conditions where you build. In cold climates where frost penetrates the ground during the winter, you will build a foundation having footers that go to a certain depth, or that have footers that are somehow insulated from the heaving effects of frost. In warm climates, where frost is not a problem you may simply pour a monolithic slab with perimeter concrete dropping into a shallow trench.

When you create your assembly-based Excel estimating workbook, you focus on the kinds of assemblies you most often build. Anything more complex is left to whatever construction cost estimating tool you use for your most accurate work.

To begin, open a blank Excel workbook, drop down a few cells from the top, and in column A begin listing, in order, the steps to building a particular assembly. Even though this is going to be for preliminary estimates, you still need to be accurate in your list. (If, for example, you are starting with the foundation and you skip the earthwork, then you are going to be way under what even the true costs will be for a preliminary estimate.) Also keep in mind that you will typically build your assembly-based estimating workbook over a period of time by creating individual assembly estimating sheets as needed, and then combining them in the estimating workbook. If you build the same assemblies over and over, then you might just find this will grow into your primary estimating tool.

Once all your main steps are listed, move into column B and begin listing subordinate steps. In the example below, you can see that “Layout Perimeter and Corners” is subsequently refined to “Set Batterboards.” Since this estimating workbook is designed to grow to fit your needs setting it up this way will make it easier to add other tasks later that are associated with the perimeter and corner work. For example, on some jobs you may need to locate utilities, or prepare a building pad before you can accomplish this main step.

In the first example above, you may notice “Install Footer Forms” is not further defined, since, in most cases, there would be no further sub-task necessary.

Now, beginning in column C of the estimating workbook, start listing everything you can think of that you would need to complete each task. The example includes form stakes and 2x4s for making the batterboards, but maybe you use pine stakes and 2x6s. The idea here is to list the items you typically use when you are doing these tasks. At this point it’s a good idea to keep things in some logical order. You might list them in order of use, or you might list them so that like items are grouped together. This should be based upon your personal preferences and on your thought processes as you estimate.

Now, let’s set up the cells for the Project Stats. These are the dimensions and other information about the project that you need to enter in order for the spreadsheet to do the calculations. Remember, when this is all set up you will just enter the Project Stats, and your spreadsheet will calculate the quantities and total costs. Insert cells as necessary to make room for the Project Stats near the top of your page.

The example shows some typical stats that will let you run a variety of calculations. At a minimum you need the length of the structure/project and width. For foundations you also need the width and depth of the footers which in this case I chose to list to the right of “Excavate Footings,” instead of under Project Stats so that it would remain with the sub-part of the structure it belongs to. Also note that all the Project Stats, regardless of where they appear in the estimating workbook, are highlighted in a distinct color. This helps you keep track of the cells that formulas are using for the calculations.

You should also set up some cells near the Project Stats where you enter costs related to labor, profit and markup. In the example below, the various Human Resources classifications are listed, along with the hourly rate for each.

This way, if you have your Cost column pull labor costs from these cells you have a better chance of staying up-to-date on labor costs. As pay rates change, you just need to change them one time up top, instead of having to go through the sheet, hunting them down one by one. 

Once you have colored and labeled the Project Stats cells in your estimating workbook, it’s time to enter the Units and Costs (for each unit). People seem to gather their costs and units from a variety of sources. Some people put together lists and have their building materials supplier fill in the blanks. Others who have been in the business for a while may maintain their own costbook. Still others get their unit and costs from companies that supply costbooks, such as Reed Cost Data Books, or Craftsman Costbooks. However you arrive at your costs for the various items, just be as accurate as you can, keeping in mind that sometimes you might use a costbook amount for one item but a local amount for another.

Next, in the top cell of the Total column of your estimating workbook, set up the formula that will generate your totals. It will likely look like this: =D19*F19. Then, copy and paste the formula to the rest of the cells in the Total column.

Finally, you’ll need to create the formulas for the QTY column. These formulas will use the numbers in your Project Stats cells to arrive at the quantities of items. Let’s do a few so you see the process.

Starting with “3’ Form Stakes,” here are the formulas and the reasoning behind them.

• =D15*4 - D15 specifies there are four corners and we need two stakes for each batterboard, and there are two batterboards at each corner, so we need four stakes per corner (D15=4) and 4X4=16.
• Backhoe: =Sum(F11,F13)/40 = The backhoe will dig the length of the perimeter and we only have one perimeter in the example (F11), so F13 is just there in the event we might have a bump-out on the structure that we want to treat separately, or another structure entirely on the same project. We know our backhoe and operator will dig 40 feet per hour in reasonable soil. Thus we divide 40 into the total perimeter to arrive at the hours of backhoe use. If you look right below this cell you will find some adjustment cells to account for varying conditions at the site. In one instance you can add a factor to the total for each additional foot of depth the trench is dug. In the other instance you can add cost to the total if the soil is rocky. There are always variables you need to account for; as you use the sheet, you will find yourself adding more and more of these. The costs for these can come directly from costbooks, or you may have records and experience that provides them. You might even include a cost variable to account for travel time.
• Remove Spoils: =(D25*E25)*(F11+F13)/2/27 The trench for the footings is 1.5 feet wide and 2 feet deep, and by multiplying those together (D25*E25) we get two components of the cubic volume of earth that will be removed. The other component, (F11+F13), comes from the length of the trench. Again there is no amount in F13 so only F11 is affecting this calculation. Once those three numbers are multiplied [(D25*E25)*(F11+F13)], we end up with the cubic feet of earth removed from the trench (504 cubic feet). We will backfill using about half of what we removed so that means we need to get rid of half. So 504 divided by 2 means we will have to haul away 252 cubic feet. We want that number expressed as cubic yards since that makes it easier to figure out what kind of truck we need, as most material for construction is hauled based upon cubic yards or tons. So, we divide 27 into 252 cubic feet to arrive at the number of cubic yards.

You will have a number of hours invested in creating these assembly-based estimating workbooks in Microsoft Excel -- but, as you develop them, you will find many of the formulas can be reused in other places with minor modifications. Ultimately the advantages of being able to do quick estimates for a variety of building projects will undoubtedly provide payback for all your effort.