Show the too formulas too thank you your hard work, have a good day

Apple (ticker: AAPLL) is considering installing a new and highly sophisticated computer system to help expand its business, which would cost $75 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 eBay 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 $5.034 million, Additional data: If the new system is purchased, revenue is expected to increase over the present level by $112 million in each of the first three years, by $96 million in Year 4, and by $85 million in Years 5 and 6. The increase in operating costs is projected at $73.5 million for each of the six years. eBay's federal-plus-state income tax rate is 37.5 percent. The project's cost of capital is 9.5 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 $27 million because of expanded services, (2) an increase in inventories of $6.5 million, and (3) an increase in accounts payable and accruals combined equal to $7.5 million. Using Excel, do the following: A. State the basic assumptions of 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. 1. Follow the analysis provided in your Ch. 12 PowerPoints and Table 12.1 in the textbook. The main difference is this project runs for six years while the example in the book runs for just four years. 2 Calculate the net cash outflow at time zero (t = 0) if eBay goes ahead with this expansion 3. Construct a table similar to that shown below to calculate net operating cash flows for Years 1 through 6 (in ta thousands). 4. Calculate the NPV of the expansion project (NPV = PV of all cash flows minus cash outflow at t= 0). It is recommended that you check the answer you get in Excel with your calculator. Both NPVsin Excel and with the calculator-should be equal. 3 Year Revenue Operating costs Depreciation (DEP) Income before tax Taxes Net income (NI) Operating cash flow (NI + DEP) Recovery of Working Capital Salvage after tax otal project 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 A. A change in any one of the assumptions in Part A will then automatically trigger a recalculation of the project's NPV. In other words, be sure to use formulas and refer back up to earlier cell addresses whenever appropriate. If your model is constructed correctly, the remaining questions should require very little time to answer. Save all of your work in Part A as EBAY-A (the tab name). The Excel file you submit will be an Excel workbook with a series of five spreadsheets. B. 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.3 million. Using this worst-case scenario as far as salvage is concerned, what is the new NPV? Other assumptions are as in Part A. Simply copy your first spreadsheet to create a new spreadsheet and name it EBAY-B. Then change the salvage value to the new value. The new NPV value should appear as soon as you enter the new salvage value, if your model has been constructed properly. C. While analyzing the project, you suddenly realize that the current tax law actually allows eBay 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 B. Calculate a new NPV and name this spreadsheet as EBAY-C. 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 $1. If your NPV cell is in units of thousands, an NPV of $1 would be shown as $0.001 in the spreadsheet, meaning that you'll need to change the format to $x.xxx.) All other assumptions are as in Part C. Name this spreadsheet EBAY-D. E. For the final spreadsheet, change annual operating costs back to $73.5 million. Then, 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 D. Save as EBAY-E. The cost of capital cell should be highlighted yellow with the cost that generates a $0 NPV. That is the definition of IRRit's the WACC that produces a zero NPV. Apple (ticker: AAPLL) is considering installing a new and highly sophisticated computer system to help expand its business, which would cost $75 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 eBay 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 $5.034 million, Additional data: If the new system is purchased, revenue is expected to increase over the present level by $112 million in each of the first three years, by $96 million in Year 4, and by $85 million in Years 5 and 6. The increase in operating costs is projected at $73.5 million for each of the six years. eBay's federal-plus-state income tax rate is 37.5 percent. The project's cost of capital is 9.5 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 $27 million because of expanded services, (2) an increase in inventories of $6.5 million, and (3) an increase in accounts payable and accruals combined equal to $7.5 million. Using Excel, do the following: A. State the basic assumptions of 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. 1. Follow the analysis provided in your Ch. 12 PowerPoints and Table 12.1 in the textbook. The main difference is this project runs for six years while the example in the book runs for just four years. 2 Calculate the net cash outflow at time zero (t = 0) if eBay goes ahead with this expansion 3. Construct a table similar to that shown below to calculate net operating cash flows for Years 1 through 6 (in ta thousands). 4. Calculate the NPV of the expansion project (NPV = PV of all cash flows minus cash outflow at t= 0). It is recommended that you check the answer you get in Excel with your calculator. Both NPVsin Excel and with the calculator-should be equal. 3 Year Revenue Operating costs Depreciation (DEP) Income before tax Taxes Net income (NI) Operating cash flow (NI + DEP) Recovery of Working Capital Salvage after tax otal project 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 A. A change in any one of the assumptions in Part A will then automatically trigger a recalculation of the project's NPV. In other words, be sure to use formulas and refer back up to earlier cell addresses whenever appropriate. If your model is constructed correctly, the remaining questions should require very little time to answer. Save all of your work in Part A as EBAY-A (the tab name). The Excel file you submit will be an Excel workbook with a series of five spreadsheets. B. 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.3 million. Using this worst-case scenario as far as salvage is concerned, what is the new NPV? Other assumptions are as in Part A. Simply copy your first spreadsheet to create a new spreadsheet and name it EBAY-B. Then change the salvage value to the new value. The new NPV value should appear as soon as you enter the new salvage value, if your model has been constructed properly. C. While analyzing the project, you suddenly realize that the current tax law actually allows eBay 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 B. Calculate a new NPV and name this spreadsheet as EBAY-C. 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 $1. If your NPV cell is in units of thousands, an NPV of $1 would be shown as $0.001 in the spreadsheet, meaning that you'll need to change the format to $x.xxx.) All other assumptions are as in Part C. Name this spreadsheet EBAY-D. E. For the final spreadsheet, change annual operating costs back to $73.5 million. Then, 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 D. Save as EBAY-E. The cost of capital cell should be highlighted yellow with the cost that generates a $0 NPV. That is the definition of IRRit's the WACC that produces a zero NPV