Understanding the Structure of Estimates in Excel: A Comprehensive Overview

Here we have an overview of the structure of the estimate in Excel. Column A is the phase, which is the master format number. B is the item number.

The item numbers are not required, but that line item is reflective of the description in column C. So where we actually have an item number, it's the item number plus the phase number that makes it unique from all other items with a different description throughout the entire estimate. Column D is the take-off quantity for each of the items identified. Notice that within the take-off column, there's a unit of measure alongside it so that you know if it's weeks, feet, or each.

And that's column E. So each one of those items is quantified with different units of measure, and therefore the cost categories must also have the same unit of measure. Column F is the labor cost. Notice that columns F and G are side by side.

They're typically grouped together. But primarily, column F has the values used by Excel to calculate different dollar amounts. The unit of measure is there for us to understand.

It represents the unit of measure for that unit cost. The reason why they're in separate cells is because weeks are actually text and the dollar amount that is shown is actually math or numbers. If they were combined, it would be converted into text and no longer calculate.

So it's important that units of measure are actually broken out separately right alongside the actual unit cost. If you don't have the unit of measure shown, then you won't understand what the unit cost represents. It'll still calculate the way it's required to calculate, but when you look at it, then you're going to say, well, what is it calculating? Is it calculating by the month, week, or foot? That's why we have to always identify any unit cost with a unit of measure, even though they cannot reside within the same cell.

---

Column H is the labor amount, which calculates the cost from that row in column D, take-off quantity, times the labor cost in column F. So the group of labor unit cost and labor amount—that's an entire group. The same application is applied to the material group, the equipment group, and the subcontractor group as well.

Each one of those groups is utilizing the take-off quantity shown in column D, multiplied by its own unit cost to give you its own amount for each cost category. Cost categories, again, are labor, material, equipment, and subcontractor. So as busy as this spreadsheet looks, if you break it down or boil it down, you'll see that it's a quantity times a unit cost equals the amount.

It's only replicated several times for the cost categories of labor, material, and equipment, as well as the subcontractor. Let's take a look at column R, which is the total cost unit. The total cost unit is the total of all the unit costs by all cost categories.

And what we're talking about is column F for labor, plus column I for material, column L for equipment, and column O for subcontractor costs, to give the total unit cost in column R. These have not been multiplied yet by column D, which will result in column T, which is the total amount. The total amount is going to be the total of your labor in column H, your material in column K, your equipment in column N, and your subamount in column Q. If you add all of those up, that will equal the total amount in column T. We should also get the same number, the same dollar amount for the total, if you take the total unit cost in column R times the take-off quantity in column D, which equals column T, the total amount. Column U, notes and alternates, or WBS codes.

This is where you fill in or populate it with any information pertinent to each item in the estimate to easily identify it for yourself, the client, or other estimators to view and have a better understanding of it. Also notice that within that column, column U, there is what we see as a 100% check. We will cover 100% check later in this class.

---

The QTL reference in column V is the quantity take-off. So here we refer to our quantity take-off sheet and we reference items 1 through 3 on that spreadsheet, which is identified in the very first column of the quantity take-off spreadsheet. Now let's look at the rows in the spreadsheet.

Starting with row 3, those are the descriptive column names that we just discussed. Let's next take a look at row 4. It's a title for the group of all the items that follow. Rows 5,6, and 7—those are all the items within that group.

And then row number 8 is the total for all the items above it within that group. In other words, everything within General Requirements—row 8 reflects the total amount for those items. That's the roll-up cost to the Level 1 Master Format 01,000. So notice that you basically have a roll-up cost, which is a General Requirements total, or another roll-up cost might be on row 13, Existing Conditions Total.

Each estimate can be reviewed based on the totals or the subtotals themselves, or you can get more granular to each item level.