Question

There are 7 steps to the question. Please advise what can I do to get all questions answered. I am ok with posting more than 1 questions for this problem as a whole.
Please use excel and demonstrate the excel formule for the same.

Case Study Description A $6M investment is considered by an electric bike manufacturing company to add a new production line for its new product, electric skateboards. The company has commissioned an exploratory study of where to place the new production line and which type of equipment to use. There are three types of machines to choose from for the company to install on the new assembly line. The machines have zero salvage value at the end of 10-year planning horizon. The company must select at least two alternatives from (i) Loan, (ii) Common Stocks, (ii) Preferred Stocks, and (iv) Retained Earnings to obtain the required amount of capital for the investment. Each of these capital sources could provide $3M to support the project. The company is anticipating rapid product penetration and aggressive growth after addition of the new production line. The major question for this case study is to find out if the project is economically justified Part I: Cash Flow Construction Please follow this step to create the cash flow of the project and obtain its economic worth. Step 1: Choose the Capital Sources and Calculate WACC Based on the instruction provided to you, choose the appropriate options to form the capital for the investment, and the calculate WACC Description Options Capital Sources Interest Rate = 8 %, Compounded Semi Annually Payback Method: Plan 3 Dividend $7, Price = $100, Brokerage Fee $1 Dividend = $5, Price = $100, Growth Rate = 4% Loan Preferred Stock 2 Common Stock 3 4 Retained Earning Step 2: Calculate MARR Set MARR equal to rounded WACC (Round up WACC)3% for your further analysis. Step 3: Choose the type of Machine and Calculate Before Tax Cash Flow (BTCF) Choose one type of Machine from the following table and calculate the BTCF Options 2 1 No. of Machines 20 20 20 $250,000 $120 $190 $200,000 $100 $300,000 $150 $210 1800 Hrs First Cost Operating Cost/Hr. Revenue/Hr. $170 Hr./Year 1700 Hrs 1500 Hrs. Useful Life 10 Years 10 Years 10 Years 5 Years Depreciation (MACRS) 7 Years 7 Years Step 4: Economic worth calculation Find (i) PW, (ii) DPBP, (iii) IRR based on BTCF Step 5: Tax rate Choose one set of the tax rates from the following table and find the overall tax rate for your project Options Federal Tax Rate State Tax Rate 8% 20% 1 2 7% 21% 9% 19% Step 6: After tax calculation construct the After-Tax Cash Flow (ATCF) and then calculate the corresponding (i) PW, (ii) AW, and (iii) IRR for the ATCF. Step 7: Choose Inflation and calculate ATCF Choose the appropriate value for the Inflation rate from the following table and calculate (i PW, (ii) ERR and (iii) IRR based on ATCF Options Inflation Rate 1 4% 2 5% 3 6%

Answer 1

C D O E M P Loan Rd Pref Stock Ro Equity Re 9.2% 6 7 Div (D) 100 8 Price (P) Growth (g) 4 % 9 9.200% P - D ( 1+g)/(COE-g) COE 10 11 Capital Structure- Preferred fund 6Mn will as below Preferred Equity can also be considered, though Common equity removes the obligation to pay 12 13 50 % 14 Loan (L) 3 Equity (E) 15 50% 16 28 % From Step 5 data 17 Tax rate (t) 18 748% =Rd " L . (1-t)+Re E WACC Answer- Step 1 19 8 % + 3 % 11.00% Answer Step 2 21 MARR- WACC + 3% 22

24 Answer Step 3 & Step 4 Option 1 25 10 (4.000.000) 20 machines 0200,000 - Total Hrs/Year Number of machines 30,000 30.000 30,000 30,000 30,000 30,000 30.000 30.000 30.000 30.000 (3,000,000) (3,000,000) (3,000,000 ) (3,000,000) (3,000,000) (3,000,000) (3,000,000) @100/hr 5.100.000 170/hr 28 Operating Cost (3,000,000) 5,100,000 (3,000,000) (3,000,000) 29 Revenue 5,100,000 5,100,000 19.20 % (768.000) 5,100,000 5,100,000 5,100,000 5,100,000 5,100,000 5,100,000 Depreciation rate 0 % 09 0% MACRS 5 year 30 20% 32% 11.52% 11.52 % 5.76% 09 31 Depreciation (800,000) (1,280,000). (460.800) (460,800) (230.400) 2,100,000 Revenue-Op Cost-Depreciation 2,100,000 EBT 1,300,000 820,000 1,332,000 1,639,200 1,639,200 2,100,000 1,859,600 2,100,000 2,100,000 2,100,000 2,100,000 2,100,000 32 2,100,000 2,100,000 2,100,000 100,000 e tax Cash Flows (4,000,000) 2,100,000 2,100,000 Present Worth (PW) at MAR 8.367.387 -NPV(MARR,Cash flows)-First Cost 35 Discounted Payback period 1.90 52 % IRR 36 37 Option 2 First Cost Total 38 9 10 20 machines @250,000 39 (5,000,000). mber of machines 34,000 34,000 34,000 34,000 34,000 34,000 34,000 34,000 34,000 34.000 (4,080,000) 6 A60 000 4.080.000) ( 4,080,000) 6460 000 6A60 000 2190/b Operating Cost 4,080,000) ( 4,080,000) 4,080,000) ( 4,080,000) (4,080,000 ) (4,080,000) (4,080,000) 6.460.000 6 460 000 41 EA60 00 6 460 000 6.460.000 6460 000 17.49 % (699,600) 0 % MACRS 7 year 14 % 24 % 4 % 0% 43 Depreciation rate 12.49% 8.93% 8.92 % 956 44 Depreciation (714,500) (979,600) (499,600) (357,200) (356,800) (357,200) (178.400) EBT 2,380,000 Revenue-Op Cost-Depreciation 45 1,665,500 1,400,400 1,680,400 1,880,400 2022 800 2,023,200 2.022.800 2.201.600 2,380,000 Before tax Cash Flows (5,000,000) 2.380,000 EBT+ addback Depreciation 46 2.380,000 2,380,000 2,380,000 2,380,000 2,380,000 2,380,000 2,380,000 2.380.000 2.380.000 Present Worth (PW) at MAR 47 9,016,372 NPV(MARR,Cash flows)-First Cost counted Payback period 479

50 51 Option 3 First Cost Total 10 (6,000,000) 52 20 machines @300.000 Hrs/Year Number of machines 53 36,000 36,000 36,000 36,000 36,000 36,000 36,000 36,000 36.000 36,000 (5 7S60 000 7560,000 ting Cost 7S60 0007ES.co 7560 000 210/ 7 560 000 7 560 000 Depreciation rate 8.92 % 14 % 1,469,400) (1,049,400) 4 % 17.49% 12.49% 096 56 24% 8.93 % 9%6 0% MACRS 7 vear 57 Depreciation (857,400) (749,400) (535,800) (535,200) (535,800) (267,600) 58 EBT 1.302.600 690.600 1.110.600 1.410.600 1,624,200 1,624,800 1,624,200 1.892.400 2,160,000 2,160,000 Revenue-Op Cost-Depreciation 2.160,000 EBT+ addback Depreciation Before tax Cash Flows (6,000,000) 2.160,000 59 2.160.000 2.160.000 2.160.000 2,160,000 2,160,000 2,160,000 2,160,000 2.160.000 Present Worth (PW) at MAR 60 6.720.741 =NPV(MARR ,Cash flows)-First Cost counted Payback period 61 34% 63