att corp capital budgeting analysisatt considering installing new and highly sophisticated c

AT&T Corp: Capital Budgeting Analysis AT&T is considering installing a new and highly sophisticated computer system, which would cost $71 million. Delivery and installation would add another $1,000,000 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 AT&T 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 $3.0 million. Additional data:  If the new system is purchased, revenue is expected to increase over the present level by $94 million in each of the first three years, by $93 million in Year 4, and by $82 million in Years 5 and 6.  The increase in operating costs is projected at $67 million for each of the six years.  AT&T’s federal plus state income tax rate is 40 percent.  The project’s cost of capital is 8.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 $24 million because of expanded services, (2) an increase in inventories of $2 million, (3) an increase in accounts payable and accruals combined equal to $4 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. b. (1) Calculate the net cash outflow at time zero (t = 0) if AT&T goes ahead with this expansion. (2) Construct an Excel spreadsheet to calculate net operating cash flows for Years 1 through 6 (in thousands). Year 1 2 3 4 5 6 Sales due to expansion _________ _________ _________ _________ _________ _________ Operating costs _________ _________ _________ _________ _________ _________ Depreciation (DEP) _________ _________ _________ _________ _________ _________ Income before tax _________ _________ _________ _________ _________ _________ Taxes _________ _________ _________ _________ _________ _________ Net income (NI) _________ _________ _________ _________ _________ _________ Net operating cash flow (NI + DEP) _________ _________ _________ _________ _________ _________ Recovery of Working Capital _________ Salvage after tax _________ Total cash flows (operating + non operating) _________ _________ _________ _________ _________ _________ NPV _________ (3) Use the Excel Function to calculate the NPV of the expansion project (NPV = PV of all cash inflows Cash outflow at t = 0). The real power of electronic 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 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 Parts a and b as tab “AT&T B” c. 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 $2.0 million. Using this worst case scenario as far as salvage is concerned, what is the new NPV? Other assumptions are as in Part b. Answer by creating a new tab in the Excel Workbook titled “AT&T C”. d. While analyzing the project, you suddenly realize that the current tax law actually allows AT&T 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, save as tab “AT&T D”. e. 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). All other assumptions are as in Part d. Calculate and save as tab “AT&T E”. f. Change annual operating costs back to $67 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. Save as tab “AT&T F”