5,661

Amazon.com, Inc.: Capital Budgeting Analysis Amazon.com (ticker: AMZN) is considering installing a new and highly sophisticated computer system to help expand its business, which would cost $74 million. Delivery and installation would add another S1.3 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 Amazon 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.XXX million, where XXX are the digits found in your KU email address, Additional data: > If the new system is purchased, revenue is expected to increase over the present level by S103 million in cach of the first three years, by S96 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 $71.5 million for each of the six years. Amazon's federal-plus-state income tax rate is 37.5 percent. The project's cost of capital is 8.5 percent. It is expected that installation of the new system would cause the following changes in working capital accounts (D) an increase in receivables of $26 million because of expanded services, (2) an increase in inventories of $53 million, and (3) an increase in accounts payable and accruals combined equal to $8 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 e-book. 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 out flow at time zero (t - 0) if Amazon goes ahead with this expansion 3. Construct a table similar to that shown below to calculate net operating cash flows for Years through 6 (in thousands). 4. Cakulate the NPV of the expansion project (NPV = PV of all cash flows minus cash outflow at t = 0). 1 recommend you check the answer you get in Excel with your calculator. Both NPVs in Excel and with the calculator should be equal Year Revenue Operating costs Depreciation (DEP) Income before tax Taxes Net income IND Operating cash flow (NI+DEP Recovery of Working Capital Salvage after tak Total project 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 A. 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 Part A as AMZN-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 S1.75 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 AMZN-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 Amazon 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 AMZN-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 I would be shown as S0.001 in the spreadsheet, meaning that you'll need to change the format to Sx.xxx.) All other assumptions are as in Part C. Name this spreadsheet AMZN-D. E. For the final spreadsheet, change annual operating costs back to $71.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 AMZN-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. Other details for the project: Each students needs to do his or her own project independently. You can check with the professor with questions. Each spreadsheet in your Excel workbook must be labeled (e.g..AMZN-A, AMZN-B, etc.) Highlight (e.g., underline, bold, italics, highlighter, etc.) the information asked for in each part (e.g., highlight the resulting NPV in Part B). Deposit your Excel workbook in D2L assessment submission folder by the deadline shown in D2L. Academic integrity is very important. Any student caught copying another student's workbook will carn a zero on this assignment. Amazon.com, Inc.: Capital Budgeting Analysis Amazon.com (ticker: AMZN) is considering installing a new and highly sophisticated computer system to help expand its business, which would cost $74 million. Delivery and installation would add another S1.3 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 Amazon 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.XXX million, where XXX are the digits found in your KU email address, Additional data: > If the new system is purchased, revenue is expected to increase over the present level by S103 million in cach of the first three years, by S96 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 $71.5 million for each of the six years. Amazon's federal-plus-state income tax rate is 37.5 percent. The project's cost of capital is 8.5 percent. It is expected that installation of the new system would cause the following changes in working capital accounts (D) an increase in receivables of $26 million because of expanded services, (2) an increase in inventories of $53 million, and (3) an increase in accounts payable and accruals combined equal to $8 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 e-book. 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 out flow at time zero (t - 0) if Amazon goes ahead with this expansion 3. Construct a table similar to that shown below to calculate net operating cash flows for Years through 6 (in thousands). 4. Cakulate the NPV of the expansion project (NPV = PV of all cash flows minus cash outflow at t = 0). 1 recommend you check the answer you get in Excel with your calculator. Both NPVs in Excel and with the calculator should be equal Year Revenue Operating costs Depreciation (DEP) Income before tax Taxes Net income IND Operating cash flow (NI+DEP Recovery of Working Capital Salvage after tak Total project 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 A. 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 Part A as AMZN-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 S1.75 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 AMZN-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 Amazon 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 AMZN-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 I would be shown as S0.001 in the spreadsheet, meaning that you'll need to change the format to Sx.xxx.) All other assumptions are as in Part C. Name this spreadsheet AMZN-D. E. For the final spreadsheet, change annual operating costs back to $71.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 AMZN-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. Other details for the project: Each students needs to do his or her own project independently. You can check with the professor with questions. Each spreadsheet in your Excel workbook must be labeled (e.g..AMZN-A, AMZN-B, etc.) Highlight (e.g., underline, bold, italics, highlighter, etc.) the information asked for in each part (e.g., highlight the resulting NPV in Part B). Deposit your Excel workbook in D2L assessment submission folder by the deadline shown in D2L. Academic integrity is very important. Any student caught copying another student's workbook will carn a zero on this assignment