Register free!

The Green Industry's Resource Center



Reply
 
Thread Tools   Display Modes
  #1  
Old 02-26-2002, 11:40 PM
CSRA Landscaping CSRA Landscaping is offline
LawnSite Bronze Member
 
Join Date: Apr 2001
Location: Beech Island, SC
Posts: 1,232
Using Excel to create a database ...

... that fills in the blanks for me.

I'm wondering how I would set up a database in Excel that made it possible to enter the Property Name, SF of Turf, & SF of Beds and then have it spit out prices in various services like aerating, ferts, mulch, etc.

Any ideas?
Reply With Quote
  #2  
Old 02-27-2002, 12:07 AM
PAPS PAPS is offline
LawnSite Senior Member
 
Join Date: Jan 2002
Location: Oakland, NJ
Posts: 404
guy..

thats just wayyyy to hard to explain over the computer... it can be done though... and its a good idea... just read a book on excel, or use there HELP feature
__________________
Bryan P.
PAPS Landscape Design & Construction
Oakland, NJ
Reply With Quote
  #3  
Old 02-27-2002, 11:25 AM
Newbie Newbie is offline
LawnSite Member
 
Join Date: Sep 2001
Location: Tuscaloosa, Alabama
Posts: 12
If you understand formula's in Excel, it should be pertty easy. You can set up one sheet using bordered and colored cells to make it easy to find where you type in the query info: SF, ranking of job, etc. set another sheet to have your set pricing for hours, cost of goods, price your job ranking system, etc. then your third sheet could be a estimate sheet to print out! This is all "off the cuff" typing but you have gave me an idea to do one also. Maybe we can compare spreadsheets for things I may miss or you may miss.

:blob3:
Reply With Quote
  #4  
Old 02-27-2002, 11:35 AM
CSRA Landscaping CSRA Landscaping is offline
LawnSite Bronze Member
 
Join Date: Apr 2001
Location: Beech Island, SC
Posts: 1,232
I'm up for it, if ever I can figure out how to put in this stinkin' formula.
Reply With Quote
  #5  
Old 02-27-2002, 12:13 PM
CSRA Landscaping CSRA Landscaping is offline
LawnSite Bronze Member
 
Join Date: Apr 2001
Location: Beech Island, SC
Posts: 1,232
Well, I finally figured it out, thanks to some help from a friend. I was having some trouble figuring out the cell reference in the formula. Here's what I have so far.

--A ----------- B ----------- C

1 Property

2 SF Turf

3 SF Beds

4 Fert ------ =b2*0.003+35
Reply With Quote
  #6  
Old 02-27-2002, 03:43 PM
LawnLad LawnLad is offline
LawnSite Senior Member
 
Join Date: Jan 2002
Location: Cleveland, Ohio
Posts: 738
Below are two formulas I use in our bidding spread sheet. I have an area for the sheet that I put in the quantity and price and the unit measurement. According to the unit measure, the formulas will print the calculation in the appropriate column.

We have four columns - materials, foreman labor, technician labor, and equipment. Each of these is totaled per sheet (broken up into tearout, lawn install, irrigation, hardscape, etc), appropriate mark ups are placed on each column and then sub totaled and then overhead/profit are added.

The draw back to using excel for a 'database' for bidding is that each formula like below is limited to seven "If" statements. Ideally a true database would use the spread sheet to calculate the information and print the bid on a report in word. We have not yet developed an access database - but will be since our current spread sheet does not make it terribly easy to job cost after the fact. Formulas start getting complicated, and it's easy to get things switched around.

Here are two formulas we use that show you how to do the if then statements.

=IF(OR($C17="Day",$C17="Ehr"),$B17*$D17,"")

=IF($C17="EA",$B17*$D17,IF($C17="SQFT",$B17*$D17,IF($C17="Bale",$B17*$D17,IF($C17="CY",$B17*$D17,IF( $C17="TON",$B17*$D17,IF($C17="LB",$B17*$D17,IF($C17="LF",$B17*$D17,"")))))))
__________________
Lawn Lad, Inc.
Cleveland, Ohio
Reply With Quote
  #7  
Old 02-27-2002, 04:00 PM
LawnLad LawnLad is offline
LawnSite Senior Member
 
Join Date: Jan 2002
Location: Cleveland, Ohio
Posts: 738
CSRA - to answer your question, you'll need a bunch of columns with the basic data you want to use as the basis for your calculations. It'll get kind of harry depending on how many things you want it to automatically calculate.

If each sheet or workbook is it's own customer, it might be easier. But if you're trying to condense onto one sheet for simplicity of review, you may have to hide a bunch of columns to make your calculations - or do them off sheet.

Square footage for the lawn might give you fertilizing price, aeration and thatching since you could charge by the square foot - assuming in column A4. So each column for each of those items would have a formula reading: = A4*.012. This would be $12.00 per thousand square feet for aerating. Change the numbers as they suit you. For 4,000, you'd get $48.00 - you might have some problems figuring in minimums. You'd have to work in a portion of the formula that checked the sum, if less than X, than XX (your minimum price). You also will have rounding issues if you don't want an odd looking number.

For lawn pricing - if you have a square foot price you could do it as above. Or if you know how much you have to charge in time increments, you could input a time number and have a calculation based on a factor of time.

I personally don't use spread sheets to calculate this information - automatically. I'd rather look at the number I come up with, and then play price theory with it. Is it too much? Is it too little? How much can I or should I adjust it? Too many calculations will "dumb" you down and you'll loose sight of the real numbers. This could be dangerous if you're trusting your computer to make your prices for you. Just be careful... is all I'm advising.
__________________
Lawn Lad, Inc.
Cleveland, Ohio
Reply With Quote
  #8  
Old 02-27-2002, 04:54 PM
Lech615 Lech615 is offline
LawnSite Member
 
Join Date: Oct 2001
Location: Long Island, NY
Posts: 105
iI am trying to follow your examples to set up my excel. I plugged in the formula and a sample sq turf, but I do not get any results under the fert column, any idea what I am doing wrong

Carl
Reply With Quote
  #9  
Old 02-27-2002, 05:03 PM
LawnLad LawnLad is offline
LawnSite Senior Member
 
Join Date: Jan 2002
Location: Cleveland, Ohio
Posts: 738
Carl -

You won't be able to cut and paste the formulas onto your spread sheet to make them work, as the identified cells correspond with my spread sheet. You'll have to adjust the formulas to match your sheet.

If you want, you can email me: Doug@lawnlad.com
__________________
Lawn Lad, Inc.
Cleveland, Ohio
Reply With Quote
  #10  
Old 02-27-2002, 06:05 PM
CSRA Landscaping CSRA Landscaping is offline
LawnSite Bronze Member
 
Join Date: Apr 2001
Location: Beech Island, SC
Posts: 1,232
Doug, You went over my head! I think I've got it a little simpler at the moment, since I'm solo. I have done what you said, I've got a lot of columns with the formulas ready and waiting for the sf info to be plugged in. And believe me, I know that these are just guidelines and not the be-all end-all. Thanks for the help!
Reply With Quote
Reply

Thread Tools
Display Modes

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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump





Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©1998 - 2012, LawnSite.comô - Moose River Media
All times are GMT -4. The time now is 04:46 PM.

Page generated in 0.10592 seconds with 7 queries