# Sticky  Estimating with Excel for the Small Contractor YouTube



## 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


----------



## mabius84

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


----------



## rselectric1

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! :thumbsup:


----------



## EthanB

Thanks for sharing these Rich. :thumbsup::thumbsup:

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.


----------



## cargin2

rselectric1 said:


> 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! :thumbsup:


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


----------



## Dustincoc

Thanks:thumbup:

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.


----------



## cargin2

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


----------



## cargin2

Dustincoc said:


> Thanks:thumbup:
> 
> 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


----------



## Texas Wax

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?


----------



## dallasroofing

Excellent post cargin


----------



## cargin2

Texas Wax said:


> 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


----------



## smheidebrecht

I think your mark up should be before the sales tax, otherwise you lose 7% on your markup to sales tax


----------



## cargin2

smheidebrecht said:


> 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

ezgc said:


> 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


----------



## Milhaus

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?


----------



## cargin2

Milhaus said:


> 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

Milhaus said:


> 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


----------



## cargin2

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


----------



## ukdrywall

great work Richard


----------



## AutumnWood Inc.

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 ?


----------



## AutumnWood Inc.

I have my Excel sheets, workbook set up .. one quick question.

How do you save your customers in the workbook ? Do you do one work book for each customer ... or divide them up or store them in one work book - if that makes sense ?

Thank you very much for posting these topic/videos I have wanted to do this for a long time but this was enough help to actually get it accomplished !!! :thumbup:


----------



## cargin2

AutumnWood Inc. said:


> I have my Excel sheets, workbook set up .. one quick question.
> 
> How do you save your customers in the workbook ? Do you do one work book for each customer ... or divide them up or store them in one work book - if that makes sense ?
> 
> Thank you very much for posting these topic/videos I have wanted to do this for a long time but this was enough help to actually get it accomplished !!! :thumbup:


Autumn

Let's be clear on the terminology. ( For a long time I wasn't because I just worked on Excel by myself and I didn't need to know.)

A workbook is what you open when you start Excel, the tabs at the bottom are worksheets within a workbook. My basic worksheets are Cover, EWS, Est letter, BWS, Invoice. 

What I built in the videos is a workbook template.

I open that and start one for each customer or each job. At then end of the sesssion I use Save As to store it as a seperate workbook.

Now if customers want to remodel the bath and build a deck at the same time I will usually keep that in one workbook. If they build a deck today and remodel a bath a year from now then it is 2 workbooks.

I try to keep all the information on a job in one workbook. Sometimes they want a price on replacing windows, building a deck and replacing an entry door. I will keep those in one workbook but have 3 EWS and 3 Est letters. Then usually 3 BWS and 3 Invoices. They may elect to do 1 or all 3. Time frame is the deciding factor on one workbook or multiple.

I do not keep PDF quotes in the workbook but I can if I choose. A topic for another video. Most PDF quotes are keep in my e-mail folders. Ihave a folder for each supplier.

So my Construction folder (in Drop Box ) has about 10 -15 active workbooks in it. Once a job is completed then it gets stored in a subfolder called completed jobs. A PDF of the invoice is stored in Invoice PDF.

Jobs were I have sent an estimate to and I have given up hope of getting the job are stored in Estimates out.

So in answer to your question I have a workbook for each job. 

I would not try to have a worksheet for each job. The workbook would become too complicated. With a workbook for each job even in multiple folders all you have to do is do a search of your construction folder to get every document you ever created about customer X, back until the creation of your system.

Thank you very much for posting these topic/videos I have wanted to do this for a long time but this was enough help to actually get it accomplished !!!

You are welcome. I am glad you were helped by my videos. Sometimes I felt like I did not do a good job of explaining or I just made stupid mistakes.

I have been busy with work, home projects and life. Right now I am on a hospital public computer because i have spent the seekend with my son Timothy in the hospital because he has a touch of pneumoia.

I will finish the roofing workbook and make a basic video, but right now I am too busy.

Rich


----------



## builder2345

Rich, thanks for much for this video info on excel. I've only watched the first video so far, but it has helped quite a bit!!! Can't wait to watch the rest!


----------



## cargin2

builder2345 said:


> Rich, thanks for much for this video info on excel. I've only watched the first video so far, but it has helped quite a bit!!! Can't wait to watch the rest!


You are Welcome

Rich


----------



## MattK

Rich, Thanks for the videos. I've watched the first 3 and modeled my sheet according to what you have. One question(you might have answered it in a later video)...whats the point of charging sales tax to labor? Rhode Island is also 7% sales tax but wouldn't it make sense to have an area for workers comp % on every dollar spent or would you put that in another area all together?

Again, sorry if this question is answered already. I'm going to start vid #4 right now.

Matt


----------



## cargin2

MattK said:


> Rich, Thanks for the videos. I've watched the first 3 and modeled my sheet according to what you have. One question(you might have answered it in a later video)...whats the point of charging sales tax to labor? Rhode Island is also 7% sales tax but wouldn't it make sense to have an area for workers comp % on every dollar spent or would you put that in another area all together?
> 
> Again, sorry if this question is answered already. I'm going to start vid #4 right now.
> 
> Matt


Matt

In Iowa new construction and remodeling is sales tax exempt. Repairs are not exempt. If I replace a whole roof it is exempt. If I repair 1 shingle then the labor is taxable.

We are all owners of the company so we do not have workman's comp.

Rich


----------



## MattK

cargin2 said:


> Matt
> 
> In Iowa new construction and remodeling is sales tax exempt. Repairs are not exempt. If I replace a whole roof it is exempt. If I repair 1 shingle then the labor is taxable.
> 
> We are all owners of the company so we do not have workman's comp.
> 
> Rich


I see. In RI, I(the business owner) am exempt from Worker's Comp but I need to carry it for my employees. I know in other states the owner is not exempt.


----------



## AutumnWood Inc.

I again want to thank you Rich for posting this thread and doing all the YouTube videos ! This has helped us a ton !


----------



## cargin2

AutumnWood Inc. said:


> I again want to thank you Rich for posting this thread and doing all the YouTube videos ! This has helped us a ton !


Autumn

You are welcome. Do you have a workbook designed and usable?

I have not finished the videos on making a roofing worksheet. My MIL died and we had the funeral stuff to do. My son and his wife had a baby a week ago, we are remodeling the laundry room/bathroom and business stuff keeps me busy. So I haven't added any new video's to the series.

I am glad you found the videos helpful. A well organized Excel workbook is a real time saver, as well as keeping you organized. On paper and a file cabinet I would be a hopeless mess. On the computer I can keep things organized.

Rich


----------



## rselectric1

cargin2 said:


> My MIL died and we had the funeral stuff to do. My son and his wife had a baby a week ago,


Rich, Sorry and Congratulations at the same time buddy.


----------



## cargin2

rselectric1 said:


> Rich, Sorry and Congratulations at the same time buddy.


She was 82 and had Alzheimer's. It was time for her to go. I'm the picture guy and the guy who can write the obituary so I had a lot of little things to do.

Here is my grand daughter (from my other son) holding the newest member of the family.

Rich


----------



## ASInsulation

Congrats!

And the videos are much appreciated. Learning quite a bit.


----------



## AutumnWood Inc.

Sorry about your loss, and congrats on the grand baby !


Yes ... we have a full running workbook with everything you went over.

I was using Word for my proposal's now I use Excel for everything !


We have a huge material sheet with all the Timber Tech, Trex, Fiberon, Treated, Cedar, Misc. that was the biggest challenge to do !

What is so nice is the accurate quotes I can put together now ... when it is this time of the season we are slammed busy with bidding on projects... In the past I would just quote by the square foot on decks because it would take to long to run the numbers on everything.


This not only speeds up everything but it is way more accurate. Which has helped us bring better pricing for our customers and we have already gained more work from it !

We are now just starting the job tracking.

Thanks again ... I feel like I need to send you a gift card or something ... this thread was the help we needed to get this done !


----------



## cargin2

AutumnWood Inc. said:


> Sorry about your loss, and congrats on the grand baby !
> 
> 
> Yes ... we have a full running workbook with everything you went over.
> 
> I was using Word for my proposal's now I use Excel for everything !
> 
> 
> We have a huge material sheet with all the Timber Tech, Trex, Fiberon, Treated, Cedar, Misc. that was the biggest challenge to do !
> 
> What is so nice is the accurate quotes I can put together now ... when it is this time of the season we are slammed busy with bidding on projects... In the past I would just quote by the square foot on decks because it would take to long to run the numbers on everything.
> 
> 
> This not only speeds up everything but it is way more accurate. Which has helped us bring better pricing for our customers and we have already gained more work from it !
> 
> We are now just starting the job tracking.
> 
> Thanks again ... I feel like I need to send you a gift card or something ... this thread was the help we needed to get this done !


That's way cool. You built the worksheet yourself. Now you understand it and how to make it work. Or improve it. Congratulations.

Your success is reward enough.

Rich


----------



## cargin2

AutumnWood Inc. said:


> Sorry about your loss, and congrats on the grand baby !
> 
> 
> Yes ... we have a full running workbook with everything you went over.
> 
> I was using Word for my proposal's now I use Excel for everything !
> 
> 
> We have a huge material sheet with all the Timber Tech, Trex, Fiberon, Treated, Cedar, Misc. that was the biggest challenge to do !
> 
> What is so nice is the accurate quotes I can put together now ... when it is this time of the season we are slammed busy with bidding on projects... In the past I would just quote by the square foot on decks because it would take to long to run the numbers on everything.
> 
> 
> This not only speeds up everything but it is way more accurate. Which has helped us bring better pricing for our customers and we have already gained more work from it !
> 
> We are now just starting the job tracking.
> 
> Thanks again ... I feel like I need to send you a gift card or something ... this thread was the help we needed to get this done !


I was curious how things are going with your new system. Have you continued to use it? Do you find it helpful in keeping track of estimates and invoices?

Rich


----------



## D.E.P.S.

Thanks for posting all the great info* Rich*. I used to use Excel a lot when I was a estimator but got away form it over the past 5 years. I have been using it strictly for estimates and Invoices lately. The videos have reminded me again that I am wasting some valuable tools that the program provides.


----------



## cargin2

I am glad you found the videos useful. 

I do all my thinking in Excel. I just have to get my thoughts on the screen and save them somewhere.

I save links and price quotes when I am working up an estimate so that I can find them later.

Rich


----------



## jaydee

rich, 
great job on videos

I have started using it a few years back myself.

handed down version from a friend,

you have showed us / me some really good tricks.
it's always nice to see someone else's ideas

mine also has line items:
for permit fee's, calculated off job total
project management & overhead
and a few others

would like to share it with you and everyone else if wanted.

don't really know how to post it though

Jeff & jd


----------



## jaydee

*saving as templates*

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


----------



## cargin2

jaydee

I am glad you liked the videos. I would love to see your spreadsheet.

Go to the last post and in the bottom left corner hit reply.
Then look for the paper clip at the top of the reply box, click on that and it should open up the places you keep files on your computer. then choose the file you want and upload it.

Rich


----------



## jaydee

here's a plank template I use.


View attachment 109917


----------



## cargin2

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


----------



## jaydee

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 :thumbsup:


----------



## cargin2

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


----------



## jaydee

roof =109922[/ATTACH]


----------



## jaydee

siding

View attachment 109923
vinyl

View attachment 109924
cement


----------



## cargin2

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


----------



## jaydee

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.



> 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 ?


----------



## cargin2

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

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


----------



## jaydee

alter away


----------



## cargin2

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


----------



## bbgcarpentry

jaydee said:


> 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


[email protected]

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

cargin2 said:


> 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


----------



## 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


----------



## bbgcarpentry

cargin2 said:


> 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


----------



## cargin2

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


----------



## 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/jy3k88hxb9bm37j/jaydee%20ROOF%20estimate3.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


----------



## bbgcarpentry

cargin2 said:


> 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/jy3k88hxb9bm37j/jaydee%20ROOF%20estimate3.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


----------



## cargin2

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


----------



## roofing4ever

I use the svg estimator program, I used quickbooks before that and some excel thing before. I will never go back and I got a free book with purchase which was pretty awesome


----------



## BMiller

Thanks for the resources!


----------



## cargin2

BMiller said:


> Thanks for the resources!


You are welcome.
Rich


----------



## NJProBuilders

*Formal Proposal*

Hello! Thank You for posting this great information!! I was wondering if you use a spread sheet for formal proposals for the homeowners? If so which program do you use?


----------



## cargin2

NJProBuilders said:


> Hello! Thank You for posting this great information!! I was wondering if you use a spread sheet for formal proposals for the homeowners? If so which program do you use?


Yes, I use Excel for Estimates and Invoices. My estimates are fairly simple. I try to get the information on one page.

I explain how I did this in video #5 and 6.

Rich


----------



## Builders Inc.

Last weekend I started at 8 pm and got hooked till 4:45 Sunday morning recreating your sheet from the you tube videos. I had to play with your mark-up to make it work with mine. 

I would like to add the proposed profit percentage (sales price) and break it down into 1% increments for those jobs where the customer is a stickler. I fluctuate between 10 and 20% depending on how the bidding is going and how sales are going for the month. I also like to know my break even figure before I bid also. 

SO as for this mark-up figuring... lets say hypothetically if your overhead is 14% and your profit is 15% then your markup would be 29% or 71% using your sheet? And why dont you add labor and materials and make that your manufacturing cost and add markup for overhead+profit all in one formula. 

Like manufacturing cost * (mark-up formula) for the longest I would (in the example) if your O.H. was 14% and profit was 15% then I would add them together to get 29. Then subtract 29 from 100% leaving 71% then divide 100 by 71 = 1.4084507. and that is what I would use to mark-up manufacturing cost to get a 15% profit. (1.4084507) I think your sheets are similar. Do you see a flaw in how I was doing it? Am I missing something?


----------



## Builders Inc.

Manufacturing cost would be materials and labor and payroll taxes to create said project. Overhead is all the other stuff.


----------



## sbskeeper

*Live Imported Internet Data*

Hello all,

First of all, I love this so much. Reading as much as I can before I start building my final calculator in excel and I came across a feature where you can import live internet data in real time, 30 minute increments, etc. More details below.

http://www.groovypost.com/howto/microsoft/use-online-data-in-excel-2010-and-2007-spreadsheets/

I know its not the best source for materials, but just as a ballpark, I want to try to use Home Depot lumber prices as an average of what to base estimates on. I'm running into a problem where it wants to import too much data that has no relevance to me. Have the same problem anytime I try to use Lowes, etc. I'm working on trying to automate this.

Has anyone else used this sort of feature successfully?

Thanks in advance for any input. :clap:


----------



## Unreal Elect

Brilliant resources. By far the biggest mistakes I made early on was not understanding all the different costs, you have to include everything. I even include in the backend my time for creating the estimates.


----------



## TravisD

Awesome Stuff, really helpful.. Thanks

http://dmccustomcreations.com


----------



## PennCoat

Wow, great post. We use a similar system. But what I appreciate most about the method is that it's easier justifying your cost to a customer.


----------



## KaplanB

*Excel estimate sheets formatted for you so you can focus on your business*

Hi All,

I’ve been reading through this thread, and it’s been very insightful to see what many of you have been challenged by in running your own businesses. The concept of estimating is not an exact science as we all know, and a lot of this comes with experience in the business. Having had my own owner/operator carpentry/general contracting firm for several years, as well as my current role as the GM of a medium sized construction company, I can certainly relate.

My path to success started with several setbacks due to not knowing what I was doing with pricing projects, and not tracking financial progress at all. When I say that out loud it sounds even worse then it does in my head! I’ve come a very long way since then, and it’s time for me to give back to the community. Over the years I’ve developed a set of spreadsheets, and an innate ability to create these based on specific parameters given to me. I’d like to share these with you, but as a first step, I am interested to learn how best I can serve this audience.

As mentioned above, my experience is both as a skilled and licensed/certified carpenter as well as a licensed general contractor. As I’ve progressed through my career I learned that it makes much more sense to focus on what I am good at, and if I can use someone else’s system (in this case Excel sheets), then it will help me leverage more time for the other more important tasks I can’t hire someone to do. 

Folks, please respond here and let me know if you’d be interested in receiving a specialized estimating document formatted for you in Excel. When you do respond, please answer the following questions which will best help describe what your needs are for me :

* Occupation (ie: what trade are you in)
* Geographical location
* Pricing structure (cost/plus or fixed/cost)
* Do you need labour, materials, and sub-contract sections
* Please share if there are any specific items you’d like to see 

If you also feel that this would not be a useful thing to obtain, then please share your feedback with me as well!

Thanks!


----------



## Carol Barns

This is awesome stuff. Great information. Thank you


----------



## cargin2

You are welcome.
The more information that I can keep in my Bill Worksheet, like how long it takes to do a project, or a portion of a project, the better I am able to estimate future jobs.
When I get home at night, the 1st thing i do after supper is to do hours and invoices.
Myself and my son keep our hours with a time tracking app and most invoices are e-mailed to me.
Most of our jobs are small jobs with unique materials. Not the kind of stuff you would keep a data base of.
And even if I could keep a data base the prices are always changing.
Thanks again.
Rich


----------



## SmallTownJoe

*Awesome Post*

cargin2, great. I showed this to my buddy who just headed out on his own to start his GC business. It helped him so much. :thumbsup:
He is struggling to figure out some of the business aspects of GCing.
Hell, I'll just ask. Could you take a 30 min phone call with me to discuss how you run your business? Because you seem to have good knowledge, It would certainly benefit us.

Thanks for a great post, regardless. :thumbup::clap:


----------



## cargin2

Thanks for the reply.
Yes I would take a phone call.
No, I am not the best business guy to talk with.
I have learned by making mistakes.
I have 30+ years in the community i work in so I can do some things on reputation. Like doing T&M jobs.

Rich


----------



## SmallTownJoe

Is the # on your profile the best way to reach you? Is there a good time to call?


----------



## cargin2

That is my home phone.
I could possibly talk later this evening. I have a birthday party for a grand child and I want to mow the yard tonight. Maybe after 9 pm CT.

e-mail me later this evening and I will let you know what time works best.
rich cargin at g mail .com
all one word of course i just jumbled it to keep bots from finding it


----------



## SmallTownJoe

I will certainly email you! Thanks so much Rich!


----------



## Dewaldt

I played it at 1.5 speed.


----------



## heating_chicago

Thanks!

Sent from my SM-J100VPP using Tapatalk


----------



## cargin2

You are welcome.


----------



## MDdecks

*Thank you*

I am a newbie to contractortalk.com but an experienced tradesman for over 15 years. In the past 4 years I made the move to owner/ operator. I am greatful for this platform and for posts like yours specifically because they are tremendously helpful. As anyone knows, how hard staring out can be, it can also be challenging because there’s only 24 hrs in a day and not a second more. To sum it up thanks for helping me utilize a dormant program in my pc that can potentially help me be more efficient and successful in my business.:thumbsup:


----------



## carpenter uk

Excel is a great program and non of the purpose made estimating programs come close for flexibility or price. I use planswift for take off and quickbooks for accounts, excel is the link between the 2 to organise and track the data. Ive wasted thousands on different all in one solutions and non of them worked for me, maybe they would if you had a rigid way of working but im a subcontractor so have to be flexible to the main contractors format. If you struggle putting a sheet together or want something very complicated to set up try people per hour. Ive had a couple written with vba code and it only cost me £80-£100 each but make sure you know exactly what you want first or you will be paying for re writes when you realise after a few weeks its not how you wanted it.


----------



## Seasons

Thanks for the great advice on how to use excel in this business. Very helpful! Especially the videos.

____________________________
Seasons Contracting LTD
Vancouver BC
www.seasonscontractingltd.com


----------



## VinylHanger

Are you going to spam every thread on here?

Sent from my SM-N975U using Tapatalk


----------



## ChrWright

LOL... On the upside a resurrected zombie thread popped up in my email this morning and got me back on the forum. :laughing:


----------



## tgeb

Well then, let me be the first to say.....Hi Chris, nice to see you again!


----------



## Jimea

Thank you Rich for the videos.

If you run an online renovation brokerage platform or a specialized marketplace in personalized assistance of renovation and architectural projects, I recommend you this onboarding form example. It can boost your website traffic, in addition to qualified leads it will provide you. Check out the first 3 CTA :

https://www.hemea.com/fr/renovation/appartement/paris


----------



## I.A Builder

Where do you enter your O/H?


----------



## peterbivens2434

These videos are very helpful. I watched all videos of this guy it helps a lot.


----------



## TriadRenovation

Thanks Cargin. I will definately watch the videos


----------

