Please read the following case study carefully. Imagine you are working as an investment analysis for ABC Company, you are given 100,000s to allocate for 5 different investment opportunities. You may select any combination of investments if the sum of initial cash outlays are equal to $100,000. Use NPV analysis in Excel to determine which out of these 5 Investments (or combination of) will be the most profitable. Provide an answer the following three questions in an Excel workbook. 1) If this $100,000 of capital is financed in the way shown below; calculate the WACC for this amount of capital. (corporate tax rate of 20%). Long term LenShort term Loan Mortgage Loan Equity 15.00085% 5.0005 85% 60,0005 % 20,00057 2) Find out with single investment or combination of investments yields the highest NPV. Use Excel to calculate each -> You will be graded on your use of formulas in excel. 3) For each Investment option calculate the IRR (Internal Rate of Return). Option 1 A $25,000 initial investment which provides cash flows in the following way: Year 1 Year 2 Year Year 4 Years 8.000$ 9,000 1.000 512,000 58.500 Option 2 An annuity which provides payments of 7000S at the end of year for 15 years. The initial investment is $75,000. Option 3 A $50,000 initial investment that provides cash flows in the following way: Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 2.000 $4,000 $8,000 $10,000 $12,000 $14,000 $16,000 Option 4 A $50,000 initial investment that provides cash flows in the following way: Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year? 25,000 54.000 58,000 $10,000 $13,000 $1,000 $1.000 Option 5 A S100,000 Invesment which provides 5000 per year for the first 5 years and then 10,000$ per year for an additional 9 years. Please read the following case study carefully. Imagine you are working as an investment analysis for ABC Company, you are given 100,000s to allocate for 5 different investment opportunities. You may select any combination of investments if the sum of initial cash outlays are equal to $100,000. Use NPV analysis in Excel to determine which out of these 5 Investments (or combination of) will be the most profitable. Provide an answer the following three questions in an Excel workbook. 1) If this $100,000 of capital is financed in the way shown below; calculate the WACC for this amount of capital. (corporate tax rate of 20%). Long term LenShort term Loan Mortgage Loan Equity 15.00085% 5.0005 85% 60,0005 % 20,00057 2) Find out with single investment or combination of investments yields the highest NPV. Use Excel to calculate each -> You will be graded on your use of formulas in excel. 3) For each Investment option calculate the IRR (Internal Rate of Return). Option 1 A $25,000 initial investment which provides cash flows in the following way: Year 1 Year 2 Year Year 4 Years 8.000$ 9,000 1.000 512,000 58.500 Option 2 An annuity which provides payments of 7000S at the end of year for 15 years. The initial investment is $75,000. Option 3 A $50,000 initial investment that provides cash flows in the following way: Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 2.000 $4,000 $8,000 $10,000 $12,000 $14,000 $16,000 Option 4 A $50,000 initial investment that provides cash flows in the following way: Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year? 25,000 54.000 58,000 $10,000 $13,000 $1,000 $1.000 Option 5 A S100,000 Invesment which provides 5000 per year for the first 5 years and then 10,000$ per year for an additional 9 years
