Answer Happy

Part A is done need help with B,C, D and E. Please show excel
formula being used
The company eBay (ticker: EBAY) 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 $6.XXX million, where XXX are the digits found in your KU email address. Additional data: R > 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 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 NPVs—in Excel and with the calculator—should be equal. 1 2 5 6 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 Total project cash flows (operating + non-operating) NPV
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 IRR—it's the WACC that produces a zero NPV.
S T U V 3 4 5 6 112000000 73500000 14478000 24022000 9008250 15013750 14478000 29491750 96000000 73500000 9144000 13356000 5008500 8347500 9144000 17491500 85000000 73500000 8382000 3118000 1169250 1948750 8382000 10330750 85000000 73500000 4572000 6928000 2598000 4330000 4572000 8902000 26000000 3931250 38833250 29491750 17491500 10330750 P Q R 61 0 1 2 62 Computer system -75000000 63 installation - 1200000 64 Working capital -26000000 65 Revenue 112000000 112000000 66 (-) Operating cost 73500000 73500000 67 (-) Depreciation 15240000 24384000 68 EBT 23260000 14116000 69 (-) Tax @ 37.5% 8722500 5293500 70 PAT 14537500 8822500 71 (+) Depreciation 15240000 24384000 72 Operating cash flow 29777500 33206500 73 (+) WC recovered 74 (+) Net salvage value 75 Total project cash flows -102200000 29777500 33206500 76 Cost of capital 9.50% 77 NPV 16407982.58 (=NPVIP76,975:V75)+P75) 78 79 Working capital Receivablestinventories - payables 80 27000000+6500000-7500000 81 26000000 82 83 Depreciation schedule: 84 Year cost Rate Depreciation 85 1 76200000 20% 15240000 86 2 76200000 32% 24384000 87 3 76200000 19% 14478000 88 76200000 12% 9144000 89 5 76200000 11% 8382000 90 6 76200000 6% 4572000 91 92 Salvage value = 6290000 93 Tax on capital gain = 6290000*37.5% = 2358750 94 Net salvage value = Salvage value-tax 95 6290000-2358750 96 3931250 97 Sheet14 Sheet15 Sheet16 4 ..