Little attention has been paid to beekeeping financials over time. The chapters in both the 1992 and 2005 editions of the classic work, The Hive and the Honey Bee, published by Dadant and Sons, Hamilton, Illinois entitled “Business Practices and Profitability” broke that tradition.
The Financial Analysis Honey Production, Pollination and Queen Rearing Spreadsheets discussed here, developed by David MacFawn, are for analyzing different management decisions to determine their impact on the financial operations of the business, and to make informed business decisions. It allows one to make their mistakes on paper rather than with real life money. This Spreadsheet is for beekeepers’ wanting to take the next step with their bee operation and wanting to expand. For existing bee operations, the spreadsheet will point the bee owner to where their problem areas are. This spreadsheet will help beekeepers expand profitably which will help ensure their long term success. One will need to understand beekeeping basics and spreadsheet basics to understand this spreadsheet. It is normally sent via email after payment of $40 US. Consulting time of up to one hour is included in the $40 US to help you configure your spreadsheet. Payment is by check or PayPal but you need to let me know what email address to send the spreadsheet to. Send an email request for more information to dmacfawn@aol.com or call 803-957-8897 EST.
The spreadsheet examines various bee investment possibilities and puts all the possibilities into play together. This allows one to determine the interactions of various investment choices and how they impact the profitability and Net Present Value (NPV) of the investment decisions. Profit margins are available to analyze, as are total dollar expenditures each year. Equipment (woodenware and extracting) are expensed in the current year (i.e. not depreciated) in this spreadsheet to determine the total cash outlay each year. However, to determine individual product line profits and margins, the equipment is depreciated. Taxes are not included. A five year basis is used since the IRS presumes that an activity is carried on for profit if it makes a profit during at least three of the last five tax years, including the current year.
Investments in bees, equipment (woodenware, extracting and honey processing equipment), labor, mileage, costs (jars, labels, medication) with respect to honey and pollination prices are included. The honey sheet includes both US ( pints, quarts, etc.) and also metric (kg, g, km, etc). The financials of pollination are also addressed as a stand-alone business and in conjunction with honey production. Honey processing equipment break even analysis is also included. In addition a page is devoted to determining what is the breakeven point to monitor and treat hives; i.e. at what mortality level & labor rate is it still profitable to monitor mite loads and treat or just replace dead-outs? I have enhanced the latest Business Plan Spreadsheet with individual product line cost / profit / and profit margins so that the proprietor knows if they are making money on individual product lines. A sheet on Travel & Living, brokerage, and trucking charges has been added.
A queen rearing and NUC production spreadsheet has been added in October, 2014. This spreadsheet includes the revenue and total expenses and profit margins for queen rearing and NUC production. Note however, there are a lot of ways to raise queens and even with grafting there are a lot of ways to organize the queen rearing operation.
In the treating break even sheet analysis, honey value, cost of bees, and labor are the predominate cost items. With lost honey, i.e. assume cannot split to make up the honey loss (management technique) we are in the 1% – 2% break even mortality range. If we assume we can split and make up any honey loss we are in the 10%-14% range break even mortality (for labor rates of $8-$12). I have a table on the treating break even tab that summarizes my results to date. So, basically the treating break even rate depends on one’s management techniques.
Honey reseller capability and purchasing and selling honey in barrels has been added to this version. Capability to enter honey purchased has been incorporated. Interest charges are also included and assume that you will pay off the balance in three months with the bee operation revenue.
This spreadsheet is strictly a bee management planning tool that allows you to consider different “what if scenarios” to determine:
– How you should set up your honey or pollination operation and the impact on the financials of the operation. Should you just produce honey, or do honey and pollination, or strictly do pollination? Pollination is cheaper initial outlay. Profit depends on pollination rate. In some states, you will need to combine pollination with honey production to maximize profits.
– Trade off things like labor rates, bee survival rates, interest rates, total $ investment in equipment, mileage, etc. and their impact on the profitability of a bee operation; i.e. can you make money?
– It allows one to input parameters based on their circumstances and trade off investments in various parameters and how quickly one builds up / invests in their bee business.
– You have to input the parameters for your operation and region.
Hence, this is really a bee operation planning tool that would be used prior to setting up your bee operation, or planning for a new year prior to using an existing accounting program, like Quickbooks, which is mostly for historical data.
The copyrighted spreadsheet is sold as is. If you have a question about how to use the spreadsheet, send me an email or call me. You are responsible for entering in the data for your particular operation. Additional tailoring is available for $35/hour with a one hour minimum. It typically takes one to two hours to tailor the spreadsheet for your particular operation; however, you have to supply your financial information. The spreadsheet is made with Microsoft Excel 2010. You can modify the existing spreadsheet yourself after payment to further suit your needs. However, replication and further distribution of the copyright spreadsheet is prohibited
David E. MacFawn
205 Ridgecreek Drive
Lexington, SC 29072
803-957-8897
dmacfawn@aol.com
November, 2016 update:
added 5 years rather than 2 years of profit forecast for each type of jar.
Made the input for the number of colonies at the beginning of the spreadsheet easier and more accurate.
Calculated labor based on metrics that the user inputs
August, 2015 update:
Attached is the latest spreadsheet for your academic use. New to this version:
– added metric for my Canadian customers
– added Travel and Living, brokerage, trucking charges
– added provision for investing money up front in the honey business
– added provision for three months interest
– added profit / loss on individual jars for honey
– calculated cost of honey for US and metric jars
– added depreciation to honey sheet
– enhanced provision for purchasing honey for resale
– enhanced label cost
– provision for expensing extracting and processing equipment in first year (A14), or taking out a loan (B398).
– added a building sheet where profit from honey, pollination, queen rearing spreadsheets are added and a Net Present Value calculated to determine if the operation can afford the building and/or equipment. Also have calculated the Net Present Value for a building at the bottom of the honey sheet for a honey only operation.
Includes:
– Requeening costs
– Value of beeswax
– selling price of 60 # pails, barrels, and metric sizes
– Product mix between barrels, 60 # pails, pints, quarts, 12 oz., 8 oz., 2 oz., 1#, and 13 different metric size containers for purchasing and selling honey.
– What you can finance / invest in a building with cost of electricity / taxes / etc. 10 year/6% interest current.
– financing equipment 10 year / 6% interest current
– I have also updated some of the hive component costs
– added the medication cost
– note that the investment in honey processing equipment (breakeven) is based on the labor rate
– cost of pint / quart / 1# jars and barrels, 60# plastic pail/12 oz. /8 oz./2 oz. and 13 metric jars
– How many jars and labels of each type you will need based on the product mix
– The cost of a truck is not included in the spreadsheet number (total cost / profit) since mileage is (in the operations expense / trips section.) I have included the cost of a truck in the monthly outlay sum in the building section to get a feel of what my monthly obligations are.
-Equipment and treating breakeven points
Some generalizations pop out:
– The bee business has a price, volume, distance relationship.
– assumes can sell all the honey
– assumes can keep your bees alive at least at the hive yield efficiency
– invest in your bees and woodenware first prior to investing in a lot of extracting equipment or a big building.
– purchase good used equipment wherever possible or commercial grade equipment
– The product mix should be based on what you can sell / your marketing plan
– the product prices (pints / quarts / 1# / pails/12 oz. / 8 oz./metric) should be market based and this spreadsheet will tell you if you are making a profit and what it is based on your selling price.
– If your wooden ware is paid for, you can do quite well after the initial investment
– For pollination, woodenware / equipment cost is the limiting factor. Here is where purchasing used or commercial grade equipment will help. This is shown by putting $0 cost in for the yearly equipment cost. ( put $0 in for pint/quart/1#/60# pail honey cost, $0 for yield, $0 equipment, $0 for beeswax value since these are honey related) in the honey spreadsheet page. You also have to adjust the travel mileage to inspect your hives on the honey spreadsheet page. The mileage on the pollination page is for carrying the hives to the pollination field.
– The Net Present Value (NPV) comparison rate is the interest rate. i.e. if the NPV is negative, your plan the way it is set up in the spreadsheet, you are losing money by investing in the bee operation as currently planned in the spreadsheet; that is based in the negative NPV you can make more money by investing elsewhere. You need to adjust / change your entries in the spreadsheet to reflect a different bee investment strategy. The NPV is discounting / translating cash flows to the present time based on the interest rate.
– The profitability / NPV is very sensitive to the hive yield and hive efficiency (your winter losses and non-yielding hives). It is also sensitive to the product price (pints/quarts/1#/ 60# pails/metric) and product mix. You should sell retail whenever possible.
Net Present Value (NPV) compares the value of a dollar today to the value of that same dollar in the future, taking inflation and returns into account. If the NPV of a prospective project is positive, it should be accepted. However, if NPV is negative, the project should probably be rejected because cash flows will also be negative. The Net Present Value (NPV) comparison rate is the interest rate. i.e. if the NPV is negative, your plan the way it is set up in the spreadsheet, you are losing money by investing in the bee operation as currently planned in the spreadsheet; that is based on the negative NPV you can make more money by investing elsewhere. You need to adjust / change your entries in the spreadsheet to reflect a different bee investment strategy. The NPV is discounting / translating cash flows to the present time based on the interest rate.
Honey Spreadsheet
Cost and Revenue Drivers
Global Variables
The variables at the top of the spreadsheet in color are global variables (i.e. changing these variables here will change these variables throughout the spreadsheet). The variables such as expensed processing equipment, yield per hive, cost per mile, interest rate, labor rate, hive yield efficiency, and retail prices for pint, quart, 8 oz., 12 oz. , metric or one pound jars of honey, the product mix of pints/quarts/1 pound jars / 8 oz. / 12 oz., 2 oz., metric and beeswax are global variables and will vary by beekeeper.
The pint/quart/one pound prices are converted to per oz. prices with the per oz. price actually used in honey pricing section of the spreadsheet. The product mix of barrels, 60 # pails/pints/quarts/1 pound jars / 8 oz. / 12 oz. / 2 oz. global variables total should equal 1.0. Changing the product mix between barrels, 60 # pails/pints/quarts/1 pound / 8 oz. / 12 oz. / 2 oz. /metric jars will allow you to see the impact on the Net Present Value (NPV). This will allow you to see if you are making money under those sales assumptions and the other inputs (global and other).
Packages & NUCs; Queens
With packages will probably delay honey production by one year. With NUCs, which typically cost more than a package, may get some honey production in first year. This spreadsheet assumes honey production first year. If you do not get honey production first year, it will affect the Net Present Value (NPV) negatively. I have also included the cost of queens to requeen your colonies.
Mileage
One needs to minimize mileage as much as possible. One should place bee yards close to your home and place bee yards within five miles of each other to minimize forage overlap and driving distance. The mileage distance is very dependent on the hive yield that you get in your area. The cost of a truck is not included in the spreadsheet number (total cost / profit) since mileage is (in the operations expense / trips section.) I have included the cost of a truck in the monthly outlay sum in the building section to get a feel of what my monthly obligations are. Mileage is a major expense; loan for truck is not included.
Equipment – woodenware
You need to purchase used or commercial grade equipment whenever possible. If purchase used equipment, you should burn any old frames and scorch out the inside of the woodenware to prevent possible disease transmission. Equipment is expensed in this spreadsheet, to make it easier to determine the total cash outlay. I also calculate a depreciated NPV also.
Labor
The spreadsheet is very sensitive to labor rate.
Honey Production Value and Jars/Labels
Honey production value is very sensitive to your sales strategy. You can purchase pints, quarts, metric, or specialty jars, or 60 # plastic containers. With specialty jars, if selling in the correct venue, you can increase the honey’s price per oz. Jars are a major expense but can be covered by type of sale and higher revenue at lower quantity. This gets back to your marketing strategy: up-scale or sell in a “farmers market.” NPV is very sensitive to hive yield.
You should set your honey value at market price by inputting the barrels, 60 # pails/pint/quart/one pound / 12 oz. / 8 oz. / 2 oz./metric prices in the global variable and the spreadsheet will back into the resulting sales margins / profit that will be reflected in the spreadsheet. Your marketing strategy defines the type of jar and label(s). The jar and label(s) will sell your honey so be very careful on what you use based on your marketing strategy, and how and where you will be selling your honey.
The product mix of 60 # pails/ pints/quarts/1 pound / 8 oz. / 2 oz. / 12 oz./metric jars global variables total should equal 1.0. Changing the product mix between 60 # pails/pints/quarts/1 pound / 8 oz. / 12 oz./metric jars will allow you to see the impact on the Net Present Value (NPV). This will allow you to see if you are making money under those sales assumptions and the other inputs (global and other).
Treatment costs
If one does not keep their bees alive, the entire spreadsheet assumptions crumple. The cost of Apivar is included in this spreadsheet for Varroa. If you develop American Foulbrood (AFB), I would recommend that you burn the entire frames and scorch out the inside of the equipment.
Pollination – Sugar Costs
If you pollinate crops, you will probably have to feed your bees. In addition, honey should probably be pulled prior to placing your hives in the field for pollination if you are interested in a honey crop. In some states, the honey value is greater than the pollination value. This may not be true in your state. Hence, to maximize revenue, one should work toward the honey crop with pollination enhancing the sales revenue later in the summer. Data fields are available to enter the pollination value for your state.
Honey Processing Equipment
The Net Present Value (NPV) is very sensitive to the equipment cost. You should delay equipment purchases as long as possible and minimize the amount / value. Purchase used equipment as much as possible. I have preloaded current equipment retail cost from Dadant for the woodenware and Maxant for the honey processing equipment. You need to change this value to reflect what you purchase equipment for. Shipping costs should be included in the prices.
There is a global variable for honey processing equipment cost at the top of the spreadsheet. This global variable equipment cost is expensed, i.e. included in the current year. Hence, you should minimize the global variable equipment cost. I have also added equipment cost in the Building section. This equipment cost in the Building section is financed by a loan at the global interest rate.
Interest Rate
NPV is very sensitive to the interest rate that you get for loans or can use your money for other non-beekeeping endeavors. I have preloaded 6% (.06) in the global variable for interest rate at the top of the spreadsheet. You need to change this value to reflect what interest rate you get for a loan. The global interest rate is the “hurdle rate” for the NPV calculation and also the interest rate for the building and equipment loans.
Hive Yield Efficiency
The hive yield efficiency is determined by the number of honey yielding colonies and survival rate. The NPV is very sensitive to the hive yield efficiency due to honey production quantities. I typically set the hive yield efficiency to 0.70 due to the current colony loss rate, i.e. we are losing 30% of our colonies each year. In some states to maximize revenues the beekeeper should produce honey during the honey flow and pollinate after the honey flow. Who you purchase NUC packages from will affect the hive yld eff due to the survival rate or hive yld efficiency.
If I go up to 64 hives, 3-4 bee yards would be necessary. I typically have a maximum of 20 hives per yard for bee foraging reasons and also this is the maximum that I want to work at one time. These yards should be approximately 5 miles apart to minimize travel expense but maximize the areas that the bees would forage over.
Minimize travel which means not moving the hives due to the low SC yields. Moving the bees to the mountains for the sourwood flow is not profitable due to the sourwood producing only 2-3 years out of every 5 years on average unless you live close to the mountains.
For honey only production (no pollination), you should leave enough honey on the colony for the winter. No sugar cost. Strategy depends on the relationship between sugar and honey prices and your management strategy. Some believe that honey is healthier for the bees than sugar.
All equipment (woodenware + extracting) expensed, i.e. written off and included, in the current year. You may want to also consider depreciating equipment for tax purposes.
Sensitive to labor.
Who purchase NUC /packages from affect the hive yld eff due to the survival rate or hive yld efficiency
Very sensitive to yield/hive #; very sensitive to per oz. revenue; sensitive to interest rate; very sensitive to hive yld efficiency or survival rate. Sensitive to labor.
No price escalation in the spreadsheet over the years for costs or revenue for simplicity. However, you can include this somewhat by the equipment prices you input. However, cost per mile, etc. is just one rate for the entire five years the spreadsheet covers.
I recommend that you save the spreadsheet, make a copy of the spreadsheet and modify the copy for your operation. If you have any problems, I can send you another copy of the spreadsheet.
You will find the quicker you build up your hive numbers, the more revenue (honey and wax) you will have to cover your equipment cost. This means your Net Present Value (NPV) will go up.
Play around with the yield /hive and change 2-5 lbs. and you will see your NPV jump. You can also change the hive yield efficiency from 0.70 to 0.80 or higher by using VSH bees and you will also see a big jump in NPV. The yield/hive is dependent on where you have your bees and the hive yield efficiency is dependent on the type of bees you are using.
Play around with the first line global variables (price and product mix, etc.) and you will see a business plan developing and some ideas on what you need to do to make money.
