Estimating With Excel For The Small Contractor YouTube

 
Thread Tools Search this Thread Display Modes
Old 01-19-2013, 04:55 PM   #1
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Estimating With Excel For The Small Contractor YouTube


All

I have recorded 12 videos on how to set up a Excel workbook for estimating small jobs.

I am a small contractor, I am self taught at Excel and I have nothing to sell.

If you have opened up an Excel workbook and saw all the the columns and rows and you said to yourself "Now what do I do" then these videos are for you.

I will teach you how to use the Excel tools, to write basic formulas and how I organize my worksheets. How to use fill and how to copy and paste. For most estimating all you need is basic math.

Video #1 Will teach you how to build a basic estimating worksheet. I'll take a guy from his pad and paper and teach him how to make Excel work for him. I intentionally keep it simple. (Note; a friend informed me that I said right click when I meant to say left click and vice versa. I'm sorry. Making the video was more difficult than I thought, despite prior planning and several failed attempts.)

Video #2 Is organizing the worksheet and making it more readable. I also teach you how to name cells, freeze panes and more practice on formula writing and using fill.

Video #3 will teach you how to build a worksheet to track your hours and your materials expenses for the job.

Video #4 In this video I make the Bill Worksheet more readable, link the totals to the Estimate Worksheet and show profitability. I also add a side calculator for figuring tax on returns and show how to insert comments in the description of each day's activities.

Video #5 I take an invoice from Microsoft's website and modifiy it for my needs.

Video #6 I make a few changes to the invoice and then make an Estimate letter. The Estimate letter gets linked to the Estimate worksheet cells. And the Invoice gets linked to the Bill Worksheet Cells.

Video #7 I set up the cover sheet to control cells throughout other tabs. I give you a preview of my Estimating Worksheet to give you a view of where we are going with this. I make a mistake at the end of the video that will have to be corrected in #8.

In Video #8 I talk about my mistake in #7, resolve the conflict with naming cells. Then I build an estimate with a 2nd workbook called Materials List Master. They I walk you through a job I did recently, from estimate through invoice. I make the invoice into a PDF and e-mail it. Then I store the invoice and the workbook in folders.

In Video #9 Note: I fixed the link. I will start to teach you how to use the basic Estimating Worksheet (EWS) to build a Roofing Worksheet. I use some SF calculators to figure roof Squares. Plus calculators to figure metal, I&W, Hip and Ridge ect. Once you have entered the data then Excel will figure materials for you in the materials list below. This is going to take at least 3 videos. It's not hard. You do these calculations every time you figure a job. I just try to automate this process. This can also be done with any building project that has a limited number of materials and tasks. The more complex the job the more difficult it is to automate the process. But it can be done. The principles involved can also be applied to vinyl siding, building decks, building a garage, drywall, suspended ceilings and so forth.

Video #10 is a continuation of the roofing worksheet building. In my worksheets I often will have the roofing worksheet as a separate worksheet within a workbook, then the roofing materials and labor will show up as a line item in the EWS.

Video # 11 More work on the roofing worksheet.

Video #12 More work on the roofing worksheet. Once you build a automated worksheet it will simplify the repetive tasks of estimating and writing a estimate/bid proposal. Simple roof can be estimated by entering a limited number of numbers in cells. The estimate letter/ bid proposal can be a standard template so that you don't have to reinvent the wheel every time you want to put out an estimate. The principles in this roofing worksheet can be applied to other areas.

As of this writing I am building the basic format of the Worksheet and then later I will go back and name cells like Total Labor. But at this point I am just building the structure. Maybe an Excel expert would build it differently. I'm not him and they videos are designed for the contractor whose kids think he is computer stupid.

I plan to do additional videos to teach you how to make a estimate letter or proposal from your estimate worksheet. How to produce an invoice from your Bill worksheet. And keep it all in one workbook.

My goal is to teach you how to build your own workbook template that is designed around your business.

I use this worksheet to keep all information about the job in one place from estimate through invoice. Then I send the invoice to my bookkeeper and she enters the information in to Quickbooks.

In each video you will see me building a formula or using fill. The process of repeating a step helps you to practice and learn by repetition. I am a slow learner and when I watch Excel is fun videos I have to watch the video repeatedly to get the concept. I also make mistakes along the way and show you how to get back on track.

If you are interested in learning how to estimate with Excel, then open a workbook and build your own workbook while watching the video. Stop the video whenever you need to and try the same tools on your workbook.

Rich

Last edited by cargin2; 02-10-2013 at 11:39 PM.
cargin2 is offline  
The Following 29 Users Say Thank You to cargin2 For This Useful Post:
AutumnWood Inc. (01-22-2013), bartstop (06-27-2015), Bilal (01-23-2017), bill67 (04-02-2015), builder2345 (02-22-2013), builderbruce (01-18-2017), CScalf (07-08-2013), Designsn28 (04-02-2016), eastward (08-24-2014), EthanB (01-20-2013), FairTrades (10-27-2015), gary2462 (04-24-2017), H. Kulak & Son (06-14-2014), hdung91 (02-15-2013), MattK (03-20-2013), Melissa (03-12-2013), Mike4D5 (03-26-2015), mikeswoods (02-02-2013), Needles (02-17-2013), robertwcaldwell (04-13-2014), SC sawdaddy (03-16-2013), SclafaniBuilder (02-04-2013), SkylineEx (03-12-2016), SmallTownGuy (02-07-2013), SmallTownJoe (04-11-2017), Steve O'D (06-20-2014), The_Modifier (01-27-2013), Vision Custom (02-10-2014), Wesota (01-07-2017)

Warning: The topics covered on this site include activities in which there exists the potential for serious injury or death. ContractorTalk.com DOES NOT guarantee the accuracy or completeness of any information contained on this site. Always use proper safety precaution and reference reliable outside sources before attempting any construction or remodeling task!

   

Advertisement

 

Old 01-20-2013, 06:09 PM   #2
Registered User
 
mabius84's Avatar
 
Trade: Residential Construction
Join Date: Dec 2012
Posts: 6
Rewards Points: 10

Re: Estimating With Excel For The Small Contractor YouTube


Thanks cargin2 for posting these very helpful and I am going to use these can't wait to see the others. Thanks again

Advertisement

mabius84 is offline  
Old 01-20-2013, 07:03 PM   #3
Pro
 
rselectric1's Avatar
 
Trade: Licensed Electrical Contractor and Remodeler
Join Date: Sep 2009
Location: Chicago Suburbs
Posts: 11,539
Rewards Points: 534

Re: Estimating With Excel For The Small Contractor YouTube


Rich,

I am also self taught at Excel. I'm a math geek, so obviously I think it's the best program ever.

It was paining me to see you change the column width in the first video, but I saw in the second one that you got the "stretching" included.

I didn't know how to do the "freeze pane" function. I'll be using this all the time since my spreadsheets are pretty long. Thanks for the info.

I am going to make this thread a sticky in the business section. I believe your videos will really help a lot of contractors.

Thank you for the effort!
__________________
220...221...whatever it takes!
rselectric1 is offline  
   
 
Old 01-20-2013, 08:20 PM   #4
Talking Head
 
EthanB's Avatar
 
Trade: Poking Things With Sticks
Join Date: Sep 2011
Location: Rhode Island
Posts: 5,174
Rewards Points: 1,014

Re: Estimating With Excel For The Small Contractor YouTube


Thanks for sharing these Rich.

I tend to underbid projects so my spreadsheet bid takeoff is critical to prevent my natural tendencies from taking over. As you illustrate, the beauty of this kind of system is that you continue to add to, and improve, the sheet as you bid more projects and gain more historical data.

My current sheet is five tabs and allows me to accurately price almost any kind of renovation I'm capable of accomplishing.
__________________
Quote:
Originally Posted by Jaws View Post
Planer.....
EthanB is offline  
Old 01-20-2013, 08:46 PM   #5
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by rselectric1 View Post
Rich,

I am also self taught at Excel. I'm a math geek, so obviously I think it's the best program ever.

It was paining me to see you change the column width in the first video, but I saw in the second one that you got the "stretching" included.

I didn't know how to do the "freeze pane" function. I'll be using this all the time since my spreadsheets are pretty long. Thanks for the info.

I am going to make this thread a sticky in the business section. I believe your videos will really help a lot of contractors.

Thank you for the effort!
I am trying to do the videos as I learned to do Excel. That is why I put the totals at the bottom because that is how most guys would do it on paper.

There are always multiple tools of performing the same function. but I saw in the second one that you got the "stretching" included. I have been using spreadsheets for 8-10 years and I just learned that last week. LOL All this time and it was right there and I didn't even know it.

Like i said "I'm a slow learner"

I'm glad you liked the videos.

Rich
cargin2 is offline  
The Following User Says Thank You to cargin2 For This Useful Post:
rselectric1 (01-20-2013)
Old 01-20-2013, 09:28 PM   #6
Boondockian
 
Dustincoc's Avatar
 
Trade: Getting the job done
Join Date: Sep 2011
Location: Canton-Potsdam Area, NY
Posts: 1,478
Rewards Points: 1,018

Re: Estimating With Excel For The Small Contractor YouTube


Thanks

I usually end up making up an entirely new spreadsheet for each job although I usually also include ways to print off materials lists and such seperately and they are a lot more like an invoice than something for internal use.
Dustincoc is offline  
Old 01-20-2013, 11:16 PM   #7
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Re: Estimating With Excel For The Small Contractor YouTube


All
I edited the 1st post to add video #4

In this video I make the Bill Worksheet more readable, link the totals to the Estimate Worksheet and show profitability. I also add a side calculator for figuring tax on returns and show how to insert comments in the description of each day's activities.

Rich

Last edited by cargin2; 01-21-2013 at 02:10 PM.
cargin2 is offline  
Old 01-20-2013, 11:20 PM   #8
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by Dustincoc View Post
Thanks

I usually end up making up an entirely new spreadsheet for each job although I usually also include ways to print off materials lists and such seperately and they are a lot more like an invoice than something for internal use.
I have a worksheet template. All information about the job from estimate through the invoice is contained in that one file. When I am done with it then I store it in a folder marked Completed jobs. The more I can record about the job the better I can come up with some historical data on how long it takes our company to do a task like roofing or hanging sheetrock.

Rich
cargin2 is offline  
Old 01-21-2013, 11:53 AM   #9
Pro
 
Texas Wax's Avatar
 
Trade: Executor of special operations
Join Date: Jan 2012
Location: DFW
Posts: 3,451
Rewards Points: 626

Re: Estimating With Excel For The Small Contractor YouTube


Awesome ....

Thanks for taking the effort to share. Great to see info on how to do it getting out there.

Used spread sheets since 1988, lotus 123 in DOS .
Imho spreadsheets are the 'hammer' of the office.

Curious - What'd you use to record the tutorials? what was the most difficult part of doing it?
Texas Wax is offline  
Old 01-21-2013, 12:59 PM   #10
New Guy
 
dallasroofing's Avatar
 
Trade: Roofing
Join Date: Jan 2013
Posts: 21
Rewards Points: 10

Re: Estimating With Excel For The Small Contractor YouTube


Excellent post cargin
dallasroofing is offline  
The Following User Says Thank You to dallasroofing For This Useful Post:
cargin2 (01-21-2013)
Old 01-21-2013, 01:55 PM   #11
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by Texas Wax View Post
Awesome ....

Thanks for taking the effort to share. Great to see info on how to do it getting out there.

Used spread sheets since 1988, lotus 123 in DOS .
Imho spreadsheets are the 'hammer' of the office.

Curious - What'd you use to record the tutorials? what was the most difficult part of doing it?
I used Screen Cast-O_Matic


The hardest thing for me was getting tongue tied, or not having the right word. At one point I highlighted a materials list. About 19 rows. The count at the bottom said 13 and yet I knew it more than that. Excel was counting the number of rows that had content in them. I was trying to talk, think on my feet and think of the next move.

I kind of gave up on being perfect and just decided to wing it. I still usually build what ever I am going to do one time off camera and then do a recording. Then watch it to look for any serious flaws. Then publish.

The next video is going to be adding an invoice with logo and link it to the Bill Worksheet. I've got grand kids tonight and a meeting tomorrow night, so it will be a couple of days. I have already done the building of the invoice in a mock up.

I am glad you liked the videos.

Rich

Last edited by cargin2; 01-21-2013 at 01:57 PM.
cargin2 is offline  
Old 01-22-2013, 09:52 PM   #12
Greenside
 
smheidebrecht's Avatar
 
Trade: Gerenal Contractor
Join Date: Jul 2011
Location: Toronto ON
Posts: 16
Rewards Points: 10

Re: Estimating With Excel For The Small Contractor YouTube


I think your mark up should be before the sales tax, otherwise you lose 7% on your markup to sales tax
smheidebrecht is offline  
Old 01-22-2013, 11:33 PM   #13
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by smheidebrecht View Post
I think your mark up should be before the sales tax, otherwise you lose 7% on your markup to sales tax
Thanks, I'll consider the change.

Rich
cargin2 is offline  
Old 01-24-2013, 11:04 PM   #14
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by ezgc View Post
Great post Rich. For those that have been using Excel for years or are just trying Excel for the first time, I'm curious how it has worked out for you so far. What are the biggest pain points or weaknesses of using Excel that you've found?

I ask for two reasons. #1 - I am a programmer who has used Excel for many years and can most likely help with any issues folks are having, just ask! #2 - I am currently designing an estimate tool that would be web based and replace the need to use Excel like this. I would love to know where people have struggled so that my tool can address those concerns, and would be able to offer a free version of my tool when it is ready to those that ask
Ezgc

You have been on several forums (here and JLC) asking for input from contractors on estimating.

I have been using spreadsheets for maybe 10 years, starting with an old Apple then Works, Excel 2003 and now Excel 07. Most were rather primitive. The 1st couple of times I opened a spreadsheet I gave up in frustration because I had no idea what to do.

Over the last 4-5 years I have been on a quest to improve my basic platform. Of course I have to work and do regular office work so I can't just play Excel. I go to Excel forums and I don't have a clue what the folks are talking about. The reason I did the videos is because when I went on the Excel forums it seemed like I used Excel for a very different reason than most of those folks, and those of us in construction need Excel to estimate and bill for our work.

If Excel knowledge is a baseball I know enough to fill one stitch. Just enough to be dangerous. But I need it to stay organized. On paper and in file cabinets I am hopeless. With computer files I have a chance.

What I want in an estimating system is
  • low cost
  • all information about a job in one place
    • from the estimate
    my notes and the numbers I used to figure out the materials
    my estimate letter or bid proposal
    my job cost accounting ( what I call my Bill Worksheet, because I did not know the proper terminology)
    my invoice or invoices
  • a materials list (good luck keeping it current)
  • a list of customers names and addresses so I don't have to look it up every time
  • as much as possible I want to have just one entry do multiple things

Right now I am making a customer list and using Vlookup with my cover sheet, this information is used for the estimate letter and the invoice.

I have a master list of materials ( just some commonly used stuff) that I compiled out of multiple sheets. Roof worksheet, deck worksheet, bathroom, additions, basement, PEX and that sort of thing.

I am trying to have one master list instead of multiple lists and try to keep that list current. We do all manner of odd ball stuff and so I will always have to get quotes from the lumber yard.

I am a small operation myself and my 2 sons. They track their time with a time track app and e-mail it to me daily and monthly.
I would like on line access to that time tracking data.

At the end of the month I reconcile the hours they turn in on their apps ( it comes in a CSV excel file) with one I have already billed out or have entered into a BWS.

I would like a system that had one entry. Once I entered hours into my BWS for a particular job, then I would like to see those hours in my reconciliation. I hope that makes sense.

I like list based Excel worksheets. List most of the commonly used roofing materials or addition building materials. Same thing on the labor side. A list of the common steps involved in building an addition. The helps me to avoid writing the same set of steps each time and helps me to remember the things that need to be included.

This post is getting kind of long so I'll just shut up for a while.

Rich
cargin2 is offline  
Old 01-25-2013, 01:55 PM   #15
Pro
 
Milhaus's Avatar
 
Trade: Residential Remodeling
Join Date: Feb 2007
Location: Iowa
Posts: 378
Rewards Points: 254

Re: Estimating With Excel For The Small Contractor YouTube


Rich - thanks for posting this. It's always helpful to see how others put their numbers together. I've been using excel, self taught for the most part as well, for years. Amazing how many tricks I still have to learn. My main question is how to keep everything organized (estimates with actual job costs) but yet still be able to tie the actual job costs into one spreadsheet for P and L, etc. Do you have all of your jobs on one Worksheet or does each job get a worksheet?
Milhaus is offline  
Old 01-25-2013, 02:06 PM   #16
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by Milhaus View Post
Rich - thanks for posting this. It's always helpful to see how others put their numbers together. I've been using excel, self taught for the most part as well, for years. Amazing how many tricks I still have to learn. My main question is how to keep everything organized (estimates with actual job costs) but yet still be able to tie the actual job costs into one spreadsheet for P and L, etc. Do you have all of your jobs on one Worksheet or does each job get a worksheet?
Milhaus

Each job gets it own Workbook unless I am doing multiple jobs for the same person at the same time. Like kitchen remodel with attic insulation. The HO needs separate invoice to give the the utility company for a rebate. Then I have several invoices in the same workbook. If I do a job for them a year from now then that job will get a new workbook.

My main question is how to keep everything organized (estimates with actual job costs) but yet still be able to tie the actual job costs into one spreadsheet for P and L, etc

That is a long answer for later. And a problem: how do you track the time it takes to do remodeling tasks and keep it in a format that you can access? I am not there yet. I have it for a few things like stain and varnishing windows or roofing.

I reply more later.

Rich
cargin2 is offline  
Old 01-26-2013, 11:24 AM   #17
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by Milhaus View Post
Rich - thanks for posting this. It's always helpful to see how others put their numbers together. I've been using excel, self taught for the most part as well, for years. Amazing how many tricks I still have to learn. My main question is how to keep everything organized (estimates with actual job costs) but yet still be able to tie the actual job costs into one spreadsheet for P and L, etc. Do you have all of your jobs on one Worksheet or does each job get a worksheet?
I just uploaded these videos to YouTube

Video #5 I take an invoice from Microsoft's website and modify it for my needs.

Video #6 I make a few changes to the invoice and then make an Estimate letter. The Estimate letter gets linked to the Estimate worksheet cells. And the Invoice gets linked to the Bill Worksheet Cells.

Rich

Last edited by cargin2; 01-27-2013 at 04:53 PM.
cargin2 is offline  
Old 01-27-2013, 12:56 PM   #18
Pro
 
cargin2's Avatar
 
Trade: Small remodeling contractor
Join Date: Aug 2011
Location: LeMars, IA
Posts: 101
Rewards Points: 115

Re: Estimating With Excel For The Small Contractor YouTube


I just uploaded Video #7.

Video #7 I set up the cover sheet to control cells throughout other tabs. I give you a preview of my Estimating Worksheet to give you a view of where we are going with this. I make a mistake at the end of the video that will have to be corrected in #8.

The purpose of these videos is to teach the beginner. I am also trying to promote some discussion on how we estimate and how we run our business. Most of us contractors live and work in isolation. We don't talk with our local compeitors about how much we charge or what our markup is. Hopefully I can learn from you and your comments. I am learning all along the way with this series of videos.

Some things I do in Excel or in business I can be done better. But sometimes I I don't know enough to know what question to ask. The more I share and talk with others the more I can learn.

In Video #8 I talk about my mistake in #7, resolve the conflict with naming cells. Then I build an estimate with a 2nd workbook called Materials List Master. They I walk you through a job I did recently, from estimate through invoice. I make the invoice into a PDF and e-mail it. Then I store the invoice and the workbook in folders.

Rich

Last edited by cargin2; 01-27-2013 at 04:50 PM.
cargin2 is offline  
Old 02-02-2013, 05:58 PM   #19
Registered User
 
ukdrywall's Avatar
 
Trade: drywall
Join Date: Feb 2007
Posts: 9
Rewards Points: 10

Re: Estimating With Excel For The Small Contractor YouTube


great work Richard
ukdrywall is offline  
Old 02-02-2013, 07:07 PM   #20
Pro
 
AutumnWood Inc.'s Avatar
 
Trade: Deck Builder
Join Date: Aug 2007
Location: Metro Detroit
Posts: 748
Rewards Points: 550

Re: Estimating With Excel For The Small Contractor YouTube


I have to say the same great thread very helpful ... Thank you ! I wonder if you use Excel to provide the proposal to the client ?

Advertisement

AutumnWood Inc. is offline  


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Made in USA product list J.C. General Discussion 339 11-23-2017 10:15 AM
Excel material list festerized File Swap 4 08-14-2012 11:31 AM
Billing on small jobs Philly Dude Business 14 11-16-2008 04:25 PM
Estimating Article Series hatchet Business 15 07-15-2004 12:47 PM

Join Now... It's Fast and FREE!

I am a professional contractor
I am a DIY Homeowner
Drywall Talk is for
PROFESSIONAL CONTRACTORS ONLY!

At DrywallTalk.com we cater exlusivly to professional contractors who make their living as a contractor. Knowing that many homeowners and DIYers are looking for a community to call home, we've created www.DIYChatroom.com DIY Chatroom is full of helpful advices and perfect for DIY homeowners.

Redirecing in 10 seconds
No Thanks
terms of service

Already Have an Account?