Jay Christofferson has taught thousands of construction professionals how to make more money by improving how they estimate jobs. Now you can benefit from his wisdom too!
Roof slope change? No problem! Material Substitutions? No sweat! This book and CD take you step-by-step through building an estimating workbook that will save you precious time and spare you from costly errors. Estimating with Microsoft Excel®, Third Edition includes formulas for calculating concrete, rebar, labor, permits, taxes, and more, and demonstrates how to apply them in your everyday construction project management.
Create databases, write formulas, and adapt shortcuts to eliminate time-consuming steps and repetitive work that eat into your profit margin using Estimating with Microsoft Excel®, Third Edition.
Jay Christofferson has taught thousands of construction professionals how to make more money by improving how they estimate jobs. Now you can benefit from his wisdom too!
Roof slope change? No problem! Material Substitutions? No sweat! This book and CD take you step-by-step through building an estimating workbook that will save you precious time and spare you from costly errors. Estimating with Microsoft Excel®, Third Edition includes formulas for calculating concrete, rebar, labor, permits, taxes, and more, and demonstrates how to apply them in your everyday construction project management.
Create databases, write formulas, and adapt shortcuts to eliminate time-consuming steps and repetitive work that eat into your profit margin using Estimating with Microsoft Excel®, Third Edition.
eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Jay Christofferson has taught thousands of construction professionals how to make more money by improving how they estimate jobs. Now you can benefit from his wisdom too!
Roof slope change? No problem! Material Substitutions? No sweat! This book and CD take you step-by-step through building an estimating workbook that will save you precious time and spare you from costly errors. Estimating with Microsoft Excel®, Third Edition includes formulas for calculating concrete, rebar, labor, permits, taxes, and more, and demonstrates how to apply them in your everyday construction project management.
Create databases, write formulas, and adapt shortcuts to eliminate time-consuming steps and repetitive work that eat into your profit margin using Estimating with Microsoft Excel®, Third Edition.
Product Details
| ISBN-13: | 9780867186987 |
|---|---|
| Publisher: | BuilderBooks |
| Publication date: | 01/01/2010 |
| Sold by: | INDEPENDENT PUB GROUP - EPUB - EBKS |
| Format: | eBook |
| Pages: | 176 |
| File size: | 9 MB |
About the Author
Read an Excerpt
CHAPTER 1
The Basics of Spreadsheets and Beyond
Computerized spreadsheets are essential to business management, and construction management is no exception. Microsoft Excel® is the most commonly used program for maintaining these spreadsheets. Even if you are proficient in Excel®, you need to invest time to practice using Excel® 2007. The program has a number of new capabilities, so this investment will be well worth the effort.
The Office button and tab-and-ribbon layout replace the traditional menus and toolbars. For the most part, the commands and features of earlier versions are there, but in different locations (fig. 1.0).
Finding Help
For specific questions about any Excel® function, button, or feature, use the Help button [X] located in the upper-right corner (under the [?] or Close button) of the ribbon. After clicking the Help button, type in a key word and choose from the topics displayed the one that addresses your question.
Undoing Mistakes
Don't be afraid to try something new. If Excel® doesn't behave as you expected it to, click the Undo button on the Quick Access toolbar (QAT) located to the right of the Office button. When you click the Undo button, the last instruction you performed will be undone. The second time you click it, the second to the last instruction you performed will be undone, and so forth. If you click the down-pointing arrow next to the Undo icon, Excel® will display a list of previous actions. By selecting one of the actions on the list, that action and those that followed it will be undone. If you decide that you really didn't want to undo, you can click the Redo button (fig. 1.1).
Using Workbooks
An Excel® workbook is the file in which you store and analyze data. Figure 1.2 shows a blank Excel® workbook. The Title bar displays the file name and application. The Office button allows you to set preferences for printing, saving, and other functions. The QAT allows you to display commonly used commands so you can see them no matter which tab on the ribbon is selected. You can also place icons for custom commands on the QAT, which will be discussed in later chapters. The Formula bar, which allows you to edit cell contents, is located above the column headings (A, B, C, etc.) and the Status bar, which alerts the user to the state of Excel®, is at the bottom of the page.
You can customize the Status bar by clicking the right mouse button (right clicking) and choosing options from the menu such as page view, zoom, sum, average, and count.
Each worksheet contains 16,384 columns (A through XFD) and more than a million rows (1 through 1,048,576). The intersection of a column and a row is called a cell. Cells are storage bins that can hold numbers, text, formulas, and formatting. Each cell has its own unique address. For example, the intersection of column D and row 5 is cell D5. Although the data stored in cells can change, the cell addresses do not change.
Adding and Deleting Worksheets
Similar to a folder in a file cabinet, a workbook contains any number of related sheets. However, each worksheet consumes space and increases the size of the workbook so unless you need them, you are better off with fewer sheets in your workbook. The default number of blank worksheets is 3. To change the default number of worksheets that open in a workbook
* Click Office button/Excel® Options/Popular.
* Select the desired number of sheets next to Include this many sheets.
You can add or delete worksheets from a workbook at any time. To add a worksheet
* Click the Insert Worksheet tab to the right of the worksheet tabs at the bottom of the workbook or right click one of the sheet tabs. If you use the first option, Excel® inserts the new sheet to the left of the Insert Worksheet tab. If you use the second option, Excel® inserts the new sheet to the left of the sheet that you right clicked.
To delete a worksheet
* Right click the tab of the sheet that you want to delete.
* Select Delete from the pop-up menu (fig. 1.3). A pop-up dialog box prompts you to either confirm the deletion or cancel.
* Select OK to delete the sheet.
Clicking the right mouse button will display a menu. Clicking the left mouse button will select an item or a cell.
Excel® automatically names worksheets with numbers: 1, 2, 3, etc. You can rename and move worksheets as follows:
* Double click the sheet's tab. The tab text is now in edit mode and can be changed.
To move (reorder) a sheet, click and hold the mouse button while on the tab of the sheet you want to move and then drag the tab to the desired position.
Controlling the Cursor
When you type data or text and then press Enter, the cursor automatically drops down to the next cell. However, you can change this default movement so the cursor moves to the right, left, or up, or does not move after the Enter key is pressed.
To change the default movement of the cursor
* Click Office button/Excel® Options/Advanced and select the direction of movement (fig. 1.4).
I prefer to uncheck the "After pressing Enter, move selection" box so the cursor does not move after the Enter key is pressed. This makes it easier to experiment with various "What If?" alternatives when preparing estimates.
Selecting Cell Ranges
A group of cells is a range. You can change the default movement of the cursor to move within a specific range. To do this, select the range of cells and press Enter.
To select a block of adjacent cells
* Select a cell in the corner of the block of cells and press Shift.
* Click a cell in the diagonal opposite corner of the desired block of cells.
All of the cells will be highlighted. When you press Enter within the block of cells, the cursor will move only within the block.
You also can select a group of non-adjacent cells.
* Press and hold the Ctrl key.
* Select each cell (fig. 1.5). Again, you will see the cells highlighted.
* Press Enter to move the cursor from one cell to the next within the selected range of cells.
* To move the cursor in the opposite direction, hold the Shift key while pressing Enter.
Writing Formulas
All formulas begin with the equal sign (=). Formulas contain variables, littorals, and operators (table 1.0). Variables are values that may change. Littorals are values in formulas that do not vary from use to use (e.g., 3, 26.7, and 100.65). Operators are commands (add, subtract, multiply, divide, etc.) that indicate what to do with littorals or variables.
You could enter a formula into cell E2 to add the values of two variables as follows:
=B6 + C5
The formula can be written with spaces, as shown, or without spaces (B6+C5). If the values stored in cells B6 and C5, respectively, were 5 and 72, the result of applying the formula would be 77. If, however, the variable in cell C5 were changed to 12, the result of applying the formula would be 17.
Naming Cells and Cell Ranges
By naming a cell or range of cells, you allow Excel® to use the name in formulas and as a reference in creating hyperlinks that allow you to easily move from one location to another within and between worksheets. For example, you could name a cell MainFloorSF to store the square footage of the main floor area of a home. Excel® then uses this name in formulas to determine the paint and drywall requirements, valuations for permits, framing labor, and so forth. Also, the named range, MainFloorSF could be used with the hyperlink feature to automatically move to the cell named MainFloorSF from another workbook location.
The range of cells shown in figure 1.5 is named SelectedRange.
To name a range
* Select the range of cells.
* Type a one-word name (no spaces) into the Name: box (to the left of the Formula bar).
* Press Enter.
This allows you to select the named range while working in any sheet in the workbook. Simply choose it from the list of names in the drop-down list in the Name box. You access this list by clicking the down arrow on the right side of the Name box.
Cells that have either row or column headings can be named automatically (fig. 1.6).
* Select the row heading cells and the cells to be named (B3:C5).
* Click the Formulas tab.
* Click Create from Selection, and verify that the labels for the cells you want named are in the column on the left.
* Click OK.
* Name the cells: C3, Principal; C4, Interest Rate; and C5, Term.
To check the labels, click cell C3 and verify that the name Principal is shown in the Name box.
Whenever the name Principal is selected from the drop-down list in the Name box, the cursor will move to C3 (fig. 1.7). Whenever you include the word Principal in a formula, Excel® will substitute the value in cell C3. For example, the formula = Principal * Interest_Rate/12 would calculate the interest for one month.
Assume that the remaining principal on a loan balance is $180,000 and the annual percentage rate (APR) is 7.5%. Using the previous formula, Excel® would insert the appropriate variables and return the monthly interest as follows: $180,000 * .075/12 = $1,125.
Formatting Cells for Accounting and for Percentage
Cell C3, which holds the value 385000, was formatted to accounting style ($385,000.00) by selecting cell C3 and clicking $ on the Home tab. Cell C4 (.0625) is correctly shown as a percentage (6.25%) by selecting cell C4 and clicking % on the Home tab.
Rounding Up Values in Cells
The Round Up function in a formula looks like this:
ROUNDUP(number,num_digits)
The num_digits is optional. If you omit it or set it at zero, Excel® rounds up the number in question to the nearest integer as in the following two examples:
=ROUNDUP(27.374,) returns the value 28
=ROUNDUP(27.374,0) also returns the value 28
If num_digits is greater than 0, Excel® rounds up the number to the specified number of decimal places as in the following example:
=ROUNDUP(27.374,2) returns the value 27.38
If num_digits is –1, Excel® rounds up the number in the tens place as follows:
=ROUNDUP(27.374,-1) returns the value 30
You can apply rounding to construction in estimating cubic yards (CY) of concrete to the nearest ¼ CY as follows:
1. Multiply the number by 4.
2. Round up the result to the nearest integer.
3. Divide the result by 4.
Here's how the formula looks using the number from the previous examples:
=ROUNDUP(27.374*4,0)/4 returns the value 27.5
Naming Formulas
As with cells and ranges, you can assign names to formulas also. So, instead of manually typing or copying the same formula from place to place, you can simply copy the name of the formula.
Suppose you needed to estimate the area of a round concrete pad for a gazebo. You could create a formula to use anywhere in your workbook that calculates the area of a circle (π[r.sup.2]). The Greek letter π (PI) represents a value of approximately 3.14. The letter r represents the radius of the circle. The radius of the circle is squared (^2).
The formula is as follows:
=3.14 * Radius^2
To name this formula
* Click Define Name in the Formulas tab. The New Name dialog box will appear (fig. 1.8).
* Enter the area of a circle formula in the Refers to: box.
* Enter the name AreaCircle in the Name: box.
To use this formula in a worksheet, you must assign a value to the radius variable. Type the word Radius in cell A1 and the number 2 in cell B1 (fig. 1.9).
Name the radius variable as follows:
* Select cells A1 and B1.
* Click Create from Selection on the Formulas tab. The Create Names dialog box appears and asks if the name is in the left column. Excel® anticipates the correct answer and asks for verification. In this case, the name label is to the left of the cell where Excel® will store the radius value.
* Verify that the box next to Left column in the dialog box is checked.
* Click OK.
* Paste the formula's name in the cell where you want the area of the circle to display — in this case, cell D1, as shown in figure 1.10.
To display the Paste Name dialog box
* Press the F3 key to display the list of available names.
* Select the appropriate name from the list (in this case AreaCircle).
* Click OK. (Alternatively, you could click Use in Formula in the Formulas tab and click Paste Names.)
Figure 1.10 shows the result of the formula in cell D1. The formula is displayed in the Formula bar. To test the formula, change the value of the variable in cell B1 and check the result.
Suppose you used this formula in several places in the workbook but then realized you had to change the formula to be more precise. Because you named it, you could change 3.14 to 3.141593 once instead of in every cell where the formula is used.
Change the formula for AreaCircle as follows:
* Click Name Manager in the Formulas tab.
* Change the formula in the Refers to: box (fig. 1.11).
* Click Close and then Yes.
Excel® updates all of the formula references for AreaCircle in the workbook. An alternative is to
* Double click AreaCircle after you click Name Manager.
* Change the formula in the Refers to: box in the Edit Name dialog box (fig. 1.12).
* Click OK and Close.
There are also two alternatives for typing the formula as follows:
=3.141593 * Radius^2
or
=PI() * Radius^2
Naming Constants
Named constants can be as useful as named formulas as the following example, which calculates taxes (e.g., on materials purchased), illustrates. You estimate tax only on the materials that will be purchased, not all items. When you buy concrete, rebar, framing lumber, hardware, etc., you add a percentage (the tax rate) to the cost of the materials.
If you want to calculate the tax on concrete and your tax rate is 6.25%, or 0.0625, you can write a formula to take the subtotal of the concrete and multiply it by .0625. If the concrete subtotal is located in cell G28, the tax formula could be placed in G29. It looks like this:
=G28*.0625
You could write a formula similar to this one for every item in your estimate that requires tax. You might use the tax formula 15 or 20 times throughout your estimate. However, if the tax rate increased to 6.75%, you would have to go back through each of the formulas and change the .0625 to .067 — a time-consuming chore. A better option is to replace the tax rate with the constant Tax in the formula (fig. 1.13). So, instead of changing 15 or 20 tax formulas when the tax rate changes, you can change the value of the constant once, and all formulas that reference the constant will automatically update.
To create a tax constant
* Click Define Name in the Formulas tab.
* Enter the name of the constant (Tax) in the Name: box.
* Enter the value of the constant (=.0625) in the Refers to: box.
Figure 1.14 shows how Excel® uses the tax constant in the following formula, which calculates the tax amount:
=B1*Tax
Cell B1 is the subtotal cost of the material to be taxed. When the tax rate changes, the tax constant can be changed in the Refers to: dialog box and all formulas that use the tax constant will automatically update.
Finding Information in a Table
Using named ranges can help you search for information in tables. For example, you might track monthly sales for various communities in which you build. If you wanted to know the April sales for the Parkway community, you could create a formula to look up this information. One way to find specific data from a table is to use the intersection operator, which is the space between the two names, along with the named ranges.
First, name the rows and columns of a table.
* Select the table (including headers).
* Click Create from Selection in the Formulas tab (fig. 1.15). The Create Names from Selection dialog box prompts you to verify that the names for the columns are in the top row and the names for the rows are in the left column.
* Click OK.
Now, wherever you may be in the workbook, you can write a formula to instantly return the data you need from the table. For example, if you want to know the April sales for the Parkway community, enter the following formula:
=April Parkway
The value of the sales, $749,000, is the intersection of the April range and the Parkway range (fig. 1.16). The order of ranges in the formula does not matter in this case. If you wrote the formulas as = Parkway April, Excel® would return the same value.
(Continues…)
Excerpted from "Estimating with Microsoft Excel"
by .
Copyright © 2010 NAHB.
Excerpted by permission of National Association of Home Builders.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
List of Figures,
List of Tables,
Foreword,
About the Author,
Acknowledgments,
Introduction,
Chapter 1 The Basics of Spreadsheets and Beyond,
Chapter 2 Improving Estimating by Using Spreadsheets,
Chapter 3 Creating a Cost Breakdown Summary Sheet,
Chapter 4 Eliminating Steps by Using Detail Sheets,
Chapter 5 Saving Time with Hyperlinks,
Chapter 6 Letting Formulas and Functions Do the Work,
Chapter 7 Calculating Profit Margin,
Chapter 8 Making Spreadsheets User Friendly with Form Controls,
Chapter 9 Automating Spreadsheets,
Notes,
Resources,
Figures,