Greetings
I need a more systematic approach to estimating my projects. I currently try to break the project into tasks and add in my materials etc. It’s very rough often wrong and I lose money. I am open to all suggestions and I also own a MAC book which if I find the write software I would also consider. Most of my projects are decks and porches with an occasional renovation.
thanks
Handy Dan
Replies
When you're wrong, is it on labor? On materials? Both?
Wrong on labor (too low) wrong on materials (too little) Handy Dan
Materials: you are either not figuring them accurately, which is a function of time spent on the estimate, and your experience with the work... OR... you are allowing extra materials to creep in as the job expands and failing to charge extra for them.
Labor: same as above. It is almost universal to be optimistic. You need to stop that if you are doing it. Maybe you're one of those guys that doesn't want to charge more because "it's not fair to the customer". Or, you don't have enough experience with the various jobs to hit the labor accurately. Are you estimating in hours? Don't do that, do it in days or weeks. Round up.
You may be a good candidate for time and materials billing. If you do that, don't give the customer an estimate in advance, as it sounds like it will be too low.
I was talking to a one man contractor recently and his system is to have the homeowner buy all materials and have them setting in the garage prior to his coming to the job site
He has been doing contracting and remodeling for many many years
He knows a lot of these people
He has a set price for any job
He charges a specific price for each task:
Install toilet $150
Install vanity $200
Install dishwahser $100
He has done everything and he knows the numbers and can give them off the top of his head
This system is in opposition to the way a lot of guys do it
BUT this has worked for him for many years
HE SAYS THE CUSTOMERS LIKE THIS SYSTEM BETTER
If you're trying to give a customer an immediate price, stop doing that. Give them a rough "ball park" number just to see if their budget will support their dreams, but tell them that you need time to check materials prices before you can give them a "hard" bid.
Take the time to think thru the job, estimating the time and the materials you need to perform each step. Don't forget to include the time you spend doing things away from the job site (pulling permits, buying materials, dump runs, dump fees, etc) and even time on the job where you aren't actually building anything (talking with the customer, waiting for inspections, etc).
Make sure that your rate includes everything necessary for you to be in business. In addition to whatever you're paying yourself, you have taxes, insurances, licenses, bonds, etc, etc. Those gotta be paid from somewhere and that usually means the customers. - lol
Also, don't forget to include sales tax in your materials estimates. I'm at 8.25% and will soon be at 9.25%. That can become pretty significant very quickly.
Write up a bid that includes everything you plan to do, how long it will take to do it, and how much it will cost (either by task, phase, or lump sum). Include deposits, progress payments, and final payment.
Most importantly, include language allowing you to charge for additional work not in the original job scope. Customers love to change their mind ("While we're at it, can we......"), and/or you may run into things that you couldn't know about until you were into the job.
Have you kept records of your past jobs like your estimate, actual time spent and actual materials spent. I would start there.
yes I find that guides the individual tasks of the job. Dan
i tend to be overly pessimistic on time but figure it can only help in the end. my system is to round task in to units of time no smaller than a 1/2 a day and often add a day or two for slop i also try to remember to add time for gathering mat. which was some thing i forgot about many times early on. as for figuring out the mat i draw pictures and do a stick by stick count and add a few i take that from framing to finish to switch covers.this has worked well for me though it is somewhat time consuming my problem is asking enough per hour to cover overhead and profit but thats partly my market and partly my stupidity
noah
Hi 3kings
Thanks for your candor. I'm thinking that if you don't lose jobs occasionally or even a third of the time you are not asking enough $
Dan
ummmm...adding in profit? Thats a purty big un right thar....and be sure to add in enough profit. 60% works....if ya can get it. 20% might work better in landing the job.
It's all relative ain't it?
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
Dan
This is a can of worms. What works for me might be too much for you and not enough for andy.
Our jobs are mostly small. 1 day to 3 weeks. In a small town or rural setting.
Here is what I do.
I use a spreadsheet template or templates. I use Excel which is Microsoft. I don't know if Mac can run excel.
I draw a picture of the project. From that I draw up a list of materials from step one to the final step in order, on the left hand side of the sheet.
For each material item( or group of items) I list out a labor task on the right hand side of the sheet. Pour post holes, build joists ect. I do figure in terms of hours.
Add 5% or 10% for contingency then sales tax. Then markup materials by 20%.
On the labor side I start all my estimates with 1-3 hours for estimating depending on the type of job. Then 1/2 to 1 hour for billing. Same thing for billing, I include the time it took to do the estimate and the billing time.
On T&M jobs I start billing from time I leave the house. 1st stop is usually the lumberyard. If I am working on your project in the shop or getting materials I am billing for my time.
I set up my worksheets so that I can have the labor at the amount I figured it would take, labor marked up 10% or labor marked up 30%. Then depending on how I feel about the project I pick one of those labor numbers.
Then after the job is done I do a bill summary with as much detail as I can (again in Excel). I have hundreds of old bill summaries stored on the computer. I can go back and find a job and figure out how long it took to install 10 Sq of vinyl siding and compare that to the future job. On paper I could never keep this stuff organized.
I have tried to build a system to track labor tasks, but it is very complicated in remodeling. We rarely hire subs and we do most tasks ourselves.
Rich
Dan
Here is a screen shot of a template I did for preparing a cabinet for a dishwasher. It was at a church for a comercial DW.
I could post an Excel attachment but you probably couldn't open it.
Rich
send him to your tutorial link
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
DanI can attest to Cargins’ spreadsheet. Btw thank you to Cargin for posting it. I got it thru a search. I actually keep it in my new customer folder and use it to adapt to different jobs. He’s already taken the time to do all the heavy lifting; all you need to do is change it to meet your needs. It’s a pretty good starting point as far as making up one that can work for you.Dan
Dan
I don't know which version what you are using. I am upgrading them constantly.
Here is my lastest version of what I call blank worksheet. It's for doing misc projects.
I will also post my door worksheet. I entered 3 doors in the worksheet.
For every door that you enter a can of foam, caulk, shims, locks automatically enter for you. Gray cells have formulas in them.
Sheet #2 has old doors that I have priced before for shoot from the hips estimates.
It probably won't do you any good, but you can use the concept.
Rich
Hey Rich, I'm having a little bit of trouble understanding some of your business logic in one of your spreadsheets I just took a look at. Looking at AA_Blank_Worksheet.xls I see where you have a preprogrammed number of $85.00 in your Crew $/Hr. column (column H). What does that $85 per hour represent? Is that supposed to represent your Labor Cost (your wage plus Labor Burden) or is that supposed to be a fully Loaded Labor Rate?
I ask because then in your summation section you have on Row 27 something that says "Labor Only Up 10%" which applies a 10% Markup to your Labor Total. That markup is for what? Just the Net Profit? Or is that for Overhead and Profit? I'm confused because then again on Row 31 you show the Labor with a 30% Markup applied. I am confused as to what is going on in terms of business logic in your worksheet. $85 per hours is so high it sounds like your thinking of it as a Loaded Labor Rate but then you seem to be marking it up again (and perhaps even twice again) which doesn't really make sense to me.
Also from an Excel programming standpoint have you considered defining cell names in your worksheets? In other words after selecting Cell I23 I would then from the Insert menu select Name>Define which would then.....
View Image
...open up the Define Name Dialog Window...
View Image
...in which I would then give the name of "Total_Labor_Cost" to the I23 cell. what then then does is it both allows me to write in Total_Labor_Cost to any formulas I want to use based on the values in Cell I23 instead of having to find the cell each time I wanted to use it and it also makes it easier to examine your programming and see what is going on. In other words when I look at the formula for Cell I27 (Labor Only Up 10%) I would see the formulas as "Total_Labor_Cost* 1.1" instead of "=I23*1.1". That way I'm looking at formulas I easily understand just what I am looking at instead of having to go and hunt down Cell I23 and see what it is doing. Does that make sense to you?
In fact I would go a step further than that. I would label and define a another cell somewhere as "Labor_Markup" so instead of the formula for Cell I27 reading "=I23*1.1" it would then read "Total_Labor_Cost* Labor_Markup".
And when you start to use multiple sheet in the same Excel workbook I think you will find this technique an incredibly helpful.
View Image
Uh oh busted. That's what I get for wandering into the business section. Confession time. I have never read Ellen Rohr's book, or any other book on running a business.
Hi Jerrald
Most of the work we do is T&M. I bill $40/ hour for myself, $25/ hour for my son Luke and $20/hour for my son Levi. So is that my Labor Cost, or my Loaded labor rate?
I write out the tasks involved in doing a job and then I assign them a # of hours. I am habitually optimistic. So if it is installing a toilet. One man can do that in one hour. I still enter it as one crew hour. At the bottom of my Blank worksheet I have 3 scenarios. #1 is what I figure the labor to be and materials marked up 20%.
#2 is materials marked up 20% and I mark up labor by 10% because I know I am an optimistic estimator.
#3 is materials is marked up 30% and labor is marked up 30% because there are alot of unknowns in the job or I really don't want the job.
Then I pick one of those scenarios based on how much work we have and how I feel about the job. Or I change the rate to 50% because I really don't want the job.
Speaking honestly, I get quite confused when reading your posts. You are speaking over my head most of the time, and I have a college education. And believe me when I see a post by Jerrald I read it, because I know you know things that I need to know, or apply.
Your post a while back about what do you see in this graph, I'm like I don't see anything, i don't know what you guys are talking about.
My rates are more or less seat of the pants and as high as I feel the market will bear. Even though I hardly know what that is except what limited feed back I glean from talking with customers as am selling the job.
Until I discovered BT I felt like I was operating in a vacuum. I can have discussions here that I can't have at the lumberyard with my competitors. I don't know what the other guys are charging. I charge what I need to in order to keep the bills paid, have health insurance and have money left over to save in my IRA and Mutual Funds. ( What's left of them)
I work in a small rural area population in town of 9,000. There are alot of carpenters, but only 2 real construction companies. They are both in commercial work. One company will venture into residential when commercial work is slow. We have alot of solo outfits or several guys working in pairs.
My wife has always been a stay at home mom so I had to support the family on one income. Most guys in construction in this area wouldn't survive if they didn't have their wife's income and benefits. My life has been hectic, because our 3rd son is very handicapped. for a post on that see this link http://forums.taunton.com/tp-breaktime/messages?msg=96278.105
My son's skills set is not complete yet, so I try to be on the job 90% of the time. So between selling, lining up materials, estimating and billing I don't have much time left over. And I have an old victorian house that is always in some state of remodeling. And rental properties. My wife does the books and pays the bills.
Most of my business is repeat customers. We will do anything from a 15 minute job to to a major remodel. We have a reputation for being expensive but the job is done right. we work for alot of old ladies.
I know that when I venture into the business section that my incomplete business skill set will be exposed, but hey that's what it takes sometimes to learn and to grow.
I sensed from HandyDan that he needed some very basic tools to help with estimating. I think you are speaking over his head with alot of terminology. That's OK. We need somebody like you to challenge us in our business skill set.
I will deal with the Excel question in the next post.
Rich
cargin — "Uh oh busted. That's what I get for wandering into the business section. Confession time. I have never read Ellen Rohr's book, or any other book on running a business."
Hey Rich we all come here to get better and smarter about what we are doing. I don't pop in here as often as I used to but I for one appreciate your honesty, candor, and generous helpful attitude whenever I see it. Ya know I'm thinking now too if you haven't ever read Rohr's How Much Should I Charge? and you say your wife is doing your books and this is a family operation then you might want to do everyone a favor and pick up a copy of her book Where Did The Money Go?- Easy Accounting Basics for the Business Owner Who Hates NumbersView Imageand make it a family project to read and understand them. I think for your son's Luke and Levi what they learn from them can help them build a solid business future for themselves. And if the learning project makes your family business more profitable it can also benefit all of you by freeing up time and money you can then use to visit, work, and play with Tim. I'll confess that I never knew that about your situation but I will now read through that whole thread. Thanks for being a good caring dad to your sons.
"Most of the work we do is T&M. I bill $40/ hour for myself, $25/ hour for my son Luke and $20/hour for my son Levi. So is that my Labor Cost, or my Loaded labor rate?"
Well, if that is what you are charging for T&M work with no markup applied on top of that is you Loaded Labor Rate by default but given the disparity between your rate and Levi's at the bottom (a 100% difference) it doesn't sound too well constructed to me and might be worth looking at again. While I am sure Levi and Luke aren't as skilled as you or as valuable to your customers as you are yet they cost you more than I think you are allowing for in their billing rates. In terms of the ratio of overhead cost to wage unskilled labor always costs more than skilled labor. The require more supervision (your time) and they aren't as nearly as productive.
"I write out the tasks involved in doing a job and then I assign them a # of hours. I am habitually optimistic. So if it is installing a toilet. One man can do that in one hour. I still enter it as one crew hour. At the bottom of my Blank worksheet I have 3 scenarios. #1 is what I figure the labor to be and materials marked up 20%.
#2 is materials marked up 20% and I mark up labor by 10% because I know I am an optimistic estimator.
#3 is materials is marked up 30% and labor is marked up 30% because there are alot of unknowns in the job or I really don't want the job."
First of all it sounds like you are making up for 'slop' in your estimating by adding contingency padding all over the place rather than working at understand processes better and estimating them better.
While one man might very well be able to install a typical average toilet in one hour (our figure would be 1.33 labor hours) does your figure include the time it takes to get set up to install the toilet and move the toilet from storage to where it going to be installed etc. etc? A how do you account for the cost of the toilets procurement? Do you charge for the time it took you to go and get the toilet from the supplier or the desk/phone time it took to order it and have it delivered?
And one of the things I stressed to my guys was when you are estimating a series of disparate disjointed unrelated tasks it is often just as important to estimate the time in between each of those tasks as it is the tasks themselves.
'Speaking honestly, I get quite confused when reading your posts. You are speaking over my head most of the time, and I have a college education. And believe me when I see a post by Jerrald I read it, because I know you know things that I need to know, or apply."
That's okay, I understand that problem entirely. But just take it slow and don't let what I say phase you. For instance in one of my earlier post here I used the term Common Cause Variation which I am sure when people here see something like that their eye's roll back in their heads and shut down. The reality is that is the fancy project management word that explains something we all intuitively know that sometimes thing just take longer or shorter that usual for no special reason. I actually created my online glossary years ago so I wouldn't have to explain what I meant by a certain word or phrase every time I used it and I can't even begin to tell you how helpful it has been to me in understanding what I am talking about too! I'm not joking.
Ya know right now I am working on reading a bunch of technical and academic papers on project management that a way way over my head but I stick with it and don't quit when I don't understand just what it is I am reading. Then a month or two later I'll read something else and all of a sudden something that had me totally confused a month ago suddenly comes into focus and makes perfect sense. (By the way the Ellen Rohr books are written in a real simple easy going folksy style so they wont remind you at all about how I write. That's one of the reasons I think they are so good for what they do.)
"Your post a while back about what do you see in this graph, I'm like I don't see anything, i don't know what you guys are talking about."
I think that is in part due to you not understanding anything about Capacity Based Markup or having a weak understanding of what Markup in general is designed to achieve. I think Rohr's books will help with that and then one day you'll all of a sudden go "Now I understand what he meant by that!" I know because that is exactly what happened to me when all of a sudden it finally clicked inside my own head. If you don't understand the difference between a Volume Based Markup and a Capacity Based Markup. (Have you read about Capacity Based Markup in Gerstel's book Running a Successful Construction Company? You know you can also read about it online via Google books too.
"My rates are more or less seat of the pants and as high as I feel the market will bear. Even though I hardly know what that is except what limited feed back I glean from talking with customers as am selling the job."
Even if your rate for you personally is as high as you think you can push it I am sure you could boost the rates for Luke and Levi without people taking notice or objection to it and have the carry more their fare share of your overhead costs and any profit earnings you might have.
"I work in a small rural area population in town of 9,000. There are alot of carpenters, but only 2 real construction companies. They are both in commercial work. One company will venture into residential when commercial work is slow. We have alot of solo outfits or several guys working in pairs."
Well I don't know but I hear that and the way that sounds to me is like there is a real opening in your local for a professionally run residential remodeling contractor.
"I know that when I venture into the business section that my incomplete business skill set will be exposed, but hey that's what it takes sometimes to learn and to grow."
That's true but that is how we all learn. I have a couple of professional groups I am a part of ( for Cold Fusion, Ajax, and Java) where I am definitely the dumbest person in the room but I enjoy them very much appreciating everything I learn there.
As for Excel I started tinkering with it back in Dec of 1990 when my ex-partner threw out as a parting shot "Well I know one thing for sure, you'll never understand the computer part of running a business" or something like that. Anyway that ticked me off and I set out to really learn it.
I don't do much with Excel anymore and have probably forgotten more than I actually know about it today. Fooling around with it tonight it took me a while to remember just how I worked with some of the functions such as VLookup and HLookup and I laughed as I thought that's why I now develop in FileMaker 10! You should check it out if you really enjoy the programming and development aspect of building your own office tools.
But when I see folks talking about Excel I'll jump in with any hints I still may have if I think it can help them in their understanding of how to work with and use the program.
View Image
jerrald
I guess I have some reading to do.
The great thing about BT is it can bump a guy out of his comfort zone and help him to take a new look at how he runs things.
It's pretty easy to just get into a pattern and run with it. That is true with how I run my business and how we run our lives.
Thanks for the advice. I have some things to chew on for a while.
Rich
Jerrald
Just one quick post and then I have to get off BT and get some work done. Very icey today. Schools are calling off and our work is in the country.
I am going to work at home on the computer and on our office/spare bedroom. We don't have that much work that I need to work on a slip and slide kind of day.
First of all it sounds like you are making up for 'slop' in your estimating by adding contingency padding all over the place rather than working at understand processes better and estimating them better.
You nailed me there. I have never been or will I ever be the 3x5 note pad kind of guy that is going to record every 1/2 hour of the day. I'm not making excuses just thinking out loud. A guy has to know who he is and how he works. Yea I build alot of slop into my estimates because I have found that nice tight estimates get me burned. Expenses and labor out run the estimate and I get stressful and the HO gets unhappy
I am too optimistic in the office and things never go the way I envisioned them going once we get out in the field. One thing Handydan could do right away is look at his last 6 months of jobs and find a % of error in his materials and labor figures.
If he is consistently 15% short on materials then the solution is easy ( add 15% contingenccy) until he can gather better data or get better methodology worked out.
Your methods are better and more precisely defined than mine but I have to make a living until I can change my methodology. Therefore the slop.
I am working mostly T&M and my estimate is to prepare the customer for the expected expense. It's better for all parties involved to have higher but realistic estimate than it is to lowball the job. (Been there done that )
I do bid some jobs, handicap work for the state programs, roofing and right now a window replacement job for a church.
Oh well just rambling. Time to do some real work.
Thanks for hanging in there with us guys who need to be drug out of the backwoods of business. I'll be looking for or ordering Ellen Rohr's books. I showed your post to DW and she said let's read them.
Rich
From: cargin — "You nailed me there. I have never been or will I ever be the 3x5 note pad kind of guy that is going to record every 1/2 hour of the day. I'm not making excuses just thinking out loud. A guy has to know who he is and how he works. Yea I build alot of slop into my estimates because I have found that nice tight estimates get me burned. Expenses and labor out run the estimate and I get stressful and the HO gets unhappy"
When contractors I work with tell me that they'll never be able" to learn this computer stuff" I often cite this quote and I think it is perfectly apropos to your thinking regarding tracking what you are doing.
If you are not going to track job cost what you are how are you ever going to develop good estimating technique?
And you know what you are exaggerating the effort involved in tracking your time to rationalize to yourself why you're not going to do it. "I have never been or will I ever be the 3x5 note pad kind of guy that is going to record every 1/2 hour of the day." While there are days where you will do six or seven different tasks in a day (you can't manage to stop for 20 seconds six times a day to record what you are doing?) but the truth is most days you probably doing 1 or maybe 2 tasks all day long. It not a big deal at all unless you decide to make it one in your own mind.
Ya know I can recall a about a decade ago when I was doing a subcontract for a trade show exhibit company where we were building a big fiberglass replica of the tail section of a corporate jet how their shop foreman approached me one day while we were waiting for a coating to set up and he asked me what I was writing down. I told him that working in their shop (we were doing our stuff in their loading dock area) was a goldmine for me because it gave me a chance to record data for a whole bunch of shop related tasks that I didn't have data for. I was there tracking what 5 or 6 or their employees were doing and just the other week I did an estimate that had elements of it that used the data I first collected way back then. If I could track 5 or 6 guys in addition to myself I don't think it's too much for any one person to track just what they are doing.
If you are not collecting data your estimates are just guesses.
With your comment to me earlier to the effect that I was speaking over the original poster HandyDan's head I don't think so at all now and I think you might be shortchanging him. He titled the topic "Job Costing and Estimates" so at least he recognizes the importance of Job Costing. I really don't know how anyone can think they can develop good estimating data if they aren't willing to track what they are doing.
When you say "Most of the work we do is T&M..." just how much is most?
Just how much work do you actually estimate and bid?
View Image
jerrald
"Argue for your limitations, and sure enough they're yours".
I've had that saying my head for about a year now. You are right. Actually I am arguing my own laziness. I don't want to get a notepad out during the day and record data and then find a way to manage it at night.
While there are days where you will do six or seven different tasks in a day (you can't manage to stop for 20 seconds six times a day to record what you are doing?) but the truth is most days you probably doing 1 or maybe 2 tasks all day long. It not a big deal at all unless you decide to make it one in your own mind.
Most days are like the 1st example. There are some days in the roofing season where we tear out in the morning do repairs for a couple hours and then shingle. Many days we are on several jobs or perhaps one guy will stay in the shop for the 1st couple hours spraying doors and other will go to the landfill and I will go look at a job.
Then we will do drywall and plumbing or painting.
But yeah you are right most of my estimates are just guesses.
now and I think you might be shortchanging him. If that's the case then I am sorry Dan.
When you say "Most of the work we do is T&M..." just how much is most? 75% is T&M
Just how much work do you actually estimate and bid?
Our gross sales were $271 K last year. Every little job has to be estimated any more. I guy has a couple of dozen shingles blown off and he thinks he needs an estimate for insurance. I want to scream "just fix it buddy".
I don't do ballpark estimates. I am no good at them and that number sticks in the customers head. But every thing we do is usually estimated. Gone are the days when people just wanted me to fix something or just build a deck and send us the bill.
Most of our jobs are in the $1,200 to $5,000 range. Some greater and some smaller.
Rich
jerrald
Also from an Excel programming standpoint have you considered defining cell names in your worksheets?
That's a new one for me. I didn't have a clue. Like I said to Dan I probably only use 10% of what Excel can do.
I have started threads and put alot of my stuff out there hoping to get feedback about using Excel here on BT and not many people respond or want to talk about it. Silver like to talk about estimating with Excel, but he is busy right now with other things. So I plug away at it, sometimes going to the tutorials in the help section.
I am trying to learn how to use this tool so I welcome any feel back.
I will have to play with the Insert feature and naming cells.
There is so much I don't know.
I am using multiple sheets after your suggestion about a year ago.
These worksheets are for my own use and I share them here at BT to help others and to get feedback.
Rich
Cargin I remember your previous posting of your tutorial and I really thinks its great that you have taken the time to do that and share it here.Attached is an excel 'program' I have developed for estimating. My main objective in presenting it here is as a demonstration of some of the very powerful things that can be done in Excel. While I hope that some of the ideas might be helpful to an Excel enthusiast, I would NOT recommend trying to use the template itself, because its too 'fragile' and prone to being broken.The basic structure of the spreadsheet is as follows:Matls List - this is a list of the common materials that we use with prices. Estimate - this is the guts of the thing - its where you 'build the project' virtually in terms of materials and labor. You can specify materials, and the system 'looks up' the price from the Matls List page. Of course, you have to specify each material exactly as it is presented in the materials list. If a material is called an 'SPF 2x4x8' in the list, and you specify a '2x4x96' in the estimate, it won't be able to retrieve the price.Note that specification of quantity in the estimate can use of to 3 fields:
- Qty
- Mult 1
- Mult 2
- Waste factorSay you are specifying i-joist for a floor. The joists will be 14' long, and there are 20 of them. In the materials list, the unit of measure for i-joists is 'ft', since we always order these cut to size, not in any standard length. The amount of i-joist material to be specified is then:
qty - 14
Mult 1 - 20
The system will calculate total footage of 280 ft. If a waste factor of 10% was specified, it would calculate 308 feet.
The idea here is to facilitate 'showing your work', so that the amount of calculation outside of the sheet is minimized. Regarding price, and lookup from the materials list - you can specify 'nonstandard' materials that are not in the list, and just enter a price manually in the 'Enter Price' column. Its not necessary to add the item to the Matls List.SUMMARY - this sheet summarizes the 100's of lines of detail in the Estimate into a manageable summary. This is very flexible and customizable to the specific project. By entering a 'code' in the column LINE-ITEM, and then setting up a summary row on the SUMMARY page with the appropriate 'SUMIF' formula, the total cost for all of the lines having that code is summed up. We also enter and summarize actual costs vs estimated on the Estimate page, and summarize these for presentation on the SUMMARY page.There are 3 semi-advanced excel functions that support most of the functionality. These are:
VLOOKUP
IF
SUMIF
If you become familiar with these functions a whole world of clever possibilities is available to you.The sheet also makes extensive use of 'named ranges' as described by Jerrald.By the way, I want to make a plug for 'Open Office' as a free alternative to Excel. It reads and creates Excel files, but its free!
leegs
WOW.
I don't know what to say, besides I can't understand it.
That will take some studying. I am going to have to search the help turtorials for the VLOOKUPIFSUMIF commands.
I understand IF and I have used it a time or two. Lookup and sumif are new to me.
That is a very complex sheet. Is it easy to get lost in it, even if you know what you are doing?
Can Excel take that estimate and produce a materials list that you can take to your supplier?
How do you update prices?
I am going to have to study that some more. I get the concept, but I don't have a clue how to implement to my situation.
I ask about the materials list for a supplier because with my worksheets it is time consuming to produce a materials list to order a job or to solicit a quote.
Thanks again.
Rich
Regarding complexity: conceptually I don't feel that its that complex. It is BIG, because it facilitates breaking a big job into tiny pieces. I do get a little lost sometimes, but I would say its lost in the project, not lost in the spreadsheet. However at a detail level, in terms of how it uses excel, I agree it is pretty complex. However a fair amount of that is aimed at making the structure and the formulas 'generic'. By this I mean that for most of the 100s of rows in the Estimate page, the same exact formula structure works - there is minimal 'spaghetti code' with specific links that are hard to maintain.You mention using colors - yes I do the same. I have a few 'structured' colors -grey for input boxes, dark blue for lines that indicated the bounds of ranges. I also use colors in an ad hoc way to highlight things. I also make extensive use of borders and font changes. I find that the more I organize and segregate the presentation of the sheet, the less my mind has to concentrate on 'what am I looking at' and I can concentrate on content.Updating prices - I just do this manually every so often. Its not too bad.Generating materials list - I generally have not tried to this as an automatic byproduct of the sheet. I can't say its not a good idea, and it would not be impossible to generate. I guess its partly because I'm still something of a novice that I don't mind the redundant effort to go through the project.
leegs
I would NOT recommend trying to use the template itself, because its too 'fragile' and prone to being broken.
I fully understand.
In my simple programs I make the cell gray if it has a formula in it and yellow if it requires data or input from me.
I don't use the yellow all the time, because the sheets are for my own use and I understand how they are designed.
Rich
Hey leegs, I can see there's a lot of thought and development in your workbook but it's going to take me a while to sort out and understand the data architecture and organization but I guess I enjoy picking my way through that kind of stuff.
"While I hope that some of the ideas might be helpful to an Excel enthusiast, I would NOT recommend trying to use the template itself, because its too 'fragile' and prone to being broken."
Why not add Cell/Sheet Protection to the cells you don't want the user to fiddle with? That's my one big huge complaint about Apple's Numbers program in that it still doesn't have Cell Protection capabilities. You could then color your cells that are for data entry by the user with some subtle background color so they would know those are the cells they can work with.
"Estimate - this is the guts of the thing - its where you 'build the project' virtually in terms of materials and labor. You can specify materials, and the system 'looks up' the price from the Matls List page. Of course, you have to specify each material exactly as it is presented in the materials list. If a material is called an 'SPF 2x4x8' in the list, and you specify a '2x4x96' in the estimate, it won't be able to retrieve the price."
The solution I might think about using to solve that problem in Excel is to set up a copy and paste macro to copy and insert the exact material name you want to use. Is something like that in your plans? I know some folks like to make selection lists via Cell Validation Lists but I hate that in Excel. If the Materials list gets huge it can still take forever to find the material you want and you still have to have a good idea of how it was spelled. That's one of the reasons I like database solutions over spreadsheet ones too. Searching for the data you want and need is faster and easier in a well designed data base solution. If I want find something I can drill down a list searching through smaller lists organized by materials category or by vendor and if that fails I can still always run a 'Find' if I can recall just one word in the material description.
View Image
Regarding cell protection, macros, etc - definitely good ideas. However I have a bias from my days in IT, where if an Excel solution got to be big or complicated enough to need that sort of additional structure, I would develop a database solution. For this specific sheet, only my boss and I use it, so I haven't been motivated to add that structure and discipline. Another factor is that the sheet is still a work in process, being revised and improved, and I don't want to make it less convenient to continue in that way.Finally, for the issue with the materials list, its not too bad to just go to that page, find the material, and copy/paste it back into the estimate page. The materials are organized by category in the Matls List.This for excel junkies only: note that the material 'key' is actually composed of two cells/columns 'Matl/Item' and 'Size/ID', which are concatenated together to enable the VLOOKUP to work. So for example a stud is described as 'SPF' & '2x4x8', whereas a deck joist would be 'PT' & '2x10x12'.
SP
leegs — "Regarding cell protection, macros, etc - definitely good ideas. However I have a bias from my days in IT, where if an Excel solution got to be big or complicated enough to need that sort of additional structure, I would develop a database solution."
Actually I was even thinking your workbook has so much going on in it it would probably be better off as a database solution.
"This for excel junkies only: note that the material 'key' is actually composed of two cells/columns 'Matl/Item' and 'Size/ID', which are concatenated together to enable the VLOOKUP to work. So for example a stud is described as 'SPF' & '2x4x8', whereas a deck joist would be 'PT' & '2x10x12'.
SP"
I have to say that when I figured out that is what you were doing in those cells that is what had me think this would be so much better in a database solution. The concatenated values would be your unique key ids.
There are going to be a couple of excel geeks here who will get into taking apart your sheet if they find this discussion but I know for most of the folks here this is going to be way bit too geeky. Personally I love taking apart stuff like that to see what people are thinking, doing, and tracking in regard to estimating.
And I'm guessing you and your boss have a big monitosr too huh?
View Image
No, no big monitors. I guess I'm a partly reformed geek :) I now spend 90% of my time trying to be a carpenter, and my boss is a very good carpenter who is willing to use my spreadsheet a little bit when he has to do some estimating. Regarding database solutions - another bias I picked up from my IT career is to be very careful about, and even resistant to, developing complex custom solutions. Its so easy to spend too much time, and its dangerous to have such solutions supported by only one person. At least a spreadsheet, even a complex one, is somewhat transparent and is not complete black box.
leegs
I have to laugh.
I develop my "simple spreadsheets" in my slow times or instead of watching tube. For instance I have a deck worksheet that I don't use very often. Not the one I posted. I just patched the materials list on to my blank worksheet for Handy dan.
But anyway I might come back to a worksheet a couple of months after developing it and I can't remember my logic. The i feel real stupid.
I try to develop worksheets where I can enter the size of the walls or the ceiling area and then let the worksheet figure the materials for studs, sheathing, SR, insulaton, siding and so forth.
There is a very simple example of this in the tutorial http://forums.taunton.com/tp-breaktime/messages?msg=104183.52 thru post number 56.
Any way your worksheet was so way over my ability to comprehend, so I could really relate to your statement
developing complex custom solutions. Its so easy to spend too much time,
In my own simple way I have done that.
I like your use of colors to help keep your mind focused on an area. color helps me to group things together and just makes me like my creatin more. I color because I can.
Anyway I had a good laugh at your post.
Rich
Hey Rich it just occurred to me did you ever read Bill Lacey's JLC article Estimating With a Spreadsheet he was a smart young guy who worked as a lumber estimator for a lumber yard so while his work is focused on materials and virtually ignores labor it is a good article and his spreadsheets are well worth a look at from the standpoint of Excel programming too.
View Image
jerrald
I have it saved on my computer. I just used it to build a little tool to figure the size of a roof when all I know is the pitch and the run. Sometimes I look at a roof without a ladder.
That article changed the way I think about estimating and about spreadsheets.
Up until that point I was using MS works, an old version. About that time we got a new used computer that had excel.
I converted everything to Excel.
At times I really struggled to figure out spreadsheets in the beginning. That's why I did the tutorial, for all those other guys like me who scream at the computer. Its much better now but in the early days of my computering I woud get so frustrated. Help was written in a language I did not understand.
About 10 years ago Rick Stacey or something like that wrote an article in JLC about estimating with a legal note pad and saving old invoices and recording what items cost. He got hammered in the next issue by the pros, but that article woke me up about changing the way I look at estimating. Boy I was really guessing back then. LOL
Anyway I decided to do the Works spreadsheet and keep materials prices handy at all times instead of going to the yard with a list and asking them to price it for me.
Rich
jerrald
I ordered Ellen Rohr's books on interlibrary loan.
Keep the overhead down.
Rich
Rich
Jerralds helped me quite a bit and he mainly works off of a Mac but he has windows as well so he can be that much more thoro when he helps people with his estimating programs. He can check the way they see things if they don't have a Mac.
You aught to....well..anyone aught to give him a shout if they wanna brush up on their estimating skills. I "think" he even has a free program you can mess around with..not sure.
As abrupt as the guy can be in his political and Global Warming threads he's a pussycat when it comes to telling you if you're doing anything wrong unless you ask him....so ask him!
andy
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
andy
So did you every move from yellow legal pad to excel?
Maybe 6 months ago you were on here looking for a simple estimating program. And if I rcall you were still using a legal pad but thought excel would work for you.
As abrupt as the guy can be in his political and Global Warming threads he's a pussycat when it comes to telling you if you're doing anything wrong unless you ask him....so ask him!
I have never seen Jerrald post anything outside of the business folder. I kind of stay out of the political stuff. I have too strong of an opinion.
I would welcome a look at an estimating program.
I been building my own system over the years. And it evolves with every job or new twist thrown at it.
The area I need improvement is in tracking my time, understanding overhead and markup. Hence Jerrald recommended the Rohr books. I have seen both of them and Micheal Stones book mentioned here and elsewhere, but I have been too busy chasing my tail to sit down and read them.
But it like a good sermon. It only does some good if you take it to heart and make some changes.
Rich
Rich..no I still haven't got past the yellow legal pad...lol..unless you consider a white legal pad any better...lol...although I did learn a lot from your tutorial but I got so busy I never got to finishing it and was going to get back to it which was when this thread got started again. It's always sumpin'...ya know?
I haven't blown it off...It's ALWAYS in my head to get back to it and I'm sure I will....and after I do I may go spend the weekend with Jerrald and check out his personalized program.
Actually I thought I was gonna have time this week since I just finished a big bathroom job (see photo gallery) (don't bother...here it is if ya want )http://picasaweb.google.com/andybuildz/KAYLINMASTERBATH?authkey=Gv1sRgCOeRvIurhaPE3gE&feat=directlink but other stuff came up...of course...and I think I'm probably starting more work in that house I did this bathroom in...geezzz.
I really do wanna get back to Excel....grrrrrrr.
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
Andy
That's beautiful.
Did you do all the staining and varnishing in the bathroom?
Alot of work in a confined space.
We tend to get jobs where it is hard to turn around in too.
Makes me long for the outdoors.
Rich
Stupidly...for some reason...I did that entire job alone from gutting the two rooms and framing them into one..to the rock and CBU's and tile and and and...was on the 2nd floor to boot. Carried everything up there and down...and up...and down...and...ugh. The stain I made myself to match the vanity...3 coats of oil poly...the trim I made myself on my Williams and Hussy...what else...what a dope...lol. I don't know how that happened... I got carried away I guess...I even picked up the tile...the invoice showed it weighed over 2000#...so I spose I carried well over 2 tons up the stairs alone...buckets of sand for the mud shower floor...billion bags O' thinset...don't ask...never again. I musta lost my mind somewhere...lol
Thanx
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
Andy I feel your pain. and thank god for nice understanding spouses!!
As for Excel it is good. Many years ago I wrote a point of sale from filemaker pro. I run Mac and it got corrupted from changing from the moterola chip and power chip. So lost it all.But I relent File Maker pro is a darn fine program. bout the same learning curve as excel but you can generate invoices letters to the peoples and stats. Even do a custom mail outs.Get your kid in school to buy it, with an educational discount and let them 'learn yea"You can even put your mother in-laws birthday on it and just happen to be gone that day.
Get your kid in school to buy it, with an educational discount and let them 'learn yea"
The discounts a good idea..saw a web site that had the discount for students and things were a LOT cheaper...not a bad idea.
Jerralds a REAL good person to work with ya on any of these things if you have a Mac especially.....if ya can only get him to. If you ever need help nudge him...he's made some programs that are really excellent!! I think he even has a free one I saw him post over at JLC.
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
Hey Shoemaker...was checking out FileMaker Pro 10 since you mentioned it...not that I have money to spend on any program right now like that even with my daughters college ID/discount but it looks pretty interesting....I also really don't want something ELSE to have to figure out right now....lol. Interesting none the less..thank you...I'll keep it in my favorites
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
I started messing with excel again...ut oh...lol. Just got back from my customers house and we're starting in with another phase and the simplest number I have to give him right now is the trim upstairs and a French door in his office which I'll start on Tuesday if he says go for it...which he will... so I thought it'd be a good lil' project to try in excel. Next are two more bathrooms in his house when the trims done which'd be a bit more complex
One question I thought of...how can you SAVE your work in excel like I do in WORD.. or can't you? Makes me nervous that I'm gonna click one little number accidently and Ka-blooey....I'm screwed and might not know it till it's too late.
Also...I always wanted to know how you guys copy the work you're doing to paste it and show it here? Whatcha call that...a snapshot? I can copy and paste a photo but no idea how to do that with...say my excel page.
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
andy
I don't know how much you know about computers. So I will assume you know nothing.
You start with a excel sheet and build a template, or use one of mine or a free template off the web.
Under File you will see Save As. click on that and give your worksheet a name.
I have a Construction folder on the computer in which I keep all construction related stuff.
Next time you want to use the template, you pull it up. As long as you use save as, and give it a new name after making any changes, then what you are working on is essentially a copy. The orginal is still intact in the file cabinet so to speak.
It's not a bad idea to give it a name before you start working on it and do save as. The worksheet will stay on the screen and you can work away. When you are done it will as you if you want to save any changes to XYZ worksheet and you click yes.
Template secure and new worksheet created.
I also save a copy of my worksheets in a seperate folder in case I screw up one.
Also...I always wanted to know how you guys copy the work you're doing to paste it and show it here? Whatcha call that...a snapshot? I can copy and paste a photo but no idea how to do that with...say my excel page.
When i post a worksheet I go to attach files, then browse, look in cargin construction folder, find my excel sheet I want to share click on that and upload.
If I want to do a screen shot then I press control and Print Scrn Sys Rq (upper right hand on keyboard). Then i open a Word doc. and hit paste of Control V.
If I want to print that in the post then that is another process. Which I explained here. http://forums.taunton.com/tp-breaktime/messages?msg=117103.115
Rich
Rich
I always wondered what Print Scrn Sys Rq was...cool...now I know.
And yeh...I figured out how to save the file. I named it when I started it but didn't see save in File...dunno what happened but it came up when I was ready to close up for the day : )
This is a perfect project to mess around with it on.
Great idea to save it in a 2nd folder. I usually print up a copy of my estimates as I get further along because I 'm always worried my PC might blow up..lol. I think thats one of the reasons I got stuck in a yellow legal pad with all my chicken scratches figuring each little number out. My chicken scratch pages I keep seperate from my more organized pages but I save em all till the jobs over. How antiquated and paronoid am I? lol
Maybe I can wean myself into the space age...lol. I might even try Jerralds program thats buried somewhere in my PC after I learn some more about Excel...lol
Mucho thanks
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
andy... you know about "files" and "folders" in windows , right ?
and you know that you should be organizing your filing system in "Windows Explorer" , right ?
and you know how to get into windows explorer ?Mike Hussein Smith Rhode Island : Design / Build / Repair / Restore
Mike...Windows Explorer is the browser I always use. Comes up automatically when I save stuff in My Documents which is where I keep all my (Word) files (my contracts and estimates etc etc)..Thats whatcha talking about right?
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
yes, that's what i'm talking aboutMike Hussein Smith Rhode Island : Design / Build / Repair / Restore
Don't worry Mike...I'm only mildly retarded...lol.View Image
At almost 58 this month it's just coming together...lol.
Better late then never I reckon : )~
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
I have started threads and put alot of my stuff out there hoping to get feedback about using Excel here on BT and not many people respond or want to talk about it. Silver like to talk about estimating with Excel, but he is busy right now with other things. So I plug away at it, sometimes going to the tutorials in the help section.
Just so you know and continue to share, I read your stuff and I am much further behind you in my learning process so it is all very valuable to me. I read here and then go and experiment. I am working on a few worksheets and all the help you guys give here is awesome. I really appreciated the tutorials. I probably wouldn't be using excel as much as I do now if it wasn't for the info I find here. Please keep it up. I will share what I have as soon as I get it to a good working point. Now that this thread has come along i guess I will be going back in and adding some new features.
Thanks Angus
Edited 3/10/2009 3:23 am ET by angusj
Angus
Thanks for the feedback support. I wasn't sure if anybody ever read those turorials.
I thought I was posting to a brick wall.
Working on spreadsheets can be a huge labor saving tool.
I am not a real organized person on paper, but the computer allows me to design templates in my good moments that I can go back and reuse without reinventing the wheel everytime.
And I can infinitely store old worksheets that are just a few clicks away.
But I need to start documenting what we spend our time on. (How do I bill out for BT time LOL) Like Jerrald said he has 1.33 hours for installing a toilet and I figure it with alot of slop.
He is right though, the time it takes to do a task is oftentimes less that the screwing around it takes to get there and get set up, and then clean up and bill for the job.
Have a good day out there in BC.
Our day is starting out with freezing rain. It's slicker than snot out there this morning.
Edit: I would love to see even your attempts at using excel. I am willing to help if you want to have thread about using excel. Obviously there are people out there with all leels of experience that can chime in with input. So when you want to post a worksheet, I'm all ears or eyes.
Rich
Edited 3/10/2009 10:04 am ET by cargin
I always learn from your posts and the discussions from them. You seem a little apologetic about your system being a work in progress. That's what makes it so valuable.
How you doing Craig
Good to hear from you.
Ya I enjoy these types of threads too.
Always a new level to step up to.
You seem a little apologetic about your system being a work in progress
I think I am soooo cool until I get on BT, then I find out there are bigger fish and better fish out there. Hopefully I can grab a dorsal fin and learn from their experience. LOL
I'm a simple guy, that does simple jobs. Simple worksheets are needed for this kind of stuff. But I still need to learn about and understand markup and setting my rates. I've been too seat of the pants.
Rich
I'm doing well.I think I'm sooo cool then...Pretty much the story of my life. :)
craig
ROFLOL
Rich
Dan
This is what I use for figuring my bills.
Rich
Rich:
Since I have worked with spreadsheets, I think your approach makes the most sense. You are putting in a markup, taxes, all the time you spend on the job including things I currently let slide like another trip to the lumberyard. I like that you are going back after the job and comparing what you thought you could do the job for against what it actually took. To have a history of documentation for all jobs seems infinitely more valuable than just using a book or software program with canned numbers that have nothing to do with you, your work habits or the nature of the jobs you take. Like you we do a lot of small jobs with finicky details and problems no job costing book will include.
Plus I already own Excel on the MAC!
Many thanks. I interested to hear more from you and others regarding what markups you make and based on what. In this tight market, the fine line between making a decent living and losing jobs to the cheaper guy is not to be underestimated. My other interest in this approach is that I want to know exactly what I am giving up if I have to nogotiate the price with the client.
thanks again Dan
Dan
I also use National Estimator. For $69 you can access 7-8 cost books.
http://craftsman-book.com/products/index.php?main_page=cbc_product_software_info&cPath=39&products_id=387
I don't use it on every job, in fact I don't use it very often. But I get into situations where I don't have a clue what a project is going to cost or I want another opinion, then I use NE.
With any of the excel sheets I posted you can copy and paste them into a blank sheet on your computer. Hight light the whole sheet, press control C then open a blank sheet on your computer and hit control V. You may have to go to Format and adjust the width of the columns.
If that doesn't work let me know and I can e-mail you what you need.
Rich
thanks Rich. Your attached Excel sheet will help me get started. It's been a few years since I used Excel and I can see I am going to need a few hours just to learn all the commands.
I will look at the other package down the road, though most of my jobs from here on in are decks, so I want to develop some kind of deck estimator template that I can simply rename and punch in the new numbers for a new job. Sound like the right approach to you?
Dan
Dan
Sounds like a plan.
If you are rusty on Excel then go thru the tutorial posted earlier. It's designed for people (like me) who know nothing about spreadsheets.
It will show you how to build a sheet and how to use most of the simple stuff that excel does. I probably only use 10% of what excel can do.
I have a deck worksheet that figures materials for you. I don't know if it is ready for prime time. I build this stuff for my own use. So sometimes it doesn't translate well to people who don't think like I do.
I'll see if I can tweek it alittle and then share it.
But more important than the estimating sheet is the bill summary and tracking your jobs and finding out where you went wrong.
Rich
Edited 3/9/2009 8:28 am ET by cargin
Just for the record, you can move Microsoft Office files on both PC and Mac. I do it all the time.
I use my Mac at home put files on a memory stick then when forced to use a PC put the stick in the USB port and your off and running.
Excel is a great program, we use it for doing the books. we give it to our accountant, he can import it to his accounting programs no problem.
Dan
Here is a simple deck template.
Prices are old.
It has to be filled in manually.
Rich
Dan
Andy suggested I post this link
I got a wild hair a while back and thought I would teach the world how to use Excel.
So I go step by step on how to set up a worksheet.
Since you use a Mac it may not do you any good.
But here is the link. It is full of excell attachments.
http://forums.taunton.com/tp-breaktime/messages?msg=104183.1
Rich
Dan
The other benefit of using templates for materials is that you can make a list of the materials commonly used on a deck job.
Then when figuring a job you just work your way down the list. This will help you to remember little $5 items that eat at your profit.
Each time you do a similar job you will see things that need to be added to the list.
You could easily do this with paper and a copy machine.
Rich
Hello Dan, in reading the title you gave to your topic I think you spelled out part of the solution to your problem. Since you aren't hitting on your estimates, you say you are "Wrong on labor (too low) wrong on materials (too little)" you need a way to spot those errors and and record the correct information so you can look it up when you need it and make sure you don't make the same mistake again.
But lets try to look at this a little bit deeper. When you say you are missing on your labor estimates is it the 'dollar' or the 'time' figure you are missing on? They are two different kinds of problems. If you are missing on the 'dollar' side of the equation then your problem is in your labor rate and while you very well might be hitting your labor hour targets your not earning the money you need to cover your costs of doing business and paying yourself a living wage. I wont go into too much depth on that now since I've done it here plenty of other times before but if that is your problem (or part of it) I typically always recommend reading Ellen Rohr's book How Much Should I Charge?: Pricing Basics for Making Money Doing What You LoveView Image and if you need it you can download my Capacity Based Markup Worksheet which is an Excel Workbook you can plug your own numbers into and it will help generate billing rates for anywhere from one (just you) to five employees.
If your labor estimating problem lies in estimating the correct amount of labor hours a task or series of tasks will take then that's another problem all together. First of all it help from both a perspective and estimate accuracy standpoint to break your project down into smaller estimatable tasks rather than trying to come up with a number that fit the whole ball of wax. That's called developing a Work Breakdown Structure which is a hoity- toity technical term for something many of us do quite naturally when we look at a project. According to wikipedia a Work Breakdown Structure is:
I emphasized successively subdividing it into manageable components since I think that's a foundation to getting labor estimating done correctly.
Once you have that done there are two basic ways to approach estimating one of which I seen alluded to here already. You can look at your project's tasks on an eye ball guesstimating basis which is to say something to the effect that "I have that pile of stair rail balusters to install and I think that task will take 3 and a half days" or you can look at your project's tasks from a 'measurement basis' which is to say "I have 120 balusters to install and given a Unit Cost Productivity Rate of .286 labor hrs. per baluster I think the baluster installation will take approximately 34.32 total labor hours" ± some Common Cause Variation. In other words it might take only 33 hrs or it might take 35 and half hours but on average I can expect the baluster installation to take 34.32 total labor hours.
I prefer the 'measurement based method' over the 'eye-ball guesstimate method' for a number of reasons. Number one is it is more scientific and help remove emotion from the estimating process. Some people are pathological under-estimators who see every task in a perfect optimized superhuman world and they underestimate labor hours and as a resulting consequence end up under pricing jobs. On the flip side you have the overly cautious type who overly pad every task always leaving more than enough time in the estimate to accomplish the task. That can backfire in two ways. One after you've padded a whole series of tasks with extra time to make sure your covered you've priced yourself out of the job and number two you expose yourself to a getting a possible case of Parkinson's Syndrome where every task you perform will expand to take up the time you have allotted to it or Student Syndrome where knowing you have left yourself with more than enough time you put starting a task off till the last minute and then get screwed when a case of Murphy's Law strikes you.
Also with the 'eye-ball guesstimate method' each time you look at a similar project with a approximately similar pile of balusters you will use a different guesstimate of time which is known in the world of statistics as a "random or drunkard's walk" or Markov Chain.
And perhaps the last major reason I prefer the 'measurement based method' over the 'eye-ball guesstimate method' is with the 'measurement based method' you are killing two birds with one stone. As you perform your materials takeoff you are building the basis of your 'measurement based' estimate. With the 'eye-ball guesstimate method' you still have to takeoff and estimate materials so why not use that data as the basis of your estimate anyway?
Bob Kovacs once gave us a quote in the Estimating Forum that he picked up from the Richardson Engineering Services book:
Which bring me to the next point and one of the key arguments I hear from contractors opposed to Unit Cost/Measurement Based estimating. They will typically argue that every job is different so you can't possibly use standard Unit Costs and Productivity Benchmarks. But in giving that argument I don't think they are really thinking. Each task may very well be different for many jobs but what you do is you use the standard Unit Costs or Productivity Benchmark for the standard task and then devote your time and estimating judgment and intuition to estimate what makes that task different in that particular circumstance!
In other words while we may figure a typical pre hung door installation to take .800 labor hours that doesn't mean that all the pre hung door installations we do are going to be typical. You need to consider things like how easy is it to get the door from where it is stored to where it is going to be installed, does the door need to be modified (cut down) before it is installed etc. etc.
There are some real basic times for real basic finish carpentry tasks mentioned in Jim Tolpin's book Finish Carpenter's ManualView Image
that you might use to get yourself started with but over time you discover others and I'm sure build a library of you own time for particular tasks. You then take the sum of those times and multiply it by the billing rate you know you need to get.
As far as modification factors to apply to some of the Unit Costs we use in my company here are some of the ones we use. In the case of a room with more than 6 corners we will add 12 minutes per additional corner. For work done in large open areas with no partitions or obstructions we'll apply a modification factor (multiply our time) buy up to .85 to account for the ease of installation. Working in cramped quarter we'll apply a modification factor of up to 1.30. Work 15' above floor level 1.10, Work 20' above floor level 1.20, Work 25' above floor level 1.3, Work 30' above floor level 1.4 etc. plus in the case of the work being done above floor level you would also figure in the time and cost it takes for you to set up the scaffolding you need to perform that kind of work too.
It's going to take you a little while before you get to be a good Unit Cost estimator but thinking this way rather than 'every job is a unique one-of-a-kind prototype' will get you there a lot faster and easier.
And it doesn't take a huge collection of Labor Productivity Benchmarks to get started either. As you have already alluded to your projects are falling into some basic categories and I am sure you have some basic tasks that you find yourself doing over and over again only in different combinations and quantities. This is one of those cases where Pareto's Rule, the 80/20 rule comes into play. 80% of your revenues probably come from 20% of the tasks you are capable of doing so the number of tasks you need to develop benchmarks for isn't nearly as big as you might fear. And once you get them out of the way with standardized base labor hours and costs you can devote most of your estimating time to thinking about the tasks you don't typically do.
Part two is you really need to track what you are actually doing on a task by task basis so you can see where your task estimates are hitting and where they are missing and why. Was the standard average time you had figured for the task wrong or were there extenuating circumstances and conditions that you missed on and didn't include in your estimate or was it just a case of Common Cause Variation (sometimes things take longer, sometimes things take shorter) you're seeing.
Long before I ever even knew anything about computers I was always tracking and logging everything and taking notes on what other people were doing and just how long it took them. I used to do this all on these little spiral bound 3x5 card notebooks that I would keep in a chest pocket or a small pocket on my tool belt. Those cards (from a card I wrote back in 1989) would contain entries like this:
That would tell me that on that particular day 5/9 I hung 5 doors with their the lockset's and hardware installed (the /Simonson note tells me that). I could then check the total time versus what I had estimated for the two different tasks (hanging the doors & installing the hardware). The other notes were telling me about materials I needed to get and the 5/10 notes are about prices for some materials I checked on while stopping a the Richards Lumber yard that morning. 'Ar 555-9724' means I arrived on the job with the 555-9724 phone number at 7:50 and the data collection would continue on the next page.
I've gotten a lot more sophisticated since then thanks to computers (I am very much a Mac guy by the way), Palm Pilots, iPod Touches and iPhones but I still often find myself reverting to those same old spiral 3x5 card notebooks from time to time.
The key thing here is you need to commit yourself heartily to collecting and building up stores of records and data or none of this will work and with every estimate you do you'll be doing it as if it was the first one you ever did. That's the tough hurdle most contractors need to get over.
Can you give us a little bit more information on how you think you are short changing yourself on your materials estimating? Typically that's the easiest part of any estimate to produce. Are you just miscalculating quantities or is not including for waste factors killing you? How about the miscellaneous and sundry items such as nails glue, caulk, etc? Is not including budget numbers for them nickel and diming you to death?
There is a lot more to successful estimating that hat I mentioned here but that's at least a start. It like any other skill in this business. Just as you can't expect to become an expert carpenter in reading a few paragraphs and spending just an hour or two studying and learning becoming a good estimator will take some time and a lot more inquisitive questions on your part too.
By the way I also couldn't help but notice you wrote: " I am open to all suggestions and I also own a MAC book which if I find the write software I would also consider." Does that mean if you find the "right" kind of software you would use it or if you find the "right" kind of software you would "write" your own?
View Image
jerrald
Andy thinks you have some estimating program that will help me.
Is there something I should be asking about?
I went to your website, and it looks like you have a program for Filemaker for estimating.
Anyway, I'm not too proud to ask for a better way to do thing. I'm not saying I am going to jump horses, but I will consider a better option.
Rich
From:
andybuildz <!----><!---->
2:27 pm
To:
cargin <!----><!---->
(56 of 57)
117568.56 in reply to 117568.55
Rich
Jerralds helped me quite a bit and he mainly works off of a Mac but he has windows as well so he can be that much more thoro when he helps people with his estimating programs. He can check the way they see things if they don't have a Mac.
You aught to....well..anyone aught to give him a shout if they wanna brush up on their estimating skills. I "think" he even has a free program you can mess around with..not sure.
As abrupt as the guy can be in his political and Global Warming threads he's a pussycat when it comes to telling you if you're doing anything wrong unless you ask him....so ask him!
andy
Rich let me see if I can clarrify some of what Andy was saying. I do have a estimating program (for Mac and Windows) that I built using FileMaker and that I sell as a commercial product.
Years ago, when I was working on it's initial development after buying the devloper's edition of FileMaker, I sent a copy of it to Andy and a couple of other folks here on BreakTime to test out and give me their ideas and comments about how it worked for them. Some of the people I invited to test the program out I invited precisely becuase they weren't using computerized programs to do their estimating. In return for their feedback (out of all the people I invited to look at it only two Breaktimers really gave me any feedback and they were Andy and Randal Weber), and in return for their contribution I gave the "lifetime" licenses meaning they can have and use my program in any version for as long as I keep developing it for free.
I however in Andy's case at the time he didn't use the program becuase he wasn't estimatimg projects, he was working on developing his own home for sale so he didn't have any need for it at the time. Then when he started a discussion here awhile back about aquireing some estimating software I had to remind him he already had a license he had earned to my product.
What I think he was referring to when he said
...might have been that I support my software by giving live training over the internet by the share a screen feature in iChatAV on the Mac platform and via GoToMyPC on the Windows platform.
I do not at this time have a free version of my Estimating program but I have plans for one in the works. Origionally when I first created my program back in the 90s I called it The Simple Estimate Worksheet and what I used to do was give it away to my subs for free so that they could estimate projects for me and then send their information back to me and I wouldn't have to transcribe it, I could just import it right into my estimate and wha-la I was done. The program has grown a lot since way back then but I got to thinking this winter that I should re-issue a version of that Simple Estimate Worksheet that my program users could then give to their subs for free to work with and submit quotes.
As I alluded to in an earlier post of mine msg#117568.35 I thought you would really enjoy working in FileMaker 10 and building your own office programs with it. And even if you don't use it to build your own programs from scratch it comes with 30 ready to use starter template solutions that you might find really useful and there are other free or shareware FileMaker programs written by third party developers out there too.
As for my Simple Estimate Worksheet maybe I can get to work on re-developing and re-issuing it again next week and if you get the FileMaker demo you'll have 30 days to take it apart and see what you can do with the program.
View Image
Just read you post!
Either your a good lier Or you have a better grip on life than most.
I believe the latter. Do you need a Canadian rep?
Years ago I was asked to sit on a computer users group at a hospital I worked at as a RN early 90's. They were still beating there heads in the wall on writing this home built software which was crep. I had seen a completely computeriszed Hospitail bult about 4 years before in Toronto. My Uncle was the Administaer and we had the grand tour before it opened.This fellow directing the meeting was a cpmplete bullet head. I suggested that and software ordered be built to a universal spec so All 3 hospital could use it. He said that was impossible but that was there goal for 10 years.I muttered get a Mac and he flipped out. Then said "your a nurse,I am the computer expert"
I nicley told him my brother was finishing his PHD in Information Managment and he designed programs on his Kaypro in 1981. He studied my small business as a custom shoemaker to understand the applied end user product's needs, and he use me as his guina pig. Saw my first drafting computer that took up a whole semi trailer in 1975, when I working as a junior draftsman.
With excel on my mac, go to file, and hit Save As. Name it something like Ram Dass Estimate 3/13/09, or put an A or a B in the name... I have to date everything. Excel is very handy for anything repetitive, too.http://www.tvwsolar.com
I went down to the lobby
To make a small call out.
A pretty dancing girl was there,
And she began to shout,
"Go on back to see the gypsy.
He can move you from the rear,
Drive you from your fear,
Bring you through the mirror.
He did it in Las Vegas,
And he can do it here."
Yeh...I have all my files, estimates. proposals saved in My Docs but I really should put dates on the file name. Very good idea..thanks!
I keep trying to do Excel but then something always seems to come up and I'm back to my dopey legal pad..lol.
Here I go again...maybe this time I'll work it through...haven't really ever given up on it..just leep getting set back.
Now I need to do that "print screen" thing Rich was talking about so I can take a snap shot of my Excel page cept it ain't workin and I'm not gonna push it...I'll just keep working on playing with my spread sheet till I have it together.
Looks OK so far...I think..lol
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
andy
You don't need to do a snap shot to show us your excel worksheet.
Just click on attach files.
Browse
My documents
Click on the file you want to show us and then upload.
Then we open your attachment.
PS Press Control and Print screen at the same time, or control Alt and Print screen
Then open a word doc and it is ready to paste whatever was on your screen.
Rich
Edited 3/13/2009 8:27 pm ET by cargin
I'd go under without legal pads, got 'em everywhere... but excel is great for concrete, block, sheetrock, framing, roofing, trim take-offs... anything I use a formula to figure.But, for legal pads, what do you like? I like the docket ones with narrow spacing and extra stiff backs... no clipboard to scratch the soapstone counter tops.http://www.tvwsolar.com
I went down to the lobby
To make a small call out.
A pretty dancing girl was there,
And she began to shout,
"Go on back to see the gypsy.
He can move you from the rear,
Drive you from your fear,
Bring you through the mirror.
He did it in Las Vegas,
And he can do it here."
But, for legal pads, what do you like?
I don't care....any brand new legal pad works for me....I just love new ones! Yellow is my preference.
Clipboard? Might as well have one a them plastic pen/pencil holders in my top shirt pocket too...lol.
I even use a calculator...can you believe it? Cept I usually add things up from top to bottom then bottom to top.. I don't even trust myself with a calculator.....lol
An accountant'd be nice : )~
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
I took a couple of online community college courses. Wasn't recommended for mac users, but I'm a rebel. They were right. About halfway through the 2nd semester, we started doing stuff that didn't work with the mac version. I still got a lot out of it though. I think I saved all the classes as pdf's... it was sorta like excel for dummies. If you want I'll try to dig 'em up, if I can remember the dates<G>http://www.tvwsolar.com
I went down to the lobby
To make a small call out.
A pretty dancing girl was there,
And she began to shout,
"Go on back to see the gypsy.
He can move you from the rear,
Drive you from your fear,
Bring you through the mirror.
He did it in Las Vegas,
And he can do it here."
Snort
I lke the Cambridge pads with the spiral rings on the top so that I can neatly fold it over. No clip board.
White paper.
Perforated at the top for tearing off sheets.
Rich
Shoemaker1 —"Do you need a Canadian rep?"
Ya never know. In the past half a year I've actually had two contractors, one from Texas and one from Oregon pitch that idea to me so it's something I am thinking about doing one day.
"Years ago I was asked to sit on a computer users group at a hospital I worked at as a RN early 90's. They were still beating there heads in the wall on writing this home built software which was crep. I had seen a completely computeriszed Hospitail bult about 4 years before in Toronto. My Uncle was the Administaer and we had the grand tour before it opened...."
That's a little ironic in that my brother's company (SiriusInnovations.com) who I also work with from time to time is also Mac centric and their primary product The Sirius Enterprise Portal is primarily used in the Fire & Rescue Service and Hospital industry.
"Saw my first drafting computer that took up a whole semi trailer in 1975, when I working as a junior draftsman."
Also back in the mid 70's the road I grew up on we used to think of as "IBM Avenue" at times becuase it had the President a couple of VPs and researchers living on it. One of my friends actually had a computer in their house and it took up most of their basement.
I guess I was the odd kid out. While I took a class in Fortran while I was in high school I never worked with a computer until late 1990-91.
View Image
Jerrald
Thank you for the reply, and clearing things up.
I sell my services and I understand you have a service to sell to, so I am not looking for freebies.
Andy mentioned it and if their are better ideas out there, I am willing to look.
There are so many computer reelated things out there now that are free that we start to think that we shouldn't have to pay for our software.
Thank you for your time and input.
I am happy in excel, ( and obviouly don't understand it all) but I will give Filemaker a look see.
I really need to take a class on some of this stuff instead of learning by trail and error.
Rich
Cargin — "I really need to take a class on some of this stuff instead of learning by trail and error."
Rich I would highly recommend Lynda.com for getting some training. I't a great resource for training videos for anyone from beginner to advanced. I'm using it right now to learn Kuler, Flex, and Cold Fusion programming and I think it great becuase it allows me to move along at my own pace and I can skip over particular lessons that have no relevance to what I ned to learn or take the lessons in what ever order I so choose.
Their Excel 2007 Essential Training training series has 5.25 total hours of video to watch and the first 11 lessons are pefectly free to watch so you can them to help you make up your mind if the training would be worth it for you but you should check out all the training they offer on other programs and operating systems. When I had to learn Windows Vista to work with one of my clients that's where I went.
View Image
Hey Jerrald....I was at the library and bookstore a ways back to browse through an Excel book but all they had were the ones yer talking about which was 2007 but on my PC I have 2003....and in the books (07) it specifially said right in the beginning not to read the book b/c it will confuse you if you have 2003....I coulda swore there was someone looking through a camera hidden in the book at ME!..lol.
How'd they know I had 2003?.. I suppose thats the years they built the programs? 2003 then again in 07?
The tutorial/video in "Help" in my program won't allow me to watch it for some reason....too bad.
NExt time I'm on the phone w/you I'll getcha to walk me through how you take a snapshot of the page. What Rich explained to me doesn't seem to work. I must be missing a step or sumpin'.
Till later
PS...tell yer dog to get back in the house...lol
a
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
By the way Dan while it doesn't touch on the actual nuts and bolts of task estimating you might also want to check out William Asdal's excellent book Defensive Estimating: Protecting Your ProfitsView Image. I bet it will help change the way you think and approach the task of estimating.
View Image
One modification I've been trying to implement is separating tasks from the job.
When I was working for someone else, I'd get a question from the boss like, "how long to assemble and install a run of 4 IKEA wall cabs in a laundry room". Not ever seeing the site, I'd say, "me and a helper, 4 hours including in/out".
We get to site, meet the homeowner, carry in our gear 3rd floor walk up (+ 20 mins), demo and carry out existing steel cabs (+40min), find out hubby picked up the cabs and they are in the basement and we only have room to assemble (then hang) 1 cab at a time (+60 min), the new washer & dryer are oversize and there is no room to move them out (+20 min for extra care), ect.
What I'm considering is a checklist to develop a "site factor" or multiplier to apply to the time it takes to do the actual task.
Depending on the project, certain site issues like masonry walls, distance to the work area from the truck and or cutting station, power and clean-up water availability, work hours, stairs, and even distance to closest hardware/lumber.
A simple example would be hanging cabinets on masonry walls. Whether I choose to add a cleat or use anchor direct, the additional material costs are small, but, now I need a hammer drill and good bits on site and there will be additional labor for both install and cleanup.
So, if my usual install rate is .5 hrs/lineal foot, I apply my "masonry factor" of 1.15, I estimate like this for a 8' run:
(8 X 0.5)* 1.15= 4.6hrs and round up to 4.75 hrs for estimating
So far it's been very helpful (accurate) when estimating tile work, especially when the client is switching back and forth between ceramic 4x4, glass mosaics and stone 12x12's. I'm working on the rest.
Good luck to us all!
Â
Â
Â
Â
The awful thing is that beauty is mysterious as well as terrible. God and the devil are fighting there, and the battlefield is the heart of man.
- Fyodor Dostoyevski
Edited 3/10/2009 2:25 pm ET by FNbenthayer
Andy
Loud applause from the peanut gallery.
Hooting and hollering. Way to go Andy.
Now if you would have posted it as an attachment then we could make changes to it and repost it to you.
By the way you have 100 LF of trim at $3 a ft and the total is $100.
Must be a glitch in that MS program. LOL
Way to go.
Rich
Rich
By the way you have 100 LF of trim at $3 a ft and the total is $100.
Yeh...I didn't use any of the formula functions...I just typed stuff in..nothings added up..obviously...just trying to see where to put things before I add it all up..C'mon...I was busy learning how to do a snapshot...geeezzz Rich...lol
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
andy
I have been messing with your worksheet.
I did a screen shot so that you can continue learning how to do the formulas.
I will post the worksheet if you want it.
I couldn't get it to embed in the post.
Rich
I like it...specially all the colors...lol. Question though about why all each individual total $$ on each line aren't next to the item like the 5 doors at $150 each..the $750 total is a bit further down on the list.
And...did the prices just automatically fill in... in the empty spaced rows?
The colors you just used for clarity, right?
On the right...the grey...the total at the bottom that juts out to the left...where'd that 1275 number come from? Doesn't seem to add up that way...(on my legal pad..lol)
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
Andy
Sorry, it was late and I was cutting and pasting and wasn't watching real close.
Try this.
Once you get the formula right in row 3, B3*C3. Then take your cursor to the lower left hand corner of the cell. An arrow will appear. Click and drag. This will fill all the cells underneath with progressively changing formulas. B4*C4 sna so forth.
I redid the hours with Sum. Looks like a big Roman E at the top. In the cell that you want a total in press sum the lines appear around a group of cells. Highlight the group of cells you want to be totaled and press enter.
Colors are to group materials. Framing, SR, Millwork ect.
Rich
Whats: 10% "CONTING" stand for?
I've been revamping the sheet as I'm thinking of things just to be able to play with it until I get exact numbers on Monday.
The math or formula's...seems it's main function is adding things up unless theres a way thats just as simple...to do: x 20%= or sumpin' like that. I've just kept a calulator in the upper corner of my screen to keep pulling down. Is that how most of all y'all do it?
I reckon you really wanna have all your numbers finalized before you total everything up cause I see it can be a lot of extra added time refiguring (changing) all the add-in's like tax and mark up etc etc, I almost wanna just lump all the taxes and %'s together and just say 40% but then I'd never know what was what really so.....
What else have I noticed? I did notice it's getting me to break things into units rather then into small jobs the way I'm used to figuring it. For instance I happen to come up with five hours (and I'm charging $75 per hour) to do all the molding work in the hallway..remove and replace.. and then noticed there happens to be five doors...hence $75 per door...so now I guess I'll always figure $75 poor door unit to remove and replace the molding from start (picking it up) to finish(cut, install, caulk & fill)~~(2nd floor)..............so that makes things easier right away.
Then I'll get to really see how my numbers work for hours. Should be interesting.
You didn't add Mark Up on your labor total...just forget to?
I haven't really read through the tutorial again yet past the first cpl of posts...being you sent me that sheet back done up the way you do it.....I ended up playing with mine some more...after looking at yours...just figuring/trying things out from what I remember from when I read your posts several months ago..I'll go back again when I finishing just trial and erroring it for a bit.... Like a kid with a new video game..lol.
Link below I copied and pasted onto a new TEST sheet but the paste is a trifle screwed up but no biggie...it shows good enough for this..
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
You wrote:
_______________
The math or formula's...seems it's main function is adding things up unless theres a way thats just as simple...to do: x 20%= or sumpin' like that. I've just kept a calulator in the upper corner of my screen to keep pulling down. Is that how most of all y'all do it?I reckon you really wanna have all your numbers finalized before you total everything up cause I see it can be a lot of extra added time refiguring (changing) all the add-in's like tax and mark up etc etc, I almost wanna just lump all the taxes and %'s together and just say 40% but then I'd never know what was what really so.....
________
The main point of using Excel is that it is NOT necessary to get numbers finalized before calculating - indeed it is simple to set things up to calculate 'Number X 20%'. You should never need to use a calculator when using excel. You set up the model with calculations,then as you change input information,the calculated results change.Do you have a fast internet connection (not dialup). You can go onto Youtube and search for 'basic excel tutorial' and get lots of short videos to explain things. Here is a link to one of them:
http://www.youtube.com/watch?v=FNPNS6MT1Kk&feature=channel
but this is not necessarily the best one there is.
Thanks...I'll check it out. Actually I really need to go through Rich's whole turtorial again b/4 I ask any more questions. I went through it several months ago but forgot most of it being I became busy with other things...for a change.
I happen to be at the library this morning and thought to check out what books they might have for the Excel thats on my PC (2003...not 2007) and lo and behold they had two books....Office 2003 For Dummies and Simplified Microsoft Office 2003 both with some good Excel chapters so hopefully I'll get to go through the video you linked me to and the books and Rich's Turtorial before I have to start work again on Monday/Tuesday on a new project.
The few hours I already spent yesterday flew by b/4 DW kicked me off the puter so she could use it as she did again today....grrrrrrrrrrr.
I figured using a calculator wasn't necessary. I spose'...I just used it to get my page set up a little more and then I planned on going back to figure all the functions out.
Thanks much and I'll letcha know how I done did before the week is up..
andy
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
Andybuildz:If you're looking for a book about using Excel specifically for construction estimating, look for "Estimating with Microsoft Excel", by Jay Christofferson. It takes you from simple spreadsheets, and progresses to show you how to use Excel's functions specifically for estimating. Like anything else, the more time and effort you put into it, the more benefit you can get out of it.
Thanks elicon...yep...the more time I've been spending the easier it's becoming..although I almost gave up a cpl of times on some things but like usual...I keep pluggin' away at it and voila' : ) I HATE numbers but I hate being disorganized more so....so I'm just F'n doing it!!!! LOL
I'll check to see if the library can get me that book but I think that guy also sells software if I remember correctly so I wonder if his book is an ad for his programs...hmmm...free from the library would work though : ) .....Rich's tutorial is very helpful so for now I'll stick with the few things I have so I don't totally confuse my delicate little mind...lol.
Thanks dude
PS...I think I'm starting to have withdrawals...I keep sweating and looking around for my yellow legal pad and calculator..lol
a
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
Edited 3/14/2009 10:35 pm ET by andybuildz
Just call your mentor...don't grab that pad yet Andy.
I was a yellow pad guy till I figured out the excell program last year. Now, I immediately go to my template and tweak it for any estimate.
One of the nicest features of my worksheet is the "progress payment" area. I "grab" chunks of the total contract to create the payment schedule. I usually move lines around to create a cashflow that I am comfortable with and it's all done automatically after I cut a paste a line.
For instance, the current client opted to forego a skylight. When I zapped the skylight option out of the schedule, the payment schedules all automatically reset.
One thing I quickly found out about spreadsheets is that I can't understand anyone elses spreadsheet and no one can understand mine LOL.
I still keep a yellow pad for jobsite notes but all my calcs are done on the spreadsheet. I create random "work areas" where I group and clump specific calculations to get the numbers that I need. My spreadsheets look like a couple pages of yellow pads with seemingly nonsensical "scribblings" off to the side. Then, I plug the results of these "sidebars" into the spreadsheet. They work for me.
One of the nice things is that I can "estimate" a similar bathroom in five minutes and know that everything is covered....including the payment schedule.
I highly recommend sticking with it and urge the stubborn holdouts to give it a try.
jim...it was GREAT reading your post. It gave me hope...lol. You sound like you almost answered the post I just posted before you even saw it 117568.103 lol.
You mean I can put my scribblings somewhere in Excel??? Don't even tell me that...lol. I do need to figure out what you're talking about in your post. I wanna figure out how to use it for other contracts
I'll get there.....I think..eeeeeeee
http://www.cliffordrenovations.com
http://www.ramdass.org
Jim,Your comments about automatically generating/updating the payment schedule caught my attention.Earlier in this thread I posted my estimating spreadsheet. I want to mention it because the functionality that summarizes the 'Estimate' sheet into the 'Summary' sheet might be interesting for you. The result is probably no better than what you are doing now, but the mechanism might be an improvement. What I like is that I can generate the guts of the estimate in whatever sequence I want. For example I can work in the 'walls' section and estimate structural things like studs and framing labor, and then finish items like baseboard, or subcontractor jobs like dryall, since these are all based on the same underlying parameters, ie linear feet of walls. But then by summarizing with the 'line item' code and the SUMIF function, the summary (which could as easily be a payment schedule) can be generated according to a very different logic, perhaps separating rough tasks from finish-related tasks/costs. The nice thing is not having to rearrange the sequence of the estimate to achieve a different sequence of summarization.
leegs
If I may be so bold.
But then by summarizing with the 'line item' code and the SUMIF function, the summary (which could as easily be a payment schedule) can be generated according to a very different logic, perhaps separating rough tasks from finish-related tasks/costs. The nice thing is not having to rearrange the sequence of the estimate to achieve a different sequence of summarization
What?
Some of us are light years behind you here. Can you show us what you mean in a very simple worksheet. I mean very simple.
I thank you for sharing your previous worksheet, but its safe from me stealing it because it was way too complex for me to understand.
Thanks
I understand if you don't have time.
Rich
Thats a good idea - see attached. Its bed time, so further explanation, if desired, will have to wait until tomorrow.Greg
greg
It's bedtime for this bonzo too.
I made a copy of the worksheet, it makes for easier viewing.
It will have to wait for morning.
Many thanks. Many thanks again.
I got andy's 1st excel attempt.
He made great strides. I reworked some things and reorganized some areas and sent him a copy, but i was impressed with the progress he has made from his last post.
Rich
Greg
I spent some time this morning going thru your worksheet. I think I understand the concepts.
I work in a simpler format. I design worksheets for specific applications with a limited number of materials.
I like to use a format where I can move down a list of materials and just enter in quanities.
I am going to attach my roofing worksheet. The top has a SF calculator.
That generates the # of squares. The actual eave and actual rake have to be entered manually because there are so many different combinations or rake on one side or rake on two sides, it's best to just look at the totals of the eave and compare to the roof layout.
The yellow cells or white are for data entry. I use yellow to call my attention to them. Grey cells have formulas in them.
The materials list is generated from the list of questions and the SF caluculator.
The default setting for labor is $65/SF. I usually move that around or base my SF number on many factors. Access, pitch, number of layers distance from home, time of year.
I want to achieve $110/hour for a crew of 3. That is why I have the number of days calculated by $110/hour. Then I can cross refence my labor number.
The Duration shingle is an upgrade from our standard shingle.
Rich
Since that article was published, I updated my roof estimating portion to calculate up to three different pitches. I have it set up for a major, minor and additional pitch areas, and it calculates the pitch factor and applies it to the flat roof area and the flat gable lengths. All values calculated from the roof framing tab automatically transfer to the roof coverage tab and the soffit and fascia tabs (one for aluminum and one for cedar).
Bill
Interesting. Thanks for the reply
I walk most of my roof jobs, because in reroofing I need to see so many things to get a handle on pricing.
Once in a while I do the measuring from the ground , that's why I have that little tool to the right for figuing the length of the rake.
I measure strong in the field, so i don't have waste figured in.
Rich
Just keep in mind what an estimate really is. What you're doing is exactimating (I hate that term), and I hope you're not doing that without getting paid first.
Bill
Free estimates is a can of worms here on BT. Them's fighting words.
I live in the land of free estimates. When I get the job I bill for my estimating time.
In my bill worksheet I bill for for some office time on every statement.
But it is difficult to get paid for every hour that I spend in the office.
Therefore I try to make myself more efficient with the time I am in the office.
Office efficiency is a continual pursuit. I am not an office type of guy either
Having BT around as a distraction. LOL
Rich
Bill, that looks like an application that I would be interested in. Have you shared that somewhere?
If I was still framing houses, I could see myself loading every number into a spreadsheet....kinda like creating a computer idiot stick.
I might just build a roof, cornice and siding height worksheet for the fun of it LOL!
Rich,I like your sheet a lot. It is well organized and easy to view, which makes it easier and less error-prone to use.I have (and will continue to consider) toyed with improving the task-specific logic in my sheet to be more like yours. In other works, to build in more smarts and specific calculations for roofing for example. However I haven't done so thus far, because then I would have more work (either manual or in spreadsheet development) to integrate all of the results into an overall estimate. Our jobs are typically additions with significant remodeling, which involve all construction phases. So I have opted for an approach that focuses more on managing and consolidating the mass of data, and is less helpful in terms of job-specific calculations.Note that within the subsections on the estimate page, i do have some simple calculations - such as number of studs in the wall section, roof area based on pitch in the roof section, etc. But its admittedly pretty basic stuff. And I have to manually specify the materials and quantities based on those calculations - no automatic material calculation.Regarding the issue of listing all commonly used materials in the estimate form, and then just entering quantities for the ones to be used - I struggle with this approach. Consider roof rafters - could be anything from 2x6 to 2x12, in any of 4 or 5 lengths each. I don't want to have to skip over so many unused items to find the ones I need. Hence my approach using VLOOKUP to retrieve info from the materials list.
Similarly, one needs 2x6s for studs, for plates, for ceiling joists, etc. But it could be 2x4s for these things. So I don't want to have to list so many optional materials for each specific usage.Anyway, I am definitely not arguing that 'my' way is any better - just explaining some differences in design philosophy in hopes of adding to the discussion. And I can say for sure that my spreadsheet will continue to evolve and change - its definitely a work in progress.As an aside, I remember Jim (Blue) talking about estimating studs based on 1 per ft, regardless of stud spacing. As a novice I sort of discounted that. Now I'm going towards 2 studs per foot! Where do they all go?Greg
leegs
I am not going to argue my way is better.
In my additions worksheet I don't list every type of 2x6. I list 8' and 9' studs and then 2x6 LF. Same with 2x4s.
Same with I-joists or 2x10s. Just LF price. I know it varies with length.
I get a 16' length price and ask for the over the counter price. I then use these prices to figure the one day jobs or the full additions. We don't do very many additions.
I can get a better price if I take a complete job list to the yard and ask for a price, and hint that the yard in the neighboring town also has the list.
If I get the job I can figure out the length of rafters or the length of joists.
I usually do an estimate for a big job by doing a preliminary estimate and then finding out if his job is within their price range. Then we can get down to brass tacks and pick out the specific doors, windows, cabinets and all the little decisions that can really add up.
Like Mike Smith taught me. Most often people are getting only one price. I work for alot of repeat customers.
Rich
Thanks for sharing that tutorial leegs.
I think I'm somewhat doing the same thing but yours actually looks like you know what you are doing. I just plug along and connect things together but if a plumber looked at it....he'd know I'm a carpenter LOL!
I'm going to try to figure out that line item designation thingy. I can see an advanatage by taggin the line with a keyword and having it automatically load someplace.
andy
10 % is for contingency expenses.
I do not mark up labor. I set my rate to cover my expenses.
As I mentioned in a much earlier post http://forums.taunton.com/tp-breaktime/messages?msg=117568.18 I set my basic worksheet up to have several pricing variables at the bottom.
I always mark up materials 20% and then labor as I figure it.
scenario #2 is Mat 20% and labor up 10%
Scenario #3 Mat 30% and labor 30% ( for alot of unknowns or I don't really want the job)
With a couple of clicks I change any of those #s.
I work in an enviroment where many contractors ar charging $25-$30/hour and are marking up materials at all.
To figure the total 1120 LF of trim at $3 in D3 press= then high light B3 press* then highlight C3 and press enter. You have just created a formula. The formula will show up at the top of the page as =B3*C3.
You can also manually type that in. Now go to D3 put your cursor in the bottom let corner until an + appears. Click and drag down and the formulas will fill in.
Rich
Gotcha...I've been looking through these couple of books I took from the library today which are real helpful being I don't have to click back n forth between screens...but it's good to then spend some time re-reading your tutorial to see what I may be missing being you're working from a construction stand point like me...unlike the books I have....so between everything I'm doing OK so far.
Here's what I have after just a total of a few hours. I pretty much have the formula thing figured out...at least the basics to keep me going. I'll play with what I think I know so far then get a bit deeper into it....like copying the formulas for proceeding columns so I don't have to refigure each column if they all happen to be the same.
Also I just got the parenthesis thing when breaking a column of numbers up to add and maybe use one cell to then multiply the above added numbers....I think I got that figured out a minute ago : ))
When I read ahead I'm like...ut oh...I better stop before I get discouraged..looks confusing.....but I'm still creeping ahead s l o w l y...lol
Lets please not talk about functions yet.....tomorrow maybe...lol
Edit: the labor/etc. numbers I added in are just arbitrary for now...I'll put all the real numbers in when i do this as the real deal.
http://www.cliffordrenovations.com
Why do I keep...http://www.youtube.com/watch?v=n7ZkQC0riwc
http://www.ramdass.org
Edited 3/14/2009 10:14 pm ET by andybuildz
andy
Give up on Excel? No..not in the least. I even got 2 books out of the library to keep puter side along with your tutorial.
Didn't ya check my last entry back in the other thread?
I've done pretty well so far in a short time.
Have my formulas figured out and a buncha other stuff. At least enough to actually use it without my calculator...lol.
The Above is from another thread
Take your hand away from the calculator and slowly back away and nobody gets hurt. LOL
I wasn't sure if you had made any of the changes I suggested in an earlier post. And then somebody deleted a post.
I just wanted to keep encouraging you to stick with it..
Rich
I JUST finished an entire proposal using it but don't wanna publish it here if you know what I mean. I could send it to you in a PM if you wanna see it but any which way....I can see the other things I need to learn by doing it this time.
I need to figure out how to pull items that would be the same for other estimates...I think that'd be under "functions"? I read through a lot of it but it was kinda confusing. I'll have to re read all this stuff and see if I can absorb what they're saying. There's SO MUCH to learn in it. Labeling cells, reference ranges.. and yada yada yada....sheeshhh.
I love how when I change a number the entire thing re-adjusts (most of the time...hmmm).
Took me forever to do this proposl with it but it's DONE! : )
I spose' I'll learn a bit each time. A class would be nice but for now......this'll do.
Thanks
andy
http://www.cliffordrenovations.com
http://www.ramdass.org
andy
e-mail me your worksheet and I'll look it over.
Jerrarld said I should label my cells with insert and names.
Everytime I try that I get a dialog box that says I used an invalid name.
I am not sure what you are talking about with functions? I am a novice.
Rich
rich...soon as dw gives up the pc I'll get back atcha. I'm on my lil' phone now and suck typing on it...I'll then try and explain what "functions" are the way I see/read it. You probably already know what it is...just not that its called that.
http://www.cliffordrenovations.com
http://www.ramdass.org
andy
soon as dw gives up the pc I'll get back atcha.
Best of luck.
My DW has a laptop, and yet she still finds things she needs to do on the desktop.
she's really pretty good about giving me computer time. We have 4 in the house, but DD is so screwed up from all the downloading she did when she was 15.
I am working on my office/spare bedroom now. When it is ready then i will buy a new Desk top just for the business and get my own e-mail address.
Rich
Rich...OK...I sent you the completed excel spreadsheet I made for this job.
btw...Functions are a speedier way to enter formula's. apparently they're ready made formula's that perform a series of operations on a specified range of values so it says in my book .....but you already knew that wise guy...lol.
Now all I have to do is figure out how to do all of that.
edit: OK...I just caught leegs post above mine...gulp...I better hurry up and read a lot faster...geezzz...lol
http://www.cliffordrenovations.com
http://www.ramdass.org
Edited 3/16/2009 9:24 pm ET by andybuildz
Regarding functions:If you are doing something in Excel, and it seems like its working but its sort of awkward or tedious and you wonder if there is a better way to do it, 9 times out of 10, there is a better way. So any time you have that nagging or dissatisfied feeling, take the opportunity to search in help to see if there is a function that supports the task you are trying to perform. This is a great way to improve your proficiency, by finding little 'excuses' to explore help to learn new things.
thanks leegs...yeh...I sorta got that..now I just need to explore that a lot more. I know thats the next step for me. I hate directions like a lot of us do so I try things by feeling around first and playing with different functions on my Excel screen and when I'm totally confused...thats when I start looking at the directions...lol. If all else fails.
It's still real confusining for me b/c this is like another language. I've NEVER dealt with any kind of program at all so this is truly excel for dummies...I mean dummy..lol.
http://www.cliffordrenovations.com
http://www.ramdass.org
Rich, interesting thread. I live and die by excel daily. I have prepared over US$30B of estimates in the last dozen years or so.The mother of all estimates I created recently was for a US$2B+ job, it consisted of 50+ worksheets, was over 6Mb and several hundred pages complete with graphs, etc. I have several templates I use, but they vary from client and job type. In my profession I am interested in getting to the bottom line and it is often not an exact science. Even in my contracting days, I was not a proponent of exact measure as I think this is often a waste of time and does not reflect reality. In my line of work I reconcile with contractors regularly and have to reflect current market conditions. Since I deal with all the major players in my market, NYC, it is not that difficult to get it right. In fact, I often save my clients money. Having said that when I built my own house recently I had a stripped down version in one sense with a tracker for labor, and a draw down on contingency. It was more a test to see how it would work in real time, since I was a big component of the labor. I once posted something here a few years back IIRC or maybe I sent it by PM. A lot of my formats I have built for clients so they are proprietary and confidential so I cannot share them here. I will try to fashion something that might give you a little more flexibility when I have some free time. BTW, just got offered a very attractive position in Dubai.
Edited 3/16/2009 9:54 pm ET by TGNY
From: cargin — "Jerrarld said I should label my cells with insert and names.
Everytime I try that I get a dialog box that says I used an invalid name."
Regarding cell naming...
Total Direct Job Costs — invalid name
TotalDirectJobCosts — valid name
Total_Direct_Job_Costs — also a valid name
View Image
Jerrald
I just figured out that you can not space the name tonight.
I don't see the point.
I have a basement worksheet. Bathroom worksheet is a seperate sheet.
Then I have the primary materials and labor sheet.
The Mat and Labor subtotals appear on my primary worksheet.
I named the cell bath_labor and that is what it shows up as on the bath sheet.
But on my primary sheet when I click on that cell it says =bath 'I67
Thanks for taking the time to comment.
Rich
What do you mean by saying that I "was" smart. I'd like to think that I'm still in that category ;-)I was told that my name popped up on this site and figured I'd check it out.
Bill
Thank you for your article on using Excel.
I have the hard copy (somewhere) and it changed the way I do my estimating.
And I now keep a copy on my computer for reference.
After reading that I moved from Works to Excel and I started developing my own stable of excel worksheets.
Now I do all my thinking in Excel. I am a visual learner, if I write someone ones name down then I will remember their name.
If I want to estimate a job or bill for a job, or schedule I turn to Excel.
See my post to leegs for the kinds of worksheets I built after reading your article.
http://forums.taunton.com/tp-breaktime/messages?msg=117568.123
Again many, many thanks.
Rich
Edited 3/17/2009 8:16 am ET by cargin
Edited 3/17/2009 9:08 am ET by cargin
andybuildz —"I need to figure out how to pull items that would be the same for other estimates..."
That's where my program (or database programs in general) beat up on excel based programs.
So Andy I have to go out to Lawrence sometime in the next couple weeks. I'm thinking either later this week or sometime next week. When ya goona be around. I'll swing over your way after I'm done out there and beat some computer sense into you. I going out to Manhassett for that family gig I told you about on Sunday but this trip to Lawrence is business.
I think I can give you a good intense session that will get you up and running on both my program and boost your excel skills.
Email your proposal workbook to me too so I can take a look at what you have done.
View Image
Jer...gotta give my guy all of this week Tomorrow into Friday b/c his FIL will be painting the rooms on Monday I'm doing all the trim in. Next week I can probably take off whenever you'll be around to beat some sense into me...lol.
Manhassett is where Katrina grew up....it's about 25 minutes West of me also on the north shore.
As I said before...I thought learning Excel would be a good first step before I play with your program being your program is also Excel based in part so my guess is nothing I learned will go to waste.
I'll shoot you an attachment of my first Excel attempt.
http://www.cliffordrenovations.com
http://www.ramdass.org