Estimating With Microsoft Excel
Chapter Preview

Introduction | Table of Contents | Back Cover

Foreword

Computerized estimating has become the standard in the construction industry over the last 15 years. As companies have computerized, they first worked on their accounting operations. The next priority for most builders has been to computerize their estimating functions. Of all the estimating programs that are available, the most widely used program in residential construction estimating is a simple spreadsheet.

In his book, Estimating with Microsoft® Excel: Unlocking the Power for Home Builders, Jay Christofferson gives you step-by-step instructions on how to create a powerful spreadsheet estimating program that meets the needs of home builders.

Jay is one of the leading experts in computerized estimating for home builders. He is a builder and has also helped other builders all around the country develop better management systems for their operations.

Excel is one of the most powerful tools any home builder can use. For most builders it can be just as powerful as your vehicle or your cellular phone and can bring you the same return. Jay does just what the title implies, he shows you how to unlock the power of Excel. He explains how to make it simple enough for anyone to use and yet powerful enough for you to base important management decisions on the information you generate from it.

Jay's approach to estimating is so simple anyone can learn it and yet so well thought-out that you can estimate everything needed to build a house three different ways:

• Estimating every stick and brick

• Subcontractor bids

• Unit price estimating for each phase

For most home builders computerized estimating accomplishes three major objectives.

1. It is much faster (many builders can complete an entire detailed estimate in 3-4 hours instead of 3-4 days).
   

2. It improves the accuracy of your estimates. Even though estimates are completed quicker, they are also much more accurate.
   

3. Your estimate is more organized. It flows from the beginning of construction to the end and it breaks down the separate work packages by vendor. This makes purchasing and cost control much easier.

This could be one of the most important books you have ever read. Enjoy it!

Leon Rogers
President of Construction
Management Associates

Introduction

In an effort to pin down the bottom-line costs of a project, home builders and remodelers have developed various methods of estimating. Detailed estimating is the most accurate approach, but it also takes the most time to complete. In a 1999 survey at the International Builders' Show (Christofferson), builders reported taking an average of 12.7 hours to complete a detailed estimate. A quicker, but less accurate way to estimate uses the "guesstimate" method. With this approach, the estimator wets his index finger, holds it high in the air, and enters the amount that comes to mind. In reality, most builders use a combination of methods; some costs in an estimate come from a subcontractor's bid, some are treated as allowances, other costs are estimated using detailed methods, and still other costs are estimated based on linear foot or square foot measures.

Computers have taken much of the drudgery out of estimating and while increasing the accuracy of estimates, have decreased the time needed to complete them. In the same survey (Christofferson,1999), builders reported that by using computer programs to generate their estimates, the average time required to complete each estimate dropped to 4.9 hours. Yet many complained that the estimating programs they have tried are extremely complicated, take a long time to learn, are too rigid to customize, or are very expensive.

An increasing number of builders and remodelers are using computer spreadsheets to solve their estimating problems. Seventy percent of builders already own spreadsheet programs (NAHB, 1997). The benefits of using computerized spreadsheets for estimating are-.

• They are inexpensive.

• They are easy to use.

• They can be customized to your style of estimating.

• They are fast.

• They are powerful.

Using spreadsheet estimating allows estimators to customize their spreadsheets to do exactly what they need. Utilizing spreadsheets can reduce the time it takes to do an estimate to one or two hours.

While building houses during the infancy of the personal computer era, I often wished for an easy-to-use computer software which was powerful enough to solve many of the management problems we faced while building. Computerized spreadsheets provided that power and ease of use and these programs continue to improve.

Several years ago, I was approached by a builder wanting to know if there was a way to create a customized estimating spreadsheet that would be powerful and effective. In the university-level Quantity Take-off and Estimating class that I teach, we began using spreadsheets to solve some of the estimating problems that we faced. Over the years, we have developed procedures and methods using computerized spreadsheets to quickly and automatically solve many of the estimating problems that challenge builders and remodelers. While teaching estimating seminars to builders and remodelers, I have seen increasing interest by builders and remodelers who want to be able to develop their own customized estimating spreadsheets.

This book focuses on teaching home builders and remodelers how to develop customized estimating programs using Excel. You will not only learn the basics (and much more) of using computer spreadsheets, but will learn step by step how to create your own automated spreadsheet estimating program. The knowledge that you will gain here can easily be applied to solve other office needs as well, so even if you are not an estimator, you will gain a tremendous amount by reading this book.

This book assumes you know how to estimate and that you have tackled some computer estimating. If you need a quick brush-up on these skills, you may consult books like Computer Estimating for Home Builders by Thomas Coleman (1998, Home Builder Press); Action Estimating for the Small-Volume and Custom Builder by William Avon Privette; (1998, Home Builder Press); and Estimating Home Construction Costs by Jerry Householder (1999, Home Builder Press); the latter provides forms and instructions for estimating.

You will learn more and faster if, as you read this book, you try these ideas and concepts on your Excel spreadsheet. Take a chance to explore on your own and discover new ways to make your spreadsheets more efficient and effective. To make that easier, a CD-ROM is included with this book. It contains the following files:

• Chapter 1.xls

• Chapter 2 Cost Breakdown Summary.xls

• Chapter 2 Database.xls

• Chapter 3 Cost Breakdown Summary.xls

• Chapter 4 Finish.xls

• Chapter 4 start.xls

• Chapter 5 Linking Finish.xls

• Chapter 5 Linking Start.xls

• Chapter 6 Formulas Finish.xls

• Chapter 6 Formulas Start.xls

• Chapter 7 Interest & Profit.xls

• Chapter 8 Macros.xls

• Chapter 9 Embedded Excel Object.doc

• Chapter 9 Excel Table Copy-Paste.doc

• Chapter 9 Integration Comma Delimited.doc

• Chapter 9 Integration Comma Delimited.txt

• Chapter 9 Integration PlanDB AutoFilter.xls

• Chapter 9 Integration Tab-Delimited File.xls

• HOME.MPP

• Sample ConstructionSoft Estimator.xlt

• Schedule Activities.xls

• Schedule letter.doc

For best results, these files should be installed to the "C:\NAHB\ Estimating with MS Excel\" path. This is particularly important for those files that go with Chapter 9:

• HOME.MPP

• Schedule Activities.xls

• Schedule letter.doc

Note that when opening some of these files a box may come onto your screen that reads

• The workbook you are opening contains macros.

• If you are sure this workbook is from a trusted source,
  click Enable Macros.

• If you are not sure and want to prevent any macros from running, click Disable Macros.

All files have been tested for viruses and will not harm your computer. Click Enable Macros in order to make full use of the files.

If you open the sample ConstructionSoft Estimator.xlt file and get a message stating in effect "Excel cannot calculate a formula. Cell references in the formula refer to ... a circular reference." Click OK. Then Tools/Options/Calculation. Then click on the box next to Iteration and click OK.

Also, you will notice some files are "start" and some are "finish." In this way, you can see how the completed Excel file should look-just in case you get stuck. However, the lessons are laid out in such a way that even if you're not terribly familiar with Excel, you'll be unlocking its power in no time.

Jay Christofferson
Program Chair
Brigham Young University,
Construction Management

Introduction | Table of Contents | Back Cover