Subscribe to TIC

An Introduction to Spreadsheets

Like most contractors, I’m always looking for ways to be more efficient and accurate when it comes to the paperwork needed in this business. There are a great many programs available that claim to fully automate your construction business. Most of them are either too expensive and/or too complicated for my small business. What I need is something easier to use and more flexible.

The tool I use the most is the spreadsheet. A spreadsheet program comes free with almost every computer purchased today. One of the best known is Microsoft Excel. You have to pay for Excel, but it could be worth it. A good free option is OpenOffice.org, which works similarly to Excel. But since I use Excel, that’s what I’ll be talking about in this article. The picture below shows how to locate the program on my computer. Yours will probably be similar.

Excel is a very powerful spreadsheet program. Don’t let the powerful part fool you. It is also one of easiest programs to learn and use. Unlike some powerful CAD programs that require you to spend hours in training and tutorials just to start using its features, any spreadsheet program can be used, in some fashion, very quickly.

The following picture shows the opening screen of Microsoft Excel. Depending on which operating system you’re using (Windows, Mac, etc.), and the version if Excel you have, your screen may look slightly different.

Here is the opening screen with some key menu items and headers identified for easy location:

A spreadsheet is nothing more than a database program with some unique features. The way a spreadsheet works is by a series of cell references. The cell reference is made up of rows and columns. The rows are numbered from 1 to 65536 and the columns are labeled from A thru IV. Each cell in this large matrix has a unique address, such as A1, P550 or IV65536. For you folks who understood geometry in high school this can be thought of as an X,Y coordinate in space. Now don’t stop reading, I won’t mention geometry again.

The picture below shows what a cell reference is. Follow down a column and stop at any row. Where the column and row intersect is the cell reference. I have typed the cell reference in a few cells to illustrate the relationship.

Entering text or numbers in a spreadsheet is straightforward. Place the square cursor on any cell, by using your mouse or the arrow keys on the keyboard, and start typing. When done typing, press the enter key. The text or number will be entered into that cell and the cursor will drop down one row.

In the picture above I typed the sentence into cell B3. It looks as if the sentence covers columns B through H. In reality, the entire sentence is in cell B3, it just spreads out across the other columns because there is nothing in the cells to the right of it.

In the picture below there is text in cells B3 and also D3. You can see the sentence in cell B3 stops at cell D3 where the new sentence starts. The sentence in D3 keeps going to the right because there is nothing in cells E3 through G3.

In order to see both sentences in their entirety we need to change the width of the columns. In the picture below I have highlighted the B column by left-clicking on the “B” in the column header, then I selected Format from the dropdown menu, then Column, then AutoFit Selection.

Here is the result:

It is helpful to set the width of the columns to what you want prior to entering your data. You can always modify them later to the exact width.

The height of the rows can be changed in the same way as the width.

The result of the row height command is shown below. I changed the height to 30 from 12.75, which is the default.

Formatting Cells

Now that we have a little background in formatting rows and columns, let’s look at formatting numbers, text, and cells. The format part of the menu is usually found in the upper right of your screen. It looks much like the format menu in Microsoft Word. In fact, they work almost exactly the same.

Let’s start by formatting a range of cells with a different background color.

Select a range of cells using your mouse. Here’s how: Press and hold the left mouse button while holding the cursor over a cell; now move down and to the right until you highlight the range of cells you want. Release the left mouse button and the selected area will be highlighted in a light grey color with a black border. Next, find and select the small arrow to the right of the icon of a pouring paint can in the top right of the menu bar.

Another drop down menu will appear showing a pallet of colors. Select the color you want, and the background color of the selected range will be changed.

The picture below shows the sequence. You can format one cell or a range of cells, an entire row or column, or a range of columns and rows.

Next, let’s take a look at how to format text and numbers in an Excel spreadsheet. By formatting text and numbers you can change the appearance of your spreadsheet to make it easier to read and understand. Excel has pre-defined number formats. Some of the most popular are: Number, Currency, Percentage and Fraction. You can format a number before or after it is entered into a cell. Just select (click on) your cell, go to the Format menu, and choose Cells. Here’s an example of formatting a cell for fractions:

Using the same procedure, you can format numbers and text to display as you desire. Change the size, color, and font of any text or numerical entry. Add patterns and borders to cells or ranges of cells. Change the alignment of numbers and text within a cell. You can center, left justify, or right justify your entries. Here’s an example of justifying text:

Formulas

Let’s talk about formulas in a spreadsheet. Here is where the real power of this program lies. Remember, from the beginning of this article, a cell reference is the unique position within the spreadsheet—references such as A1, B66, and AB5025. A formula is simply an action performed relating two or more cells together. In the picture below I have entered numbers into four cells, B3 through B6. I center-justified those numbers and formatted them as Currency.

In cell B8 I entered the following formula: =B3+B4+B5+B6.  This formula tells the program to add together the numbers in cells B3, B4, B5, and B6, and put the result in the cell that the formula was typed into—in this example, cell B8. You can see that the result of that addition formula is $746.75. Excel does the math for you.

Now, this is important: The equals sign (=) in front of the formula is mandatory! The equals sign tells the spreadsheet that the alpha-numeric text that follows is a formula, and not just a bunch of letters and numbers.

Another important consideration with formulas is the order of execution of mathematical operators (the signs for “plus,” “minus,” “times,” etc.). To change the order of evaluation, enclose the part of the formula to be calculated first in parentheses. For example, the following formula: =5+2*3 produces 11 because Excel calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.

In contrast, if you use parentheses to change the syntax, such as =(5+2)*3, Excel will add 5 and 2 together first, and then multiply the result by 3 to produce 21.

If you want to edit the contents of a cell, simply double-click the cell. If you want to delete the contents of a cell, simply select (click) a cell, and press the Delete button on your keyboard. What if you want to delete more than one cell? Say, in the above example, you want to delete cells B5 and B6 from your formula. Simply click and hold (click and keep the mouse button pressed down) on cell B5, and drag your mouse down to include cell B6. Press Delete, and they’re gone!

Autosum

Here’s a quick and easy way to perform simple formula tasks:

The Autosum button:

This can be a really handy way to add up a lot of cells quickly. I’ll show you. (Note: the following screen shots are from a Mac. Don’t let that throw you off.)

Say you have a long list of numbers you need to add up, and you don’t want to write the formula out by hand.

You want the Total to be in cell B16. So, select (click on) cell B16, then click the Autosum button (it should be in your menu, up top somewhere). Excel will automatically select all the cells above and get ready to add them together.

Hit Enter (or Return) on your keyboard, and boom!, there’s the result.

Here’s something neat you can do with the Autosum function. Say you have a list of numbers you know you’re going to keep adding to, but you want to keep track of the Total as you go along. All you need to do is the Autosum trick we just learned, but farther down the spreadsheet.

Note that the highlighted section shows you the cells that are included in your formula. You can change those numbers to suite your needs.

Now, if you put a number in cell B15, Excel will automatically add it to the total.

If you put another number into cell B16, it would add that to the total, too.

These are just a couple of examples showing what you can do with the Autosum button. It’s definitely worth playing around with.

Real-world Examples

Now I’m going to create a simple spreadsheet using the features we have discussed in this article, and also some that will be covered in future articles.

As contractors, we generate bids/estimates frequently. This can be a very time-consuming process, and if you do a lot of the same type of work, it can become very repetitive. In the spreadsheet example below, I have created a simple form that can speed up this process.

With a little time and effort, you can create a similar form that works perfectly for your unique business needs.

(Click to enlarge)

The top part of the form has all the customer information, a job description, and some of the costs associated with this project. I selectively formatted various rows with color to make the whole spreadsheet easier to read. Additionally, I created another column, “Costs by Trade,” that summarizes each trade’s total costs.

Look at the bottom of the sheet and you can see some tabs labeled “HVAC Parts,” “Cabinets and Trim Parts,” and so on. These are different sheets within the same Excel “Workbook.” Much like a file folder with multiple pages, this is a very powerful feature. Notice the orange color on cell C20. I highlighted that to show the power of multiple sheets. In the following figure you can see the sheet named “Framing Lumber.” This sheet contains all the rough lumber and their respective costs. I intentionally made the list small and manageable, but you get the idea. All you have to do is input the quantity of each item you need for this particular job and Excel summarizes the total cost.

Remember the orange cell C20? In that cell on the “Summary Detail” sheet I typed the formula =’Framing Lumber’!E19. This formula instructs Excel to place the value of cell E19 in the Framing Lumber sheet into cell C20 on the Summary Detail sheet.

Create as many sheets as you need for your respective trades and costs. Then link those summarized costs to the Summary Detail sheet.

The following figure shows the bottom of our Summary Detail sheet. It shows a continuation of our project costs and the Sub-totals and Grand Totals of the project.

(Click to enlarge)

You can see that I entered my percentages for Mark-up and Profit. These are in different cells, and can be changed to suit your specific requirements, even from job to job. The formulas have been created to automatically recalculate when the percentages are changed.

Ok, now we have a nice accurate estimate with all the information you need to present to the customer. Of course, you don’t want the customer to have of this information, just the summarized prices. You could take all the information and retype it in another program. But why not create another sheet in this workbook that you can print and give to the customer?

(Click to enlarge)

What you choose to put on this form is totally up to you. All I did was to link data from other sheets in the workbook to the appropriate cells in the Customer Print Out sheet. It literally took me less than 5 minutes to create this new sheet.

Now I’ll show you how to format this sheet for printing. First, we have to define the print area.

Using your mouse, highlight the area you want to print. Then select: FilePrint AreaSet Print Area. Then select: FilePage Setup to align the print area onto the paper in your printer. Depending on the size of your print area you may need to select Landscape mode, or fit the print area to more than one page tall.

By now, I’m sure you have realized the potential power of a spreadsheet program. With just a few of the techniques shown in this article you can create a form that can really help you in the day-to-day operation of your business. Of course, we have just scratched the surface of the power of this program. In future articles we will explore more advanced formulas, formatting, sorting your data, and adding charts and graphs to help you understand what the data is telling you.

If you’d like to download the spreadsheet seen in this article, you can do so HERE.

•••

AUTHOR BIO

Chuck Kiser got his start in the trades in high school in Arizona. During school and the four years that followed Chuck learned a lot about the carpet and tile trade from his brother-in-law, and some residential plumbing skills working for a new construction builder in Phoenix. An offer from Uncle Sam put an eight-year gap in the trades while he played around with submarines and other forms of military transportation. After the Navy, Chuck entered into the Aerospace and Defense field, working in R&D for Advanced Composite materials. During this time Chuck returned to school to get his Undergraduate and Graduate degrees in Business and Management.

Ten years went by very quickly and, not wanting to live out of a suitcase for the rest of his life, Chuck re-entered the trades in the mid ’90s. Starting your own business is a challenge, but it was well worth it. Long hours of research paid off, and even after more than a few mistakes, it actually makes money—sometimes.

These days Chuck focuses on frame-to-finish residential carpentry, with the occasional remodel thrown in to keep things interesting. When not working, Chuck surfs the web trying to keep up with the changing construction industry, and the tools and technology that support it. Chuck and his wife Joan live in Palos Park, IL.

Comments/Discussion

31 Responses to “An Introduction to Spreadsheets”

  1. al

    Chuck,

    It is really great that you took the time to share some
    of your spreadsheet knowledge. I would like to let everyone know to allways use caution when creating or
    updating a spreadsheet because sometimes your formulas can
    get confused and you get bogus numbers.
    So before you finish double check the numbers and formulas, also what you see on your monitor
    may not end up printing out, doing a print preview helps
    with this.

    Good Job,
    Al

    Reply
    • Chuck Kiser

      Good Points Al, I should have mentioned the formula checking. We can’t forget the old adage ‘garbage in, garbage out’. When I enter a formula I always double and triple check the results the old fashioned way with pencil and paper. In future articles I will cover copying formulas. A powerful feature that save time. But can also induce errors if you are not careful.

      Reply
  2. Dave MacMorris

    I appreciate the information regarding the spreadsheets. I have been using the spreadsheet, but wasn’t aware of the options described above. I have been doing things lets say long hand. Thank you very much for the insight.

    Dave

    Reply
    • Chuck Kiser

      I’m glad it helped Dave. I must admit I started using spreadsheets in the VisiCalc days. Many years ago. The latest versions have features I still have not used. Maybe someday…..

      Reply
  3. Dreamcatcher

    Good article Chuck!
    I wish I had this to follow years ago. I’ve just been stumbling my way through Excel all this time. I have down most of what you covered here. Although the use of ‘sheets’ is new to me and I will definitely use that in the future.

    Not to get too far off on a tangent but I really wish someone would write an article on “How to Estimate”. I find that to be the real hard part.

    Following or tracking down the material prices, specialty item costs, getting sub prices, and knowing when to be more or less detailed.

    For instance, I noticed in your framing material estimate, you don’t have nails. Is that covered elsewhere or was that part of the estimate just for example? I also see that you have a “markup” that you disclose to the client. I always set up my spreadsheet to automatically change all the prices and only tell me (in a non-printed area) what the total markup cost is. Then the customer just sees the marked up cost. Otherwise, I would think I could catch some flack when the customer sees I “overcharged” them 20% on materials and they decide to try the “I’ll get the materials for you” trick. On that same track, I see that you have a line item “upcharge” then another labeled “profit”, both which are disclosed to the client. But isn’t that 20% upcharge considered part of your profit? I try to stay simple with the ‘you pay’ end of the estimate, only showing ‘material’ and ‘labor’ but I give much more detail in the upper portion, breaking down line items to give a sense of “this is the ‘stuff’ you are getting for your material budget”.

    But, I really don’t know if that is a good or bad strategy. Maybe we can get a discussion to that effect.

    Thanks
    DC

    Reply
    • Chuck Kiser

      Ah yes DC, the never ending how to estimate better conversation. In my opinion there are a few basics that cannot be overlooked. You need to know your fixed costs and what you need to earn per hour to live the way you want. Beyond that it is window dressing. What you do or do not show to the customer is based on the customer, size of the job, commercial or residential.

      Tracking down prices for materials and subs will always take time. Of course, the longer you are in business the more detail you will have on hand. Historical information is the most accurate way to to estimate jobs. This assumes your history is not too old.

      Material markup is a fact of doing business. If a customer thinks for a minute they can use your relationships with suppliers, your expertise and knowledge of the products for no cost to them, they are deluded. And I have no problem defending markup. Time and knowledge cost money!

      Additionally, I will explain to a quizzical customer that my material markup covers the cost of any warranty work. If I purchase the item I warranty the item and the installation. If the item is customer purchased I only warranty the installation.

      Reply
    • Scott W

      Whenever I hear someone ask how to estimate, my heartrate quickens and hair on the back of the neck starts to raise! I don’t think the question is how to estimate (figuring labor and materials is pretty easy), but rather how to price. I think that anyone who tells you have to mark up materials by x, labor by y, and a profit of z, is doing you a great disservice. Cost-plus pricing is a surefire way to win some jobs that lose money, and overprice (and lose) jobs that should have paid you handsomely. Some jobs will work out just fine, but even a blind squirrel finds a nut every now and again.
      Further, I’d argue that that sort of pricing schema isn’t all that likely to encourage you (and subs) to improve your processes (and when I say processes, I mean everything from how you plan, purchase, do your paperwork, and produce your work). If you hold steadfast to the idea that your material takeoff and labor estimates are correct, and somebody else underbids you, it’s pretty easy to complain that they were wrong and/or lowballing to get the work. Maybe not – maybe they’ve figured out a way to frame a roof in four days instead of five. Maybe their shrewd negotiators and have set up some win/win long term contractual agreements with suppliers. Or they have invested in developing some templates for estimating, tracking costs, and invoicing that cuts the time to do these tasks in half. This article and the comments posted demonstrate that there is quite a spectrum of tools and practices put to use, and this spectrum very likely has a significant impact on costs.
      In the real world, pricing is dependent on what the market will bear, factoring in competition, locale, economic climate, and so on. I honestly think that pricing practices (and the associated drivers) are one of the least understood or acquired skills of the small business owner in this country, and is one of the biggest factors in how and why small businesses go under. I don’t think that it is possible to even scratch the surface with a series of articles, but I do think that anyone out there can really help themselves by some good old fashioned objective thinking on the subject. A good place to start is with some reading on the subject of Lean Manufacturing (interestingly enough), and the related subject of Lean Accounting. The good thing is that Lean Accounting tends to be much simpler than what most of us believe accounting is all about, but it does require some serious paradigm shifting.
      Good Luck!

      Reply
      • James Bunch

        This excel is what I use for bidding and for seeing where I stand on estimating costs. I believe most contractors do not make enough profit and therefore fail in business.
        Here is my formula:
        labor
        materials
        overhead
        markup
        So let’s use an example for a custom door, the door cost you say 500.00 to you raw, you sand,stain,and finish, pre-hang,install.
        I charge 250-350 for finishing,which I taught myself to do,there is PROFIT in this.
        Prehang is about 250 dollars since it is custom.
        Hardware is cost plus markup.
        Install depends on what you have to do,example tear out old door and jamb, stucco, ect….this is all to prep new door for install.
        Here are my costs:
        Door 500.00 Plus markup 30-40%= 150-200 so door is 650-700
        Hardware (varies, but lets say 150 bucks.
        finishing is 250-350.
        tear out and install is a days work @ 55-75 bucks per hr.
        that is 400-600 bucks.
        Final bill:
        Door $650.00-700
        Finishing $250-350
        Hardware $150
        labor $400-600
        overhead Varies
        Bill customer for $1450-1800
        forgot the prehang 150-300
        Remember guys, you are professionals and should be paid accordingly, lawyers and doctors charge for their services and do not give away anything for free, you have paid your dues and should do the same. Stick to your guns and walk away from jobs that do not make you money or you will be out of business very soon.
        I came up with a formula to tell customers before I start a job that their front door should cost .05-1.50% of their home, and this gets the ball rolling if they really want a nice door.
        Good luck PRO’S
        James Bunch

        Reply
    • Chuck Kiser

      Good information Chuck, I use Google Docs as well. For my work I use it mostly for collaboration and not document generation. It works but has limited functionality.

      Reply
  4. Ed Burt

    The real value is the ability to create spreadsheets that reduce the time and the pain of estimating projects. Whether you show your client the details of the estimate or simply provide them with a summary, Chuck is demonstrating that you can automate the process. Rather than recreate the wheel for each project, you do it just once and you can modify it as necessary.

    And I concur with Al: it is IMPERATIVE that one tests and retests the spreadsheet as it is being developed. I would suggest using data from several past estimates as a check when finished. I use Excel extensively in preparing estimates, cash flow projections, financial statements, managing client databases, and performing investment analysis. And in spite of using computer spreadsheets for thirty years (remember Visicalc?), I still occasionally get “bitten” by mistakes in my formulas.

    If you receive the Journal of Light Construction or Remodeling, they typically have several articles per year featuring spreadsheets used for estimating, determining profit, and managing your sales process that you can download for free (my favorite price.)

    Reply
    • Chuck Kiser

      Great information Ed. One of the best ways to learn more about spreadsheets is to download these free work books and dissect them. Look at how the author formatted cells. Examine the formulas that were used. Pick the parts that work for you and use them in your own worksheets.

      Reply
  5. Rich Cargin

    Chuck

    Excellent article. I too wish I had seen this 10-15 years ago. I remember opening my 1st spreadsheet, seeing all those blank cells and thinking, “Now what?”

    I literally had no idea where to start, and the help menu was confusing. We didn’t have u tube videos back then. It is very difficult to take a skill (like using a spreadsheet) and write a tutorial. The basic steps to using a spreadsheet can be easily overlooked by a veteran user. You have done a good job of making the tutorial user friendly to the beginner.

    I look forward to seeing your next installments.

    Rich

    Reply
  6. Joe Stoddard

    Great article Chuck. I’m always looking for Excel resources for our JLC readers and will post a link over on JLC to your article.

    On that note, I came across this unbelievable set of resources on YouTube. The author teaches at a community college and has put his entire college curriculum on YouTube for free – complete with the workbooks and written resources. Here’s how to get there:

    1)Go here first http://www.youtube.com/user/excelisfun

    2)Watch the first video (which should start to play as soon as the page loads) which explains how get everywhere else in his tutorials

    3) Go to “Playlists” link at the top

    4) Find the Excel Basics Series. This is a 23 part series that starts roughly where this article leaves off, but then works through pretty much anything the typical small contractor or woodworker (or any employee at a company) would need to know about Excel.

    And if that’s not enough …

    5) View the “Highline Excel Class” (Highline is the name of his communigy college) The first 6 videos are review of the “basics” series…from there he goes into more and more advanced topics including the VLOOKUP and HLOOKUP functions (which are widely used when creating Estimating databases in Excel) .

    There are 8-10 complete college courses uploaded… over 1400 videos total. Most are 6-8 minutes in length and formatted so you can easily do the whole thing in one sitting. Work on one every day for a month and you’ll have a great understanding of spreadsheets.

    And if that’s STILL not enough – there’s another guy he collaborates with who wrote the “Mr. Excel” books – and is also a college prof in Akron OH. He has written tutorials here:
    http://www.mrexcel.com/articles.shtml

    – with a great Excel forum here:
    http://www.mrexcel.com/forum/index.php

    And of course we handle a fair amount of Excel questions at JLC Online – both in the Business Technology forum at (http://bit.ly/JLCBizTech) and the Estimating and Mark-up forum that Bob Kovacs moderates.

    Again – thanks for a great article – Excel (or for that matter Google Sheets or OpenOffice.org spreadsheets – another free alternative) are tools that everyone should know how to use. There’s no limit to what you can do with spreadsheets!

    JLS

    Reply
  7. Rick Voege

    Chuck,

    Great article. Excel is much like carpentry. You are limited only by your imagination.

    I have used FMPro to do my accounting and job costs. About 10 years ago, a customer showed me a trick with excel. He did a drawing, of a project he had in mind. Since then, I have become a fan of spreadsheets.

    To start, I developed a system to do scale drawings. 2d, but accurate to 1/8”, for spaces as big as 32’ x 50’. Then, I used the drawing to do a materials take off list. That evolved to using it to do estimates, very detailed job costing, as build history, circuit breaker configuration, and a history of appliances and fixtures installed, with very detailed product and warranty information. I am very open with my customers, so they get this spreadsheet at the end of the job.

    If something I have installed is defective, I don’t have to go to the job for the warranty information. As well as using the detailed information to help estimating in the future.

    If you are interested in my template and would like a sample of a drawing, let me know.

    Rick

    Reply
    • Edward Surowiec

      Great article Chuck, Its been years since I used Excel and you helped to get me back up to speed with the newer version.
      Please add me to the the mail list for a copy of your template.
      Thanks
      Ed

      Reply
  8. Ronald Sauve

    This article is a great resource, along with all of the links in the comments to additional resources. THANKS!
    Anyone know of a similar resource for Access? I’d like to learn how to use that for my business.
    Ron
    ronaldsauve@yahoo.com

    Reply
  9. Rich Cargin

    Chuck

    Joe Stoddard posted the Excelisfun U-Tube videos and they appear to be the cat’s meow for learning Excel.

    I intend to watch and learn from them.

    But I encourage you to publish more articles on how you use Excel in your business.

    Knowing the tools and crafting a workbook that applies to a small construction business are 2 different things.

    I am anxious to see your next article to see how you organize your workbook.

    As my basic workbook changes and evolves I have paradigm shift moments, where I see new ways of doing things that I never occured to me before. Oftentimes it’s the comments of others that has helped me to improve my workbook.

    Rich

    Reply
  10. George

    Good introductory explanation of simple usage of Excel. Performing sanity checks on the calculations, to make sure they make sense is an ongoing process, in my opinion.

    Reply
  11. Matt Follett

    I would have commented on this article earlier but I’ve been screwing around with Excel instead. Your article couldn’t have been more timely. To say I was a rudimentary Excel user would be an insult to rudimentary Excel users, but I was amazed at its power & versatility. I knew at some time I was going to have to invest my time in really getting to know the program. Since reading your article I have been busy putting together templates for estimates, cost sheets, & any other bit of information I can think of that I need filed in an organized fashion (which is pretty much everything).

    Also, thanx to all who have posted additional information & references/links to it. I’ll be waiting for those follow up articles.

    Reply
  12. David Sturm

    Nice article Chuck: I just want to say that mark up and Gross Profit and Net Profile are not the same. In your quote, do you have the labor burden(FICA, WC/GL for employees,etc) in your labor? Then where is your overhead like salary,rent for the office(even a home office)Insurance, cell phone, etc.
    The gross profit will confuse most in thinking this is in fact their money to take home(Everyone should pay themselves first whether by the hour or salary) Gross Profit isn’t their profit and should never be confused with their take home pay. Net profit is whatever (sometimes there isn’t, but typically it ranges in the 5%-9%) is left after the overhead is paid.
    I always you the multiplier of 1.65 or 1.67 times the labor and material to make my numbers work. I hope this helps.
    David-

    Reply
    • Chuck Kiser

      David,

      Thanks for the comments David. In my example I kept things very simple to illustrate the features of Excel. I agree with your comments. In your case you use a multiplier to arrive at your numbers. I assume you have enough history to show that works well. For many of the younger companies that data is not available or not accurate. In those cases they will need to crunch every number, every time to get an accurate cost. Another great use for Excel!

      Thanks again.

      Reply
  13. David Sturm

    Chuck, thanks for the response. I agree with the “starters” in Excel. We use Home Tech as our spread sheet and mark up labor and trades 1.65, allowances like plumbing and lighting fixtures 1.20 as most homeowners can look them up on the web, but we tell them we do this modest markup for job site supervision. We try to maintain between a 35%-40 Gross Profit margin with a 5-10% net profit.
    David-

    Reply
  14. Kevin

    This looks like a simple way to to do your job costing, if possible could I please get a copy of that template

    Thank you

    Reply
  15. Scott

    Hi Chuck
    Not sure if this is still active for replies, but I’ll give it a shot.
    I found your explanation very useful and I’ve created a very useful tool to save me time in my estimating, so thanks for taking the time to explain a few years back.
    I created a worksheet which has 8 separate tabs at the bottom, these subsequent sheets are a breakdown of seperate areas within the estimate – for example groundwork, main build, plastering etc.
    The first sheet is an overview and totalizer of the separate costs of each of these. I have linked the cells from each of these subsequent sheets, which have the sub totals in, to cells on sheet one.
    What I’m struggling with is making subsequent copies of my master copy. I need these copies for estimating each new job lead.
    Copy and paste only ever copies the single first sheet instead of the whole workbook. Also I need the links to copy ideally, although it wouldn’t be a big problem to manually ‘re-enter these.
    Thanks to anyone who can spare a little time to help

    Reply
  16. Michael Schuler

    Thanks so much, Chuck. Excellent, easy to understand presentation of basics. I’ve been procrastinating study of this topic. Your article and the online resources cited by some of the commenters will get me going!

    (And special thanks to James Bunch for providing the perfect application example for me in my world.)

    Mike Schuler

    Reply

Leave a Reply to Rick Voege

Click here to cancel reply.

Please note: Your first comment will be held for moderation/review by our staff before it appears. After you have one comment approved, all of your subsequent comments will appear immediately. Read our comment policy for more information.