joshvolk.com

Q: How can spreadsheets be used for crop planning?

A: This is a version of an article I first published in Growing for Market's November 2010 issue.

 

Using Spreadsheets for Crop Planning and Record Keeping

I have to admit I like farming on paper. I plan out my entire season: where everything goes in the fields, how much of what to plant, when it will be planted, how much I'll harvest and when, all on paper. Actually I do it on a computer. It's all so neat and clean and perfect on paper (or the screen). I do also like going out in the field in the spring and actually doing the farming. At that point things frequently diverge from the plan, a bit, but having the plan keeps me focused and definitely keeps my stress level much lower. It also provides a form for keeping good records and using those records to improve the planning for the following year.

In the February 2010 issue of GFM (and reprinted on this website here) I wrote about my method for mapping out fields and keeping records on those maps. There are two other steps to my process of making the maps that I didn’t really mention in that article: creating a harvest plan, and creating a planting plan. I use computer spreadsheets for all three of these steps in my planning process. If you’re already someone who is comfortable with computer spreadsheets I hope this article will give you some ideas for how to organize your planning in those sheets and more ideas for how to implement the sheets. If you’re not familiar with spreadsheets and you spend a lot of time in the office every year creating crop plans, perhaps this will convince you that this is a tool worth learning. If you’re a computer wiz and you’re familiar with creating databases or programming, you can ignore all of the spreadsheet references and translate the concepts into other platforms.

I use a computer spreadsheet program called NeoOffice which is free, open source software for Macs (neooffice.org) and looks and feels a lot like Microsoft Excel (openoffice.org is the PC or Linux version). Recently I transferred all of my sheets into Numbers for Mac, which works a bit differently and was quite a learning curve.

Everything I do on the computer could be done by hand on paper and if you don't have a computer some of the techniques here will still be useful. The advantage of using a computer is that if you set up your spreadsheets properly they will automatically do calculations for you, and they are also easy to quickly sort in different ways. The computer is a tool and the spreadsheet programs are extensions of that tool. They are a tool that take a bit of skill to use well, just like most tools on the farm, so don't expect to sit down and have the thing work for you first try.

 

Examples of Spreadsheet Utility

The kinds of automatic calculations that I use vary. I use the spreadsheet to calculate dates. For example if I tell it I want to plant broccoli on April 1 and that it takes four weeks for broccoli plants to mature in the greenhouse it will tell me that I should seed the broccoli in flats on March 4. I also use it to calculate yield projections. If I expect a yield of 250 lbs of potatoes per 100 row feet, and I tell it that I'm planting 12, 180 foot beds it will tell me that I should expect 5400 lbs of potatoes. If I tell it I'm hoping to give 200 CSA members 3 lbs of potatoes 8 times from that one planting it will tell me that I'll have a surplus of 600 lbs. There are many more examples but you get the idea.

There are a couple of keys here to making this work for you. First, you have to set up the sheets and the formulas that are calculating the numbers so that they make sense for you and they tell you what you want to know. You also have to put in good numbers at the start and make sure they formulas are correct. If your yield numbers are no good, the yield projections that the computer spits out won't be either.

Automatic calculations are nice but being able to sort large quantities of information quickly is probably even more useful. Once I have my plantings in the sheet, or even while I'm putting them in the sheet, I can sort them. I do all sorts of sorts: I sort them by crop so that I can look at all of my lettuce plantings for example; or I sort them by date so I can look at what's being planted in a particular week; or I sort them by location so I can look at what's being planted where. A word of warning, be very careful sorting, you can get into a lot of trouble here by disassociating connected parts of the sheet.

In another example of sorting I can pull out all of the transplanted crops, make a chart of all of the seeding in the greenhouse by seeding date and leave space on that chart for keeping records on what actually happens during the season. Then I print the chart out at the beginning of the season and a copy lives in the greenhouse for easy reference and record keeping, as well as another copy in my personal book for making to do lists. I make similar charts for seed orders, beds to be prepared, plantings, and harvests. More on those later.

 

Creating a Harvest Plan

The first step in my planning process for the season is to create my ideal harvest plan. For this step I make a chart which is similar to the field maps I wrote about in February. Across the top of the chart I list the harvest weeks of the year. I do all of my planning by week. If I were to plan for a specific day to do something it I’d probably be wrong 95% of the time. If I project a week that I’m going to do something I’m much more likely to be right and it’s nice to build in a little flexibility during a week.

If I’m making a plan for CSA harvest I fill in the number of units per share for the items under the week that I want to distribute each item. I can use the spreadsheet to give a count of the number of unique items planned for each week. I can also total up the projected value of the shares. Links to examples of these spreadsheets are posted on my website (slowhandfarm.com/More_about_the_share.html), and are available for my CSA members to view if they want to get an idea of what we’re planning for the shares.

For other markets I make the same type of chart, but instead of a share by share quantity I put a total quantity.

 

Creating the Planting Plan

Based on the harvest plan I then create a planting plan designed to give me the quantities of the crops I want from the harvest plan, at the times I want them. The final step in the process is creating the maps which I wrote about in February. The process is a bit circular because usually I create a harvest plan that is somewhat unrealistic, requiring too much space and that becomes most obvious when I create maps, at which point I have to go back and edit the harvest plan and planting plan to match the space available.

For the planting plan my sheet is usually quite large and I carry as much information about each planting as possible within a single row of the sheet. Depending on the farm (I’ve created these for at least five unique farms at this point) I modify the information I include, as well as the formulas that do the automatic calculations. I’ll give an example of the type of information that I include for each individual planting based on my very small farm.

I start with the crop type and then the variety. I use the next ten columns to create a yield calculator. For example, I put in columns for the unit (pounds, bunches, etc.), a typical yield for a known space, the price per unit, the number of weeks I want to harvest from that planting, and the quantity I want to harvest each week (information that comes from the harvest plan I’ve already made). Formulas in other columns then tell me how much my total expected yield is, how much of that I need for my market, and how much is surplus, as well as projecting a gross income from that planting. I don’t always use this section for all of my crops, but it’s helpful if I’m trying to guess how much of a new crop to plant, or if I want to get a sense of how much a planting might gross.

The second group of columns is all of the planting information needed in the field. Is the crop direct seed or transplant? How many rows in a bed? What’s the in line spacing? What field is it going into? How many beds are being planted (or fractions of beds)? What week are we hoping to get it in the ground? And, any special notes for the planting. Some of those formulas in the yield calculator use some of this information, and all of it is useful to give to a planting crew to make it absolutely clear how to plant the crop.

My third grouping of columns is a harvest projection calculator. Like the yield calculator section this is mostly useful for figuring out how to adjust planting weeks in order to get as close as possible to a hoped for harvest week. It includes data fields for catalog days to maturity and “actual” weeks to maturity, which are based on past experience, or best guesses. Using the actual weeks and the planting week it calculates a projection of the first harvest week, and if given a number of weeks that the planting is to be harvested it will also give the final harvest week projection.

A fourth grouping gives all of the information necessary for growing starts in a greenhouse. For direct seeded crops these fields are left blank, but if the crop is to be transplanted, and therefore started in the greenhouse, all of the information that the greenhouse crew needs is here: the week to seed the starts which is calculated from the planting week and the weeks to maturity in the greenhouse, the number of plants needed based on the number of beds and the spacing, estimates of the number of seeds that should be seeded based on a generous cushion and the germination rates of the seeds, plug tray sizes to use, the number of trays to seed, and any special notes on the seeding. For direct seeded crops there are a separate group of columns that detail the seeding method to be used, any seeder settings and particular notes.

Finally there is a group of columns for planning and organizing seed ordering. This starts with a section that estimates the number and weight of seeds needed for the planting to help with selecting the most appropriate quantity when looking through seed catalogs. There are also columns for indicating the seed company to order from, the order quantity, the order code and the cost.

 

I want to reiterate that every single separate planting gets all of this information included - except some of the seed ordering information that I’ll talk about later. So, if you are planting three different carrot varieties into one bed on the same week, each one of those would be considered a separate planting. Similarly, if you’re planting four beds of the same carrot variety for three weeks in a row, that would be considered three separate plantings.

This might seem like a lot of repeated information, and it is, but it’s relatively easy to copy much of the information over from similar plantings. Also, during the season this really makes it much clearer to everyone on the crew what the plan is for any individual planting.

The only columns that I don’t repeat information in are the seed order columns. For most varieties I’m performing multiple plantings each season, which means that the total quantity of seed that I need of that variety is the sum of all of those plantings. For seed order information I consolidate the seed order information for each separate variety into the first seeding of the year to keep things less confusing.

The resulting spreadsheet is very large, usually over 50 columns wide and over 400 rows deep. It is far too big to be able to print out and use on paper in its raw form.

 

How to Organize All of this Information

If you were going to do something similar on paper, each row would be like index card. There would be an index card with each planting on it and all the corresponding yield, planting, seeding, and seed order information. You could rearrange the index cards by planting date, or by crop, or by variety, or by seed company to order from, etc. but it would take a bit of time considering there would probably be hundreds of them. With the computer you can just sort the rows depending on how you want to use them.

Once I’ve gone back and forth a few times between the harvest plan, the planting plan and the maps to make sure that it all fits and makes sense I’ll consider the plan solid and I’ll start breaking the information apart into separate sheets that are useable to make the seed order, to create a bed preparation schedule, a transplanting schedule, a direct seeding schedule a greenhouse seeding schedule and a harvest record sheet.

Computer spreadsheets allow you to reference selected information from other sheets in creating a new sheet, and to combine those pieces of information with new information. The first sheet I usually create for myself is the seed order. For this sheet I pull out only the information I need to make the order and then I sort the sheet by seed company so that all of the orders are separated for me. At that point I simply have a list that I can read down for each separate seed company, and I have all of the order numbers and prices to compare as I go. I make notes on a print out as I go to keep track of back orders or items that aren’t available so that I make sure to order those from other companies, or so that substitutions are noted in the planting plan.

The rest of the sheets that I make are designed to include the information that is needed only for the task being performed (e.g. direct seeding, or greenhouse seeding), and I also include blank columns next to numbers I want to have records of. I make sure that the information on the sheet can be printed out on a single sheet of letter sized paper, without any of the row flowing over onto a separate sheet. That way all of the information for a planting is contained on one line and is easy to see in a glance. For example, when the planting crew goes out into the field, they have a clipboard with a sheet that tells them the crop, the variety, how many trays they should have gotten from the greenhouse, what the week of the planting should be, what field the planting is going into, how many beds are to be planted at what spacing, and any additional notes. The sheet is sorted by date and field so that all of the plantings for the week are grouped together, and within a particular week they are grouped by the field they are going into. There are also blank spaces that the crew fills in after planting to note what day the crop was actually planted, where it was planted, and then they can also note any changes to the information given. These sheets are both the to do list and the record sheets indicating not only that the task was done, but also when and how.

 

Timeline

This entire process is not a short one. Plans often include 400-600 separate plantings in one season. Even when it’s just being edited from a previous year and not created from scratch, I give myself two full weeks of sitting in front of previous years’ records, a computer screen and lots of seed catalogs to complete the process. Usually that two weeks is spread out over a month or two. Here in the Northwest I like to do my planning in November, hoping to finish before the New Year and have my seed order in by early January. We start seeding in the greenhouse in late January so it’s easiest if I’m all done by then and the seed is already arriving.

With the plan finished and printed out on paper, I pretty much leave the computer spreadsheets behind for the season and work entirely off of the print outs that I make in January. Even though there are frequent edits to the plan over the course of the season due to weather, labor, and unforeseen pest issues, having a plan saves an incredible amount of time during the actual growing season, moving a chunk of the decision making to the “off” season, when there’s more time to think decisions through. Further, on a farm where you, as a manager, have to pass on all the details to a crew, or crew leaders, having those details spelled out in a standard format that they can easily access saves a lot of communication time, and makes tasks clearer. When the crew is good at keeping records it is easy to see what has already been done, and what is still remaining to be done, just by glancing at the combined to do/record sheets.

 

I’ve taught a number of people to use this system over the years. My partner, who now manages one of the farms that I used to work on, was one of those people and she told me it took her two or three seasons of going through the process to really understand the details. Another advantage I see to creating such detailed plans, especially in conjunction with good record keeping, is that it accelerates the learning process for how to plant out the farm successfully. At this point, after many years of farming I might be able to plant out the farm from shear seasonal muscle memory. When I was first starting, there was no way I could do that, and so creating the plan in the off season gave me a real head start to understanding what I needed to plant, how much, and when.

 

 

Additional Tips for Using Excel

The following is a cheat sheet of features I find useful to understand in computer spreadsheets that I’ve created for workshops that I teach on my crop planning method. Learning how to sort effectively is the one other essential skill not mentioned below.

 

useful excel shortcuts and terms

Cell Address - the column letter followed by the row number (e.g. F35)

relative cell address - when you put a cell address in a formula it defaults to a relative cell address, meaning relative to the cell the formula is in. The cell address in the formula will change if you move the formula to another cell.

absolute cell address - you can make the column, or row, or both absolute (meaning they won't change if you move the formula to another cell). This is done by putting a $ in front of the letter, or number, or both (e.g. $F35, F$35, or $F$35 - meaning three different things)

Referencing - this lets you reference cells in other sheets, even other workbooks. This works just like other cell addresses. Be aware that if you move cells in the referenced sheet (by sorting or any other method) the cell will still be referenced but the information that was in it will not.

 

Formulas I use in crop planning

=+-/*() - pay attention to where your parenthesis are in the formula, it makes a big difference sometimes.

+- - adding (or subtracting) a number to a date is equivalent to adding (or subtracting) days. Very useful.

sum() - this adds up all of the cells in a range. Ranges of cells start with the top left cell address, are separated by a colon (or sometimes double periods), and end with the bottom right cell address (e.g. A1:C55, or A1..C55)

count() - This counts the number of cells in a range that have a number in them

countA() - This counts the number of cells in a range that have anything in them

If() - I used to use more if statements but they take up a lot of space and aren't really so useful in the end. Basically they will do one thing if a cell matches the criteria you're looking for, or something else if it doesn't.

Nesting formulas - I use a lot of formulas inside of formulas. You can nest a lot of formulas but usually it's easier to use multiple steps, showing each intermediate step in another column. This is much easier to edit in the future and to understand when you forget how the formula works.

 

Learn key stroke shortcuts

These save a lot of time when you add up how many times you use most of them, and they help save your wrists. the most common ones I use are below. I've also included some right click features on a pointer.

arrow keys - I find these faster and more accurate than the pointer (mouse/trackpad) in most cases

holding shift - this allows you to select a range by simply selecting the first and last (or last and then first) cells in the range

holding ctrl (command on a mac)- this allows you to select multiple, non-contiguous cells (mostly for deleting, doesn't work with copy and paste)

ctrl x, c, v - cut, copy and paste. right clicking on a mouse gets you there sometimes but the keyboard is usually faster.

ctrl d, r - fill down and fill right. I use these a lot. These paste whatever is in the top or left cell into all of the other cells in a range. Unfortunately NeoOffice doesn't have ctrl r so you have to use copy and paste instead.

ctrl b, u, i - bold, underline and italic. Once turns it on, a second time turns it off.

ctrl s - I don't use this one nearly enough - Save your work often!

right clicks - on row and column headings you can add and subtract columns

this is a shortcut to formatting the cells as well.

scrolling - having a mouse with scrolling is very nice for working on spreadsheets. I'm excited about the new mice (and track pads) that allow scrolling in both directions.

 

Help - use the help feature when you can't remember how something works. It's usually the top right pull down menu. in time and space

 

Epilogue

You can download samples of the spreadsheets I'm talking about above here. There's also a pdf version of a presentation I do on this same topic included there.

I add to this site because I like collecting this information and I’ve found similar sites incredibly helpful.  I hope this site is helpful for you, and if it is, please consider a donation to help me spend more time putting up more.  Thanks!

shf gfm