Proforma Income Statement Capital Budgeting

Lets return to the proforma income statement we created for Microsofts Z Box (info given below) that we did in Week 2 and complete the analysis to determine if the project is desirable.

Using the spreadsheet you constructed in Week 2 and the cost of capital calculations you computed in Week 8 to determine if Microsoft should continue with the Z Box project. Use the following capital budgeting techniques.

1. Payback period

2. Net present value

3. Internal rate of return

Now lets test the sensitivity of the project to some changes in the assumptions.

4. Take the cost of capital you previously computed (in week 8) and add 2% to the value (for example, if WACC was 12%, make it 14%) and recalculate NPV. What happens to IRR? Is the project still desirable?

5. Suppose the cost of goods sold percentage rises by 3%. Compute the payback period, NPV and IRR. Use the original WACC you computed.

6. How sensitive is NPV to the changes made in 4 and 5?

Week 8 Information Use the 10-year Treasury Bond rate as the risk free rate and assume that the market risk premium is 8% to find Microsofts cost of equity. Assume that Microsofts bonds are rated AAA and that Microsofts corporate tax rate is 21%.
Assume that Microsofts bonds have no floatation costs, but the cost of issuing equity is 4.t%. Find Microsofts weighted average cost of capital.
Assume that the project will be financed with internal funds. You will need to find additional financial information from the Wall Street Journal and online at sites like http://finance.yahoo.com to complete your calculation.
Do this analysis in the same spreadsheet where you computed the operating cash flows for the project. Put the work in a separate tab.
Next week, when we tackle capital budgeting, well use these cost of capital and cash flow estimates to determine if the project is desirable.
Weights
MSFT Market Capitalization (E) (Trillion)	1,201,000,000,000	1.201 Trillion
Debt (D)	5,516,000
Equity Weight E/(E+D)	99.999541%
Debt Weight D/(E+D)	0.0004593%
Cost of Equity
Bets	1.09
Risk Free Rate (10 Yr Treasury)	0.70%
Market Risk Premium	8%
Cost of Equity	9.42%
Cost of Debt
AAA Bond Yield (Debt Rate)	3.16%
Corporate Tax Rate	21%
After Tax Cost of Debt	2.50%
WACC	9.4200%

Microsoft information can be found on Yahoo Finance or Wall Street Journal Financials

Also Please complete this work in excel (showing answers and formulas) :)