Feasibility Assessment Template

Feasibility Assessment 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 project 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 For a Guide to Using the Feasibility Template Click On the Icon Below MENU FORWARD TO INPUTS Template GuideState University information in the input cells. particular situation by turning off the protection. under another name and retain the original for reference. On the Icon BelowINPUT CAPITAL STRUCTURE AND INPUT PRODUCTS, INITIAL VOLUME, MARGIN PER EXPENSE INFORMATION ANTICIPATED SALES GROWTH RATE Percent Financed 50.00% Product Name Fertilizer Long Term Interest Rate 8.00% Units tons Loan Term 10 Initial Volume 20,000 Total Plant Property & Equip $1,245,000 Sales growth 1% Loan Amount $622,500 Price/unit 230.00 $ Working Capital $112,000 Short Term Interest Rate 7.00% Product Name C Units lbs Equity Information Initial Volume 0 Common stock $522,500 Sales growth 0% Tradeable common stock (1=yes) 0 Price/unit -$ Revolving period (years) 7 Dividend rate on common 8.0% Percent Member Business Prefered Stock 100,000 $ VARIABLE COST OF PRODUCTION PER UNIT Dividend Rate on Preferred Stock 12.0% Product Unit Fertilizer tons Feed tons Tax Information C lbs Property Tax as % of Prop and Plant 0.50% D lbs Income Tax Rate 50.00% There are also inputs on the "Personnel Expense", "Depreciation", Payroll Information MENU % of Payroll Tax to Salaries 5.00% % of Retirement Tax to Salaries 15.00% % of Employee INS Tax to Salaries 10.00% Benefits as % of Salaries 30.00% Wage Inflation 1.00% Utilities Electricity/month 1,000 $ Water/month 250 $ Gas/month 1,000 $ Telephone/month 200 $ Total Utilities 2,450 $ Other Expense Inflation Rate 1.00% Maintenance as % of Plant & Equip 3.00% Insurance as % of Plant & Equip 2.00% Discount rate for NPV calculation 12.00% Profit Allocation (all percentages relate to before tax income) Percentage to Unallocated Reserve 5% Percentage to Cash Patronage Refund 25% Percentage to Qualified Stock Patraonage Refund 35% Percentage to Non-Qualifited Stock Patronage Refund 35% FORWARD TO OPERATIONS SUMMARY FORWARD TO RETURN ON INVESTMENT BACK TO INTRODUCTION FORWARD TO PERSONNEL EXPENSE FORWARD TO DEPRECIATION FORWARD TO EXPENSE PROJECTIONINITIAL VOLUME, MARGIN PER UNIT AND GROWTH RATE Product Name Feed Units tons Initial Volume 5,000 Sales growth 1% Price/unit 200.00 $ Product Name D Units lbs Initial Volume 0 Sales growth 0% Price/unit -$ 100% PRODUCTION PER UNIT VC/unit 200.00 $ 180.00 $ -$ -$ Personnel Expense", "Depreciation", & "Expense Projection" sheets OPERATIONS SUMMARY ON INVESTMENT PERSONNEL EXPENSE EXPENSE PROJECTIONDepreciation Return On Investment Quality Percent 0% Discount Rate 0.00%Sales Projections Year 1 Year 2 Year 3 Year 4 Year 5 Fertilizer tons 20,000 20,200 20,402 20,606 20,812 Feed tons 5,000 5,050 5,101 5,152 5,203 C lbs 0 0 0 0 0 D lbs 0 0 0 0 0 25,000 25,250 25,503 25,758 26,015 Gross Sales Projection This sheet summaries the volume and price and sales growth information from the input page. There is no input on this Year 1 Year 2 Year 3 Year 4 Year 5 Fertilizer Total Volume 20,000 20,200 20,402 20,606 20,812 Price/Unit $230.00 $230.00 $230.00 $230.00 $230.00 Gross Sales $4,600,000 $4,646,000 $4,692,460 $4,739,385 $4,786,778 Feed Total Volume 5,000 5,050 5,101 5,152 5,203 Price/Unit $200.000 $200.000 $200.000 $200.000 $200.000 Gross Sales $1,000,000 $1,010,000 $1,020,100 $1,030,301 $1,040,604 CTotal Volume 0 0 0 0 0 Price/Unit $0.00 $0.00 $0.00 $0.00 $0.00 Gross Sales $0 $0 $0 $0 $0 DTotal Volume 0 0 0 0 0 Price/Unit $0.00 $0.00 $0.00 $0.00 $0.00 Gross Sales $0 $0 $0 $0 $0 TOTAL GROSS SALES $5,600,000 $5,656,000 $5,712,560 $5,769,686 $5,827,382 Production Expense Fertilizer 4,000,000 $ 4,040,000 $ 4,080,400 $ 4,121,204 $ 4,162,416 $ Feed 900,000 $ 909,000 $ 918,090 $ 927,271 $ 936,544 $ C -$ -$ -$ -$ -$ D -$ -$ -$ -$ -$ TOTAL VARIABLE EXP. 4,900,000 $ 4,949,000 $ 4,998,490 $ 5,048,475 $ 5,098,960 $ MENU BACK TO INPUTS FORWARD TO RETURN ON INVESTMENT FORWARD TO OPERATIONS SUMMARYYear 6 Year 7 Year 8 Year 9 Year 10 21,020 21,230 21,443 21,657 21,874 5,255 5,308 5,361 5,414 5,468 0 0 0 0 0 0 0 0 0 0 26,275 26,538 26,803 27,071 27,342 input page. There is no input on this page. Year 6 Year 7 Year 8 Year 9 Year 10 21,020 21,230 21,443 21,657 21,874 $230.00 $230.00 $230.00 $230.00 $230.00 $4,834,646 $4,882,993 $4,931,823 $4,981,141 $5,030,952 5,255 5,308 5,361 5,414 5,468 $200.000 $200.000 $200.000 $200.000 $200.000 $1,051,010 $1,061,520 $1,072,135 $1,082,857 $1,093,685 0 0 0 0 0 $0.00 $0.00 $0.00 $0.00 $0.00 $0 $0 $0 $0 $0 0 0 0 0 0 $0.00 $0.00 $0.00 $0.00 $0.00 $0 $0 $0 $0 $0 $5,885,656 $5,944,513 $6,003,958 $6,063,998 $6,124,638 4,204,040 $ 4,246,081 $ 4,288,541 $ 4,331,427 $ ######### 945,909 $ 955,368 $ 964,922 $ 974,571 $ 984,317 $ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ 5,149,949 $ 5,201,449 $ 5,253,463 $ 5,305,998 $ #########This sheet calculates loan amortization and interest. There are no inputs on this sheet. Total Investment $1,245,000 Long Term Interest Rate 8.00% Percent Financed 50.00% Loan Amount $622,500 Loan Term 10 Year 1 Year 2 Year 3 Year 4 Year 5 Beginning Balance $622,500 $579,529 $533,121 $482,999 $428,869 Interest Rate 8.00% 8.00% 8.00% 8.00% 8.00% Interest $49,800 $46,362 $42,650 $38,640 $34,309 Annual Payment $92,771 $92,771 $92,771 $92,771 $92,771 Principal $42,971 $46,409 $50,121 $54,131 $58,461 Ending Balance $579,529 $533,121 $482,999 $428,869 $370,407 Working Capital $112,000 Short Term Interest Rate 7.00% Interest Amount $7,840 Total Interest Expense $57,640 $54,202 $50,490 $46,480 $42,149 MENU BACK TO INPUTS FORWARD TO RETURN ON INVESTMENT FORWARD TO OPERATIONS SUMMARYYear 6 Year 7 Year 8 Year 9 Year 10 $370,407 $307,269 $239,079 $165,435 $85,899 8.00% 8.00% 8.00% 8.00% 8.00% $29,633 $24,582 $19,126 $13,235 $6,872 $92,771 $92,771 $92,771 $92,771 $92,771 $63,138 $68,189 $73,644 $79,536 $85,899 $307,269 $239,079 $165,435 $85,899 $0 $37,473 $32,422 $26,966 $21,075 $14,712This 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 Position #1 $60,000 $18,000 5% 3,900 $ $81,900 Position #2 $40,000 $12,000 5% 2,600 $ $54,600 Position #3 $25,000 $7,500 0% -$ $32,500 Position #4 $25,000 $7,500 0% -$ $32,500 Total Personnel $150,000 $45,000 $6,500 $201,500 Costs MENU BACK TO INPUTS FORWARD TO RETURN ON INVESTMENT FORWARD TO OPERATIONS SUMMARYThis Sheet summaries expenses. The only input is for "supplies and miscellaneous" expenses. Labor Year 0 Year 1 Year 2 Year 3 Year 4 Salaries 150,000 $ 151,500 $ 153,015 $ 154,545 $ Benefits 45,000 $ 45,450 $ 45,905 $ 46,364 $ Overtime 6,500 $ 6,565 $ 6,631 $ 6,697 $ Total Labor -$ 201,500 $ 203,515 $ 205,550 $ 207,606 $ Production Expenses 4,900,000 $ 4,949,000 $ 4,998,490 $ 5,048,475 $ Utilities 29,400 $ 29,694 $ 29,991 $ 30,291 $ Total Variable -$ 5,130,900 $ 5,182,209 $ 5,234,031 $ 5,286,371 $ Fixed Maintenance 37,350 $ 37,724 $ 38,101 $ 38,482 $ Insurance 24,900 $ 25,149 $ 25,400 $ 25,654 $ Property Tax 6,225 $ 6,287 $ 6,350 $ 6,414 $ Depreciation 59,881 $ 83,901 $ 67,281 $ 55,629 $ Interest 57,640 $ 54,202 $ 50,490 $ 46,480 $ Total Fixed -$ 185,996 $ 207,263 $ 187,622 $ 172,659 $ Other Supplies 25,000 $ 10,000 $ 10,100 $ 10,201 $ 10,303 $ Miscellaneous* 40,000 $ 20,000 $ 20,200 $ 20,402 $ 20,606 $ Total Other 65,000 $ 30,000 $ 30,300 $ 30,603 $ 30,909 $ Income Taxes 66,761 $ 66,734 $ 71,078 $ 75,397 $ Total Expenses 65,000 $ 5,413,657 $ 5,486,506 $ 5,523,334 $ 5,565,336 $ * Year 0 miscellaneous expenses may include legal fees, licenses, permits and other organizational expenses. MENU BACK TO INPUTS FORWARD TO RETURN ON INVESTMENT FORWARD TO OPERATIONS SUMMARYmiscellaneous" expenses. Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 156,091 $ 157,652 $ 159,228 $ 160,820 $ 162,429 $ 164,053 $ 46,827 $ 47,295 $ 47,768 $ 48,246 $ 48,729 $ 49,216 $ 6,764 $ 6,832 $ 6,900 $ 6,969 $ 7,039 $ 7,109 $ 209,682 $ 211,779 $ 213,896 $ 216,035 $ 218,196 $ 220,378 $ 5,098,960 $ 5,149,949 $ 5,201,449 $ 5,253,463 $ 5,305,998 $ 5,359,058 $ 30,594 $ 30,900 $ 31,209 $ 31,521 $ 31,836 $ 32,154 $ 5,339,235 $ 5,392,627 $ 5,446,554 $ 5,501,019 $ 5,556,029 $ 5,611,590 $ 38,867 $ 39,255 $ 39,648 $ 40,044 $ 40,445 $ 40,849 $ 25,911 $ 26,170 $ 26,432 $ 26,696 $ 26,963 $ 27,233 $ 6,478 $ 6,543 $ 6,608 $ 6,674 $ 6,741 $ 6,808 $ 48,153 $ 47,268 $ 46,425 $ 37,038 $ 27,672 $ 27,672 $ 42,149 $ 37,473 $ 32,422 $ 26,966 $ 21,075 $ 14,712 $ 161,558 $ 156,708 $ 151,534 $ 137,419 $ 122,895 $ 117,274 $ 10,406 $ 10,510 $ 10,615 $ 10,721 $ 10,829 $ 10,937 $ 20,812 $ 21,020 $ 21,230 $ 21,443 $ 21,657 $ 21,874 $ 31,218 $ 31,530 $ 31,846 $ 32,164 $ 32,486 $ 32,811 $ 78,825 $ 80,954 $ 83,057 $ 39,550 $ 45,963 $ 44,476 $ 5,610,836 $ 5,661,820 $ 5,712,990 $ 5,710,152 $ 5,757,374 $ 5,806,150 $ other organizational expenses. 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 Fertilizer $0 $4,600,000 $4,646,000 $4,692,460 $4,739,385 Feed $0 $1,000,000 $1,010,000 $1,020,100 $1,030,301 C $0 $0 $0 $0 $0 D $0 $0 $0 $0 $0 Total $0 $5,600,000 $5,656,000 $5,712,560 $5,769,686 Expenses Variable $0 $5,130,900 $5,182,209 $5,234,031 $5,286,371 Fixed $0 $185,996 $207,263 $187,622 $172,659 Other $65,000 $30,000 $30,300 $30,603 $30,909 Total Expenses $65,000 $5,346,896 $5,419,772 $5,452,256 $5,489,939 EBIT ($65,000) $256,944 $225,469 $247,403 $261,399 Profit before Patronage ($65,000) $199,304 $171,267 $196,913 $214,919 Cash Patronage Refund 49,826 $ 42,817 $ 49,228 $ 53,730 $ Qualified Patronage Refund $69,756 $59,943 $68,920 $75,222 Non-Qualified Redeemed $0 $0 $0 $0 Before Tax Income $133,522 $133,468 $142,156 $150,795 Tax -$ 66,761 $ 66,734 $ 71,078 $ 75,397 $ After Tax Profit ($65,000) $66,761 $66,734 $71,078 $75,397 Estimate of Cash Flows Year 0 Year 1 Year 2 Year 3 Year 4 After Tax Profits (65,000.00) $ 66,761 $ 66,734 $ 71,078 $ 75,397 $ Depreciation -$ 59,881 $ 83,901 $ 67,281 $ 55,629 $ Principle -$ 42,971 $ 46,409 $ 50,121 $ 54,131 $ Additional Asset Purchased -$ -$ -$ -$ Common stock div $41,800 $52,961 $51,391 $52,827 Prefered dividend $12,000 $12,000 $12,000 $12,000 Gross Cash Flow from Operations $196,398 $210,578 $207,279 $206,248 Common stock redemption -$ -$ -$ -$ Qualified Redemption -$ -$ -$ -$ Non-Qualified Redemption -$ -$ -$ -$ Cash Flow (65,000) $ 99,627 $ 99,209 $ 93,766 $ 87,290 $ Cumulative Cash Flow 34,627 $ 133,836 $ 227,602 $ 314,892 $ (does not consider increases or decreases in working capital loan) MENU BACK TO INPUTS FORWARD TO RETURN ON INVESTMENTYear 5 Year 6 Year 7 Year 8 Year 9 Year 10 $4,786,778 $4,834,646 $4,882,993 $4,931,823 $4,981,141 $5,030,952 $1,040,604 $1,051,010 $1,061,520 $1,072,135 $1,082,857 $1,093,685 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $5,827,382 $5,885,656 $5,944,513 $6,003,958 $6,063,998 $6,124,638 $5,339,235 $5,392,627 $5,446,554 $5,501,019 $5,556,029 $5,611,590 $161,558 $156,708 $151,534 $137,419 $122,895 $117,274 $31,218 $31,530 $31,846 $32,164 $32,486 $32,811 $5,532,011 $5,580,866 $5,629,933 $5,670,602 $5,711,410 $5,761,674 $271,686 $275,609 $279,865 $334,466 $355,603 $356,533 $229,536 $238,136 $247,444 $307,499 $334,528 $341,821 57,384 $ 59,534 $ 61,861 $ 76,875 $ 83,632 $ 85,455 $ $80,338 $83,348 $86,605 $107,625 $117,085 $119,637 $0 $0 $0 $69,756 $59,943 $68,920 $157,650 $161,909 $166,113 $79,100 $91,927 $88,951 78,825 $ 80,954 $ 83,057 $ 39,550 $ 45,963 $ 44,476 $ $78,825 $80,954 $83,057 $39,550 $45,963 $44,476 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 78,825 $ 80,954 $ 83,057 $ 39,550 $ 45,963 $ 44,476 $ 48,153 $ 47,268 $ 46,425 $ 37,038 $ 27,672 $ 27,672 $ 58,461 $ 63,138 $ 68,189 $ 73,644 $ 79,536 $ 85,899 $ -$ -$ -$ -$ -$ -$ $53,835 $54,654 $55,136 $13,857 $6,059 $9,143 $12,000 $12,000 $12,000 $12,000 $12,000 $12,000 $207,315 $211,570 $216,087 $184,213 $190,720 $191,785 -$ -$ 522,500 $ -$ -$ -$ -$ -$ -$ 69,756 $ 59,943 $ 68,920 $ -$ -$ -$ 69,756 $ 59,943 $ 68,920 $ 83,019 $ 81,777 $ (441,738) $ 14,955 $ 33,182 $ 15,824 $ 397,911 $ 479,688 $ 37,950 $ 52,905 $ 86,086 $ 101,910 $ Average EBIT $286,498This Sheet Summaries The Changes in Owner's Equity. There are no inputs on this page Year 0 Year 1 Year 2 Year 3 Common Stock 522,500 $ 522,500 $ 522,500 $ 522,500 $ Qualified Stock Credits -$ 69,756 $ 59,943 $ 68,920 $ Non Qualified Stock Credits -$ 69,756 $ 59,943 $ 68,920 $ Preferred Stock 100,000 $ 100,000 $ 100,000 $ 100,000 $ Unallocated Equity $9,965 $18,529 $28,374 Total Members Equity 622,500 $ 771,978 $ 760,916 $ 788,713 $ Equity Calculations Common Stock $522,500 Common Stock Redeemed 0 0 0 Total Common Stock $522,500 $522,500 $522,500 $522,500 Qualified Stock Credits Issued $69,756 $59,943 $68,920 Qualified Stock Credits Redeemed 1 yr rev -$ -$ Qualified Stock Credits Redeemed 2 yr rev -$ Qualified Stock Credits Redeemed 3 yr rev Qualified Stock Credits Redeemed 4 yr rev Qualified Stock Credits Redeemed 5 yr rev Qualified Stock Credits Redeemed 6 yr rev Qualified Stock Credits Redeemed 7 yr rev Qualified Stock Credits Redeemed 8 yr rev Qualified Stock Credits Redeemed 9 yr rev Qualified Stock Credits Redeemed 10 yr rev Total Qualified Redeemed -$ -$ -$ Total Qualified Stock Credits $69,756 $59,943 $68,920 Non-Qualified Stock Credits Issued $69,756 $59,943 $68,920 Non-Qualified Stock Credits Redeemed 1 yr rev -$ -$ Non-Qualified Stock Credits Redeemed 2 yr rev -$ Non-Qualified Stock Credits Redeemed 3 yr rev Non-Qualified Stock Credits Redeemed 4 yr rev Non-Qualified Stock Credits Redeemed 5 yr rev Non-Qualified Stock Credits Redeemed 6 yr rev Non-Qualified Stock Credits Redeemed 7 yr rev Non-Qualified Stock Credits Redeemed 8 yr rev Non-Qualified Stock Credits Redeemed 9 yr rev Non-Qualified Stock Credits Redeemed 10 yr rev Total Non-Qualified Redeemed -$ -$ -$ Total Non-Qualified Stock Credits $69,756 $59,943 $68,920Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 522,500 $ 522,500 $ 522,500 $ -$ -$ -$ -$ 75,222 $ 80,338 $ 83,348 $ 86,605 $ 37,868 $ 57,141 $ 50,718 $ 75,222 $ 80,338 $ 83,348 $ 86,605 $ 37,868 $ 57,141 $ 50,718 $ 100,000 $ 100,000 $ 100,000 $ 100,000 $ 100,000 $ 100,000 $ 100,000 $ $39,120 $50,597 $62,504 $74,876 $90,251 $106,977 $124,068 812,064 $ 833,772 $ 851,699 $ 348,087 $ 265,987 $ 321,260 $ 325,504 $ 0 0 0 522500 0 0 0 $522,500 $522,500 $522,500 $0 $0 $0 $0 $75,222 $80,338 $83,348 $86,605 $107,625 $117,085 $119,637 -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ 69,756 $ 59,943 $ 68,920 $ -$ -$ -$ -$ -$ -$ -$ 69,756 $ 59,943 $ 68,920 $ $75,222 $80,338 $83,348 $86,605 $37,868 $57,141 $50,718 $75,222 $80,338 $83,348 $86,605 $107,625 $117,085 $119,637 -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ 69,756 $ 59,943 $ 68,920 $ -$ -$ -$ -$ -$ -$ -$ 69,756 $ 59,943 $ 68,920 $ $75,222 $80,338 $83,348 $86,605 $37,868 $57,141 $50,71838%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,000,000 $ 30,000 $ ` Total Buildings 1,000,000 $ 30,000 $ Equipment and Heavy Rolling Stock Description Value 50,000 $ 50,000 $ 10,000 $ 50,000 $ 50,000 $ Total Equip and Heavy Rolling Stock 210,000 $ Total Plant Property and Equipment 1,245,000 $ Land -$ Total Land, Plant Property and Equipment 1,245,000 $ Additional Purchases 1 2 3 4 Annual Total Depreciation Year 1 2 3 4 Buildings $24,872 $24,872 $24,872 $24,872 Special Purpose Buildings $2,000 $2,800 $2,800 $2,800 Equipment and Heavy Rolling Stock $30,009 $51,429 $36,729 $26,229 Light Truck and Vehicles $3,000 $4,800 $2,880 $1,728 Additional Depreciation 0 0 0 0 Total Depreciation $59,881 $83,901 $67,281 $55,629 Buildings 39 year Straight Line Special Purpose Buildings 10 year with percentage from table Conveying and metering equipment Feed Trucks #5 Feed mill Pellet mill Load out equipment Processing Plant #2 #3 #4 MENU BACK TO INPUTS FORWARD TO RETURN ON INVESTMENT BACK TO OPERATION SUMMARYEquipment and Heavy Rolling Stock 7 year with percentage from table Light Trucks and Vehicles 5 year with percentage from table Buildings Cost $1,000,000 Life 39 Salvage $30,000 Period 39 Depreciation per year for 39 years $24,872 Special Purpose Buildings Cost $20,000 Life 10 Year Depreciation Rate 1 $2,000 10% 2 $2,800 14% 3 $2,800 14% 4 $2,800 14% 5 $2,800 14% 6 $2,800 14% 7 $2,800 14% 8 $2,800 14% 9 $2,800 14% 10 $2,800 14% Equipment and Heavy Rolling Stock Year Depreciation Rate 1 $30,009 14.29% 2 $51,429 24.49% 3 $36,729 17.49% 4 $26,229 12.49% 5 $18,753 8.93% 6 $18,732 8.92% 7 $18,753 8.93% 8 $9,366 4.46% Light Trucks and Vehicles Year Depreciation Rate 1 $3,000 20.00% 2 $4,800 32.00% 3 $2,880 19.20% 4 $1,728 11.52% 5 $1,728 11.52% 6 $864 5.76%equipment and other property. Special Purpose Buildings Description Value10,000 $ 10,000 $ Total Special Purpose Building 20,000 $ Light Trucks and Vehicles Description Value5,000 $ 5,000 $ 5,000 $ Total Light Trucks and Vehicles 15,000 $ 5 6 7 8 9 10 5 6 7 8 9 10 $24,872 $24,872 $24,872 $24,872 $24,872 $24,872 $2,800 $2,800 $2,800 $2,800 $2,800 $2,800 $18,753 $18,732 $18,753 $9,366 $1,728 $864 0 0 0 0 0 0 $48,153 $47,268 $46,425 $37,038 $27,672 $27,672 Outbuilding Storage shed #3 #4 #5 Delivery truck Company car Forklifts0.0613This sheet summaries the feasibility of the project. It provides net present value, benefit cost ratio and internal rate of The only input is the discount rate. Discount Rate 12.00% Year 0 1 2 3 Gross Margin $5,600,000 $5,656,000 $5,712,560 Discount Factor 1 0.892857143 0.797193878 0.711780248 PV of Income $0 $5,000,000 $4,508,929 $4,066,087 Total Expense $65,000 $5,413,657 $5,413,657 $5,486,506 Less Depreciation and Term Interest $109,681 $130,263 $109,930 Cash Expenses $1,310,000 $5,303,976 $5,283,394 $5,376,575 Discount Factor 1 0.892857143 0.797193878 0.711780248 PV of Expenses $1,310,000 $4,735,693 $4,211,889 $3,826,940 Benefits Less Costs ($1,310,000) $296,024 $372,606 $335,985 PV Benefits Less PV Costs ($1,310,000) $264,307 $297,040 $239,147 Total PV of Income $32,802,827 Total PV of Expenses $32,150,267 Net Present Value $652,560 Internal Rate of Return 22.80% PV Benefit/PV Cost Ratio 1.02 Return on Assets -0.90% 15.86% 13.49% 15.31% (after tax income/total PPE investment) Average Return on Assets 16.61% Payback Period (years) 6 (payback period only displayed if less than 10 years) MENU BACK TO INPUTS BACK TO OPERATION SUMMARYbenefit cost ratio and internal rate of return 4 5 6 7 8 9 10 $5,769,686 $5,827,382 $5,885,656 $5,944,513 $6,003,958 $6,063,998 $6,124,638 0.635518078 0.567426856 0.506631121 0.452349215 0.403883228 0.360610025 0.321973237 $3,666,740 $3,306,613 $2,981,857 $2,688,996 $2,424,898 $2,186,738 $1,971,969 $5,523,334 $5,565,336 $5,610,836 $5,661,820 $5,712,990 $5,710,152 $5,757,374 $94,269 $82,462 $76,900 $71,006 $56,164 $40,907 $34,544 $5,429,065 $5,482,874 $5,533,935 $5,590,814 $5,656,826 $5,669,245 $5,722,830 0.635518078 0.567426856 0.506631121 0.452349215 0.403883228 0.360610025 0.321973237 $3,450,269 $3,111,130 $2,803,664 $2,529,000 $2,284,697 $2,044,387 $1,842,598.11 $340,620 $344,508 $351,721 $353,699 $347,132 $394,752 $401,807 $216,470 $195,483 $178,193 $159,995 $140,201 $142,352 $129,371 16.49% 17.46% 18.02% 15.28% 22.97% 24.88% 23.88% 6 7 8 9 10Analysis of Member Return This Sheet Summaries the Member's Return. The only inputs are the tax rates Member's Income Tax Rate Initial Investment $522,500 Capital Gains Tax Rate Year 1 Year 2 Year 3 Year 4 Year 5 Cash Patronage 49,826 $ 42,817 $ 49,228 $ 53,730 $ 57,384 $ Stock Dividends 41,800 $ 52,961 $ 51,391 $ 52,827 $ 53,835 $ Qualified Stock Received 69,756 $ 59,943 $ 68,920 $ 75,222 $ 80,338 $ Non-Qualified Stock Redeemed -$ -$ -$ -$ -$ Qualified Stock Redeemed -$ -$ -$ -$ -$ Sale of Stock Taxable Income 161,383 $ 155,721 $ 169,539 $ 181,779 $ 191,557 $ Taxable Gain on Stock Member's Income Tax 48,415 $ 46,716 $ 50,862 $ 54,534 $ 57,467 $ After Tax Cash Income 43,211 $ 49,061 $ 49,758 $ 52,023 $ 53,752 $ Return on Investment 8% 9% 10% 10% 10% Average ROI for Common 15% Cash flows ($522,500) 49,061 $ 49,758 $ 52,023 $ 53,752 $ Net Present Value: Common ($120,591) Internal Rate of Return:common 6% Preferred Stock Return Dividends 12000 12000 12000 12000 12000 Return on Investment 12% 12% 12% 12% 12% Average ROI Preferred 12%are the tax rates 30% 28% Year 6 Year 7 Year 8 Year 9 Year 10 59,534 $ 61,861 $ 76,875 $ 83,632 $ 85,455 $ 54,654 $ 55,136 $ 13,857 $ 6,059 $ 9,143 $ 83,348 $ 86,605 $ 107,625 $ 117,085 $ 119,637 $ -$ -$ 69,756 $ 59,943 $ 68,920 $ -$ -$ 69,756 $ 59,943 $ 68,920 $ -$ 197,536 $ 203,602 $ 268,113 $ 266,719 $ 283,155 $ -$ 59,261 $ 61,081 $ 80,434 $ 80,016 $ 84,946 $ 54,927 $ 55,916 $ 149,811 $ 129,562 $ 147,491 $ 11% 11% 29% 25% 28% 54,927 $ 55,916 $ 149,811 $ 129,562 $ 147,491 $ 12000 12000 12000 12000 12000 12% 12% 12% 12% 12%