1

Construction Estimating Using Excel

Construction estimating usually involves the estimating of material, labor,

equipment, overhead and contingencies. For this course, we will only look at

construction materials. Usually we do a quantity take-off from the drawings or

plans. Estimators may also be involved in planning and scheduling. About 62

percent of cost estimators work in the construction industry. Cost estimators held

about 221,000 jobs in 2006. We will look at the following groups of materials.

Concrete

Earthwork

Finishes

Masonry

Metals

Openings

Paving

Thermal & Moisture Protection

Wood

This is NOT all the material groups.

In construction estimating we use many different units, to list a few:

Cubic feet, cf, ft3, cu ft

Cubic yards, cu yd, yd3,

Feet, ft

Square feet, sf, ft2, sq ft

Tons

Pounds, lbs, #

Linear feet, lf

Square, sq

Foot board measure, fbm

Gallons, gal

This is NOT all the units used in construction estimating.

Many estimators use the Master Format by the Construction Specifications Institute.

MasterFormat™ is the specifications-writing standard for most commercial building

design and construction projects in North America. It lists titles and section

numbers for organizing data about construction requirements, products, and activities. By standardizing such information, MasterFormat facilitates

communication among architects, specifiers, contractors and suppliers, which helps

them meet building owners’ requirements, timelines and budgets.

2

GENERAL REQUIREMENTS SUBGROUP Division 01 General Requirements

FACILITY CONSTRUCTION SUBGROUP Division 02 Existing Conditions

Division 03 Concrete

Division 04 Masonry Division 05 Metals

Division 06 Wood, Plastics, and

Composites

Division 07 Thermal and Moisture Protection

Division 08 Openings

Division 09 Finishes Division 10 Specialties

Division 11 Equipment

Division 12 Furnishings Division 13 Special Construction

Division 14 Conveying Equipment

Division 15 Reserved Division 16 Reserved

Division 17 Reserved

Division 18 Reserved Division 19 Reserved

FACILITY SERVICES SUBGROUP

Division 20 Reserved Division 21 Fire Suppression

Division 22 Plumbing

Division 23 Heating, Ventilating, and Air Conditioning

Division 24 Reserved

Division 25 Integrated Automation Division 26 Electrical

Division 27 Communications

Division 28 Electronic Safety and Security

Division 29 Reserved

3

SITE AND INFRASTRUCTURE SUBGROUP

Division 30 Reserved Division 31 Earthwork

Division 32 Exterior Improvements

Division 33 Utilities Division 34 Transportation

Division 35 Waterway and Marine

Construction Division 36 Reserved

Division 37 Reserved

Division 38 Reserved

Division 39 Reserved

PROCESS EQUIPMENT SUBGROUP

Division 40 Process Integration Division 41 Material Processing and

Handling Equipment

Division 42 Process Heating, Cooling, and Drying

Equipment

Division 43 Process Gas and Liquid Handling, Purification,

and Storage Equipment

Division 44 Pollution Control Equipment

Division 45 Industry-Specific

Manufacturing

Equipment Division 46 Reserved

Division 47 Reserved

Division 48 Electrical Power Generation

Division 49 Reserved

4

Earthwork13a

Let’s get started estimating. We have to excavate a ditch for the footing of a

building. The ditch is 4’ wide at the bottom and 8’ wide at the top. That means the

average width is 6 feet. The side slope is 1:1 and the ditch is 2’ deep. The volume of the ditch would be the cross-sectional area times the linear feet of ditch. If the

area is in square feet and the length is in linear feet, then the volume will be in

cubic feet. To convert cubic feet to cubic yards, you divide by 27.

The values in orange are input and the values in yellow are calculated. A cell

address is defined by it column first and then the row number, like C7. In cell G12, the equation looks like this, =D12*F12. All equations must start with = or +. The *,

is the arithmetic operator for multiplication. We are multiplying the value in cell

D12 by value in cell F12. The equation in G15 is =SUM(G12:G14). The SUM is a built-in function. G12:G14, represents a range of cells from G12 to G14, including

G13. We use a colon for adjacent cells and a comma for none adjacent cells in a

range. The equation in J29 is =G29/27. This is where we convert cubic feet to cubic yards. The /, is the arithmetic operator for division. Arithmetic operators have an

order of precedence:

1. Parentheses, ()

2. Exponentiation, ^

3. Multiplication and division, *, /

4. Addition and subtraction, +, -

5

Excel will do parentheses first and addition and subtraction last. If operators have

the same order of precedence, then it does them left to right.

Earthwork14a

In our next worksheet, we are to excavate 6 inches of top soil from the building,

sidewalks and parking lots. After the construction is completed for the building,

sidewalks and parking lots, we will backfill with the top soil that we removed. Some will be left over so it will have to be removed from the project.

In cell H11, we calculate the area by =D11*E11. Since the length and width are in feet, the area will be in square feet. In cell J11, we calculate the volume by

=H11*0.5/27. We multiplied by 0.5 feet, (6 inches), and divided by 27 to get it into

cubic yards. Rows 18 and 28 are calculated by using the SUM function, like =SUM(H11:H16). In cell J32, it shows there will be 178 cubic yards that will have to

be removed from the project. This will add to the cost of the project because of the

labor, equipment and haul distance to remove the soil from the project.

Note: The lines were created with the border command. It is easy to create the

form you need in Excel. You just select the cells and apply the type of border from a list of borders. The colored cells were created with the fill color command.

6

Paving15a

In this worksheet, we are to calculate the tons of asphalt and base material. First

we have to calculate the area in square feet. The asphalt is 5 inches thick and is

32.5 tons / 1000 square feet. The base material is 8 inches thick and is 36.8 tons / 1000 square feet.

The area in cell H11 is calculated by multiplying the length times the width, like

=D11*E11. The sum in cell H14 is calculated by =SUM(H11:H13), the SUM

function. The value in I17 is calculated by dividing the sum by 1000, like

=H14/1000. To calculate the tons of asphalt, we multiply that by 32.5, like

=I17*32.5. To calculate the sub grade material, we multiply by 36.8, like

=I19*36.8. If we knew the cost of a ton of asphalt or sub grade material, then we

could calculate the cost of this material. If we had production rates for placing

asphalt and sub grade material we could come up with labor and equipment costs.

Note: In construction estimating you have lots of data. That is another reason to

use Excel. If you find a mistake, it is easy to change the data. It will automatically

recalculate all values for you. It would be much harder to do with pencil and a

paper form. There are fewer calculation mistakes in Excel because you don’t have

to do the calculations on a calculator and then write it down on a paper form. The

number precision in Excel is 15 digits.

7

Concrete21a

This spreadsheet has two pages. It covers the amount of concrete in cubic yards,

the reinforcing steel used in the footing in pounds, wire mesh used in the slab and

sidewalks in rolls, forms, vapor barriers and expansion joint filler.

In cell H10, I have used a different function, =PRODUCT(D10:G10). This could also

have been calculated by =D10*E10*F10*G10. The equation in cell J27 is

=H27*1.043. A #5 bar weighs 1.043 pounds per linear foot. So 1290 linear feet

times 1.043 pounds per linear foot is 1345 pounds. The equation in J30, =H30/750,

calculates the rolls of wire mess. We have 2964 square feet of wire mess at 750

square feet per roll then we will need 4 rolls.

Comment:

Why use Excel

Ease of use

Easy to adapt to your companies needs

Easily performs mundane calculations

8

Concrete21a sheet 2

This sheet covers the formwork for the footings and sidewalks, vapor barrier and

expansion joint filler.

Now that we know the square feet of form work, we could calculate the labor cost

from the production rate. The vapor barrier comes in 2000 square feet per roll. So

in cell, J35, the equation is =F35/2000. Now we can’t buy 1.5 rolls so we will have

to order 2 rolls. Numbers are great but sometimes they don’t tell the whole story.

Estimating is an art and a science. Everybody may not get exactly the same answer

but they should all be close. The expansion joint is in linear feet. If the expansion

joint material came in four foot long pieces, then 30/4 would yield 7.5 pieces. Now

we can’t buy a half of a piece so we would need 8 pieces.

Note: We have used two functions, SUM and PRODUCT. There are about 341

worksheet functions in Excel.

9

Masonry9a

This spreadsheet covers the square feet of brick veneer from which we get the

number of brick required. We also need mortar and wall ties.

Again in this spreadsheet I used the PRODUCT function to calculate the square feet

of wall, =PRODUCT(D10:F10). Now for running bond, there are 675 bricks / 100

square feet so in cell I27 we divide by 100 and multiply by 675, =J25/100*675.

Notice we added 5% waste for brick but 40 % waste for mortar, =I27*0.05 and

=I31*0.4. There is one wall tie for each 2.66 square feet so =J25/2.66666 will

calculate how many wall ties.

Note: In the equation =J25/100*675, since division and multiplication have the

same order of precedence, it will perform division first and multiplication next. It

performs the operations left to right when they have the same order of precedence.

10

Masonry10a sheet2

This sheet covers Concrete block, mortar and reinforcement.

In column G we see that some of the numbers are negative. Column G shows the

square feet of wall but we have some windows and doors in the wall so they have

to be subtracted. It takes 112.5 blocks per 100 square feet of wall, so

=J21/100*112.5 and 5% waste, =G23*0.05. It takes 2.3 cubic feet of mortar for

100 square feet of wall, =J21/100*2.3 and 30% waste, =G27*0.3. The horizontal

reinforcing is 0.5 linear feet / square foot on every 3rd course. We have 5% waste

and 5% for laps. The reinforcement comes in 20 foot lengths. So at 1300 linear

feet, we would need 65 pieces. The amount of waste comes from company

experience. As you accumulate data from many projects, you can better estimate

how much to add for waste.

11

Metals6a

This spreadsheet calculates the number of squares of metal decking. A square is

100 square feet. Again we add 5% waste.

Metals7a

This spreadsheet shows a take-off of structural steel and bar joist. The unit is in

pounds. It could be in tons as well.

Cell L10 looks like, =D10*G10*H10.

12

Wood2a

This spreadsheet shows the quantity of lumber need in foot board measure, (fbm).

Foot board measure is the length in feet times the width in inches times the

thickness in inches divided by 12 but in this case we need to include the quantity,

=PRODUCT(D10:G10)/12.

Notice, it shows that lumber comes in 8’, 12’, 16’ and 18’ lengths. It comes in other

lengths. You can’t always get the lengths you want so you might have to adjust the

data in this worksheet.

13

Wood11a

This spreadsheet shows the quantities for sills, girders, floor joist, joist headers and

plywood.

Again we calculate the foot board measure,(fmb). Plywood usually comes in 4’ by 8’

sheets so we calculate the number of sheets, =F29/(4*8). We added 5% waste for

the plywood. Notice we have 61.5 linear feet of floor joist at 16” on center. That

means we would have about 46 spaces between the floor joists, =D19/(16/12). The

12 in the equation is to convert 16” to feet. Now we need one to start with and an

extra one so we need 48 joists.

Note: In the equation, =F29/(4*8), Excel will perform the multiplication of 4 by 8

first because of the parentheses and then it will divide last.

14

Thermal11a

This spreadsheet covers built-up roofing material.

The roofing material is in squares which are 100 square feet. The slag is in pounds

at the rate of 100 pounds per square. The flashing is in linear feet. Again I used the

PRODUCT and SUM functions.

Openings1a

This shows a take-off of the glass and aluminum tubing. It shows the doors and

door frames. You need to take off the type and quantity for the doors.

15

Finishes9a

This shows the sheet rock required for the walls and ceilings of a house. You

determine the linear feet of wall that is 8’ tall. You calculate the square feet of wall

and divide by (4’ x 8’) to find the number of sheets. The walls use ½” sheet rock

while the ceiling is 5/8”.

16

Thermal10a

This shows how many linear feet of cant strip is needed on the edges of a roof.

So there you have it. You start out with a blank form like (Generic Estimating

Sheet) or create your own form.

17

You add your data, equations and that is all there is to it. Two other blank forms

are: Reinforcing Steel Work Sheet and Structural Steel Work Sheet.

So in Excel we can create many different forms with only the border command.

Once we get the form completed we can add headings to the columns. Once that is

done, we can add our data and begin calculating. We only used two commands;

SUM and PRODUCT. I added the fill color to differentiate between the data and

calculations. All the files are included in the installation file, ConstrEst.exe .

18

1. Create a new folder, like CEUE on your hard drive.

2. Run ConstrEst.exe

3. Click Yes and you will see:

4. Click Yes and browse to the folder, CEUE

19

5. Click OK to install the files in that folder.

6. Click OK to end. You should have 31 files in that folder. There should be 30

Excel files and one text file.

I always say “I don’t know how engineers ever got along without Excel.”