Spreadsheet Instructions

Crop Planning and Record Keeping for Diversified Farms
By Dan Kaplan, Brookfield Farm.

(Understanding this article is somewhat dependent on knowing a bit about computers and spreadsheets. You don't need to know tons, but we've assumed the basics. The words in CAPITAL letters are spreadsheet commands).

Keeping track of seed inventory, planting dates, greenhouse schedules, field records, and harvest records can be difficult on any vegetable farm. When you try to produce over 250 varieties of over 60 crops the task can be daunting. Trying to make sure that the 520 shareholders, who's money you've already spent, have full bags of delicious varied produce for 26 weeks, can be enough for a large migraine.

Using computer spreadsheets for crop planning and record keeping is an efficient way to manage a monstrous amount of information. I use Microsoft Excel, in order to create many small spreadsheets, so that the information isn't too cumbersome. I turn these sheets into data generators and graphic displays They can be used for any type of operation, not just a CSA. They are tools to answer the questions that we all have - how many pounds of beet seeds do I need to order to make sure all of my customers are happy in July? What date do I need to seed how many flats of broccoli in the greenhouse to have enough starts to plant on May 15th? Where did I plant my carrots last year?

CROP PLAN

It all starts here - trying to convert my wildest dreams into an actual bed count. ("If I want to make 520 shareholders happy with carrots this year how many beds do I have to grow?). Here's what the columns mean:
A = the crop
B = the unit in question
C = the yield per 350' row
D = the quantity of product distributed per week
E = the number of weeks of distribution of product
F = the total seasonal yield needed
G = the production last year
H = total rows needed to grow for the season
I = the total beds needed to grow for the season

. All of this is data, except columns F, H, and I which are results generated by formulas derived from the data. In this case column F is the sum of (column D x column E) x the number of shares. The formulas are powerful because you can change some data and the results will automatically change. In this way, you can play around with different scenarios to see how they play out. For example, maybe I could plan on 3# of broccoli each week for distribution. How would that effect my acreage in production?

The yield estimate (C) is crucial - it is generating all of the results. Importantly, it includes a "fudge factor" of 10% which helps give the whole plan a bit of "wiggle room." It's important to have a plan, but the plan needs to be flexible enough to deal with the vagaries of weather and all of the other imponderables of being a farmer. I want to grow more than is needed - better to till it in then to have to hide at home during distribution to avoid my shareholders who want to know where their carrots are.

FIELD PLAN
Once the crop plan is set, it's time to find a place in the fields to grow everything. CUT and PASTE columns A (crops) & I (total beds) from the crop plan onto a new worksheet. Then CREATE a new spreadsheet to represent each field. Each row of these new spreadsheets represents an actual bed in an actual field. Then CUT and PASTE the crops from the worksheet onto the fields where you want them to "grow." INSERT columns before and after the crop on the new sheets and they look like this:

A = field prep
B = planting date
C = crop
D = variety (seed plate number, if direct seeded)
E = season extension notes
F = planting date (for double cropping or cover cropping)
G = crop (for double cropping or cover cropping)

This is where I figure out planting dates and varieties. This is also the place to work out crop rotations - graphically - using CUT and PASTE. These commands allow experimentation and creativity. I make copies of each field plan for all of the apprentices so they know what is going where, when, and how.

FIELD PLANTING SCHEDULE
Now just COPY and PASTE columns A, B, C, D, and E from each field plan onto a new spreadsheet which becomes a master plan. The data on this new sheet can be SORTED in many ways to generate more information. First, SORT by planting date. This is now a field planting schedule.

It can be used to look ahead to figure out what needs to be plowed next week, what should be stale bedded, what should be seeded, etc.

SEED ORDER
SORT the master plan by crop and INSERT some columns after to make a Seed Order.

A = crop
B = variety
C = number of rows
D = number of transplants needed for a 350' row
E = total plants needed (includes an extra 20%)
F = seeds to sow a 350' row
G = the unit of seeds in question
H = the total quantity of seeds needed
I = the viable seed on hand from last year (inventory)
J = the adjusted seed need (after inventory has been subtracted)
K = the source of the seed
M = the catalog number (not shown)
L = the cost of seed (not shown).

The estimate for seeds needed sow a row (F) is crucial. This number generates our entire seed order and is the difference between being right and having to call Johnny's at 10am and ask them to Fedex another 1/4 pound of carrot seed since we just ran out. We can SUM the total cost of our seed as we're making it to ensure that we're within budget. And at the end of the day we can SORT the sheet by "seed source" (Johnny's, Fedco, etc.), PRINT out the relevant data, and send them what is now our seed order. It's also an instant record.

GREENHOUSE SCHEDULE
From the seed order, COPY columns A, B, C, D, and E onto a new spreadsheet, INSERT columns before and after and you now have a Greenhouse Schedule. This makes for a quick and easy way to ensure all supplies are on hand and all of the crew knows what to do for the day.

A = actual seeding date
B = greenhouse seeding date
C = crop (and planting date)
D = variety
E = number of rows to plant in field
F = plants needed for a 350' row
G = total plants needed (includes an extra 20%)
H = total flats needed (we're using 72s)

PLANET JR. PLANTER PLATE SIZE CHART
Go back to the master plan, SAVE it as a NEW FILE, SORT it by crop, CUT out any crop that is not direct seeded, and now we have a chart which can be put in the seeding box and used for easy reference in the field.

FIELD RECORDS
Planning is necessary, but the important part is really record keeping, so I can learn from year to year and make the plan better and better. Once all of this is in the computer, record keeping becomes quite simple. SAVE each field plan as a NEW FILE and they are now Field Records.

When I go out seeding I take a pen and a small notebook. After each bed I mark down the date, crop, variety, and seed plate number. At the end of every week I sit down with the field records and enter in the information from the notebook. I turn the season extension column into a notes section. Each week, I take a "field walk" through my field records and enter any relevant information which might come to mind. At the end of the season I have a field record for every field.

HARVEST RECORD
We count boxes before they go in the cooler and mark it down on a weekly harvest record sheet.

We compile these at the end of the season to make a yearly harvest record. This is the data that goes back into the crop plan for the next season, thereby completing the cycle of planning and learning.

There is no substitution for knowledge and understanding. These spreadsheets are just tools to hold information. They will only be useful if the information is correct for your situation. Sandy soils will yield different than loam, different seeders will need more seed to get each row just right, etc. However, it is helpful to rid my brain of lots of information so that I can use it to think when the going gets tough. When it is 95 F and the irrigation needs to be turned on, the distribution has just started, the harvest crew is inexperienced, the truck just burned its clutch, and the tractor won't start - it's nice to know I have enough carrot seed in the hopper to get down to the end of the row. You know what I mean....

Dan Kaplan runs Brookfield Farm, a 520-share CSA in Amherst, MA and is a partner in CSA Works. To order disks with these spreadsheets send a check for$50 to Brookfield Farm, 24 Hulst Rd, Amherst MA, 01002.