# Using Excel to create a database ...

Discussion in 'Business Operations' started by CSRA Landscaping, Feb 26, 2002.

1. ### CSRA LandscapingLawnSite Bronze Memberfrom Beech Island, SCPosts: 1,232

... 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. ### PAPSLawnSite Senior Memberfrom Oakland, NJPosts: 404

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

3. ### NewbieLawnSite Memberfrom Tuscaloosa, AlabamaPosts: 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:

4. ### CSRA LandscapingLawnSite Bronze Memberfrom Beech Island, SCPosts: 1,232

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

5. ### CSRA LandscapingLawnSite Bronze Memberfrom Beech Island, SCPosts: 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

6. ### LawnLadLawnSite Senior Memberfrom Cleveland, OhioPosts: 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,"")))))))

7. ### LawnLadLawnSite Senior Memberfrom Cleveland, OhioPosts: 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.

8. ### Lech615LawnSite Memberfrom Long Island, NYPosts: 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

9. ### LawnLadLawnSite Senior Memberfrom Cleveland, OhioPosts: 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

10. ### CSRA LandscapingLawnSite Bronze Memberfrom Beech Island, SCPosts: 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!