This assignment needs to be formatted with multiple spreadsheet tabs.

Apple Inc.: Capital Budgeting Analysis Apple Inc. (ticker: AAPL) is considering installing a new and highly sophisticated computer system in one of its manufacturing facilities, which would cost $71.0 million. Delivery and installation would add another $1.2 million to the initial cost. The new computer system has a 5-year class life under MACRS. Because of the half-year convention, it will take six years for Apple to fully depreciate the cost of the system (MACRS percentages: 20, 32, 19, 12, 11 and 6 percent), and at the end of year 6 the salvage value of the system is estimated to be S5.XXX million, where XXX are the digits found in your KU email address. Additional data: If the new system is purchased, revenue from greater sales is expected to increase over the present level by $105 million in each of the first three years, by S97 million in year 4, and by $88 million in years 5 and 6. (These improvements are incremental, not cumulative.) The increase in operating costs is projected at $68 million for each of the six years. Apple's federal-plus-state income tax rate is 40 percent. The project's weighted average cost of capital (WACC) is 12.0 percent. It is expected that installation of the new system would cause the following changes in working capital accounts (1) an increase in receivables of $25 million because of expanded business, (2) an increase in inventories of S6 million, and (3) an increase in accounts payable and accruals combined equal to S5 million. Using Excel, do the following: a. State the basic assumptions o the problem in an "assumption block." This means that you should display in the upper left-hand corner of the spreadsheets all the relevant quantitative information given above, whether it actually changes in the problem or not. Enter the numbers in thousands. For example, enter the $25 million increase in receivables as $25,000 in the spreadsheet. b. Calculate (1) the project's net cash outflow at time zero (t - 0) if Apple goes ahead with this expansion; (2) construct a table along the following lines to calculate the project net cash flows for years 1 through 6 (in thousands); and (3) calculate the NPV of the expansion project. (Note: The NPV function in Excel assumes the first cash flow is for period 1 not time 0. Thus, the initial time 0 cash outflow needs to be outside the NPV function, but still part of the calculation. Confirm the correct NPV by checking it with your calculator.) Year Additional revenue due to expansion Additional operating costs (excluding Depreciation (DEP) EBIT (or operating income) Taxes on operating income After-tax project operating income (OI) Operating cash flow (OI +DEP) Recovery of Working Capital Salvage after tax Project net cash flows (operating + non-operating) NPV The real power of Excel spreadsheets is their ability to perform sensitivity analyses (answer "what-if" questions). It is therefore crucial that you set up your model such that the stated assumptions in Part (a) are the underlying determinants of the capital budgeting calculations performed in Part (b). A change in any one of the assumptions in Part (a) will then automatically cause a recalculation of the project's NPV. In other words, be sure to use formulas and refer to earlier cell addresses whenever appropriate. If your model is constructed correctly, the remaining questions should r equire very little time to answer Save all of your work in Parts (a) and (b) as AAPL-A and B (the tab name) Because computer hardware and software prices are notoriously difficult to estimate beyond a couple of years -let alone resale values the salvage value of the new system is somewhat "iffy." However, the Forecasting Department guesses that the salvage value will not drop below $1.4 million. Using this worst-case scenario as far as salvage is concerned (i.e., a salvage value of S1.4 million), what is the new NPV? Other assumptions are as in Parts (a) and (b). Answer by duplicating the assumption block and NPV analysis to create a new spreadsheet. Save as AAPL-C. c. d. While analyzing the project, you suddenly realize that the current tax law actually allows Apple to depreciate the computer system over four years instead of six. Assume percentages of 33, 45, 15, and 7 percent, and assume that the system will, in fact, be used for six years as originally planned. All other assumptions are as in Part (c). Calculate a new NPV on a new spreadsheet, and save as AAPL-D Your boss would also like to know just how sensitive the NPV of the project is to operating costs. What is the highest annual operating cost increase associated with the project that would result in a positive NPV? (HINT: Find the answer using the "Goal Seek" function in Excel. Solve for the operating cost that sets NPV to S1). All other assumptions are as in Part d. Recalculate a new NPV on a new spreadsheet, and save as AAPL-E. (Hint: If your NPV cell is in units of thousands, an NPV of S1 would be shown as S0.001.) e. f. Change annual operating costs back to S68 million. Find the IRR of the project using "Goal Seek." (HINT: Solve for the cost of capital that sets the NPV to zero). All other assumptions are as in Part (e), with operating costs back to S68 million. Save as AAPL-F the spreadsheet showing your IRR solution. Apple Inc.: Capital Budgeting Analysis Apple Inc. (ticker: AAPL) is considering installing a new and highly sophisticated computer system in one of its manufacturing facilities, which would cost $71.0 million. Delivery and installation would add another $1.2 million to the initial cost. The new computer system has a 5-year class life under MACRS. Because of the half-year convention, it will take six years for Apple to fully depreciate the cost of the system (MACRS percentages: 20, 32, 19, 12, 11 and 6 percent), and at the end of year 6 the salvage value of the system is estimated to be S5.XXX million, where XXX are the digits found in your KU email address. Additional data: If the new system is purchased, revenue from greater sales is expected to increase over the present level by $105 million in each of the first three years, by S97 million in year 4, and by $88 million in years 5 and 6. (These improvements are incremental, not cumulative.) The increase in operating costs is projected at $68 million for each of the six years. Apple's federal-plus-state income tax rate is 40 percent. The project's weighted average cost of capital (WACC) is 12.0 percent. It is expected that installation of the new system would cause the following changes in working capital accounts (1) an increase in receivables of $25 million because of expanded business, (2) an increase in inventories of S6 million, and (3) an increase in accounts payable and accruals combined equal to S5 million. Using Excel, do the following: a. State the basic assumptions o the problem in an "assumption block." This means that you should display in the upper left-hand corner of the spreadsheets all the relevant quantitative information given above, whether it actually changes in the problem or not. Enter the numbers in thousands. For example, enter the $25 million increase in receivables as $25,000 in the spreadsheet. b. Calculate (1) the project's net cash outflow at time zero (t - 0) if Apple goes ahead with this expansion; (2) construct a table along the following lines to calculate the project net cash flows for years 1 through 6 (in thousands); and (3) calculate the NPV of the expansion project. (Note: The NPV function in Excel assumes the first cash flow is for period 1 not time 0. Thus, the initial time 0 cash outflow needs to be outside the NPV function, but still part of the calculation. Confirm the correct NPV by checking it with your calculator.) Year Additional revenue due to expansion Additional operating costs (excluding Depreciation (DEP) EBIT (or operating income) Taxes on operating income After-tax project operating income (OI) Operating cash flow (OI +DEP) Recovery of Working Capital Salvage after tax Project net cash flows (operating + non-operating) NPV The real power of Excel spreadsheets is their ability to perform sensitivity analyses (answer "what-if" questions). It is therefore crucial that you set up your model such that the stated assumptions in Part (a) are the underlying determinants of the capital budgeting calculations performed in Part (b). A change in any one of the assumptions in Part (a) will then automatically cause a recalculation of the project's NPV. In other words, be sure to use formulas and refer to earlier cell addresses whenever appropriate. If your model is constructed correctly, the remaining questions should r equire very little time to answer Save all of your work in Parts (a) and (b) as AAPL-A and B (the tab name) Because computer hardware and software prices are notoriously difficult to estimate beyond a couple of years -let alone resale values the salvage value of the new system is somewhat "iffy." However, the Forecasting Department guesses that the salvage value will not drop below $1.4 million. Using this worst-case scenario as far as salvage is concerned (i.e., a salvage value of S1.4 million), what is the new NPV? Other assumptions are as in Parts (a) and (b). Answer by duplicating the assumption block and NPV analysis to create a new spreadsheet. Save as AAPL-C. c. d. While analyzing the project, you suddenly realize that the current tax law actually allows Apple to depreciate the computer system over four years instead of six. Assume percentages of 33, 45, 15, and 7 percent, and assume that the system will, in fact, be used for six years as originally planned. All other assumptions are as in Part (c). Calculate a new NPV on a new spreadsheet, and save as AAPL-D Your boss would also like to know just how sensitive the NPV of the project is to operating costs. What is the highest annual operating cost increase associated with the project that would result in a positive NPV? (HINT: Find the answer using the "Goal Seek" function in Excel. Solve for the operating cost that sets NPV to S1). All other assumptions are as in Part d. Recalculate a new NPV on a new spreadsheet, and save as AAPL-E. (Hint: If your NPV cell is in units of thousands, an NPV of S1 would be shown as S0.001.) e. f. Change annual operating costs back to S68 million. Find the IRR of the project using "Goal Seek." (HINT: Solve for the cost of capital that sets the NPV to zero). All other assumptions are as in Part (e), with operating costs back to S68 million. Save as AAPL-F the spreadsheet showing your IRR solution