Excel Crop Planner for Market Gardeners

In Singapore there aren't many opportunities to set up your own market garden, much less in the middle of our Covid lockdown. To fight quarantine-induced boredom and learn a bit about a new business idea, I began reading The Market Gardener by JM Fortier and Crop Planning for Vegetable Growers. Following along, I decided to build my own crop planning spreadsheet to model the economics and operations of a market garden farm. I finally have something halfway functional, so I thought I would share it with the Permies community for feedback.

In its current form, this tool organizes crop and planting season reference data and uses that to model the growing season for your farm. You can specify how much of each crop you want to deliver for your CSA shares, as well as customize the number of shares you're offering. The tool features an Annual Plan sheet which helps model some of the long-term financial performance of your farm. Once you have configured your CSA offerings, the tool can create a farm plan including seed bills, greenhouse and planting schedules, and a full field prep calendar for you to follow.

I want to emphasize that my background is in spreadsheets, not farming, so there is a lot of room for improvement. The two things that I'm hoping to get feedback on are the user-friendliness of the tool and whether its model lines up with the reality of farming. Please feel free to comment below with any bugs, questions, or suggestions!

Github Link: https://github.com/gtoleary5/MarketGardenCropPlanner/releases
Download the Source code .zip file and extract it to access the Excel workbook and ReadMe doc.

Holy cow, Gavin! This is a tremendous amount of work!

I haven't run a market garden, but this seems like a robust tool for someone who does, or intends to.

Thank you for sharing your work with us.
(Also, welcome to Permies!)

I do run a market garden CSA is not my model but we do do some boxes, and I do a market which is basically one huge CSA box so I think the sheet could be used still.

First comment is the sheet "Weekly CSA share content" appears to be blank, meaning there is no clue how to populate it. It would also be nice (when there is something to fill in there) to be able to see what crops would be available. of course that might be there on your copy of the spreadsheet.

Other comments, the finance shorthand PMT IRR etc are not known to me not will they be to many and looking at the PMT it assumes there is debt to be serviced?

It's fine for me as I am fairly used to spreadsheets but most of the other farmers I know use apps on their phone to track this type of thing and don't want to be coming in to the computer (I have only a dumb phone)

Hi Ash & Skandi, thank you for taking a look!

Skandi Rogers wrote:First comment is the sheet "Weekly CSA share content" appears to be blank, meaning there is no clue how to populate it. It would also be nice (when there is something to fill in there) to be able to see what crops would be available. of course that might be there on your copy of the spreadsheet.

To populate the Weekly CSA Share Content, you can click the Recalculate Availability button on the Intro sheet. That will cause the worksheet to calculate which crops are available during your season. From there you can then click Create Farm Plan to generate the calendar, seed bill, crop sheets, etc. In hindsight, I realize that I should clean up the Intro sheet to make it a bit clearer what the proper order of execution is.

Skandi Rogers wrote:Other comments, the finance shorthand PMT IRR etc are not known to me not will they be to many and looking at the PMT it assumes there is debt to be serviced?

Very good point. In this case the PMT is the annual loan payment on your startup costs. When designing the tool I assumed that I would have to take out a loan to finance my startup costs. I will clean up the Annual Plan sheet to make it a bit clearer as well.

Skandi Rogers wrote:It's fine for me as I am fairly used to spreadsheets but most of the other farmers I know use apps on their phone to track this type of thing and don't want to be coming in to the computer (I have only a dumb phone)

Interesting, I'll need to do some research on what sort of features the mobile apps carry.

Thanks again for your feedback. I'm planning a second release for this Saturday and will be recording a short video tutorial to showcase the tool's features.

Skandi Rogers wrote:
It's fine for me as I am fairly used to spreadsheets but most of the other farmers I know use apps on their phone to track this type of thing and don't want to be coming in to the computer (I have only a dumb phone)

Do you happen to know which apps they are using? Thanks!

New release has been posted. I just realized I can't edit my OP so here's the new link: https://github.com/gtoleary5/MarketGardenCropPlanner/releases

I will be recording a short video tutorial this weekend and should have something posted in the next few days.

AgSquared  and tend are two I have heard people mention, but I think they are much more comprehensive packages with both app and a full computer program.

Wow! This is awesome. I downloaded it and looked around. Very customizable and great data collection. I really enjoy making spreadsheets and organizing data. Creating something like this is way above my level. I can certainly learn to utilize this. We mostly want to sell to caterers. There are many seasonal events in our little town. Not so much a market for a CSA. This workbook can be adjusted to accommodate that info.

I'll enter in some of info and let you know if anything looks odd. So far it's totally rad.

I just took a quick scan down through your "Read Me" file and your Worksheet.
There is no doubt that you put a lot of effort in building your system.
However, some suggestions to help other people step into actually using this worksheet.

1. Build a step-by-step INSTRUCTIONS listing. Refer people back to the worksheet shown in #2 below.
2. Show an example of a completed system worksheet.

With those many more people might pick up on your system.

Github Link: https://github.com/gtoleary5/MarketGardenCropPlanner/releases/tag/v1.01
Download the Source code .zip file and extract it to access the Excel workbook and ReadMe doc.

---

New release has been posted. I just realized I can't edit my OP so here's the new link: https://github.com/gtoleary5/MarketGardenCropPlanner/releases
-----



Gavin: gorgeous work , brother. i teach org farming online and if ok, this could be something so valuable.....
my gratitude

23sep:   i downloaded your new rev, and hopefully on a soon-like rainy day, i shall tackle the prospect of going hi-tech in my farming
courses......this mere CONCEPT is evolution. THIS is one portal where we all are best served to travel, for our progeny.

NOT that i have an opinion or anything.
most exemplary, Sir Gavin.


- im

New release and thanks to who-ever put me in the daily-ish! https://github.com/gtoleary5/MarketGardenCropPlanner/releases

Sena Kassim wrote:I'll enter in some of info and let you know if anything looks odd. So far it's totally rad.

Thank you Sena! Please let me know if you think of any ways we can make this better.

Jesse Glessner wrote:However, some suggestions to help other people step into actually using this worksheet.

1. Build a step-by-step INSTRUCTIONS listing. Refer people back to the worksheet shown in #2 below.
2. Show an example of a completed system worksheet.

Hey JayGee, thank you for taking the time to look through this and leaving feedback. These are really great points, so I've just published a new release containing a filled-in Example Workbook along with an expanded user manual. Let me know what you think!

im imlach wrote:gorgeous work , brother. i teach org farming online and if ok, this could be something so valuable.....

Thank you Im and please feel free to use this in your classes! I would love to hear any suggestions or feedback from your students. Best of luck in your work!

Skandi Rogers wrote:AgSquared  and tend are two I have heard people mention, but I think they are much more comprehensive packages with both app and a full computer program.

I checked out AgSquared's site over the weekend and they've got a really polished toolkit. This spreadsheet is a long ways away from competing with the big dogs, but maybe someday...

[quoteHey JayGee, thank you for taking the time to look through this and leaving feedback. These are really great points, so I've just published a new release containing a filled-in Example Workbook along with an expanded user manual. Let me know what you think!

Happy to give the feedback. I used to have to build complicated Excel worksheets when I worked at Huges in CA. I was always being asked, "How did you do that?", and "How do you use the worksheet?" so I was just speaking from experience.

The addition of a filled in worksheet and the Instructions should get anyone through building their own!  GOOD WORK!!!

How do you debug?

Sheets("Calendar").Activate                                                                     'Activate the Calendar sheet
   calRowNum = Application.WorksheetFunction.Match(CLng(actionDate), weekRange, 0)                 'Set the calRownum as the row number of the matching date in col A of the calendar sheet
   

Carolyn Hurley wrote:How do you debug?

Sheets("Calendar").Activate                                                                     'Activate the Calendar sheet
   calRowNum = Application.WorksheetFunction.Match(CLng(actionDate), weekRange, 0)                 'Set the calRownum as the row number of the matching date in col A of the calendar sheet
   

Hi Carolyn, could you please message me with a screenshot or copy of the error message you're seeing? I'm thinking there's either an issue with the actionDate or the weekRange variable here.

IF you are on one worksheet in Excel that needs a reference to or from another worksheet simply do the following:

1.  Make sure your cursor is in the proper CELL on the worksheet ( say worksheet #4) that needs the data. Press the EQUAL sign on the keyboard.

2.  Move your cursor to the TABBED worksheet ( say worksheet # 2) where the data needed resides and click on that Tab.

3.  Locate the CELL you need to reference in (worksheet #2) and click in that cell then hit the ENTER KEY.

4.  You should be automatically switched back to the worksheet ( w/s #4) to the CELL location you originally started out in.

5.  Note that your CELL (w/s #4) is now filled in with a FORMULA that referenced back to the worksheet ( w/s #2) with the data.

5.  Any time the DATA in the referenced worksheet (w/s #2 ) changes it will automatically update the other worksheet ( w/s #4 )

Once you do one of these operations you will be amazed at how simple it is.
Need to do sums from every worksheet into a SUMMARY worksheet?  Just do the same operation on every worksheet & cells line by line in the summary worksheet.

I love what you have done. I'm in the same boat as you. I'm great with spreadsheets, loved the market gardener and want to do it someday, but have never farmed before. So my comments are from that point of view.

- Less is sometimes more. Try and remove anything that isn't necessary or condense when you can.
- I'm not a fan of Macros because you can't see how the calculation are being done.
- Be very clear about what tabs are for results, input, and reference.
- The nomenclature in the calendar is not intuitive. Would be great if it could export to a phone calendar app.
- Some information is easier presented in a graph than spreadsheet. For example the annual plan could be gantt chart.
- The "Weekly CSA Share Content" may be easier if it was an output instead of input. Its also not clear that i can't override when plants cant be planted.

Good luck!

Hi Gavin,

Thank you for this great work!

Jaap

Gavin - Thanks for all your hard work!!

I am currently operating a CSA and looking for software to help plan it. Most of the available spreadsheets and websites are just glorified data entry tools rather than using software to do the heavy work. Yours has some great features that actually uses software to automate the plan. THANK YOU.

There is a lot to absorb and figure out your thought process and calculations. I've come across some things that I can't figure out;

- Number of CSA weeks offered starting at date and ending at date. How do I do that? the annual start and end dates do not seem to provide that functionality. If I need to offer a 35 week CSA starting on 6/18 and ending on 10/15 How do I limit that?   It seems that an even more useful calculation would be to specify the harvest target weeks and have the spreadsheet calculate the seeding dates in order to deliver the crop on the harvest target dates. This sheet seems to tell me what is available even if I don't plan to harvest that crop that week. It seem backwards to what you need.

- Constraints seem to apply to both DS and TP plants. It may be better to incorporate the constraints on the DS and TP pages for the specific plants. The TP plants theoretically can be planted outside of the ideal conditions as they are started in the green house.

- Calendar is fantastic! I love how it tells you when to seed and when to transplant. I am not sure however if the planting that says plant 55 ft of cucumbers is 55 row feet or bed feet. Also on the field prep it  outputs: Plant Cover Crop Group 202104B 460 ft what is the 202104B  is that the YYYYMM(planting group?) or is the MM the bed number?

- Beds? In a market garden there are only so many beds available at x feet long. is there a way to determine at any given point in time  how many TOTAL bed feet are required? or a constraint on total bed feet available and limit to that. This would help to know if you need to build more beds or cut back on crops.

Summary - My ideal would be to specify what plants I want to provide in the CSA. Specify the start date of CSA distribution and the last date of CSA distribution. Then specify number of Shares. Then the software would do the heavy lifting and calculate when to seed, plant in order to have the plants available for the CSA distribution. Total bed feet required and seed quantities.

edit ----
- I thought found how to change the dates for harvesting by putting zeros in the crops one the Weekly CSA Share content but I found that that tab is not included in any of the calculations for planting or for seed calculations. All the planting and seed requirements are based on output sheets and not on input sheets. I would thing that using the Weekly CSA share content and adjust the planting schedule and seed requirements on that would be useful.

Gavin thanks again for sharing this! it is great work.

Mark

Hi Gavin,

This is fantastic work and something we plan to use as soon as we can.

On opening both versions and enabling macros though I get a run-time error 53 message, File not found user 32

Any advice as I'm not great at diagnosing macro issues

Thanks

John

I sent a message to Gavin, but maybe one of you have figured this out... How do you modify the crop name without disrupting the formulas?  Also, what page is the root page for adjusting plants you intend to plant so that it will populate the modified name on the CSA sheets?  For example, I am growing two kinds of Basil.  I want to have the specific variety (as well as the company code) in the cell but when I hit the "Populate CSA Content" button on the intro page it doesn't carry over and in addition it creates errors in the corresponding sheets.  Thanks for any and all help!

EDIT  I think I may have solved it.  The order of operations I believe is to start with the sheet "Planting Constraints."  If you input the specific variety there and copy and paste onto the "TP" and "DS" sheets, it will populate onto the "Weekly CSA Share Content" and "Weekly CSA Harvest Target" pages when you click on the "Populate CSA Share Content" button on the intro page.  Hope this helps anyone struggling with the same issue.  

Note Add2. does NOT work with all versions of excel, but can be reduded to Add.
Code fails on startDate with error 2029 contained in cells(2,3).

Private Sub updateCalendarWeeks()
Dim startDate, ghDate, endDate As Date

'   Check Field Ops for first date

Sheets("Field Ops").Activate                                                                                                          'Activate the Field Ops sheet
KillFilter                                                                                                                                           'Kill all filters on the sheet
Range("A1").Select                                                                                                                          'Select cell A1
Selection.AutoFilter                                                                                                                        'Autofilter the sheet
ActiveWorkbook.Worksheets("Field Ops").AutoFilter.Sort.SortFields.Clear                             'Clear previous sort fields on this sheet
ActiveWorkbook.Worksheets("Field Ops").AutoFilter.Sort.SortFields.Add Key:= _
       Range("C:C"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
       :=xlSortNormal                                                                                                                      'Sort the Field Ops table on Plow in Cover Crop (ascending)
startDate = CDate(Format(Cells(2, 3).Value, "mm/dd/yyyy"))                                                                      'Save the start date as the earliest Field Op date

Hey Gavin,

Solid work on the spreadsheets. Would you be down to make a plan for me if I send you all the pertinent info (seed types, row sizes, etc,)?
Im in zone 6a US. I will happily pay for the convenience.

Thanks!

Hi !

Can anyone help me with this workbook ? It says that there are errors and to debug !(??)

What should I do ?