Lawn Service - Need help with estimation equation for my business

Brendon Willis · 2011-01-06 18:24:03

Hello all, you seem very helpful.

I am preparing a systematic bidding and estimating process for my company this year so that as I expand, other employees can prepare estimates for me. Also so that when costs go up, I can merely change a multiplier and update all my prices. I am working in Excel. I don't need Excel help at the time, I need equation help! A bit of useful information;

When we show up at a lawn, we generally do four things; Mow, string trim ( AKA weedeat), edge the sidewalks and driveways, and blow off any debris.

I am trying to develop a simple and systematic estimating process for my mowing services. Calculating the price per 1k square feet on weed control and fertilizing has been easy. Now I'm to the weekly maintenance part. Here is what I have come up with so far for finding the price. PLEASE critique me and help me on brainstorming if you have any ideas.

I need to calculate mowing per 1k square feet, edging per linear foot, and string trimming per linear foot. The "blowing off" of the debris is equivalent to approximately 60% of the linear feet for edging. Generally, mowing is the longest task, string trimming second longest, edging is third longest, and blowing is the quickest task. I have come up with a realistic and basic lawn "per thousand" equation;

Minimum charge for any lawn. ($35) + $1.00 per 1000 sq. ft. This keeps me where I like to be on half acre lawns and full acre (43.56 thousand square feet) lawns ($75-80). And half acre lawns at $55 which is about right. Problem is below;

I based this on my $75/hour labor rate and 40k square feet taking 45 minutes. The problem is that last year I kept records of our time (me and my guy) on lawns, but didn't keep time on separate tasks; mowing, edging, and trimming. So I need to figure out exactly how to reduce my mowing price per thousand proportionally in order to add the edging and trimming prices INTO that equation and still keeping on AVERAGE the same price per thousand. (Normally the amount of trimming and edging is directly correlated to the size of the lawn. On commercial accounts however, there can be a much more significant amount of edging and trimming.

Ideas? Help?

Bob · 2011-01-07 08:30:21

hi Brendon,

I've had to make some assumptions; but I'll say what they are so you can adjust if you want.

Minimum charge for any lawn. ($35) + $1.00 per 1000 sq. ft.

so I'll take the $35 as a call out charge.

I based this on my $75/hour labor rate and 40k square feet taking 45 minutes.

so I'll assume that the edges take 15 minutes.

So I split the $75 as follows: $35 for call out; $30 for mowing; $10 for edges

Here's my spreadsheet formulas:

and here's some calculations based on that an acre:

That, at least, gives your figures for an acre and for a half acre.

Limitations of the model:

(i) You only get $75 for a 40 figure in C2. If the job is much larger the rate per hour drops as you only get the $35 once. That's probably ok for jobs that last less than a day, because you are giving your 'better' customers a lower proportionate price and you have only got to travel to the job once. If you had a huge job of, say, 4000K sq ft the hourly rate drops to about $40. You could allow for this by making a daily call out charge.

(ii) I don't know how long the edge type jobs take. The edge will vary with the perimeter, which is not proportional to the area. The example works ok but, if you get a long thin lawn to do, the area may be small but the edges make take a long time. Try comparing two rectangles of 250 x 160 and 40 000 x 1. These have the same area, but the perimeters are very different!
To get around this, you'll have to time all four jobs and then come up with a realistic price for each. You can substitute new values in C3 and C4 for this, or even create two new rows to cover edging, trimming and blowing off as separate prices.

Hope that helps,

Bob

Brendon Willis · 2011-01-08 06:31:11

That is very helpful. Thanks so much!

Brendon Willis · 2011-01-08 10:18:48

Great input. My difficulty now is calculating the time and price for each extra crew member on the job site. I did my front yard today and came up with this;

18,224 square feet to mow = 14 minutes or .77min/1k square feet
545 linear feet to edge = 4 minutes or 136ft/min
235 linear feet to trim = 1.25 minutes or 188ft/min
18 trees to trim (spacing will always be different) = 3.75 minutes or .2min/tree
and blowing off drive and sidewalk is about 1.5 times the time it takes to edge

It took me a total of 29 minutes. Here's the approximate percentages;
Mowing = 48%
Edging = 14%
Trim Linear = 4%
Trim Obstacles = 13%
Blow Debris = 21%

Based off of these percentages I can gather some useful info;

How many guys should be on this property. If I can make fairly "even groupings" of divided percentages, I can assign that number of guys. Kind of confusing but example;
Here, Mowing is about half. Since it's half, it would only be efficient to have one guy mowing and one other guy to do everything else. However, if mowing was closer to 33%, and some two other methods of grouping by task could equal approximately the same percentage, 33%, then I could send 3 guys and they would all finish closer to the same time. (or 6 guys depending on the size of property, etc...) If I had 3 guys on the property where mowing was 50%, and had only one guy mowing, then one guy spends 50% on the mower, and the two guys manage the other 50% in double time, meaning they have to sit there waiting on the mowing guy to finish.

My problem now is pricing. I guess I take the total man-minutes per property and add those up and multiply them by my labor rate of $1.25 per minute. I was hoping that my "estimating" system in excel would tell me the total time it would take to finish the job as well, but it will only be the total man-minutes, not the total on-site minutes. Any ideas on how to achieve the total on-site time out of all of this data?

Brendon Willis · 2011-01-08 12:17:33

18,224 square feet to mow = 14 minutes or .77min/1k square feet = $17.50
545 linear feet to edge = 4 minutes or 136ft/min = $5.00
235 linear feet to trim = 1.25 minutes or 188ft/min = $1.56
18 trees to trim (spacing will always be different) = 3.75 minutes or .2min/tree = $4.69
and blowing off drive and sidewalk is about 1.5 times the time it takes to edge = $7.50
$36.25 at 1.25/minute plus the $35 call out is $71.25 which is too expensive for a 18k square foot lawn by about 15 bucks. Hmm.

My labor rate needs to either be down around $40 or my minimum charge needs to be $20. But I won't do a lawn for less than $35.00. Hmm.

I haven't calculated my man-hour rate yet. I just threw out $75, which seems pretty steep compared to most other guys. Once I figure my actual rate up, maybe I will get better results.

Bob · 2011-01-10 00:49:50

hi Brendon,

Wow, that's a lot of data.

I don't think there's an easy way to automate the decison making process for how many staff to deploy, using Excel. It's almost becoming a job for relational database software like Access. For a small company it would take more hours than it's worth to create the algorithms. So what would I do?

Use your data to calculate how much of a task can be done in one hour by one person. Do this for all your tasks and leave space to expand this later.

Enter these into cells that you can then use in subsequent formulas.

Have cells to enter, for each task, how much has to be done, and alongside, how many people you intend to assign to the task.

Then you can set up formulas for how long each task will take and hence the total time.

You can look at it, and make decisions yourself for whether to use more than one person on a job, and that will reflect in the total time taken. But it won't stop you from double booking someone's time; that's where you'd need a relational database that handles people's hours of work.

Do this for a typical job and see how it comes out.

Then allocate cells to the pricing per hour for each task and use these figures to calculate the cost per task, the amount you will pay to each employee and the total cost to the customer.

Then try it out on other jobs. You may need to 'tweek' the figures to reflect types of job.

Eventually, when you've run it for a while for yourself, you might then be able to make invoices from it. Do you know enough about Excel to transfer data onto a second sheet so you can make a customer version of the pricing? You can set up the 'workbook' so that the front sheet is the one that the customer sees, with the finished quotation on, whilst keeping the other bits hidden on subsequent sheets.

Once that works you could also have a sheet per employee, that details the jobs they have done and how much they have earned.

This data transfer to other sheets can be automated. If you haven't done that before, it's best to take it a small step at a time.

Don't let other employees use it until you've got all the bugs out and then protect / hide cells that you don't want messed up. Keep backup copies for when they accidentally delete the master!

Bob

Brendon Willis · 2011-01-10 13:52:09

Thanks, Bob!

Yeah, initially I just planned on creating this as an estimator for bidding contracts. I think I will keep it simple and just as an estimating program right now. I have learned how to transfer data automatically between sheets. I will be useful as I have a sheet for materials and supplies costs and markups, as well as a sheet for labor times per [xx] units. Right now I'm working on the labor sheet. Then I will combine my costs, taxes, markups, and profit onto a sheet for me to see, save, print out for accounting, or adjust numbers manually. And then I will have a fourth sheet which will be the final estimate sheet for the customers. All I will need to do on my bid prep sheet (the third sheet) is fill in boxes for how many square feet, linear feet, or specific attention items my customers have. Turf, flowerbeds, bushes, trees, edging linear feet, etc... It will automatically apply all my pricing rates and material requirements.

The only thing I wish I would be able to do is keep all of these in a database and if I need to change my labor rate or some other cost, I can go in and change it and it will automatically update every contract. I think this is something for Access though.

Bob · 2011-01-11 00:46:34

hi Brendon,

If you put the variables at the top of a workbook then all formula cells should update when you change prices. The only thing to watch for, and it happens in Access as well, is that a price increase will show up in all the old contracts as well which means your achives are wrong.

You can make an archive copy on a separate workbook page using paste special, paste values, that will prevent subsequent changes to old data. It should be possible to automate this but I think it means learning how to make macros. You might prefer to steer clear of that and just do it 'manually' (ie. copy and paste special) when a contract is complete and when you are about to update your charges.

Before I retired I taught maths and ICT. The Advanced level ICT course required students to do the sort of thing we've been talking about, as coursework, so mail again if you want to know how to do something. I had quite a few worksheets on 'how to do xxx' , mostly for Access, which I could make available if you're interested.

[http://www.bundy.demon.co.uk/rbuhelpfiles/aanRBUhelp%20Page.htm]

Bob

Last edited by Bob (2011-01-11 00:53:25)

Math Is Fun Forum

#1 2011-01-06 18:24:03

Lawn Service - Need help with estimation equation for my business

#2 2011-01-07 08:30:21

Re: Lawn Service - Need help with estimation equation for my business

#3 2011-01-08 06:31:11

Re: Lawn Service - Need help with estimation equation for my business

#4 2011-01-08 10:18:48

Re: Lawn Service - Need help with estimation equation for my business

#5 2011-01-08 12:17:33

Re: Lawn Service - Need help with estimation equation for my business

#6 2011-01-10 00:49:50

Re: Lawn Service - Need help with estimation equation for my business

#7 2011-01-10 13:52:09

Re: Lawn Service - Need help with estimation equation for my business

#8 2011-01-11 00:46:34

Re: Lawn Service - Need help with estimation equation for my business

Board footer