View Full Version : Using Excel to create a database ...

CSRA Landscaping

02-26-2002, 11:40 PM

... 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?

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

Newbie

02-27-2002, 11:25 AM

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:

CSRA Landscaping

02-27-2002, 11:35 AM

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

CSRA Landscaping

02-27-2002, 12:13 PM

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

LawnLad

02-27-2002, 03:43 PM

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,"")))))))

LawnLad

02-27-2002, 04:00 PM

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.

Lech615

02-27-2002, 04:54 PM

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

LawnLad

02-27-2002, 05:03 PM

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

CSRA Landscaping

02-27-2002, 06:05 PM

Doug, You went over my head! :eek: 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!

LawnLad

02-27-2002, 08:50 PM

Okay... here's the equation - pay no attention to the number 17, as it is simply the row in which I copied the formula. The $ sign fixes the number so when you copy the cell, it doesn't change the numbers in the formula, since excel likes to think too much.

=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,"")))))))

Hope this will clarify. Follow the example above:

On the bid sheet:

Column A = Description of item

Column B = Quantity of item

Column C = Unit of measure (EA, SQFT, BALE, etc)

Column D = Price

Column E = hidden column - don't worry about for moment

Therefore, in the equation above, If C, the unit of measure, equals EA, SQFT, etc. then multiple B (quantity) times D (price). This formula rests in the materials column.

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

Sitting in the equipment column, if C, the unit of measure equals DAY, EHR (equip hours), then multiple B (quanity) times D (price).

Therefore, if I have in the following columns

A: Rototiller B: 2 day C: DAY D: $30.00

The column for equipment will pick up that this is an equipment calcualation, not material or labor, and therefore drop $60.00 into the equipment column.

Hope this helps.

PS - I separate the foreman and technician hours into two columns and designate them with F & T in the Unit of Measure (U/M) column. So I don't have to constantly type in the hourly rate, I have it fixed in an assumptions area with other mark up numbers (overhead, profit, etc). This way I reference the same number, and if I want to change the labor rate throughout the whole project, I only have to type it once, not each time labor was applied to the job.

If you want more info or explanation on this, email me.

vBulletin® v3.8.6, Copyright ©2000-2015, Jelsoft Enterprises Ltd.

0