For step 1 use option 4 and option 1 For step 3 use option 3 For step 5 use option 1 For step 7 use

For step 1 use option 4 and option 1 For step 3 use option 3 For step 5 use option 1 For step 7 use option 3 Please use excel and demonstrate the excel formulas for the same.

image text in transcribed

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, (iii) 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. Options i Capital Sources Loan Description Interest Rate = 8 %, Compounded Semi Annually Payback Method: Plan 3 Dividend = $7, Price = $100, Brokerage Fee = $1 Dividend = $5, Price = $100, Growth Rate = 4% - 2 3 4 Preferred Stock Common Stock 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. 2 3 20 20 Options No. of Machines First Cost Operating Cost/Hr. Revenue/Hr. Hr./Year Useful Life Depreciation (MACRS) 1 20 $200,000 $100 $170 1500 Hrs. 10 Years 5 Years $250,000 $120 $190 1700 Hrs. 10 Years 7 Years $300,000 $150 $210 1800 Hrs. 10 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 State Tax Rate 8% 7% 9% Federal Tax Rate 20% 21% 19% 3 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 ATCE 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 1 Inflation Rate 4% 5% 6% WN 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, (iii) 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. Options i Capital Sources Loan Description Interest Rate = 8 %, Compounded Semi Annually Payback Method: Plan 3 Dividend = $7, Price = $100, Brokerage Fee = $1 Dividend = $5, Price = $100, Growth Rate = 4% - 2 3 4 Preferred Stock Common Stock 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. 2 3 20 20 Options No. of Machines First Cost Operating Cost/Hr. Revenue/Hr. Hr./Year Useful Life Depreciation (MACRS) 1 20 $200,000 $100 $170 1500 Hrs. 10 Years 5 Years $250,000 $120 $190 1700 Hrs. 10 Years 7 Years $300,000 $150 $210 1800 Hrs. 10 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 State Tax Rate 8% 7% 9% Federal Tax Rate 20% 21% 19% 3 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 ATCE 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 1 Inflation Rate 4% 5% 6% WN