User`s Manual - ATTRA - National Center for Appropriate Technology
Contents
1. a two dollar per flat decrease in cherry tomato prices over a five week period from January 1 to February 5 Or you could explore more complicated scenarios such as What if cherry tomato prices fall in in January and cherry tomato growers have a partial crop failure Could we replace that lost production by increasing production by another grower Would these setbacks force us to delay hiring a new truck driver Very often these simulations will point out weaknesses in your plans and show you that you need to change something in your initial investments or operating expenses There are countless scenarios that you can investigate in this way Food Hub Planning Workbook National Center for Appropriate Technology NCAT Page 5 Acknowledgment amp disclaimers Funding to develop this workbook was provided by the Rural Business Enterprise Grant program of USDA Rural Development award number 49 015 752017047 The views and opinions expressed in this publication do not necessarily reflect those of the funders the United States government or any Federal or State agency These materials are the result of tax supported research and as such are not copyrightable They may be freely reprinted as long as credit is given to the National Center for Appropriate Technology as the source There are many financial risks in starting a food hub and anyone considering starting a food hub should consult with legal and financial professionals Neither NCAT nor2. by entering a different value in cell B73 Row 29 Diesel amp Gas Transportation amp Equipment assumes that the cost of diesel fuel gas and transportation equipment will be two percent of net revenues This estimate does not include maintenance repairs or labor for drivers and other employees This percentage will fluctuate with fuel costs and other factors You can change it by entering a different value in cell B74 Row 35 Depreciation needs to be filled in if you showed any fixed assets on the nitial Investment sheet that need to be depreciated according to Internal Revenue Service IRS guidelines The IRS guidelines are complicated and we make no attempt to summarize them here Once you determine the monthly depreciation amount for each item add all items together and enter the total amount for each month in row 35 Row 38 Interest on Line of Credit needs to be filled out if you entered a positive line of credit loan amount on line 22 of the nitial Investment page Estimate the interest that you would need to pay on this line of credit As noted earlier almost all startup food hubs need to borrow money to get through their initial months so don t just ignore this row The Income Statement sheet is set up to provide a one year forecast If you are creating a two or three year forecast you will need to Unhide Columns O through AN To do this select columns N and AO by holding down the right mouse key while you drag acros
3. the authors nor the funders assume any liability for any loss or damage caused or alleged to be caused directly or indirectly to any person or entity by the information or instructions contained in this workbook Food Hub Planning Workbook National Center for Appropriate Technology NCAT Page 6
4. Food Hub Planning Workbook User s Manual June 2015 Introduction The Food Hub Planning Workbook enables food hub planners and managers to model or forecast the financial performance of a hypothetical or existing food hub The workbook consists of four Excel spreadsheets plus this brief user s manual One of these spreadsheets Template has all fillable fields set at zero Based on your estimates of weekly and monthly sales volumes prices and operating costs such as salaries transportation fuel and equipment the spreadsheet will generate an income statement and a cash flow statement Cells containing formulas are locked preventing you from accidentally overwriting or deleting the formulas The other three Excel files TX Food Hub Year 1 Year 2 and Year 3 show the values that we used in our 2015 Feasibility Study for a Texas Organic Food Hub These three files may be used as an addendum to that report since they provide a nearly complete record of our assumptions and calculations You may also find these files useful as a source of comparisons or ballpark values The workbook and Feasibility Study for a Texas Organic Food Hub are both available as free downloads from NCAT s ATTRA website www attra ncat org This workbook is being released in a beta version and has undergone only limited testing for usability and accuracy While these spreadsheets were created with care they contain many formulas and should be tested to be sure
5. d for Other Fruits For each vegetable or fruit variety you will need to estimate cases sold per week and a price per case The number of cases sold must be entered for each week However the price you enter for the first week of each month is automatically copied into the remaining weeks of that month Like the Salaries sheet both the Vegetable Sales and Fruit Sales sheets are set up to provide a one year forecast If you are creating a two or three year forecast you will need to Unhide Columns GP through VJ To do this select columns GO and VK by holding down your right mouse key while you drag across the small letters GO and VK at the top of the spreadsheet Both columns will become shaded Left click anywhere in the shaded columns and select Unhide from the dropdown menu You will now see the columns for Year 2 and Year 3 Food Hub Planning Workbook National Center for Appropriate Technology NCAT Page 3 You can edit the information in the PKG and LBS PKG columns however you like Information in these cells is for your reference only and does not enter into any calculations or formulas You will notice many cells with the error message DIV 0O Ignore these messages as they are harmless The error message will disappear for each month where you show sales An indispensable guide to prices is the Market News Reports website of the USDA Agricultural Marketing Service AMS which may be found at http cat ma
6. es or problems If you are creating a two or three year cash flow you will need to Unhide Columns N through AK To do this select columns M and AL by holding down the right mouse key while you drag across the small letters M and AL at the top of the spreadsheet Both columns will become shaded Left click anywhere in the shaded columns and select Unhide from the dropdown menu You will now see the columns for Year 2 and Year 3 The template assumes that all initial investments take place in January of Year 1 So in Year 1 the January Beginning Balance is equal to the sum of all items on the Initial Investment worksheet In Years 2 and Year 3 however the January Beginning Balance is equivalent to the December Ending Balance from the previous year Likewise no numbers are copied automatically from the Initial Investment worksheet to the Year 2 or Year 3 Cash Flow This means that you will need to fill in manually rows 21 2 37 39 41 5 49 51 55 7 and 63 4 indicating any expenses such as rent payments or licensing fees that apply to your situation and you expect to recur in Year 2 or Year 3 You will probably leave most of these cells at zero because they reflect one time deposits or other expenses that take place only in the first year Step 6 Look at what if scenarios Your most common what if simulation will probably be changing the number of cases sold or the weekly prices For example you could look at the impact of
7. ksheets You will also need to enter some values manually into the Income Statement worksheet although other values will be filled in automatically Once you have filled in these five worksheets a Food Hub Planning Workbook National Center for Appropriate Technology NCAT Page 1 one year Cash Flow worksheet will be generated automatically You will need to enter a few more numbers manually if you want to create a cash flow for Year 2 and Year 3 Please note e We have made the worksheets as user friendly as possible although you do need basic familiarity with Microsoft Excel e Items that do not apply to your situation can generally be ignored renamed or deactivated by setting them to zero e Cells containing formulas are locked to prevent you from overwriting the formulas You can select locked cells by clicking on them to see what formulas they contain but you can t change any formulas e For sample values and ballpark estimates look at the three TX Food Hub files You will notice some minor differences in formatting between the TX Food Hub files and the Template e One major difference is that each of the three TX Food Hub files contains a Balance Sheet The Template does not generate a balance sheet although it provides much of the information that would be needed to create a balance sheet e Users who have advanced Excel skills will see many possibilities for customizing the worksheets In general nothing prevents you fr
8. om doing this There are no password protected areas However we advise extreme caution before unlocking the sheets or altering any of the formulas It is easy to introduce errors that will destroy the integrity and accuracy of the worksheets Step by Step Instructions for using the Template Before completing the Income Statement you will need to fill in the nitial Investment Salaries Vegetable Sales and Fruit Sales worksheets These four worksheets can be completed in any order but the instructions below start with the nitial Investment and Salaries sheets since these are the least time consuming Step 1 Complete the Initial Investment sheet Hub organizers need to think long and hard about what will they will purchase and lease initially You will probably revisit these assumptions many times as they will have a dramatic impact on your food hub s cash flow and profitability Check and adjust as necessary the names in column A of the Initial Investment sheet These expense categories come from our Feasibility Study for a Texas Organic Food Hub All of these cells are left unprotected and items can be renamed however you like or deleted Once you have adjusted the category names to your liking estimate a cost for each item on the Initial Investment sheet It may be useful to refer to the three TX Food Hub files for examples and ballpark values All items on the Initial Investment page are automatically copied to Year 1 of the Cash Flow
9. rketnews usda gov cat Be forewarned that using this site takes considerable trial and error but is a skill worth achieving for any hub manager Step 4 Complete the Income Statement sheet To begin check and adjust the names in column A of the Income Statement You can t add rows but you can rename most items in Column A Extra rows are also provided for Other Revenues and Other Expenses Now fill in all values that apply to your situation estimating revenue costs and expenses as best you can Note that some cells in the Income Statement sheet have already been filled in automatically with values from the Salaries and Initial Investment sheets You can t change these values or the names of their rows In rows 5 and 13 enter any income and expenditures for packing materials that you plan to buy at bulk rates and sell to your members Leave these rows blank if you don t plan to sell packing materials to your members Row 7 Sale Returns Allowances assumes that three percent of sales will be rejected by customers because of cosmetic issues spoilage or other quality control reasons This is an approximate industry average but can be changed by entering a different value in cell B72 Rows 11 amp 12 Cost Vegetables and Cost Fruits assume that grower members in your food hub will receive 70 percent of sales revenues This is a common arrangement and close to the average reported by food hubs nationally but you can change it
10. s the small letters N and AO at the top of the spreadsheet Both Food Hub Planning Workbook National Center for Appropriate Technology NCAT Page 4 columns will become shaded Left click anywhere in the shaded columns and select Unhide from the dropdown menu You will now see the columns for Year 2 and Year 3 When you unhide the columns for Year 2 and Year 3 you will notice that you have the option of adjusting the Sale Returns Allowances Cost Vegetables Cost Fruits and Diesel amp Gas Transportation Equipment percentages for each of these years by changing the numbers in cells 072 074 and AB72 AB74 The template assumes that all initial investments take place in January of Year 1 No numbers are copied automatically from the Initial Investment worksheet to the Year 2 or Year 3 portions of the Income Statement Consequently you will need to fill in manually rows 21 2 31 37 39 40 and 58 9 indicating any expenses that you expect to recur in Year 2 or Year 3 You will probably leave most of these cells at zero because they reflect one time deposits or other expenses that take place only in the first year Step 5 Check the Cash Flow sheet If you ve completely filled out the nitial Investment Salaries Vegetable Sales Crop Sales and Income Statement worksheets the Cash Flow for Year 1 should now be automatically filled out So if you were creating a one year financial forecast your work is done Just check for any discrepanci
11. sheet and also copied to the green shaded cells in the Income Statement where they are treated as January Year 1 expenses You cannot add rows to the nitial Investment sheet but five rows are provided for Other Initial Investments Food Hub Planning Workbook National Center for Appropriate Technology NCAT Page 2 Pay special attention to Line 22 Line of credit loan Almost all startup food hubs need to borrow money to get through their initial months so don t just ignore this row and leave it blank If your food hub shows a negative cash flow go back and increase the line of credit loan amount to see how much you would need to borrow to cover expenses and stay solvent Step 2 Complete the Salaries sheet The job titles in Column A of the Salaries sheet come from our Feasibility Study for a Texas Organic Food Hub You are free to change them Estimate salaries for the jobs at your food hub as best you can In general we advise making salaries high enough to limit turnover since this can be disastrous in the early years of a food hub If you are unfamiliar with the staffing requirements of a food hub several complete job descriptions are available in Appendix 5 of our Feasibility Study for a Texas Organic Food Hub and may be useful as a reference Leave rows blank or set at zero if you don t need them You can add rows to the Salaries sheet if you need to add more job titles To add a row click the small number in the leftmo
12. st column to select an entire row Then click Insert Sheet Rows from the dropdown list under Insert in the Cells section of the Home menu In the Salaries sheet Columns B through N cover a one year period If you want to generate a longer forecast two or three years you will need to Unhide Columns O through AN To do this select columns N and AO by holding down your right mouse key while you drag across the small letters N and AO at the top of the spreadsheet Both columns will become shaded Left click anywhere in the shaded columns and select Unhide from the dropdown menu You will now see and be able to fill in the columns for Year 2 and Year 3 Payroll tax rate assumptions are stated at the bottom and can be changed for each year of the hub s operations Health insurance for employees is not shown on the Salaries sheet but will be entered later on the Income Statement Note also that we have not provided for any funds for a defined benefit plan such as a 401 k plan as this would be unusual in a startup phase of a food hub Step 3 Complete the Vegetable Sales and Fruit Sales sheets We have included 38 common vegetables and 12 common fruits on the Vegetable Sales and Fruit Sales worksheets All can be renamed Ignore rows that you don t need and leave their values set at zero You cannot add rows to this sheet However five extra rows are provided for Other Vegetables and five extra rows are provide
13. they are error free We welcome your feedback Robert Maggiani and Mike Morris National Center for Appropriate Technology ncatsw ncat org About the Template The template can be used to forecast a food hub s financial performance over a period of up to three years You can customize the template in many ways such as adding products and employees or changing the names of expense categories In addition to its usefulness as an analytical tool we intend the workbook to be an educational tool Filling out the fields will force you to consider every detail of a food hub s operations and will often highlight inconsistencies in your assumptions showing you how all the pieces of a food hub fit together Users are advised that the template requires very detailed inputs of data In order to model a food hub selling just ten different products you would need to enter hundreds of prices and volumes While time consuming we believe this level of detail is unavoidable and necessary for food hub planners Most food hubs run on slim profit margins leaving little room for error or inefficiencies If you are not ready to fill in the values in the template you are probably not ready to start or manage a food hub When you open the template you will see that it is made up of six worksheets indicated by the six tabs at the bottom of your screen You will be entering most of your data into the Initial Investment Salaries Vegetable Sales and Fruit Sales wor
Download Pdf Manuals
Related Search