Here is the information from Week 2 and Week 8 that may be useful.
Proforma Income Statement - Capital Budgeting Let's return to the proforma income statement we created for Microsoft's 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. Payback period Net present value 3. Internal rate of return Now let's 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? Suppose Microsoft has decided to introduce a new game playing console, the Z Box. Before they launch the Z Box, they conducted an analysis to see if the Z Box would be a desirable investment. The company estimated that it would sell 2.5 million Z Box's per year at a price of $800 for the next six years. After the first year of sales, the quantity sold will increase by 3% per year for the remaining life of the project. The initial capital outlay is determined to be $1.9 billion and a $800 million outlay in net working capital (NWC) would also be required. Assume that there is a one- time investment in NWC and that this will be recovered at the end of the project. Assume that the equipment used will be depreciated using the MACRS Juyear schedule and that the equipment has a salvage value of zero. At the end of year 6, the equipment will be sold for its book value. Also, assume that that the tax rate is 21%. Debts MSFT market cap Debt $ $ 1,200,000,000,000.00 87,015,000,000.00 Equity weight Debt weight 93.24% 6.76% Cost of Equity Beta Rish-free rate Market risk premium 1.09 0.68% 8.00% Cost of equity 9.40% Cost of Debt AAA bond yield Corporate tax rate 2.71% 21.00% After tax cost of debt 2.14% WACC 8.767% Name Gross Profit Total Revenue Business Revenue Cost of Revenue Cost of Goods and Services Operating Income Expenses Selling, General and Administrative Expenses Research and Development Expenses Total Operating Profit/Loss Non-Operating Income Expenses, Total Total Net Finance Income Expense Net Interest Income Expense Interest Expense Net of Capitalized Interest Interest Income Other Income Expense, Non-Operating Pretax Income Provision for Income Tax Net Income from Continuing Operations Net Income Reported Effective Tax Rate Fiscal year ends in Sep 30 USD 2016 58,374,000 91,154,000 91,154,000 32,780,000 32,780,000 26,078,000 19,198,000 11,988,000 26,078,000 (439,000) 20,539,000 20,539,000 0 2017 62,310,000 96,571,000 96,571,000 34,261,000 34,261,000 29,025,000 19,942,000 13,037,000 29,025,000 876,000 25,489,000 25,489,000 2018 72,007,000 110,360,000 110,360,000 38,353,000 38,353,000 35,058,000 22,223,000 14,726,000 35,058,000 1,416,000 16,571,000 16,571,000 2019 82,933,000 125,843,000 125,843,000 42,910,000 42,910,000 42,959,000 23,098,000 16,876,000 42,959,000 729,000 39,240,000 39,240,000 -1,190,000 25,639,000 5,100,000 20,539,000 20,539,000 -67,000 -26,141,000 29,901,000 4,412,000 25,489,000 25,489,000 19,000 -986,000 36,474,000 19,903,000 16,571,000 16,571,000 50,000 -2,388,000 43,688,000 4,448,000 39,240,000 39,240,000 -115,000 Estimated operating cash flows 915,000 1,153,000 4,283,000 590,000 Proforma Income Statement - Capital Budgeting Let's return to the proforma income statement we created for Microsoft's 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. Payback period Net present value 3. Internal rate of return Now let's 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? Suppose Microsoft has decided to introduce a new game playing console, the Z Box. Before they launch the Z Box, they conducted an analysis to see if the Z Box would be a desirable investment. The company estimated that it would sell 2.5 million Z Box's per year at a price of $800 for the next six years. After the first year of sales, the quantity sold will increase by 3% per year for the remaining life of the project. The initial capital outlay is determined to be $1.9 billion and a $800 million outlay in net working capital (NWC) would also be required. Assume that there is a one- time investment in NWC and that this will be recovered at the end of the project. Assume that the equipment used will be depreciated using the MACRS Juyear schedule and that the equipment has a salvage value of zero. At the end of year 6, the equipment will be sold for its book value. Also, assume that that the tax rate is 21%. Debts MSFT market cap Debt $ $ 1,200,000,000,000.00 87,015,000,000.00 Equity weight Debt weight 93.24% 6.76% Cost of Equity Beta Rish-free rate Market risk premium 1.09 0.68% 8.00% Cost of equity 9.40% Cost of Debt AAA bond yield Corporate tax rate 2.71% 21.00% After tax cost of debt 2.14% WACC 8.767% Name Gross Profit Total Revenue Business Revenue Cost of Revenue Cost of Goods and Services Operating Income Expenses Selling, General and Administrative Expenses Research and Development Expenses Total Operating Profit/Loss Non-Operating Income Expenses, Total Total Net Finance Income Expense Net Interest Income Expense Interest Expense Net of Capitalized Interest Interest Income Other Income Expense, Non-Operating Pretax Income Provision for Income Tax Net Income from Continuing Operations Net Income Reported Effective Tax Rate Fiscal year ends in Sep 30 USD 2016 58,374,000 91,154,000 91,154,000 32,780,000 32,780,000 26,078,000 19,198,000 11,988,000 26,078,000 (439,000) 20,539,000 20,539,000 0 2017 62,310,000 96,571,000 96,571,000 34,261,000 34,261,000 29,025,000 19,942,000 13,037,000 29,025,000 876,000 25,489,000 25,489,000 2018 72,007,000 110,360,000 110,360,000 38,353,000 38,353,000 35,058,000 22,223,000 14,726,000 35,058,000 1,416,000 16,571,000 16,571,000 2019 82,933,000 125,843,000 125,843,000 42,910,000 42,910,000 42,959,000 23,098,000 16,876,000 42,959,000 729,000 39,240,000 39,240,000 -1,190,000 25,639,000 5,100,000 20,539,000 20,539,000 -67,000 -26,141,000 29,901,000 4,412,000 25,489,000 25,489,000 19,000 -986,000 36,474,000 19,903,000 16,571,000 16,571,000 50,000 -2,388,000 43,688,000 4,448,000 39,240,000 39,240,000 -115,000 Estimated operating cash flows 915,000 1,153,000 4,283,000 590,000
