Explore the intricacies of structuring an estimate in Excel and understand the importance of each column and their interconnectedness. Learn how different aspects, like the phase, item number, take-off quantity, labor cost, unit of measure, and more, play a critical role in creating an accurate estimate.
Key Insights
- The structure of an estimate in Excel involves various columns like the phase (Column A), item number (Column B), description (Column C), take-off quantity (Column D), and labor cost (Column F). The item number combined with the phase number makes each item unique, ensuring an accurate estimate.
- Understanding the unit of measure is crucial as it aligns with the cost categories. Weeks, feet, and each, all have different units of measures which must align with their respective cost categories. This is critical in calculating the correct unit cost and in avoiding misinterpretation of calculations.
- The Excel sheet also includes columns for labor amount, material cost, equipment cost, and subcontractor cost. These are calculated using the takeoff quantity and their respective unit costs. Additionally, the total cost unit and total amount columns provide a comprehensive overview of the costs, aiding in the understanding of the estimate.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
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 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 of it so that you know if it's week, feet, or each.
And that's column E. So each one of those items are 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 column F and G are side-by-side.
They're typically grouped together. But primarily, column F has the values that are 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 is actually text and the dollar amount that is shown is actually math or numbers. If they were combined, it would turn itself into text and no longer calculate.
%20(1).png)
Learn Construction Estimating
- Nationally accredited
- Create your own portfolio
- Free student software
- Learn at your convenience
- Authorized Autodesk training center
So it's important that units of measure are actually broken out separately right alongside of 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 a month or a week or a 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. And what that does is it calculates the cost from that row on column D, takeoff quantity, times the labor cost of column F. So the group of labor unit cost and labor amount, that's an entire group. So the same application is applied to the material group and the equipment group and the subgroup as well.
Each one of those groups are utilizing the takeoff quantity shown in column D, times 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 on 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, column O for subcosts, equals the total unit cost in column R. These have not been multiplied yet by column D, which will result, though, 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 column R times the takeoff quantity in column D. And that will equal column T, the total amount. Column U, notes and alternates, or WBS codes.
This is where you infill or populate it with any information pertinent to each item in the estimate to easily identify for either yourself, or for the client, or for 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 takeoff. So here we refer to our quantity takeoff sheet and we reference items 1 through 3 on that spreadsheet, which is identified in the very first column of the quantity takeoff 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 and all the items within that, that's the total amount on row 8. That's the roll-up cost to the Level 1 Master Format 010000. So notice that you basically have a roll-up cost, which is a General Requirement 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.
