Capital Budgeting Case, could use a spread sheet please?

Capital Budgeting Case FIN 350, Autumn 2017 You have just been hired by IBM in its finance division. Your first assignment is to determine the net cash flows and NPV of a proposed new generation of servers. Capital expenditures to produce the new sewers will require an investment in fixed assets of $0.85 billion in 2017 (t=0). The product family is expected to have a life of five years: IBM will start selling the servers in 2018 (t=l) and will be selling them up until 2022 (t=5). First-year (i.e. 2018) revenues for the new servers are expected to be $3 billion. The server familfs revenues are expected to grow by 20% for the second year (2019), grow by 10% for the third year (2020), and then decrease by 25% for the 4th year (2021) and finally decrease by 30% for the final year (2022) of sales. Your job is to determine the rest of the cash flows associated with this project. Your boss has indicated that the operating costs and net working capital requirements are similar to the rest of the company s products. Since your boss hasn't been much help, here are some tips to guide your analysis: 1. 2. Obtain IBM's financial statements. Download the annual income statements and balance sheets for the last four fiscal years from Google finance (finance.google.com). Enter IBM's ticker symbol (IBM) and then go to "Financials." Click "Annual," to ensure you're getting annual, instead of quarterly, data. Next, copy and paste the income statements and balance sheets into Excel. You are now ready to compute the free cash flow for each year. Set up the timeline and computation of the free cash flow in separate, contiguous columns for each year of the project life. Be sure to make outflows negative and inflows positive. a. b. c. Assume that the projeds profitability will be similar to IBM's existing projects in 2016 and estimate costs each year by using the 2016 ratio of non-depreciation costs to revenue: (Cost of Revenue, Total + SG&A + R&D) / Total Revenue where SG&A is Selling/General/Admin. Expenses, Total. You should assume that this ratio will hold for this project as well. Determine the annual depreciation by assuming IBM depreciates these assets using the MACRS 5-year depreciation schedule (see the next page). Note that since this project is not the whole company, you can apply a depreciation charge in year O (i.e. 2017) against profit elsewhere in the company, generating a depreciation tax shield (you vhll have a negative tax bill). Thus, the only operating cash flow for 2017 will be a negative tax bill (which is a cash inflow), associated with the depreciation tax shield of the project. Determine IBM's 2016 effective tax rate as 1- (Income After Tax/lncome Before Tax) Then, use this effective tax rate as the IBM effective tax rate for all years from 2017 to 2022.