Alfalfa Dehydration Feasibility Template Developed for: Ag Marketing Resource Center Developed by: Phil Kenkel and Rodney Holcomb, Oklahoma State University This templates is designed to assist you in assessing the feasibility of a typical alfalfa dehydration The template assumes that the operation purchases forage and hay and then sells alfalfa pellets The firms pays Input cells are shaded in blue. Each sheet is currently protected allowing you to only input information in the input cells. The template can also be modified and expanded to meet your particular situation by turning If you choose to modify the template we would advise you to save it under another name and To get started go to the "Input Page" For comments or suggestions contact: Phil Kenkel, kenkel@okstate.edu 405-744-9818 Rodney Holcomb, holcorb@okstate.edu 405-744-6272 MENU INPUTS UTILITIES MARKET PROJECTION LOAN AMORITIZATION PERSONNEL EXPENSES EXPENSE PROJECTION OPERATIONS SUMMARY DEPRECIATION RETURN ON INVESTMENTState University of a typical alfalfa dehydration operation. and then sells alfalfa pellets and/or alfalfa meal. information in the input cells. particular situation by turning off the protection. under another name and retain the original for reference.INPUT CAPITAL STRUCTURE AND OPERATIONAL SUMMARY EXPENSE INFORMATION Months of Forage Operation Percent Financed 50.00% Months of Hay Operation Long Term Interest Rate 8% Hours of Operation /Month Loan Term 10 Hours of Operation /Year Total Plant Property & Equip 1,370,000 $ Annual volume from Forage Loan Amount 685,000 $ Annual Volume from Hay Working Capital 1,296,939 $ Total Annual volume-tons Short Term Interest Rate 6% % of Total Volume to Pellets % Forage vs. Hay TAX INFORMATION Field Moist (Forage) Property Tax as % of PP&E 0.50% Pellet Moisture Income Tax Rate 60.00% Process Rate (tons pellets/hour) Hay price/ton PAYROLL INFORMATION Typical hay moisture % of Payroll Tax to Salaries 5.00% Custom Hauling cost/ton mile % of Retirement Tax to Salaries 15.00% Average distance field to co-op % of Employee INS Tax to Salaries 10.00% Hay price/ton:dry matter Benefits as % of Salaries 30.00% Actual Price per Ton Forage Wage Inflation 1.00% price/ton forage Hours of Operation 2260 Hay and Forage weighted cost/ton Hay and Forage wht cost/ton pellets UTILITIES Power Req. (KW/hour) Electricity cost per Kilowatt 0.05 $ Horsepower per hour 1,200.00 OTHER Gas Cost per MCF 3.00 $ Expense Inflation Rate MCF Gas Needed/Ton 13,288.80 Maintenance as % of PP&E Electricity Cost/Year 101,157.60 $ Insurance as % of PP&E Electricity Cost/Ton 5.60 $ Discount rate for NPV calc Water Cost/Year 3,000.00 $ Water Cost/Ton 0.17 $ WORKING CAPITAL Natural Gas Cost/Year 39,866.40 $ WC req. for Hay & forage Purchases Natural Gas Cost/Ton 2.21 $ Additional WC as % of Sales Telephone Cost/Year 2,400.00 $ Additional WC in Dollars Telephone Cost/Ton 0.13 $ Additional WC for Contingency Total Utilities/Year 146,424.00 $ Total Working Cap Requirement Total Utilities/Ton 8.10 $ INPUT PRODUCTS, INITIAL VOLUME, MARGIN PER 5.6 UNIT AND ANTICIPATED SALES GROWTH RATE 5.7 Product Name Pellets Product Name Meal 200 Units tons Units tons 2260 Initial Volume 18,080 Initial Volume 0 8960 Sales growth 1% Sales growth 1% 9120 Price/unit* 105.00 $ Price/unit* 108.50 $ 18080 * Price/unit FOB to plant 100% VARIABLE COST BREAKDOWN 47% Pellets $/unit Meal $/unit 60% Maintenance 7.58 $ Maintenance -$ 10% Custom Hauling 5.67 $ Custom Hauling -$ 8 Raw Materials 30.98 $ Raw Materials -$ $40 Total 44.23 $ Total -$ 10% $0.18 VARIABLE COST OF PRODUCTION PER UNIT 30 Product Unit VC/unit $44.44 Pellets tons 44.23 $ $40.00 Meal tons -$ $17.78 $32.00 $30.98 895.2 1.00% 10% 1% 12% 1,145,067 $ 8% 151,872 $ -$ 1,296,939 $ EXPENSE PROJECTIONS DEPRECIATION MENU: Click Below to Move to the Indicated Page FORWARD TO RESULTS OTHER INPUTS ENTRIES ARE IN PERSONNEL EXPENSESDepreciation Return On InvestmentReturn On Investment Quality Percent 0% Discount Rate 0.00%MENU BACK TO INPUTS FORWARD TO RESULTS Forage Needs Natural Gas Pellet dry matter/ton 1,800.00 Water ton-harvested 666.6666667 Forage needed/ton pellet 4,500.00 Water/ton pellets 200 Tons Forage needed/ton pellet 2.25 Water removed/ton 466.6666667 Forage needed (tons) 18,984.00 BTU's/lb. water 1500 Forage Cost 759,360.00 $ BTU's needed/ton 700000 Forage Cost/Ton Pellets 42.00 BTU's needed 13,288,800,000 MCF Gas needed 13288.8 Hay Needs Gas Cost/MCF 3.00 $ Pellet dry matter/ton 1,800.00 Gas Cost/Year $39,866 Hay needed/ton pellet 2,000.00 Gas Cost/Ton $2.21 Tons Hay needed/ton pellet 1.00 Hay Needed (tons) 9,642.67 Other Utilities Hay Cost 385,706.67 Water Cost/Year 3,000.00 $ Hay Cost/Ton Pellets 21.33 Water Cost/Ton 0.17 $ Telphone Cost/Year 2,400.00 $ Telephone Cost/Ton 0.13 $ Electricity Horse Power Req./Hour 1,200.00 Electricity Cost/KW 0.05 $ KW/HP 0.746 Electicity use per year 2,023,152.00 Electricity Cost/Year $101,157.60 Electricity Cost/Ton $5.60 Variable Costs Pellets Custom Hauling 102,513.60 $ Custom Hauling/Ton Pellets 5.67 $ Meal Custom Hauling 102,513.60 $ Custom Hauling/Ton Pellets 5.67 $ MENU BACK TO INPUTS FORWARD TO RESULTS Sales Projections Year 1 Year 2 Year 3 Year 4 Year 5 Pellets tons 18,080 18,261 18,443 18,628 18,814 Meal tons 0 0 0 0 0 18,080 18,261 18,443 18,628 18,814 Gross Sales Projection This sheet summaries the volume and price and sales growth information from the input page. There is no input on Year 1 Year 2 Year 3 Year 4 Year 5 Pellets Total Volume 18,080 18,261 18,443 18,628 18,814 Price/Unit $105.00 $105.00 $105.00 $105.00 $105.00 Gross Sales $1,898,400 $1,917,384 $1,936,558 $1,955,923 $1,975,483 Meal Total Volume 0 0 0 0 0 Price/Unit $108.500 $108.500 $108.500 $108.500 $108.500 Gross Sales $0 $0 $0 $0 $0 TOTAL GROSS SALES $1,898,400 $1,917,384 $1,936,558 $1,955,923 $1,975,483 Production Expense Pellets 799,592 $ 807,588 $ 815,664 $ 823,820 $ 832,058 $ Meal -$ -$ -$ -$ -$ TOTAL VARIABLE EXP. 799,592 $ 807,588 $ 815,664 $ 823,820 $ 832,058 $ Year 6 Year 7 Year 8 Year 9 Year 10 19,002 19,192 19,384 19,578 19,774 0 0 0 0 0 19,002 19,192 19,384 19,578 19,774 input page. There is no input on this page. Year 6 Year 7 Year 8 Year 9 Year 10 19,002 19,192 19,384 19,578 19,774 $105.00 $105.00 $105.00 $105.00 $105.00 $1,995,237 $2,015,190 $2,035,342 $2,055,695 $2,076,252 0 0 0 0 0 $108.500 $108.500 $108.500 $108.500 $108.500 $0 $0 $0 $0 $0 $1,995,237 $2,015,190 $2,035,342 $2,055,695 $2,076,252 840,379 $ 848,783 $ 857,271 $ 865,843 $ 874,502 $ -$ -$ -$ -$ -$ 840,379 $ 848,783 $ 857,271 $ 865,843 $ 874,502 $ MENU BACK TO INPUTS FORWARD TO RESULTS This sheet calculates loan amortization and interest. There are no inputs on this sheet. Total Investment $1,370,000 Long Term Interest Rate 7.50% Percent Financed 50.00% Loan Amount $685,000 Loan Term 10 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Beginning Balance $685,000 $636,580 $584,529 $528,574 $468,422 $403,759 $334,246 Interest Rate 7.50% 7.50% 7.50% 7.50% 7.50% 7.50% 7.50% Interest $51,375 $47,744 $43,840 $39,643 $35,132 $30,282 $25,068 Annual Payment $99,795 $99,795 $99,795 $99,795 $99,795 $99,795 $99,795 Principal $48,420 $52,051 $55,955 $60,152 $64,663 $69,513 $74,726 Ending Balance $636,580 $584,529 $528,574 $468,422 $403,759 $334,246 $259,519 Working Capital $1,296,939 Short Term Interest Rate 6.00% Interest Amount $77,816 Total Interest Expense $129,191 $125,560 $121,656 $117,459 $112,948 $108,098 $102,885Year 8 Year 9 Year 10 $259,519 $179,188 $92,832 7.50% 7.50% 7.50% $19,464 $13,439 $6,962 $99,795 $99,795 $99,795 $80,331 $86,356 $92,832 $179,188 $92,832 $0 $97,280 $91,255 $84,779This sheet allows you to input salaries and overtime assumptions for various positions. Benefit calculations are based on the percentage you entered on the "Input" sheet. Occupation Salary Benefits Overtime% Overtime Total Manager $45,000 $13,500 5% 2250 $60,750 Processing/2 $38,400 $11,520 5% 1920 $51,840 Harvesting/12 $249,600 $74,880 5% 12480 $336,960 N/A $0 $0 5% 0 $0 Total Personnel $333,000 $99,900 $16,650 $449,550 Costs MENU BACK TO INPUTS FORWARD TO RESULTSThis Sheet summarizes expenses. The only input is for "supplies and miscellaneous" expenses. Labor Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Salaries $333,000 $336,330 $339,693 $343,090 $346,521 $349,986 Benefits $99,900 $100,899 $101,908 $102,927 $103,956 $104,996 Overtime $16,650 $16,817 $16,985 $17,155 $17,326 $17,499 Total Labor $449,550 $454,046 $458,586 $463,172 $467,804 $472,482 Production Expenses $799,592 $807,588 $815,664 $823,820 $832,058 Utilities 146,424.00 $ $147,888 $149,367 $150,861 $152,369 Electricity 101,157.60 $ $102,169 $103,191 $104,223 $105,265 Natural Gas 39,866.40 $ $40,265 $40,668 $41,074 $41,485 Total Variable $1,400,061 $1,414,062 $1,428,203 $1,442,485 $1,456,909 Fixed Maintenance 127,000 $ $128,270 $129,553 $130,848 $132,157 Insurance 12,700 $ $12,827 $12,955 $13,085 $13,216 Property Tax 13,700 $ $13,837 $13,975 $14,115 $14,256 Depreciation $135,039 $224,328 $161,659 $117,342 $85,615 Interest $129,191 $125,560 $121,656 $117,459 $112,948 Total Fixed $417,630 $504,822 $439,798 $392,849 $358,191 Other Supplies $0 $0 $0 $0 $0 Miscellaneous $0 $0 $0 $0 $0 Total Other $0 $0 $0 $0 $0 Total Expenses $1,817,692 $1,918,884 $1,868,001 $1,835,334 $1,815,101 MENU BACK TO INPUTS FORWARD TO RESULTSYear 6 Year 7 Year 8 Year 9 Year 10 $353,486 $357,021 $360,591 $364,197 $367,839 $106,046 $107,106 $108,177 $109,259 $110,352 $17,674 $17,851 $18,030 $18,210 $18,392 $477,206 $481,978 $486,798 $491,666 $496,583 $840,379 $848,783 $857,271 $865,843 $874,502 $153,893 $155,432 $156,986 $158,556 $160,142 $106,318 $107,381 $108,455 $109,539 $110,635 $41,900 $42,319 $42,742 $43,170 $43,601 $1,471,479 $1,486,193 $1,501,055 $1,516,066 $1,531,226 $133,478 $134,813 $136,161 $137,523 $138,898 $13,348 $13,481 $13,616 $13,752 $13,890 $14,399 $14,543 $14,688 $14,835 $14,983 $85,699 $46,416 $46,416 $8,974 $8,974 $108,098 $102,885 $97,280 $91,255 $84,779 $355,022 $312,138 $308,162 $266,340 $261,524 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $1,826,500 $1,798,331 $1,809,217 $1,782,406 $1,792,751 0.00% 0.00%This sheet summaries income, expenses and net profit. There are no inputs on this sheet Gross Sales Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Pellets $0 $1,898,400 $1,917,384 $1,936,558 $1,955,923 $1,975,483 $1,995,237 Meal $0 $0 $0 $0 $0 $0 $0 Total $0 $1,898,400 $1,917,384 $1,936,558 $1,955,923 $1,975,483 $1,995,237 Expenses Variable $0 $1,400,061 $1,414,062 $1,428,203 $1,442,485 $1,456,909 $1,471,479 Fixed $0 $417,630 $504,822 $439,798 $392,849 $358,191 $355,022 Other $0 $0 $0 $0 $0 $0 $0 Total Expenses $0 $1,817,692 $1,918,884 $1,868,001 $1,835,334 $1,815,101 $1,826,500 Before Tax Profit $0 $80,708 ($1,500) $68,557 $120,590 $160,382 $168,737 Tax -$ 48,425 $ (900) $ 41,134 $ 72,354 $ 96,229 $ 101,242 $ After Tax Profit $0 $32,283 ($600) $27,423 $48,236 $64,153 $67,495 Estimate of Cash Flows Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 After Tax Profits -$ 32,283 $ (600) $ 27,423 $ 48,236 $ 64,153 $ 67,495 $ Depreciation -$ 135,039 $ 224,328 $ 161,659 $ 117,342 $ 85,615 $ 85,699 $ Principle -$ 48,420 $ 52,051 $ 55,955 $ 60,152 $ 64,663 $ 69,513 $ Cash Flow -$ 118,902 $ 171,677 $ 133,127 $ 105,426 $ 85,104 $ 83,680 $ (does not consider increase or decrease in working capital loan) MENU BACK TO INPUTS FORWARD TO RESULTSYear 7 Year 8 Year 9 Year 10 $2,015,190 $2,035,342 $2,055,695 $2,076,252 $0 $0 $0 $0 $2,015,190 $2,035,342 $2,055,695 $2,076,252 $1,486,193 $1,501,055 $1,516,066 $1,531,226 $312,138 $308,162 $266,340 $261,524 $0 $0 $0 $0 $1,798,331 $1,809,217 $1,782,406 $1,792,751 $216,859 $226,125 $273,289 $283,501 130,115 $ 135,675 $ 163,974 $ 170,101 $ $86,743 $90,450 $109,316 $113,400 Year 7 Year 8 Year 9 Year 10 86,743 $ 90,450 $ 109,316 $ 113,400 $ 46,416 $ 46,416 $ 8,974 $ 8,974 $ 74,726 $ 80,331 $ 86,356 $ 92,832 $ 58,433 $ 56,535 $ 31,934 $ 29,542 $ MENU BACK TO INPUTS FORWARD TO RESULTS This sheet calculates depreciation. You enter descriptions and values for buildings, equipment and other property. Depreciation Buildings 39 year Straight Line Special Purpose Buildings 10 year Straight Line Equipment and Heavy Rolling Stock 7 Yr MACRS with half year convention Light Trucks and Vehicles 5 Yr MACRS with half year convention Buildings Description Value Salvage #1 Plant 400,000.00 $ 50,000.00 $ #2 -$ #3 -$ #4 -$ #5 -$ Total Buildings 400,000.00 $ 50,000.00 $ Equipment and Heavy Rolling Stock Description Value #1 Hay Processing Equip 270,000.00 $ #2 Processing Equipment 469,500.00 $ #3 Installation 80,000.00 $ #4 Miscellaneous 20,000.00 $ #5 -$ Total Equip and Heavy Rolling Stock 839,500.00 $ Total Plant Property and Equipment 1,270,000.00 $ Land 100,000.00 $ Total Land, Plant Property and Equipment 1,370,000.00 $ Annual Total Depreciation Year 1 2 3 4 Buildings $8,974 $8,974 $8,974 $8,974 Special Purpose Buildings $0 $0 $0 $0 Equipment and Heavy Rolling Stock $119,965 $205,594 $146,829 $104,854 Light Truck and Vehicles $6,100 $9,760 $5,856 $3,514 Total Depreciation $135,039 $224,328 $161,659 $117,342 Buildings 39 year Straight Line Special Purpose Buildings 10 year with percentage from table Equipment and Heavy Rolling Stock 7 year with percentage from table Light Trucks and Vehicles 5 year with percentage from tableBuildings Cost $400,000 Life 39 Salvage 50,000.00 $ Period 39 Depreciation per year for 39 years $8,974 Special Purpose Buildings Cost $0 Life 10 Year Depreciation Rate 1 $0 10% 2 $0 14% 3 $0 14% 4 $0 14% 5 $0 14% 6 $0 14% 7 $0 14% 8 $0 14% 9 $0 14% 10 $0 14% Equipment and Heavy Rolling Stock Year Depreciation Rate 1 $119,965 14.29% 2 $205,594 24.49% 3 $146,829 17.49% 4 $104,854 12.49% 5 $74,967 8.93% 6 $74,883 8.92% 7 $74,967 8.93% 8 $37,442 4.46% Light Trucks and Vehicles Year Depreciation Rate 1 $6,100 20.00% 2 $9,760 32.00% 3 $5,856 19.20% 4 $3,514 11.52% 5 $3,514 11.52% 6 $1,757 5.76%equipment and other property. Special Purpose Buildings Description Value #1 -$ #2 -$ #3 -$ #4 -$ #5 -$ Total Special Purpose Building -$ Light Trucks and Vehicles Description Value #1 Forklift 30,500.00 $ #2 -$ #3 -$ #4 -$ #5 -$ Total Light Trucks and Vehicles 30,500.00 $ 5 6 7 8 9 10 $8,974 $8,974 $8,974 $8,974 $8,974 $8,974 $0 $0 $0 $0 $0 $0 $74,883 $74,967 $37,442 $37,442 $1,757 $1,757 $85,615 $85,699 $46,416 $46,416 $8,974 $8,9740.0613MENU BACK TO INPUTS BACK TO INTRODUCTION This sheet summaries the feasibility of the project. It provides net present value, benefit cost ratio and internal rate The only input is the discount rate. Page down to enter the B44results of your sensitivity analysis+B7 Discount Rate 12.00% Year 0 1 2 3 Gross Margin 1,898,400 $ 1,917,384 $ 1,936,558 $ Discount Factor 1.000000 0.892857 0.797194 0.711780 PV of Income 19,861,464 $ -$ 1,695,000 $ 1,528,527 $ 1,378,404 $ Total Expense 1,370,000 $ 1,817,692 $ 1,817,692 $ 1,918,884 $ Less Depreciation and Interest 264,230 $ 349,888 $ 283,315 $ Cash Expenses 1,370,000 $ 1,553,461 $ 1,467,804 $ 1,635,569 $ Discount Factor 1.000000 0.892857 0.797194 0.711780 PV of Expenses 17,647,583 $ 1,370,000 $ 1,387,019 $ 1,170,124 $ 1,164,166 $ Benefits Less Costs (1,370,000) $ 344,939 $ 449,580 $ 300,989 $ PV Benefits Less PV Costs (1,370,000) $ 307,981 $ 358,403 $ 214,238 $ Total PV of Income 11,120,158.26 $ Total PV of Expenses 10,466,325.84 $ Net Present Value 653,832.42 $ Internal Rate of Return 22.85% PV Benefit/PV Cost Ratio 1.0625 Payback Period *Payback Period only displayed if less than 10 years Return on Assets 0.00% 2.36% -0.04% 2.00% (after tax profits/PP&E investment) NPV IRR B/C Natural Gas Price $20 MCF: Approximate Break-even Point Months 2.50 $ 692,426.36 $ 23.44% 1.0664 Forag/Hay 3.00 $ 653,832.42 $ 22.85% 1.0625 6 Forage + 0 hay 3.50 $ 615,238.48 $ 22.25% 1.0586 6 to 1 4.00 $ 576,644.54 $ 21.64% 1.0547 6 to 2 Hay Cost/Ton $85/ton: Approximate Break-even Point 6 to 3 30.00 $ 952,548.93 $ 27.38% 1.0937 6 to 4 40.00 $ 653,832.42 $ 22.85% 1.0625 6 to 5 50.00 $ 355,115.91 $ 18.09% 1.0330 5.6 to 5.7 60.00 $ 56,399.40 $ 13.01% 1.0051 5 to 6 Forage Cost/Ton $64/ton: Approximate Break-even Point * These would not be exact 30.00 $ 1,168,418.28 $ 30.54% 1.1174 40.00 $ 653,832.42 $ 22.85% 1.0625 Discount rate50.00 $ 139,246.56 $ 14.46% 1.0127 8% 60.00 $ (375,339.30) $ 4.73% 0.9673 10% Alfalfa Pellet Price $98.83: Approximate Break-even Point 12% 95.00 $ (405,230.27) $ 4.09% 0.9613 14% 100.00 $ 124,301.07 $ 14.20% 1.0119 16% 105.00 $ 653,832.42 $ 22.85% 1.0625 *Break-even Discount Rate: 110.00 $ 1,183,363.76 $ 30.75% 1.1131 115.00 $ 1,712,895.11 $ 38.23% 1.1637 Baseline Break-even Points calculated with all other paramaters $3.00 Natural Gas Cost $40 Raw Materials/Hay $105 Alfalfa Pellet Price 5.6 Months of Operation Using Forage 5.7 Months of Operation Using Hay 100% Pellet to Meal Ratio 12% Discount Rate ****cost ratio and internal rate of return 4 5 6 7 8 9 10 1,955,923 $ 1,975,483 $ 1,995,237 $ 2,015,190 $ 2,035,342 $ 2,055,695 $ 2,076,252 $ 0.635518 0.567427 0.506631 0.452349 0.403883 0.360610 0.321973 1,243,025 $ 1,120,942 $ 1,010,849 $ 911,570 $ 822,040 $ 741,304 $ 668,498 $ 1,868,001 $ 1,835,334 $ 1,815,101 $ 1,826,500 $ 1,798,331 $ 1,809,217 $ 1,782,406 $ 234,801 $ 198,563 $ 193,797 $ 149,301 $ 143,696 $ 100,230 $ 93,753 $ 1,633,200 $ 1,636,771 $ 1,621,304 $ 1,677,199 $ 1,654,635 $ 1,708,987 $ 1,688,653 $ 0.635518 0.567427 0.506631 0.452349 0.403883 0.360610 0.321973 1,037,928 $ 928,748 $ 821,403 $ 758,680 $ 668,279 $ 616,278 $ 543,701 $ 322,723 $ 338,712 $ 373,934 $ 337,990 $ 380,707 $ 346,708 $ 387,599 $ 205,097 $ 192,194 $ 189,446 $ 152,890 $ 153,761 $ 125,026 $ 124,797 $ 3.52% 4.68% 4.93% 6.33% 6.60% 7.98% 8.28% NPV IRR B/C (2,498,154) $ N/A 0.7027 (1,948,091) $ N/A 0.7795 (1,398,029) $ N/A 0.8492 (847,966) $ -7.13% 0.9126 (297,903) $ 6.32% 0.9706 252,160 $ 16.38% 1.0238 653,832 $ 22.85% 1.0625 768,400 $ 24.61% 1.0764 These would not be exact because of weekends NPV B/C$1,033,011 1.0846 $830,605 1.0736 $653,832 1.0625 $498,729 1.0511 $362,030 1.0397 Break-even Discount Rate: 22.85% as reflected by IRR with all other paramaters at base line levels