#1




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




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




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




I'm up for it, if ever I can figure out how to put in this stinkin' formula.

#5




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




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




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




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




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




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 beall endall. Thanks for the help!

«
Previous Thread

Next Thread
»
Thread Tools  
Display Modes  


Layout Style: