Updated Construction Estimation Spreadsheet in CSI MasterFormat

This spreadsheet is a revision of the previous worksheet for residential and light commercial construction take-offs and project estimation used by my previous companies and which many readers of this article have and may be using today, with my permission. Before I get into the changes in this document over the previous, let me give you a brief outline of the evolution of it.

In 1975, I went into business for myself building and remodeling residential buildings. Soon, I discovered a need for a uniform accounting system to keep accounting and estimation on the same plane; to track the accuracy of the estimation and to keep projects in line and on target. This was before the days of the desktop computer and most everything was done by hand. So, I styled my chart of accounts after the 5 digit Uniform Construction Index (UCI) developed by the Construction Specifications Institute (CSI) and all estimating was accomplished by pen, paper and calculator.

In 1983, I purchased my first computer. This was a major decision because a 64K Apple IIe was $5000 – and, in 1983 $5000 was real money. I purchased one with two 5.25 floppy disk drives; this streamlined the process with the program software in the Drive A and the data in Drive B. The alternative was continually swapping the program and the data disks in Drive A. One year later the 128K chip was released. I installed it and had a really rocking machine. Now, I could produce a six page spreadsheet at the click of the Enter Key (the mouse was not yet in common usage), using the VisiCalc platform.

In 1985, I upgraded to my first IBM® clone machine, with 1meg of ram and a 40Meg hard drive and began to produce the company accounting system in dBase. I enjoyed programming in dBase and our company ran on dBase until the mid-90s when I encountered a banker who demanded we switch to QuickBooks® (he probably owned stock). Meanwhile, our estimation was continued on the ever-expanding and updated spreadsheet system, using the Lotus 1-2-3 platform.

At this time, the MasterFormat® consisted of 16 divisions. Our spreadsheet reflected this on the summary page, while loosely hanging on to the old UCI sections on the take-offs pages. With these improvements, our estimation program was slowly becoming obsolete in respect to accounting and divisions, but it was still effective as a thorough and accurate estimation system for small contractors.

Eventually, CSI dropped the UCI label, replacing it with MasterFormat®. MasterFormat® was first published jointly by CSI and Construction Specifications Canada (CSC) in 1978. Revised versions of MasterFormat® with 16 divisions were published jointly in 1983, 1988 and 1995. In 2004, the divisions were expanded from 16 to 50, with updated versions in 2010, 2011 and 2012. Effectively, for residential and light commercial construction, there are less than 30 divisions to be concerned with. Many of the new divisions have to do with process, mining and other construction activities. [Thanks to Bob Johnson of J and J consultants for his assistance with the history of MasterFormat®.]

This major change made our old estimation form very obsolete, although it was still an effective estimation tool. The problem gnawed at me for some time but I did not want to take the time to fix it because it would mean several days of re-writing, testing, mind-numbness and eye-glazing. However, I finally went into this project to get the monkey off my back – and I am proud to say, I did it without any help from Mountain Dew®!

Some may ask, why a spreadsheet? Why not use one of the professional database platform systems on the market? My answer is simple: There are arguments to be made in favor of the benefits of using standardized programs that backup to a main server for large companies that exercise continual review of projects, project estimation and accounting activities of their project managers. Some small and medium-sized contractors prefer to use the estimation tools provided by QuickBooks® even though they are limited in applications and scope. However, for most small contractors, and especially one-man shops, nothing compares to a spreadsheet where units, items and events can be created or updated on the fly; where you don’t have to fight the database to obtain the correct number or data plugin and; where the estimator is able to add more notes and/or lines with each unit than a database system allows. For large projects, it may be convenient to have the computer compare the estimation and accounting; for homes and light commercial, it takes little additional time to do this visually or by hand – and this time was more than saved during the estimation process.

So, for all you spreadsheet fanatics, here is the new, improved and updated construction estimation spreadsheet for residential and light commercial. The workbook is divided into four sheets, or sections. The first sheet is the Cost Summary. This page presents the costs according to 26 MasterFormat® divisions. The second sheet is the Residential Draw Sheet which converts all the take-off data to conventional lending draw divisions (I would like to see QuickBooks® do this!). Turn this in to your banker instead of burning the midnight oil converting data from bidding reports. The third sheet is the Data Take-offs. This section is over five hundred lines of construction items in MasterFormat®. Finally, I have included the One Page Schedule – this is new to the updated spreadsheet.

Before you use the sheets, please observe a few simple rules. Usage will be explained here, section-by-section. In the header of the document, feel free to change the Company Name and contact phone to identify you and/or your company. In the footer, please do not remove the copyright information. You are free to use this form with no royalties or fees but it is unlawful to remove the credit to the creator of this document.

COST SUMMARY SECTION
1. Do not fill in any cost numbers – they are in red. This will be automatically populated as you enter data in the Data Take-off section.
2. Enter data only for Project Name, Client, Architect, Location, Job No, SF fin and SF unfin in the first four rows. (SF fin / unfin means Square Footage finished and unfinished).
3. Enter the sales tax rate and contingency, overhead and profit on rows 33,34 and 45, column E.
4. Once again, do not modify any red numbers. There is one exception to this rule: the spreadsheet is set up for Idaho construction (custom or spec) and Washington speculative construction. If you are building for Washington custom construction, you will want to remove this row (33) and/or place it at the bottom of the page, after the total quote.

RESIDENTIAL DRAW SHEET
1. This sheet is fully automated. Even the project name is self populated from the Cost Summary Section.
2. When printing the document, you will want to hide column E if the project is speculative; hide column D if the project is custom.

DATA TAKE OFFS
1. Do not enter any data on a blank row.
2. Do not enter any data in the extension column.
3. Do not enter any data on the capitalized section title rows.
4. This worksheet is set up for unit costing. The headings are as follows: Class is the MasterFormat® six-digit number (three sets of doubles); all numbers ending with 99 were added by me. If you decide to add work items, I would recommend you check MasterFormat® first (see #5). If they do not have an appropriate line item, add one yourself and end the number with 99. CSI has their own protocol for this but it gets more involved in digits and decimals and is unnecessary for a contractor who is not building skyscrapers or developing chemical treatment plants. Class Title is the MasterFormat® class title; some have been moderately altered to be more descriptive. Description – this is for your notes. List vendors and competing prices; you might want to identify the limit of the scope for this line, whether you are combining lines or other such information.
5. You can download the latest MasterFormat® numbers and titles pdf file at [click this link].
6. This is a raw form and has not been expanded for detail. On many division sections, or work items, you may want to add additional lines for notes or details, especially if you are separating labor and materials for items do in-house. Add all the lines you want, but remember to drag the calculations down in the extension column and confirm, by comparing totals on the three calculations pages, you have not altered the formulas.
7. Note: the MasterFormat® has two divisions for estimating cabinets: 06 00 00 Wood, Plastics, and Composites (section 06 40 00 Architectural Woodwork) and 12 00 00 Furnishings (section 12 30 00 Casework). I chose to remove it from division 06 and retain it in division 12 because it enhances clarity to separate cabinets from finish carpentry and trim, in that cabinets are more identifiable as furnishings than trim. It also makes more sense when analyzing the Cost Summary section.

ONE PAGE SCHEDULE
1. The project name and job number are automatically populated from the Cost Summary.
2. Feel free to swap your company logo and information but do not remove the copyright information.
3. The schedule runs as a bar chart (by week), similar to a Gantt chart. This is not set up as a critical path, as is, however, you could establish critical paths in the bar (Gantt) chart by use of bar colors.
4. For more information on the process of one-page scheduling, get the book, “The One-Page Project Manager” by Clark Campbell published 2007, John Wiley and Sons.

Hope you find this helpful. If you have any questions, feel free to contact me via the contact page on this website, or at dean@cougargulch.com. Please note: You agree to use this spreadsheet at your own risk. I will not be responsible for any damage (direct or collateral) to you or your project from the use or misuse of this spreadsheet, or for any errors, real or perceived, within this spreadsheet. I have tested and found no errors – this does not mean errors do not exist. If you find an error, please contact me and report it. Thank you.

About the author: Dean Isaacson

Over thirty-five years a small business owner and general contractor. Previously held Certified Safety Administrator status from the National Association of Safety Professionals. Over one dozen expert witness cases before moving to Idaho from Seattle area. Currently marketing for Extreme Industrial Coatings, VinylFire and BestLeather. Will help you with project management and marketing. We do cold calls and trade shows.

This entry was posted in how-to, project management and tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , . Bookmark the permalink. Both comments and trackbacks are currently closed.
  • Books by Cougar Gulch

  • Categories


  •  

  • Friends and Sponsors

     

  •  

  • Meta