Estimating Spreadsheet Best Practices – Optimal Use of Multiple Sheets and Tabs
Posted: July 7, 2016 | Estimating
What can we discern as possible best practices in a construction estimating spreadsheet? Let’s look at the use of multiple sheet “tabs” in an Excel estimate for organizational purposes.
Three Sheets Every Estimate Should Contain
In a spreadsheet program like Excel, a single spreadsheet file may contain multiple sheets, and each sheet has a tab to facilitate accessing that sheet. The tabs can be labeled to identify the contents of the sheets, and can even be color coded, hidden, and/or protected. For the purpose of a construction estimate, a common practice is to use two or more tabs in a single spreadsheet file to organize different parts of the estimate. In our sample, from the British Columbia Ministry of Transportation and Infrastructure, there are three tabs:
1. Project Worksheet
2. Assumptions Worksheet
3. Cost Element Worksheet
Let’s take a look at why these three sections represent the three primary tabs every estimate spreadsheet should contain.
Project Summary Tab
Ever estimate should have a separate tab that serves as a project overview and estimate summary. Information on the summary worksheet should include a project description, location, scope summary, project number, RFP date, RFP number, and RFP amendment number (if applicable). It should also include a bid date/time, Owner information, and general contractor information (if this is a subcontractor estimate). It should list the estimate status, person in charge of the estimate and, of course, the total estimate amount. Depending upon whether the estimate is being prepared by an Owner, architect, contractor, construction manager, or subcontractor, there will be differing degrees and types of information needed on the summary.
Commentary and Assumptions Tab
The commentary and assumptions worksheet can serve as a checklist of things to prepare, perform, or consider during the course of developing the estimate. Owners will have a different set of considerations (including property acquisition and design costs) than a contractor bidding on the construction portion of a specific project.
Estimate Details Tab
This worksheet is the workhorse of the estimate. It will include takeoff quantities, unit costs of labor and material, support costs, overhead, and profit. In the case of our example, which is set up for an Owner, the line-item costs are input as a lump sum (and color coded in blue). Project management, planning, and design costs are automatically calculated as a percentage of one or more of the direct costs. This worksheet also includes a column to add a contingency amount for each line item.
Working with an Excel template for performing an estimate is a best practice, no matter what the title and content of the various worksheets are. Starting each estimate from scratch is a formula for errors and omissions.
The three tabs mentioned above provide the contractor with a consistent way to organize an estimate, easily find information, and document pertinent information discovered over the course of producing the estimate. In addition, there are a number of other tabs a contractor might consider adding, depending on the nature of the work and the complexity of the estimated projects:
Estimate Calculations: The contractor can maintain calculations in the Estimate Details tab, or may elect to keep all calculations in a separate tab. These calculation items might include various elements of work that may be needed for a single bid line item. It could also include labor productivity calculations, disposable materials and supplies, and support items, such as scaffolding and cranes, etc.
Estimate Details “Sub-tabs”: For contractors preparing their own estimate for various divisions of work, such as HVAC, electrical, and plumbing, keeping separate tabs for each division or trade can aid organization of data, as well as facilitate multiple people working on the same estimate.
Request for Clarifications and Pre-Bid Records: A separate tab is a good place to jot notes about items that need further clarification from the Owner, as well as other data pertinent to preparing an estimate, such as information provided at prebid meetings and RFP amendments. This might also be a place to include photos and other notes from pre-bid site visits.
Bid Submission Checklist: While the Summary or Assumptions tab may have this information on small projects, it could be useful to have a checklist for submission of a final bid package, including a list of all documents that need to be signed, small business and diversity requirements, bonds, and other information required by the Owner.
Bid Results: It’s always a good idea to record the results of the final bid right in the worksheet. This is particularly useful for unit price contracts, as it gives a range of the cost expectations of competitors.
Contract Performance: If you win the bid, this tab represents the “as-built” estimate for the project. Variances from the bid can be easily calculated to help future accuracy and notes can be added for aid with future estimates.
About the author: Paul Levin is the Founder, Editor, and Publisher of WPL – publisher of Construction Pro Network. He possesses a degree in Civil Engineering from the University of Maryland and a Masters degree in Engineering Administration from George Washington University. Mr. Levin has worked as an arbitrator for the American Arbitration Association and served as a Senior Consultant with Revay & Associates, specializing in scheduling, cost control, dispute resolution, and project.