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 projected 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'Oklahoma State University of a project information in the input cells. particular situation by turning off the projection. under another name and retain the original for reference.INPUT CAPITAL STRUCTURE AND INPUT PRODUCTS, INITIAL VOLUME, MARGIN PER EXPENSE INFORMATION ANTICIPATED SALES GROWTH RATE Percent Financed 60.00% Product Name Bread Long Term Interest Rate 8% Units lbs Loan Term 10 Initial Volume 3,225,000 Total Plant Property & Equip $5,779,500 Sales growth 1% Loan Amount $3,467,700 Price/unit 1.30 $ Working Capital $1,000,000 Short Term Interest Rate 6% Product Name B Units lbs Tax Information Initial Volume 0 Property Tax as % of Prop and Plant 0.50% Sales growth 0% Insurance/Total PPE 2.00% Price/unit -$ Income Tax Rate 50.00% Payroll Information VARIABLE COST OF PRODUCTION PER UNIT % of Payroll Tax to Salaries 1% Product Unit % of Retirement Tax to Salaries 15.00% Inputs $/lb % of Employee INS Tax to Salaries 68.00% Packaging $/lb Benefits as % of Salaries 30.00% Cost #3 $ Wage Inflation 1.00% Cost #4 $ Bread lbs Utilities Electricity/month 10,000 $ Product Unit Water/month 250 $ Cost #1 $ Gas/month 500 $ Cost #2 $ Telephone/month 200 $ Cost #3 $ Total Utilities 10,950 $ Cost #4 $ B lbs Other Expense Inflation Rate 1.00% Maintenance as % of Plant & Equip 3% Insurance as % of Plant & Equip 2% There are also inputs on the "Personnel Expense" and Discount rate for NPV calculation 8%INITIAL VOLUME, MARGIN PER UNIT AND SALES GROWTH RATE Product Name C Units lbs Initial Volume 0 Sales growth 0% Price/unit -$ Product Name D Units lbs Initial Volume 0 Sales growth 0% Price/unit -$ PRODUCTION PER UNIT VC/unit Product Unit VC/unit 0.16 $ Cost #1 $ -$ 0.12 $ Cost #2 $ -$ -$ Cost #3 $ -$ -$ Cost #4 $ -$ 0.28 $ C lbs -$ VC/unit Product Unit VC/unit -$ Cost #1 $ -$ -$ Cost #2 $ -$ -$ Cost #3 $ -$ -$ Cost #4 $ -$ -$ D lbs -$ Personnel Expense" and "Depreciation" sheetsDepreciation Return On Investment Quality Percent 0% Discount Rate 0.00%This sheet summaries the volume and price and sales growth information from the input page. There is no input on this Sales Projections Year 1 Year 2 Year 3 Year 4 Year 5 Bread lbs 3,225,000 3,257,250 3,289,823 3,322,721 3,355,948 B lbs 0 0 0 0 0 C lbs 0 0 0 0 0 D lbs 0 0 0 0 0 3,225,000 3,257,250 3,289,823 3,322,721 3,355,948 Gross Margin Projection Year 1 Year 2 Year 3 Year 4 Year 5 Bread Total Volume 3,225,000 3,257,250 3,289,823 3,322,721 3,355,948 Margin/Unit $1.30 $1.30 $1.30 $1.30 $1.30 Gross Margin/Unit $4,192,500 $4,234,425 $4,276,769 $4,319,537 $4,362,732 BTotal Volume 3,225,000 3,257,250 3,289,823 3,322,721 3,355,948 Margin/Unit $0.000 $0.000 $0.000 $0.000 $0.000 Gross Margin/Unit $0 $0 $0 $0 $0 CTotal Volume 0 0 0 0 0 Margin/Unit $0.00 $0.00 $0.00 $0.00 $0.00 Gross Margin/Unit $0 $0 $0 $0 $0 DTotal Volume 0 0 0 0 0 Margin/Unit $0.00 $0.00 $0.00 $0.00 $0.00 Gross Margin/Unit $0 $0 $0 $0 $0 TOTAL GROSS MARGINS $4,192,500 $4,234,425 $4,276,769 $4,319,537 $4,362,732 Production Expense Bread 906,225 $ 915,287 $ 924,440 $ 933,685 $ 943,021 $ B -$ -$ -$ -$ -$ C -$ -$ -$ -$ -$ D -$ -$ -$ -$ -$ TOTAL VARIABLE EXP. 906,225 $ 915,287 $ 924,440 $ 933,685 $ 943,021 $ input page. There is no input on this page. Year 6 Year 7 Year 8 Year 9 Year 10 3,389,507 3,423,402 3,457,637 3,492,213 3,527,135 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3,389,507 3,423,402 3,457,637 3,492,213 3,527,135 Year 6 Year 7 Year 8 Year 9 Year 10 3,389,507 3,423,402 3,457,637 3,492,213 3,527,135 $1.30 $1.30 $1.30 $1.30 $1.30 $4,406,360 $4,450,423 $4,494,927 $4,539,877 $4,585,276 3,389,507 3,423,402 3,457,637 3,492,213 3,527,135 $0.000 $0.000 $0.000 $0.000 $0.000 $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 0 0 0 0 0 $0.00 $0.00 $0.00 $0.00 $0.00 $0 $0 $0 $0 $0 $4,406,360 $4,450,423 $4,494,927 $4,539,877 $4,585,276 952,452 $ 961,976 $ 971,596 $ 981,312 $ 991,125 $ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ 952,452 $ 961,976 $ 971,596 $ 981,312 $ 991,125 $ This sheet calculates loan amortization and interest. There are no inputs on this sheet. Total Investment $5,779,500 Long Term Interest Rate 8.00% Percent Financed 60.00% Loan Amount $3,467,700 Loan Term 10 Year 1 Year 2 Year 3 Year 4 Year 5 Beginning Balance $3,467,700 $3,228,326 $2,969,803 $2,690,598 $2,389,056 Interest Rate 8.00% 8.00% 8.00% 8.00% 8.00% Interest $277,416 $258,266 $237,584 $215,248 $191,124 Annual Payment $516,790 $516,790 $516,790 $516,790 $516,790 Principal $239,374 $258,523 $279,205 $301,542 $325,665 Ending Balance $3,228,326 $2,969,803 $2,690,598 $2,389,056 $2,063,391 Working Capital $1,000,000 Short Term Interest Rate 6.00% Interest Amount $60,000 Total Interest Expense $337,416 $318,266 $297,584 $275,248 $251,124Year 6 Year 7 Year 8 Year 9 Year 10 $2,063,391 $1,711,673 $1,331,817 $921,573 $478,509 8.00% 8.00% 8.00% 8.00% 8.00% $165,071 $136,934 $106,545 $73,726 $38,281 $516,790 $516,790 $516,790 $516,790 $516,790 $351,718 $379,856 $410,244 $443,064 $478,509 $1,711,673 $1,331,817 $921,573 $478,509 $0 $225,071 $196,934 $166,545 $133,726 $98,281This 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 # Employees Salary Total Salaries Benefits Overtime% General/Administration 4 $153,894 $193,894 $58,168 5% General Manager 1 $100,000 $100,000 $30,000 Administrative Assistant 2 $40,000 $80,000 $24,000 Other 1 $13,894 $13,894 $4,168 Transportation 1 $40,716 $40,716 $12,215 5% Customer Service 1 $55,882 $55,882 $16,764 5% Sales/Marketing 1 $221,958 $221,958 $66,587 5% Operators 80 $2,638,876 $2,638,876 $791,663 5% Bread Processing 60 $32,986 $1,979,157 $593,747 Packaging 20 $32,986 $659,719 $197,916 Sanitation 1 $67,656 $67,656 $20,297 5% Plant Management 1 $30,782 $30,782 $9,234 5% Total Personnel $3,209,763 $1,824,760 Costs Overtime Total 11,730.50 $ $263,793 100,993.65 $ $153,924 35,780.03 $ $108,426 14,480.70 $ $303,026 133,482.88 $ $3,564,022 3,382.81 $ $91,336 1,539.08 $ $41,555 $301,390 $4,526,082This Sheet summaries expenses. The only input is for "supplies and miscellaneous" expenses. Labor Year 0 Year 1 Year 2 Year 3 Year 4 Salaries $3,209,763 $3,241,861 $3,274,279 $3,307,022 $3,340,092 Benefits $1,824,760 $1,843,008 $1,861,438 $1,880,052 $1,898,852 Overtime $301,390 $304,404 $307,448 $310,522 $313,627 Total Labor $5,335,913 $5,389,272 $5,443,164 $5,497,596 $5,552,572 Production Expenses $906,225 $915,287 $924,440 $933,685 Utilities $131,400 $132,714 $132,714 $132,714 Total Variable $6,426,897 $6,491,166 $6,554,750 $6,618,971 Fixed Maintenance 170,385 $ $172,089 $173,810 $175,548 Insurance 113,590 $ $114,726 $115,873 $117,032 Property Tax 115,590 $ $116,746 $117,913 $119,092 Depreciation $951,110 $693,545 $509,570 $378,211 Interest $337,416 $318,266 $297,584 $275,248 Total Fixed $1,688,091 $1,415,371 $1,214,750 $1,065,131 Other Supplies $0 $0 $0 Miscellaneous $0 $0 $0 Total Other $0 $0 $0 $0 Total Expenses $8,114,987 $7,906,537 $7,769,500 $7,684,102Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 $3,373,493 $3,407,228 $3,441,300 $3,475,713 $3,510,471 $3,545,575 $1,917,841 $1,937,019 $1,956,390 $1,975,954 $1,995,713 $2,015,670 $316,764 $319,931 $323,130 $326,362 $329,625 $332,922 $5,608,098 $5,664,179 $5,720,820 $5,778,029 $5,835,809 $5,894,167 $943,021 $952,452 $961,976 $971,596 $981,312 $991,125 $132,714 $132,714 $132,714 $132,714 $132,714 $132,714 $6,683,833 $6,749,344 $6,815,511 $6,882,339 $6,949,835 $7,018,006 $177,303 $179,076 $180,867 $182,676 $184,503 $186,348 $118,202 $119,384 $120,578 $121,784 $123,002 $124,232 $120,283 $121,486 $122,701 $123,928 $125,167 $126,419 $378,579 $214,106 $50,000 $50,000 $50,000 $0 $251,124 $225,071 $196,934 $166,545 $133,726 $98,281 $1,045,493 $859,124 $671,080 $644,933 $616,397 $535,279 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $7,729,326 $7,608,468 $7,486,591 $7,527,272 $7,566,232 $7,553,285 0.00% 0.00%This sheet summaries income, expenses and net profit. There are no inputs on this sheet Gross Margins Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Bread $0 $4,192,500 $4,234,425 $4,276,769 $4,319,537 $4,362,732 B $0 $0 $0 $0 $0 $0 C $0 $0 $0 $0 $0 $0 D $0 $0 $0 $0 $0 $0 Total $0 $4,192,500 $4,234,425 $4,276,769 $4,319,537 $4,362,732 Expenses Variable $0 $6,426,897 $6,491,166 $6,554,750 $6,618,971 $6,683,833 Fixed $0 $1,688,091 $1,415,371 $1,214,750 $1,065,131 $1,045,493 Other $0 $0 $0 $0 $0 $0 Total Expenses $0 $8,114,987 $7,906,537 $7,769,500 $7,684,102 $7,729,326 Before Tax Profit $0 ($3,922,487) ($3,672,112) ($3,492,731) ($3,364,565) ($3,366,594) Tax -$ (1,961,244) $ (1,836,056) $ (1,746,365) $ (1,682,283) $ (1,683,297) $ After Tax Profit $0 ($1,961,244) ($1,836,056) ($1,746,365) ($1,682,283) ($1,683,297)Year 6 Year 7 Year 8 Year 9 Year 10 $4,406,360 $4,450,423 $4,494,927 $4,539,877 $4,585,276 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $4,406,360 $4,450,423 $4,494,927 $4,539,877 $4,585,276 $6,749,344 $6,815,511 $6,882,339 $6,949,835 $7,018,006 $859,124 $671,080 $644,933 $616,397 $535,279 $0 $0 $0 $0 $0 $7,608,468 $7,486,591 $7,527,272 $7,566,232 $7,553,285 ($3,202,108) ($3,036,167) ($3,032,344) ($3,026,356) ($2,968,010) (1,601,054) $ (1,518,084) $ (1,516,172) $ (1,513,178) $ (1,484,005) $ ($1,601,054) ($1,518,084) ($1,516,172) ($1,513,178) ($1,484,005)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 Special Purpose Buildings Description Value #1 Plant 2,000,000 $ #1 #2 #2 #3 #3 #4 #4 #5 #5 Total Buildings 2,000,000 $ Total Special Purpose Building Equipment and Heavy Rolling Stock Light Trucks and Vehicles Description Value #1 Flour System 200,000 $ #1 #2 Bread Line 1,554,500 $ #2 #3 Bun Line 1,460,000 $ #3 #4 Bread and Bun Pans 100,000 $ #4 #5 Miscellaneous 365,000 $ #5 Total Equip and Heavy Rolling Stock 3,679,500 $ Total Light Trucks and Vehicles Total Plant Property and Equipment 5,679,500 $ Land 100,000 $ Total Land, Plant Property and Equipment 5,779,500 $ Annual Total Depreciation Year 1 2 3 4 Buildings $50,000 $50,000 $50,000 $50,000 Special Purpose Buildings $0 $0 $0 $0 Equipment and Heavy Rolling Stock $525,801 $901,110 $643,545 $459,570 Light Truck and Vehicles $0 $0 $0 $0 Total Depreciation $575,801 $951,110 $693,545 $509,570 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 table Buildings Cost $2,000,000 Life 20Salvage $1,000,000 Period 20 Depreciation per year for 39 years $50,000 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 $525,801 14.29% 2 $901,110 24.49% 3 $643,545 17.49% 4 $459,570 12.49% 5 $328,579 8.93% 6 $328,211 8.92% 7 $328,579 8.93% 8 $164,106 4.46% Light Trucks and Vehicles Year Depreciation Rate 1 $0 20.00% 2 $0 32.00% 3 $0 19.20% 4 $0 11.52% 5 $0 11.52% 6 $0 5.76%buildings, equipment and other property. Special Purpose Buildings Description Value Special Purpose Building -$ Trucks and Vehicles Description Value Light Trucks and Vehicles -$ 5 6 7 8 9 10 $50,000 $50,000 $50,000 $50,000 $50,000 $50,000 $0 $0 $0 $0 $0 $0 $328,211 $328,579 $164,106 $0 $378,211 $378,579 $214,106 $50,000 $50,000 $50,000 0.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 $4,192,500 $4,234,425 $4,276,769 Discount Factor 1 0.892857143 0.797193878 0.711780248 PV of Income $43,862,826 $0 $3,743,304 $3,375,658 $3,044,120 Total Expense $5,779,500 $8,114,987 $8,114,987 $7,906,537 Less Depreciation and Interest $1,288,526 $1,011,811 $807,154 Cash Expenses $5,779,500 $6,826,462 $7,103,177 $7,099,383 Discount Factor 1 0.892857143 0.797193878 0.711780248 PV of Expenses $77,712,186 $5,779,500 $6,095,055 $5,662,609 $5,053,201 Benefits Less Costs ($5,779,500) ($2,633,962) ($2,868,752) ($2,822,614) PV Benefits Less PV Costs ($5,779,500) ($2,351,752) ($2,286,951) ($2,009,081) Total PV of Income 24558187.68 Total PV of Expenses $46,127,879 Net Present Value ($21,569,691) Internal Rate of Return #DIV/0! PV Benefit/PV Cost Ratio 0.53cost ratio and internal rate of return 4 5 6 7 8 9 10 $4,319,537 $4,362,732 $4,406,360 $4,450,423 $4,494,927 $4,539,877 $4,585,276 0.635518078 0.567426856 0.506631121 0.452349215 0.403883228 0.360610025 0.32197324 $2,745,144 $2,475,531 $2,232,399 $2,013,145 $1,815,426 $1,637,125 $1,476,336 $7,769,500 $7,684,102 $7,729,326 $7,608,468 $7,486,591 $7,527,272 $7,566,232 $653,459 $629,704 $439,177 $246,934 $216,545 $183,726 $98,281 $7,116,041 $7,054,398 $7,290,149 $7,361,534 $7,270,045 $7,343,546 $7,467,952 0.635518078 0.567426856 0.506631121 0.452349215 0.403883228 0.360610025 0.32197324 $4,522,373 $4,002,855 $3,693,416 $3,329,984 $2,936,249 $2,648,156 ########## ($2,796,504) ($2,691,666) ($2,883,789) ($2,911,111) ($2,775,118) ($2,803,669) ($2,882,676) ($1,777,229) ($1,527,323) ($1,461,017) ($1,316,839) ($1,120,824) ($1,011,031) ($928,145)