Estimating With Excel For The Small Contractor YouTube

 
Thread Tools Search this Thread Display Modes
Old 03-22-2014, 07:58 PM   #81
I'm The BOSS
 
jaydee's Avatar
 
Trade: builder / remodeler
Join Date: Mar 2014
Location: Spencer, Ma
Posts: 1,904
Rewards Points: 4,733

Re: Estimating With Excel For The Small Contractor YouTube


here's a plank template I use.


Attachment 109917

Last edited by jaydee; 05-02-2014 at 04:59 PM.
jaydee is offline  
The Following User Says Thank You to jaydee For This Useful Post:
bartstop (06-27-2015)

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 03-22-2014, 08:10 PM   #82
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


jaydee

Cool. Thanks for posting that. I have a few things to do around the house tonight. I will look at in detail later.

Rich
cargin2 is offline  
Old 03-22-2014, 08:19 PM   #83
I'm The BOSS
 
jaydee's Avatar
 
Trade: builder / remodeler
Join Date: Mar 2014
Location: Spencer, Ma
Posts: 1,904
Rewards Points: 4,733

Re: Estimating With Excel For The Small Contractor YouTube


That's the stripped version.

I have them for decks, roofing, siding and others.
I save each one as a template, roof, siding ect.ect.

have the basic materials in each one.

if I don't use that item I keep the quantity a zero.

If I use a different material, I add that to the template along with the price. I'll recheck the price to verify it.

hope it helps whoever needs it.

Suggestions or additions are welcome.

Jeff
jaydee is offline  
   
 
Old 03-22-2014, 10:41 PM   #84
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


Jeff

Could you show us one of your worksheets, say for roofing.

Maybe a small jobs worksheet that you have actually used to estimate a job with the name left blank?

What do you use the code for in column A?

I like the line for hand holding.

Rich
cargin2 is offline  
Old 03-23-2014, 01:11 AM   #85
I'm The BOSS
 
jaydee's Avatar
 
Trade: builder / remodeler
Join Date: Mar 2014
Location: Spencer, Ma
Posts: 1,904
Rewards Points: 4,733

Re: Estimating With Excel For The Small Contractor YouTube


roof =109922[/ATTACH]

Last edited by jaydee; 05-02-2014 at 04:59 PM.
jaydee is offline  
Old 03-23-2014, 01:13 AM   #86
I'm The BOSS
 
jaydee's Avatar
 
Trade: builder / remodeler
Join Date: Mar 2014
Location: Spencer, Ma
Posts: 1,904
Rewards Points: 4,733

Re: Estimating With Excel For The Small Contractor YouTube


siding

Attachment 109923 vinyl

Attachment 109924 cement

Last edited by jaydee; 05-02-2014 at 04:59 PM.
jaydee is offline  
Old 03-23-2014, 09:15 AM   #87
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


Jaydee

You asked for additions or suggestions.
For starters I would name the cells that control other cells.
For instance when I look at the line in the roofing worksheet for sales tax M56 I see this formula =G54*$D$11.

If you name you cells then it could look like this Sales_Tax*Sub_Total.

Also when you start to use fill if you refer to a named cell then it stays on that cell. For instance H15*E9 (labor rate). When you want to use fill with that formula Excel will make the next cell H16*E10.

What you want is H15*Labor_Rate. Then using fill it will read H16*Labor_Rate.

You also repeat some information like permit fee, sales tax and overhead. The control cells are in the header and they are used down below. It may be better to put the control cells down below or reorganize where you do your totals.

I am not being critical. My sheets have improved when I have let others tell me how confusing my sheets were to someone else. When I come back to estimates I wrote several years ago I wonder what was I thinking?

I will look at it again this evening.

Rich
cargin2 is offline  
Old 03-23-2014, 10:23 AM   #88
I'm The BOSS
 
jaydee's Avatar
 
Trade: builder / remodeler
Join Date: Mar 2014
Location: Spencer, Ma
Posts: 1,904
Rewards Points: 4,733

Re: Estimating With Excel For The Small Contractor YouTube


I understand some of that.

what is the importance of naming the cell, ( sales tax on materials for example )
that is named on the beginning of that line, does it serve another purpose.

Quote:
What you want is H15*Labor_Rate. Then using fill it will read H16*Labor_Rate
. again is the purpose for tracking or visual ?

I just saw your u-tube series and will be trying the tracking part. want to share ?
jaydee is offline  
Old 03-23-2014, 03:16 PM   #89
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


jaydee

Naming cells, I had a hard time understanding the need for that too.

what is the importance of naming the cell, ( sales tax on materials for example )
that is named on the beginning of that line, does it serve another purpose.

When I look at your sheet the control cells are at the top. let's say for some reason the formula gets screwed up in the sales tax or in the overhead cells down at the bottom of the sheet. You open it up and it will say =G54*$D$11. Ok I know what G54 is because it is right above the cell in question (G55), but what is D11. Scroll back to the top to find the answer. Oh, Sales tax.

This not a big problem if you have a single sheet worksheet with 50 lines. But when the control cell is in another tab or is 200 lines away then it is a problem.

It is much cleaner to look at the cell that says =G54*mark_up. Then I know right away that my formula's got mixed up . Maybe I added a cell and it moved things on me.

Go to Video #2 at 6:50 to see how naming a control cell will help you with fill.

I hope this makes sense to you.

Rich
cargin2 is offline  
Old 03-23-2014, 03:27 PM   #90
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


jaydee

Do I have your permission to alter one of your worksheets and then post it, just as a learning exercise?

The reason I made the videos was so that other guys would have the tools to build their own worksheets, not like mine, but built around the way that they think.

I think an Excel WS is best if you know how it was built and it functions the way that you think.

Rich
cargin2 is offline  
Old 03-23-2014, 03:32 PM   #91
I'm The BOSS
 
jaydee's Avatar
 
Trade: builder / remodeler
Join Date: Mar 2014
Location: Spencer, Ma
Posts: 1,904
Rewards Points: 4,733

Re: Estimating With Excel For The Small Contractor YouTube


alter away
jaydee is offline  
Old 03-23-2014, 09:43 PM   #92
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


jaydee

Here is my 1st set of alterations. I want to get some joint compound on my kitchen this evening so I am going to post this now. I have other alterations I will do later.

My goal is to get all the information I need about an estimate in front of me one sheet if possible. On small jobs that is possible. On larger jobs that is not possible.

Therefore when I looked at the roof worksheet I tried to eliminate repetitive cells. So I moved the sales tax, mark up and management to the bottom where those control cells are used to figure the total job price.
I got rid of the name of the sheet, because it is already at the top of Excel and this WS is mostly for internal use.

I will post further alterations later.

Rich
Attached Files
File Type: xls jaydee ROOF estimate2.xls (30.0 KB, 254 views)
cargin2 is offline  
Old 03-23-2014, 11:16 PM   #93
Pro
 
bbgcarpentry's Avatar
 
Trade: carpenter
Join Date: Apr 2009
Location: Toronto
Posts: 543
Rewards Points: 500

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by jaydee
I started with a basic template then mad ones for roofing, decking, siding and others. save them as a template and us as a master list. Then, when doing one for estimate "save as" under customers name for example jones=deck or smith remodel. it allows you to have specific material list's for those types of jobs already included with the pricing already there, anyone interested forward your email or contact info. the info on here has help me, so we all should share
bbgcarpentry@gmail.com

I'm excited I found this thread I was just starting to research how I could systemise my pricing and make my life a little easier.send me what you have and I send you my stuff when I get everything in order.

Thanks

John
bbgcarpentry is offline  
Old 03-23-2014, 11:21 PM   #94
Pro
 
bbgcarpentry's Avatar
 
Trade: carpenter
Join Date: Apr 2009
Location: Toronto
Posts: 543
Rewards Points: 500

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by cargin2
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
Wow there are some good people on this site thanks
bbgcarpentry is offline  
Old 03-23-2014, 11:33 PM   #95
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


bbg

You still have to watch the videos and build your own system. It can be very rewarding.
I did the videos so that folks would have the tools to build their own worksheets.
Rich
cargin2 is offline  
Old 03-24-2014, 10:21 PM   #96
Pro
 
bbgcarpentry's Avatar
 
Trade: carpenter
Join Date: Apr 2009
Location: Toronto
Posts: 543
Rewards Points: 500

Re: Estimating With Excel For The Small Contractor YouTube


Quote:
Originally Posted by cargin2
bbg You still have to watch the videos and build your own system. It can be very rewarding. I did the videos so that folks would have the tools to build their own worksheets. Rich
i tried to watch it and it crashed my lap top now it has a virus and is f......d.it was a old lap top but be careful anyone tying to watch it
bbgcarpentry is offline  
Old 03-24-2014, 11:17 PM   #97
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


jaydee

I reworked the roofing worksheet. I put the totals at the top of the page. With Excel we are not limited to doing things like we did with a notebook. We can put the totals anywhere we want to. By putting the totals at the top then you can an infinite number of rows below to do the estimate with.

With Excel the goal is to make the WS do as much work as possible. Put the calculator away. So I put some SF calculators at the top of the sheet and and used the totals to fill in the materials automatically.

The yellow cells require input from the user. The beige cells are controlled by the data you enter in the yellow cells.

I don't think you can get a virus from a YouTube video. You can get a virus from opening an Excel sheet that is posted here. A good antivirus won't let you open a file that has a virus in it.

Rich
Attached Files
File Type: xls jaydee ROOF estimate3.xls (33.5 KB, 259 views)
cargin2 is offline  
Old 03-25-2014, 08:52 AM   #98
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


jaydee

I made a few minor changes to the Roofing worksheet. I forgot to put color in a few cells.

I took the terminology (SF=Square Foot stuff) and since it is not vital information I put it in a Comment in Cell D20 (Unit). There is a red corner in the cell, hover over it and you will see a box open up.

I eliminated the estimate by line because you already know who did the estimate and most WS are for internal use. Now in a big company you can use that information.

I also inserted a hyperlink in Certainteed shingles. That can be very helpful when you begin to send out estimates by e-mail. You can show the customer what products you are using.

Hyperlinks can be useful on bathroom estimates when you are looking for items like shower faucets and lights at a big box store. Once you estimate the job and get the job then you can go back and find item that you quoted.

I am going to post this Worksheet as a Dropbox link . That way it is easier to edit or delete if I want to.

https://www.dropbox.com/s/jy3k88hxb9...0estimate3.xls

I am not saying that this worksheet is without errors. I took the uploaded roof worksheet and tweeked it just for the purpose of discussion.

Rich

Last edited by cargin2; 03-25-2014 at 08:55 AM.
cargin2 is offline  
Old 03-25-2014, 09:48 PM   #99
Pro
 
bbgcarpentry's Avatar
 
Trade: carpenter
Join Date: Apr 2009
Location: Toronto
Posts: 543
Rewards Points: 500

Re: Estimating With Excel For The Small Contractor YouTube


[QUOTE="cargin2"]jaydee I made a few minor changes to the Roofing worksheet. I forgot to put color in a few cells. I took the terminology (SF=Square Foot stuff) and since it is not vital information I put it in a Comment in Cell D20 (Unit). There is a red corner in the cell, hover over it and you will see a box open up. I eliminated the estimate by line because you already know who did the estimate and most WS are for internal use. Now in a big company you can use that information. I also inserted a hyperlink in Certainteed shingles. That can be very helpful when you begin to send out estimates by e-mail. You can show the customer what products you are using. Hyperlinks can be useful on bathroom estimates when you are looking for items like shower faucets and lights at a big box store. Once you estimate the job and get the job then you can go back and find item that you quoted. I am going to post this Worksheet as a Dropbox link . That way it is easier to edit or delete if I want to. https://www.dropbox.com/s/jy3k88hxb9...0estimate3.xls I am not saying that this worksheet is without errors. I took the uploaded roof worksheet and tweeked it just for the purpose of discussion. Rich[/QUOTE




How about quickbooks would that work for estimating some one told me it's what they use for there deck pricing
bbgcarpentry is offline  
Old 03-25-2014, 10:32 PM   #100
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


bbg

I do not have Quickbooks, I do have Quicken. And yes I can write up an estimate and an invoice in Quicken.

You still have to do a lot of calculator work to figure the estimate and then you can write it up in Quicken. I was not impressed by the estimate that I created and then I e-mailed to myself.

I did not try the invoice. To be fair I only spent 10 minutes on it. I suppose if I spent some more time developing it it may look better.

It will store your material prices for you and I suppose it would work if you had a limited number of items to keep track of. Like in roofing or vinyl siding.

Rich

Advertisement

cargin2 is offline  
The Following User Says Thank You to cargin2 For This Useful Post:
bbgcarpentry (03-26-2014)


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 338 10-24-2017 12:02 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?