Construction Estimate Template Built From Takeoff Quantities

A construction estimate template should start from the takeoff, not from a blank price list: each measured row carries its unit, waste and order quantity, and the pricing columns only read from it. This one has 22 columns in three blocks, a worked example, a markup-to-margin table and a matching quote view. There is no file to download, so paste the tables into a blank workbook. The method behind them is in how a pre-bid number is built.

Why a typed quantity is the wrong place to start

Many free templates open with description, quantity, unit cost and total. In a construction estimating spreadsheet like that, the quantity is a blank you type into, so the sheet cannot tell a measured count from a guess. When the plans change, you retype it and hope the price follows.

Put measured quantities first instead. Each price then traces back to something countable, so a second person can check it, and the customer's view is built from the same rows.

Construction estimate template: the columns, line by line

The sheet has 22 columns in three blocks, lettered A to V so the formulas can be copied as written. If you would rather receive a priced estimate than build one, how a priced estimate is assembled shows the steps.

The block 3 formulas for row 5 are short:

• Labor, Q5: =F5*K5*L5, which is net quantity x labor-hours per unit x loaded rate per labor-hour.

• Material, R5: =I5/H5*M5, which is packs x pack price.

• Direct cost, S5: =Q5+R5+N5+O5.

• Price, U5: =ROUND(S5*(1+T5),0).

• Margin, V5: =(U5-S5)/U5.

Four steps build the sheet:

1. Put the title in row 1, the plan issue in B2 and the headings in row 4. The first line sits in row 5, which is where the formulas above point.

2. Type the ID in A and the division in C, then fill the rest of block 1 by lookup, as the next section shows.

3. Type block 2, and the markup in T as 22%, not 22. A bare 22 prices the line at 23 times its cost.

4. Copy the block 3 formulas down. In row 7, sum Q, R, S and U, then enter =(U7-S7)/S7 in T7 and =(U7-S7)/U7 in V7 for the blended markup and margin.

These formulas need only INDEX, MATCH, COUNTIF, ROUND, IF and SUM, which Excel and Google Sheets both have. The lock step below is Excel's.

Column J holds the row type, and the type decides which inputs a row uses. Make J a drop-down list and type SELF,SUB,ALLOW in the Source box. The type then always comes from a fixed list, and you can filter every SUB line when you chase quotes.

Blank input cells count as zero, so one set of formulas serves every type, except pack: it divides, so use 1 for counted items. Price the install of an ALLOW item on its own SELF row.

A construction estimate example: quantities, waste and order quantity

The example below is invented: 480 SF of porcelain floor tile set by your own crew, and six hollow metal doors from a subcontractor. Every input is a placeholder. All dollar figures in the example are illustrative: round numbers chosen to make the arithmetic easy to follow, not market prices, wage rates or quotes. Your own numbers will differ, so replace each one with yours. Block 1 looks like this.

H is the units in one pack, typed as a plain number: 15 for a 15 SF carton, 1 for a counted item. Format C as text so 09 keeps its zero.

The takeoff already did the rounding. 480 SF plus 10% waste is 528 SF, or 35.2 cartons, so 36 cartons (540 SF) are ordered, which is 12.5% over net because whole cartons add waste. This sheet does not repeat the sum, since two places doing one sum is how they disagree. It pulls the result by ID: =INDEX(Takeoff!J:J,MATCH(A5,Takeoff!A:A,0)) returns the order quantity.

The takeoff sheet that feeds this one keeps order quantity in column J, so change the letters if yours differ. Fill B, D, E, F, G and H the same way, changing only the source column: sheet B, item C, unit E, net quantity F, waste G, pack I. Type the division in C yourself, because the takeoff keeps it inside the item text.

To lock block 1, clear the Locked box on the cells you type in: the ID in A, the division in C, block 2 and the markup in T. Then turn on worksheet protection. Microsoft says it is not meant as a security feature, so it guards against slips, not people. A quantity you must change here is one the takeoff got wrong, so fix it there.

Pricing columns: labor, material, equipment, subcontract

Labor runs on net quantity, because the crew sets 480 SF, not the 540 SF on the order. Material runs on order quantity, because the supplier ships whole cartons. Mixing the two is an easy slip. Block 2 holds what you type, shown here with illustrative dollar figures.

A loaded rate is wage plus burden. Use the same basis in K and L: labor-hours with an hourly rate per worker, or crew-hours with a rate for the whole crew. Equipment is 3 days at $40.00 a day. The door line has only a sub quote, but it still carries your quantity of 6, so dividing the quote by 6 gives $880.00 a door to compare with other quotes. Block 3 does the arithmetic, again in illustrative dollars.

Labor is 480 x 0.10 x $50.00 = $2,400.00, and material is 540 / 15 x $40.00 = $1,440.00. With $120.00 of equipment, the tile line costs $3,960.00. Labor is 60.6% of it, so check the hours first.

Column U rounds each price to whole dollars. The tile line is $3,960.00 x 1.22 = $4,831.20, shown as $4,831. The door line is $5,280.00 x 1.08 = $5,702.40, shown as $5,702.

Column P is the easiest one to skip. Write where each input came from and when it expires: a supplier quote with its date, a past job number, or the word assumption. The GAO cost estimating guide, written for federal programs, says a well-documented estimate is one a reviewer can repeat, update and trace back to its sources. A source column leaves the same trail on a bid sheet.

A framing takeoff in stock lengths is the clearest case of a pack. Residential jobs add options and allowances, covered in how a residential estimate is built.

If the template is ready but the quantities are not, measuring is the step to hand to an outside desk, since every price column depends on it.

Markup and margin without mixing them up

Using the same illustrative figures, a 22% markup on the tile line adds $871 to a cost of $3,960.00, because $4,831 - $3,960.00 = $871. That $871 is 18.0% of the $4,831 price, so the line earns an 18.0% margin, not 22%. Markup is measured on cost and margin on price. If the amount also has to pay overhead, read column V as gross margin: what is left of the price before overhead, not what you keep.

The first table converts five markups on an illustrative cost of $100.00.

The second starts from the margin you want and gives the markup to type.

To earn a 22% margin on the tile line, the price is $3,960.00 / (1 - 0.22) = $5,076.92. That is a 28.2% markup.

Mixed markups blend, which is why column V shows margin. Say the tile line carries 22% and the door line 8%. Together they add $871 + $422 = $1,293 on $9,240.00 of cost, a blended markup of 14.0% and a blended margin of 12.3%, as the total row shows. The door line alone earns a 7.4% margin, and it pulls the whole quote down.

A flat 22% on the whole direct cost would give $9,240.00 x 1.22 = $11,272.80, which is $739.80 above the per-line total of $10,533, because it prices the subcontract line at the self-performed rate. That can be the right call in your market. Make it a choice you can see in column T, not a side effect of one typed cell.

Round the price column, not the total. The unrounded lines add to $4,831.20 + $5,702.40 = $10,533.60, which rounds to $10,534, but the rounded lines add to $4,831 + $5,702 = $10,533. The quote lists whole dollars, so the estimate must add the same way.

The quote view: what the owner sees from the same lines

A construction quote template starts as the estimate with the cost columns taken away: the owner sees the line, quantity, unit and price, while your hours, rates and markup stay on the estimate tab. Build the quote tab by looking up each line by ID, so its price is the price in column U and not a retyped copy. Send the quote tab as a PDF, not the workbook, because hidden columns can be unhidden. For the example, the quote tab reads as follows, in the same illustrative dollars.

Print two lines under it. These are illustrative:

• Valid to: Nov 9.

• Excludes: subfloor repair and moisture testing (line 1); finish hardware and field painting (line 2).

Contracts use the three words loosely, and estimate, quote and proposal compared sets out who relies on each. Bid proposal sections and wording covers the terms a full proposal carries. On public work, the owner's bid form usually sets the format.

Validity, exclusions and scope changes

A quote should not stay open longer than the shortest quote under it, unless you choose to carry that price risk. The tile quote is dated Oct 12 and holds 30 days, so it expires Nov 11. A door quote dated Oct 19 that holds 21 days expires Nov 9. So the quote view should read valid to Nov 9, or earlier for a buffer. On a public bid, the bid documents can require a longer hold than a supplier gives. That gap is risk you price or get the supplier to extend.

Keep a scope tab with one row per exclusion, tied to a line ID, so the printed exclusions and the estimate cannot disagree.

After award, the contract controls how a change is priced. On AIA-based contracts, AIA Contract Documents describes a change order as an agreement among the owner, contractor and architect that records changes to the work, the contract sum or the contract time, and says the contract sets the pricing method. A change that also touches a subcontract is documented separately between the contractor and the subcontractor. Your own contract and state law control.

Unless the contract fixes a unit price, do not price a change at a line's average. In the illustrative example, the tile line works out to $10.06 per SF of price ($4,831 / 480). Say a change order after award adds 40 SF of the same tile. At the same 22% markup, if the contract allows it, repricing the line takes the price from $4,831 to $5,222, as the table in the next section shows. That adds $391, or about $9.78 per SF on the rounded prices. This is below the $10.06 line figure, mostly because the $120.00 of equipment does not grow with the area.

Keeping the template honest between bid revisions

Say addendum 1 raises the tile area from 480 SF to 520 SF. The takeoff is reissued and the lookup carries the new order quantity into block 1. The table uses the same illustrative dollars.

The new direct cost is 520 x 0.10 x $50.00 = $2,600.00 of labor, plus 39 x $40.00 = $1,560.00 of material, plus the same $120.00 of equipment, or $4,280.00. At 22% the price is $4,280.00 x 1.22 = $5,221.60, shown as $5,222. The door line stays at $5,702, so the quote total is $5,222 + $5,702 = $10,924.

Three check cells catch what a total cannot. Name the tabs Takeoff and Estimate:

• Priced once, beside each ID on the takeoff tab: =COUNTIF(Estimate!A:A,A5) must read 1. A 0 is an unpriced line and a 2 is a line priced twice.

• Current, once on the estimate tab: =IF(Takeoff!B2=B2,"current","STALE"). Type the plan issue you priced against into B2 on the estimate tab by hand, and keep the current issue in B2 on the takeoff tab. Never link the two cells, or the flag cannot fire.

• Quote ties, on the quote tab: the quote total minus the estimate total must be 0.

On addendum day the fix is made once, in the takeoff, and every price follows it.

Frequently asked questions