Veggie Compass: A Spreadsheet-based Tool to Calculate Cost of Production for Diversified Organic Vegetable Farmers (2024)

Keywords: budget tools; farm business management; pricing decision support

History of Veggie Compass Program

In an effort to address the aforementioned needs related to farm financial analysis and cost-of-production determination, the Veggie Compass project was initiated in 2007. The project began with a Wisconsin organic farmer who approached the University of Wisconsin’s Center for Integrated Agricultural Systems (CIAS), asking for a partnership in the development of a whole farm cost accounting spreadsheet for diversified vegetable growers as he could not find an existing program that adequately met the needs of such farmers. At the onset of the project, to ensure that this new tool would be useful and relevant to organic diversified vegetable producers in the upper midwestern United States, the team recruited a panel of ten organic vegetable farmers to better understand the necessary components for a readily implementable cost analysis and whole farm planning tool. Beyond ease of use, common themes emerged, such as the ability to interface with existing farm accounting programs and the need to be sufficiently flexible to allow growers to enter farm-specific data values.

As a result of these recommendations, the Veggie Compass spreadsheet was developed to facilitate the analysis of farm records. Using farmer-provided cost, sales, and labor data, the spreadsheet calculates the cost of production for each crop, the profitability of each crop, the profitability of each market channel (e.g., CSA, farmer’s market, wholesale, retail), and the profitability of each crop within each market channel. With this detailed cost of production information, farmers can more accurately set prices based on cost of production. Once a user has 1 year of data to use as a baseline, the tool can also be used to predict the outcome of future farm scenarios based on changes in crop types, volumes, or markets.

In 2011, the Veggie Compass team commenced another critical partnership for the continuation of its development and outreach with the Southern Sustainable Agriculture Working Group (SSAWG). In collaboration with the Wisconsin organic farmer partner and a new farmer partner from Virginia, SSAWG initiated a series of farmer profitability trainings using the Veggie Compass system. In this process, based on farmer feedback, the SSAWG team members modified the spreadsheets, adding features and text to create a more robust system that is more easily understood from the farmer perspective.

Veggie Compass was developed using Excel 2007 spreadsheet software (Microsoft, Redmond, WA). Six worksheets are linked together, carrying data values throughout the overall spreadsheet and performing various analyses. Users enter their data in the first three input worksheets, each marked by individual tabs at the bottom of the spreadsheet. Information is then generated in the last three output worksheets with calculations based on formulas embedded within the spreadsheet, providing the user with profit and loss figures and breakeven prices for specific crops in specific market channels.

Program description

When the file is opened, Input Sheet One is viewed. At the bottom of the page, there are clickable tabs labeled “Step 1: Expenses Input,” “Step 2: Sales Input,” “Step 3: Production Input,” “Cost of Production,” “Sales Output,” and “Profit and Loss (P & L) by Market Channel.” The Veggie Compass spreadsheet requires the user to input farm data in the first three tabs (steps one through three). On Sheet One (Fig. 1), growers enter farm expenses. Sheet Two (Fig. 2) requires users to enter all sales information for their farming operation. On Sheet Three (Fig. 3), growers enter seed cost, number of transplants, field areas, and production (field growing and harvest/packing) labor hours for each crop.

Throughout the sheets, formulas have been embedded into cells that calculate values based on a farm’s specific data. These formulas are password protected and cannot be changed by the user. The spreadsheet uses information from the three input sheets to calculate each crop’s cost per unit for production through harvest and packing, breakeven prices, gross margins, unit net profit, and crop net profit by market channel, presenting these values on the spreadsheet’s three output sheets. Growers can use this information to identify crop profitability in each market channel to which they sell. Thus, farmers can adjust their selection of crops grown, the quantity of crop grown, the market channels through which they sell, and prices charged for each crop sold in each market channel.

Several pieces of data are necessary to generate the detailed calculations provided by Veggie Compass. These include total farm expenses (both fixed/overhead and variable costs); farm sales by crop and by market channel; quantity of each crop sold in each market channel; seed cost of each crop; number of transplants grown in the greenhouse for each crop; field growing area of each crop and total area; hours devoted to field production for each crop; hours devoted to harvesting and packing for each crop; hours worked in the greenhouse; “not crop-specific” hours spent in field growing and harvesting/packing; crop-specific costs for field production (e.g., trellising for tomato); and crop-specific costs for harvest and packing [e.g., twist ties for bunching onion (Allium fistulosum)]. Farmers should be able to obtain these values from farm business accounting programs, crop sales, Schedule F or comparable tax documents, farm planning/planting records, and payroll/labor records.

Entering farm data

Input Sheet One is based on the same farm expenses categories as used by the Internal Revenue Service (IRS) Schedule F tax form. These categories include labor expenses (including wages and employee benefits); repair and maintenance of machines, trucks, buildings, and equipment; fuel; general production supply expenses (soil amendments, seeds and plants, pest management, and custom hire costs); additional supply costs for the greenhouse, packing shed, and other uses; purchase of equipment and tools; equipment rental; freight; storage and warehousing; sales and marketing costs; operating expenses, such as property tax, professional development, rent, business insurance, and cost of organic certification; and utility costs. Equipment depreciation is also included as a farm expense, with the user choosing the depreciation amount (and thus the calculation method). For example, the grower may simply use depreciation claimed for taxes or straight-line depreciation calculated as part of a farm accounting system. The Veggie Compass tool requires the user to distribute expenses over the various activities (greenhouse, field production, harvest and packing) of the farm business as appropriate. On row three, the farmer must establish names for the various market channels (e.g., restaurant, wholesale, farm stand, retail) used by their operation. The CSA and resale channels are locked, however, due to unique formulas associated with the specific attributes of these market channels. On the final row of the sheet, summaries of both total farm expenses and labor costs for each activity and sales category are provided.

On Input Sheet Two, the user enters sales income and sales volume by crop and market channel. Farmers can obtain this information from historical sales receipts/invoices, CSA logs, farmers’ market logs, or other account statements or tax records. This category tends to be most difficult for CSA producers, where the price of a share is established before the production season begins. However, growers can estimate this information through various strategies of pricing the values of each CSA box, with each individual item priced at market price or as relative percentage of the total cost of a share.

Input Sheet Three requires specific production information, including seed costs, number of transplants grown in the greenhouse, and labor required to produce each crop. Labor hours not only must be assigned to each specific crop grown but must be specific to different categories of production activities: field growing (i.e., all activities up until harvest) and harvest and packing. This differentiation allows producers to better understand the factors contributing to their cost of production with respect to labor, which can comprise up to 75% of the cost of producing a crop. Additional costs related to the field growing and harvest/packing of specific crops must also be entered, such as stakes for tomatoes, rowcover for baby lettuce (Lactuca sativa), bags for potatoes (Solanum tuberosum), and twist ties for kale (Brassica oleracea), among others. In addition to entering labor records, the grower is required to enter the area of each crop planted in acres, row feet, or square feet. Total greenhouse hours and labor hours devoted to field production and harvest and packing tasks not specific to a crop (e.g., primary tillage, sanitation of pack house) are distributed among the crops as a proportion of the total area grown.

Growers will have tax records for hired hourly labor and some idea of their own and family labor and volunteer labor (if applicable), but allocating this total labor to different tasks can be challenging. Users often begin by allocating labor hours based on the share of total acres planted to each crop, and then adjusting these shares based on their experience of extra time needed for some crops relative to other crops. Alternatively, some growers reported intensive data collection efforts they had used to track labor devoted to key crops to have a better sense of actual labor needs for these crops. However, such efforts are time-consuming and expensive and often do not generate benefits that exceed the costs; most users are satisfied with a simple method for allocating labor that reasonably approximates the actual labor allocations.

Input Sheet Three provides a key calculation carried through many of the formulas in the spreadsheet—the true production labor costs per hour for the overall farm operation. This value is based on the labor hours entered into Input Sheet Two and the labor expenses entered into Input Sheet One. Thus, this value does not reflect an hourly pay rate, but the overall labor expense for all farm employees, including wages, payroll taxes, and employee benefits.

Calculating costs of production

Output Sheet One (“Cost of Production by Crop”) (Fig. 4) contains calculations for cost of production, detailing the cost to produce each crop through field production and harvest and packing. Protected formulas specific to individual cells use farmer-entered data from the three input sheets to complete the required calculations. In the first three data columns, crops grown, seed costs, and number of plants produced in the greenhouse are carried over from the Input Sheet Three. A crop’s share of greenhouse expenses is calculated from that crop’s relative proportion of the total transplants produced in the greenhouse. Crop-specific field areas and labor costs for both field growing and harvest/packing are brought over from the Input Sheet Three, with non-crop-specific labor and remaining nonspecific field expenses allocated by field area, and non-crop-specific harvest/packing expenses calculated based on the crop’s proportion of time reported for harvest/packing of as compared with the total. Crop-specific labor costs for field growing are calculated by multiplying actual labor costs per hour (as calculated on Input Sheet Three) and multiplying by field growing crop-specific labor hours. Non-crop-specific labor hours, as allocated by field area, are calculated by multiplying non-crop-specific hours spent field growing (as entered in Input Sheet Three) multiplied by actual labor cost per hour, and multiplying that total by the proportion of field production space devoted to that crop. Allocating non-crop-specific labor hours by field area is a simple approximation that is easy to implement in the spreadsheet and meets most growers’ needs. However, the approximation is subject to error, such as if the crop requires extra tillage or extra passes to end a cover crop.

Total crop cost up to harvest is provided by summing that crop’s seed cost, that crop’s share of the greenhouse cost, and that crop’s share of the total field growing costs. This cost is valuable as information regarding crop expenses before the labor investment in harvesting the crop. There may be times, depending on market prices in a given production season, that a farmer may choose not to harvest a crop if the costs of harvest are more than the price the farmer would receive by selling the crop. Crop-specific nonlabor costs (e.g., bags for packing, twist ties, etc.) are carried over from Input Sheet Three. The crops share of total harvest and pack costs are calculated by summing crop-specific costs and the non-crop-specific costs as allocated by the proportion of crop.

The last two columns in the Cost of Production sheet calculate the total cost of producing each crop (summing seed and greenhouse costs of each crop, the crop’s share of total field growing costs, and the crop’s share of harvest and packing costs). Total cost per unit is then calculated by dividing this figure by units sold; total unit cost of each crop is calculated by dividing the total crop cost to produce/harvest by the total units sold. This value only reflects operational production costs, and does not include overhead and market channel expenses.

Output Sheet Two (Fig. 5) calculates the crop cost of production, crop gross margin, unit breakeven price, unit net profit, and crop net profit for each crop within each market channel. Because each market channel has unique expenses associated with it, the unit cost of producing a crop for one market channel can differ from the cost for another market channel. Thus, the crop cost of production is calculated based on the units sold of each crop within each market channel. Sales data, number of units sold, and unit average price for each market channel are carried over from Input Sheet Two. Unit cost of production and total crop cost of production values are carried over from the calculations provided by the Cost of Production sheet. However, these values do not yet include the overhead costs of general management and administration, as well as market channel expenses. Crop gross profit for each market channel is calculated by subtracting crop cost of production from total crop sales generated by each market channel, divided by the total crop sales in that market channel. Crop-specific breakeven prices per unit are calculated by summing the cost of production, harvest, and packing for each unit, plus adding a relative proportion of general administrative and marketing costs specific for each market channel. Crop net profit per market channel is calculated by subtracting the cost of production from total sales per each crop in that market channel and adding a proportion of the market-specific administrative and marketing costs. Unit net profit is calculated by dividing crop net profit by number of units sold. Total sales output for crop cost of production, total unit net profit, and total crop net profit are calculated by summing crop-specific data for each market category.

Output Sheet Three (“Profit and Loss” sheet) (Fig. 6) summarizes the sales and costs of the whole farm business and its overall profitability. The percent of total sales in each market channel is calculated to demonstrate the contribution of each market channel to the total farm sales. The crop costs of production for each market channel, as totaled on the sales output sheet, are automatically brought over to Output Sheet Three. Production expenses as percent of total sales are calculated for each market channel to reveal what proportion of the sales is needed to cover the production expenses. Total gross profit, as well as gross profit as a percent of total sales, is calculated for each market channel by subtracting sales from total expenses. Gross profit does not account for specific market channel expenses and the general management and administrative expenses. The specific expenses associated with each market channel, which were entered Input Sheet One, are automatically brought over to this sheet. The general management and administration expenses, which were also entered into Input Sheet One, are now allocated to each market channel by sales volume. The unique market channel expenses and the general management and administration are added together to provide the total of nonproduction expenses for each market channel. The percent of sales is provided to indicate the proportion of the sales needed to cover the cost of market channel and general management and administration expenses.

The net profit for each market channel is also calculated on this sheet. The total of the unique market channel expenses and the general management and administration are deducted from the gross profit to reveal the net profit. The net profit provides the dollar value of the profit or loss for each market. The percentage of sales that is net profit is provided to reveal the profitability of each market channel. The percentage of total net profit for each market channel is calculated to show the contribution of each market to the farm’s total net profit. Nonoperating income, as entered into the Sales Input sheet, is automatically brought over to the profit and loss sheet. Finally, taxable income is calculated by adding total farm net profit and the nonoperating income.

Farmer feedback: Program use and validation

Since 2008, the Veggie Compass program has been trialed using actual annual farm expenses and labor data from five organic diversified vegetable farms in the upper midwestern United States. These farms represented the average size and scope of the certified organic vegetable farms in the region. The project team met with each of the participating farmers to assist with the entry of information into the spreadsheet, gathering information as to where further clarity was needed with respect to spreadsheet use. In addition, the project team consulted with the farmers as to the validity of the calculations provided by the spreadsheet and the applicability of the numbers to future farm business and management decisions.

Beyond outreach efforts in the upper midwestern United States, the Veggie Compass tool has been integrated into farm-management and cost-of-production workshops in other areas of the United States, particularly in the southeastern U.S. SSAWG, with funding from a USDA Risk Management Education grant, held four “Growing Farm Profits” workshops in 2012 to 2013 that were attended by 236 producers. Growers attended this workshop anticipated integrating the labor and harvest collection worksheets and the Excel-based spreadsheet into their farm-management operations during the following season. Comments submitted on postworkshop surveys distributed six months following the workshop included the following:

• “The workshop has had a great impact on how I think about my farming operation. We are [now] tracking costs in each enterprise and thinking more about profit margins. [We are] trying to use this information to make better decisions about what to grow and what to sell. [We] started a new enterprise this year based on our estimates that it could be more profitable than other things we have been raising.”

• “I have changed my method of keeping records. I started looking at the things I needed to record from a different perspective- not just cost of seed, but literally from start to finish of each of our products. To include labor, time, water, etc. in order to see if we are breaking even or making a profit and how much of one... Your course made me look at things from a different point of view. I felt smarter and more informed of what I need to improve my farm.”

• “I’m not a farmer, but an educator. Part of my work consists of providing outreach, training and technical assistance to small farmers and ranchers in Texas. The CD and the ideas behind Veggie Compass and Growing Farm Profits are very powerful. Probably the stronger element of Veggie Compass is the profit analysis by marketing channels… I have been talking to farmers about Veggie Compass and other similar spreadsheets to highlight all of the elements they should consider to keep records on. It is an eye opener for them.”

Future program development

The Veggie Compass program continues to evolve. The project team has thus far validated the program with three farmers located in the upper midwestern United States. This consisted of a full-day session of consulting with the farmers, confirming correct data entry, and discussing program output. Thus far, the greatest variation in the data occurs with the calculation of labor hours per crop and activity, which, according to farmer input, can vary up to 25% from timesheet values. However, farmers have confirmed that the data has allowed them to undertake informed changes in their marketing and production strategies. During the 2014 vegetable production season in Wisconsin, the project team will collaborate with a coalition of CSA farmers to further test methods to allow for accurate collection of crop-specific labor data, the most significant hurdle to the widespread implementation of this program. Alternative strategies of collection, such as focusing collection on key activities throughout the growing season, grouping crops with similar production characteristics, and creating benchmark values applicable to farms of different sizes and levels of mechanization will be explored.

Although originally designed for organic producers in the upper midwestern United States, this tool can be used by diversified vegetable producers across the United States. The spreadsheet is available online (CIAS, 2013) and is free to download, along with an accompanying user manual and data collection forms. CIAS maintains the spreadsheet and the website, ensuring updates occur as necessary.