You have recently graduated from the University of North Georgia with a BBA degree, and you have taken a job with a local manufacturing company. Your boss has asked you to analyze a potential new product, and to recommend if the company should produce and sell the product. Specifically, your boss wants you to prepare a spreadsheet that shows the free cash flows the product would generate, and shows what the products net present value and internal rate of return are and what your recommendation is.

Marketing information

Your company already has spent $225,000 to conduct market research about the demand for the product, which indicates the optimal wholesale price for the product would be $15.00 per unit, based on the prices of similar products that competitors sell. The market research also indicates that demand for the product would last for five years. At a price of $15.00 per unit, the market research suggests that sales would be 200,000 units in the first year, and unit sales would increase 6.5% per year over the remaining four years of the products life.

Production information

Your companys production manager estimates manufacturing the product would require a machine that costs $975,000 and falls in the 3-year MACRS depreciation class. The machines expected salvage value in five years is expected to be $250,000. The production manager also estimates the products variable costs, consisting of raw materials and labor, would be $12.50 per unit, and the annual fixed costs excluding depreciation would be $400,000. He states the product could be manufactured in a building your company owns, which has no other use.

Financial information

Your companys stock price is $40.23 per share, the last annual dividend was $1.76 per share, and market analysts who follow your companys stock expect the dividends to grow forever at a rate of 4.0% per year. The companys beta is 1.4 and Treasury bills are paying 1.87% per year. The companys bonds have a par value of $1,000, pay a coupon of 5.5% per year, semiannually, have 15 years to maturity, and are trading at $1,048. The companys treasurer estimates that the new product would require a $300,000 increase in net working capital. She also has told you the companys target capital structure is 60% debt and 40% equity, the companys tax rate is 28%, and she expects the stock market return over the next year will be 6.5%.

using this format

B D E F G H 1 0 1 2 3 4 -$900 -100 2,600 $2.00 2,525 $2.00 2,450 $2.00 $1.046 2,685 $2.00 $1.018 $5,370 2,735 2,000 297 $5,032 $338 $1.221 $4,900 2,992 12 13 Investment Outlays at Time = 0 14 CAPEX = Building and Equipment ANOWC = Additional net operating working 15 capital needed Operating Cash Flows over the Project's 16 Life (Time = 1-4) 17 Unit sales 18 Sales price 19 Variable cost per unit 20 Sales revenues = Units x Price 21 Variable costs = Units X Cost/unit 22 Fixed operating costs except depr'n 23 Depreciation: Accelerated from table below 24 Total operating costs 25 EBIT (or operating income) 26 Taxes on operating income 40% 27 EBIT (1 T) = After-tax project operating income 28 Add back depreciation 29 EBIT (1 -T) + Depreciation 30 Terminal Cash Flows at Time = 4 31 Salvage value (taxed as ordinary income) Tax on salvage value = 0.4 x (SV - BV of 32 equipment at t = 4) 33 After-tax salvage value ANOWC = Recovery of net operating 34 working capital Project free cash flows = EBIT(1 t) + DEP 35 -CAPEX - ANOWC $1.078 $5,200 2,803 2,000 405 $5,208 -$8 2,000 $5,050 2,640 2,000 135 $4,775 $275 110 63 $5,055 -$155 135 -62 -$93 $203 297 $500 -3 -$5 405 $400 $165 135 $300 63 -$30 50 20 30 100 -$1,000 $500 $400 $300 $100 36 37 3 38 39 15% $135 4 7% $63 2 45% $405 2 25% $225 40 41 42 43 3 25% $225 4 25% $225 Depreciation Accelerated 1 Cost: $900 Rate 33% Depreciation $297 Alternative depreciation Straight line 1 Cost: $900 Rate 25% Depreciation $225 Project Evaluation @ WACC = 10% Accelerated Formulas INPV $78.82 =NPV(D43,F35:135)+E35 IRR 14.489% =IRR(E35:135) MIRR 12.106% =MIRR(E35:135,043,D43) Payback 2.33 =G12+(-E35-F35-G35)/H35 44 45 46 Straight line $64.44 13.437% 11.731% 2.60 47 48 B D E F G H 1 0 1 2 3 4 -$900 -100 2,600 $2.00 2,525 $2.00 2,450 $2.00 $1.046 2,685 $2.00 $1.018 $5,370 2,735 2,000 297 $5,032 $338 $1.221 $4,900 2,992 12 13 Investment Outlays at Time = 0 14 CAPEX = Building and Equipment ANOWC = Additional net operating working 15 capital needed Operating Cash Flows over the Project's 16 Life (Time = 1-4) 17 Unit sales 18 Sales price 19 Variable cost per unit 20 Sales revenues = Units x Price 21 Variable costs = Units X Cost/unit 22 Fixed operating costs except depr'n 23 Depreciation: Accelerated from table below 24 Total operating costs 25 EBIT (or operating income) 26 Taxes on operating income 40% 27 EBIT (1 T) = After-tax project operating income 28 Add back depreciation 29 EBIT (1 -T) + Depreciation 30 Terminal Cash Flows at Time = 4 31 Salvage value (taxed as ordinary income) Tax on salvage value = 0.4 x (SV - BV of 32 equipment at t = 4) 33 After-tax salvage value ANOWC = Recovery of net operating 34 working capital Project free cash flows = EBIT(1 t) + DEP 35 -CAPEX - ANOWC $1.078 $5,200 2,803 2,000 405 $5,208 -$8 2,000 $5,050 2,640 2,000 135 $4,775 $275 110 63 $5,055 -$155 135 -62 -$93 $203 297 $500 -3 -$5 405 $400 $165 135 $300 63 -$30 50 20 30 100 -$1,000 $500 $400 $300 $100 36 37 3 38 39 15% $135 4 7% $63 2 45% $405 2 25% $225 40 41 42 43 3 25% $225 4 25% $225 Depreciation Accelerated 1 Cost: $900 Rate 33% Depreciation $297 Alternative depreciation Straight line 1 Cost: $900 Rate 25% Depreciation $225 Project Evaluation @ WACC = 10% Accelerated Formulas INPV $78.82 =NPV(D43,F35:135)+E35 IRR 14.489% =IRR(E35:135) MIRR 12.106% =MIRR(E35:135,043,D43) Payback 2.33 =G12+(-E35-F35-G35)/H35 44 45 46 Straight line $64.44 13.437% 11.731% 2.60 47 48