I completely all the problems except problem 4. It would be great for help with problem 4 based off of the work I've done on the excel file.

4.Then, perform sensitivity analysis as follows:

a.We have assumed that initial sales (i.e. sales in 2017) will be $3.5 billion, but we arenot sure about this. Let?s try to figure out what is our margin of error for thatnumber. That means that we need to do sensitivity analysis on the level of initialsales - of course, this will also affect costs and NWC, as costs and NWC are aproportion of sales, as per assumptions (2a) and (2d), respectively. Start from 10%of our estimate of $3.5 billion (i.e. initial sales of 10%*$3.5 billion = $350 million)and go all the way to 150% (i.e. initial sales of 150%*$3.5 billion = $5.25 billion), inincrements of 10%. Calculate the NPV for each of the 15 estimates. What is thetipping point of initial sales that turns the NPV to negative?

b.In part (2a), you estimated costs based on the historical ratio of costs to revenues.What will the NPV be if costs are 2% higher or 2% lower than that number? (Forexample, if you had calculated that costs are 80% of revenues, use 78% and 82%). Organize their answer in the form of a two-page (single-spaced) memo tome with attachments (the attachments do not count toward the page count).

Capital Budgeting Case FIN 350, Autumn 2016 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 servers will require an investment in fixed assets of $0.8 billion in 2016 (t=0). The product family is expected to have a life of five years: IBM will start selling the servers in 2017 (t=1) and will be selling them up until 2021 (t=5). First-year (i.e. 2017) revenues for the new servers are expected to be $3.5 billion. The server family's revenues are expected to grow by 20% for the second year (2018), grow by 15% for the third year (2019), and then decrease by 15% for the 4th year (2020) and finally decrease by 40% for the final year (2021) 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. 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. 2. 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. Assume that the project's profitability will be similar to IBM's existing projects in 2015 and estimate costs each year by using the 2015 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. b. 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 0 (i.e. 2016) against profit elsewhere in the company, generating a depreciation tax shield (you will have a negative tax bill). Thus, the only operating cash flow for 2016 will be a negative tax bill (which is a cash inflow), associated with the depreciation tax shield of the project. c. Determine IBM's 2015 effective tax rate as 1-(Income After Tax/Income Before Tax) Then, use this effective tax rate as the IBM effective tax rate for all years from 2016 to 2021. d. Calculate the level of net working capital required each year by assuming that the level of NWC in a given year will be a constant percentage of the project's sales FOR THE NEXT YEAR. Thus, the NWC in 2016 will be based on expected sales in 2017 and the NWC level for the project will be zero in 2021 (because sales in 2022 are zero). Use IBM's 2015 NWC/Sales to estimate the required percentage: for that ratio, use only Accounts receivable - Trade, Net, Total Inventory and Accounts Payable to measure NWC, and ignore other components of the NWC, as since other components of current assets and liabilities are harder to interpret and are not necessarily reflective of the project's required NWC. For example, if this ratio is 5%, then the NWC level in year 0 (i.e. 2016) is 5% of expected sales in year 1 (i.e. 2017), the NWC in year 1 (i.e. 2017) is 5% of expected sales in year 2 (i.e. 2018), etc. 3. Determine the IRR of the project and the NPV of the project at a cost of capital of 10% using the Excel functions. There are 6 cash flows: from year 0 (2016) to year 5 (2021). For the calculation of NPV, include cash flows 1 through 5 in the NPV function and then subtract the initial cost (i.e., = NPV(rate, CF1:CF5) + CF0). For IRR, include cash flows 0 through 5 in the cash flow range (i.e., =IRR(CF0:CF5)). 4. Then, perform sensitivity analysis as follows: a. We have assumed that initial sales (i.e. sales in 2017) will be $3.5 billion, but we are not sure about this. Let's try to figure out what is our margin of error for that number. That means that we need to do sensitivity analysis on the level of initial sales - of course, this will also affect costs and NWC, as costs and NWC are a proportion of sales, as per assumptions (2a) and (2d), respectively. Start from 10% of our estimate of $3.5 billion (i.e. initial sales of 10%*$3.5 billion = $350 million) and go all the way to 150% (i.e. initial sales of 150%*$3.5 billion = $5.25 billion), in increments of 10%. Calculate the NPV for each of the 15 estimates. What is the tipping point of initial sales that turns the NPV to negative? b. In part (2a), you estimated costs based on the historical ratio of costs to revenues. What will the NPV be if costs are 2% higher or 2% lower than that number? (For example, if you had calculated that costs are 80% of revenues, use 78% and 82%). Each group should organize their answer in the form of a two-page (single-spaced) memo to me with attachments (the attachments do not count toward the page count). You should submit a typed hardcopy in class on Wednesday, November 16th. MACRS For most business property placed in service after 1986, the IRS allows firms to depreciate the asset using the MACRS (Modified Accelerated Cost Recovery System) method. Under this method, you categorize each business asset into a recovery class that determines the time period over which you can write off the cost of the asset. The table indicates the percentage of the asset's cost that may be depreciated each year, with year 0 indicating the year the asset was first put into use. Year %of CapEx Depreciated 0 20.00% 1 32.00% 2 19.20% 3 11.52% 4 11.52% 5 5.76% In Millions of USD (except for per share items Revenue Other Revenue, Total Total Revenue Cost of Revenue, Total Gross Profit Selling/General/Admin. Expenses, Total Research & Development Depreciation/Amortization Interest Expense(Income) - Net Operating Unusual Expense (Income) Other Operating Expenses, Total Total Operating Expense Operating Income Interest Income(Expense), Net Non-Operating Gain (Loss) on Sale of Assets Other, Net Income Before Tax Income After Tax Minority Interest Equity In Affiliates Net Income Before Extra. Items Accounting Change Discontinued Operations Extraordinary Item Net Income Preferred Dividends Income Available to Common Excl. Extra Items Income Available to Common Incl. Extra Items Basic Weighted Average Shares Basic EPS Excluding Extraordinary Items Basic EPS Including Extraordinary Items Dilution Adjustment Diluted Weighted Average Shares Diluted EPS Excluding Extraordinary Items Diluted EPS Including Extraordinary Items Dividends per Share - Common Stock Primary I Gross Dividends - Common Stock Net Income after Stock Based Comp. Expense Basic EPS after Stock Based Comp. Expense Diluted EPS after Stock Based Comp. Expense Depreciation, Supplemental Total Special Items Normalized Income Before Taxes Effect of Special Items on Income Taxes Income Taxes Ex. Impact of Special Items Normalized Income After Taxes Normalized Income Avail to Common Basic Normalized EPS Diluted Normalized EPS 12 months ending 2015-12-31 81,741.00 81,741.00 41,057.00 40,684.00 19,512.00 5,247.00 304 587 -259 66,479.00 15,262.00 683 15,945.00 13,364.00 13,364.00 13,190.00 13,364.00 13,190.00 982.7 13.6 5 - 13.9 12 months ending 2014-12-31 92,793.00 92,793.00 46,386.00 46,407.00 21,385.00 5,437.00 374 1,472.00 -1,877.00 73,549.00 19,244.00 742 19,986.00 15,752.00 15,752.00 12,023.00 15,751.00 12,022.00 1,010.00 15.6 4.25 - 16.85 12 months ending 2013-12-31 98,367.00 98,367.00 49,683.00 48,684.00 22,214.00 5,743.00 370 1,031.00 -137 78,945.00 19,422.00 822 20,244.00 16,881.00 16,881.00 16,483.00 16,881.00 16,483.00 1,103.04 15.3 3.7 - 16 12 months ending 2012-12-31 102,874.00 102,874.00 52,513.00 50,361.00 23,463.00 5,816.00 -843 81,408.00 21,466.00 1,074.00 22,540.00 16,999.00 16,999.00 16,604.00 16,999.00 16,604.00 1,155.45 14.71 3.3 - -1,040.00 1,168.00 14.71 In Millions of USD (except for per share it Cash & Equivalents Short Term Investments Cash and Short Term Investments Accounts Receivable - Trade, Net Receivables - Other Total Receivables, Net Total Inventory Prepaid Expenses Other Current Assets, Total Total Current Assets Property/Plant/Equipment, Total - Gross Accumulated Depreciation, Total Goodwill, Net Intangibles, Net Long Term Investments Other Long Term Assets, Total Total Assets Accounts Payable Accrued Expenses Notes Payable/Short Term Debt Current Port. of LT Debt/Capital Leases Other Current liabilities, Total Total Current Liabilities Long Term Debt Capital Lease Obligations Total Long Term Debt Total Debt Deferred Income Tax Minority Interest Other Liabilities, Total Total Liabilities Redeemable Preferred Stock, Total Preferred Stock - Non Redeemable, Net Common Stock, Total Additional Paid-In Capital Retained Earnings (Accumulated Deficit) Treasury Stock - Common Other Equity, Total Total Equity Total Liabilities & Shareholders' Equity Shares Outs - Common Stock Primary Issue Total Common Shares Outstanding As of 2015-12-31 7,686.00 508 8,194.00 27,353.00 28,554.00 1,551.00 3,912.00 293 42,504.00 29,341.00 -18,616.00 32,021.00 3,487.00 475 10,752.00 110,495.00 6,028.00 7,913.00 1,190.00 5,271.00 13,867.00 34,269.00 33,428.00 33,428.00 39,889.00 253 162 28,121.00 96,233.00 53,262.00 146,124.00 -155,518.00 -29,611.00 14,262.00 110,495.00 965.73 As of 2014-12-31 - - - - As of 2013-12-31 8,476.00 10,716.00 0 350 8,476.00 11,066.00 28,925.00 30,252.00 31,831.00 31,836.00 2,103.00 2,310.00 4,216.00 4,171.00 751 1,967.00 47,377.00 51,350.00 39,035.00 40,475.00 -28,263.00 -26,654.00 30,556.00 31,184.00 3,104.00 3,871.00 594 310 13,251.00 12,758.00 117,271.00 126,223.00 6,864.00 7,461.00 10,025.00 8,641.00 1,130.00 3,008.00 4,601.00 3,854.00 16,961.00 17,190.00 39,581.00 40,154.00 34,991.00 32,856.00 34,991.00 32,856.00 40,722.00 39,718.00 129 1,741.00 146 137 30,556.00 28,543.00 105,403.00 103,431.00 52,666.00 51,594.00 137,793.00 130,042.00 -150,715.00 -137,242.00 -27,861.00 -21,601.00 11,868.00 22,792.00 117,271.00 126,223.00 990.52 1,054.39 As of 2012-12-31 10,412.00 717 11,129.00 28,705.00 30,578.00 2,287.00 4,015.00 1,424.00 49,433.00 40,502.00 -26,505.00 29,247.00 3,787.00 1,687.00 8,047.00 119,213.00 7,952.00 9,592.00 3,589.00 5,592.00 16,900.00 43,625.00 24,088.00 24,088.00 33,269.00 448 124 32,068.00 100,353.00 50,110.00 117,641.00 -123,131.00 -25,764.00 18,860.00 119,213.00 1,117.37 Pro Forma Income Statement 0 1 2016 2017 3,500,000,000.00 2,818,120,649.37 681,879,350.63 160,000,000 256,000,000 (160,000,000.00) 425,879,350.63 (25,899,027.91) 68,936,632.42 (134,100,972.09) 356,942,718.21 Revenue Cost Gross Profit Depreciation EBIT Tax (16.19%) Net Income Cash Flow 2016 2017 2018 2019 2020 2021 Bottom Up Approach Tax Shield Approach 25,899,027.91 25,899,027.91 612,942,718.21 612,942,718.21 710,668,195.06 710,668,195.06 803,593,737.58 803,593,737.58 685,292,352.96 685,292,352.96 409,683,627.77 409,683,627.77 a Revenue Cost Or Revenue, Total Selling/General/Admin. Expenses, T Research & Development Total Cost Ratio (Cost/Revenue) b Year 2016 2017 2018 2019 2020 2021 c d 81,741.00 41,057.00 19,512.00 5,247.00 65,816.00 80.52% Asset MARC Depreciation 800,000,000 0.2000 800,000,000 0.3200 800,000,000 0.1920 800,000,000 0.1152 800,000,000 0.1152 800,000,000 0.0576 IBM's 2015 Effective Tax Rate Income After tax Income Before Tax Effective Tax Rate 13,364.00 15,945.00 0.1619 NWC Accounts Receivable - Trade, Net 22,876.00 27,353.00 Total Inventory Account Payable Sales 2015 Ratio (NWC/Sales) 2015 1,551.00 6,028.00 81,741.00 27.99% NWC 2016 2017 2018 2019 2020 2021 979,508,447.41 1,175,410,136.90 1,351,721,657.43 1,148,963,408.82 689,378,045.29 - 2016 2017 2018 2019 2020 2021 979,508,447.41 195,901,689.48 176,311,520.53 (202,758,248.61) (459,585,363.53) (689,378,045.29) 2016 2017 2018 2019 2020 2021 (1,753,609,419.50) 417,041,028.73 534,356,674.52 1,006,351,986.20 1,144,877,716.48 1,099,061,673.06 Change in NWC Free Cash Flow rate NPV IRR 10% 1,287,620,730.22 31% (800,000,000) 2 2018 4,200,000,000.00 3,381,744,779.24 818,255,220.76 153,600,000 664,655,220.76 107,587,025.70 557,068,195.06 Depreciation 160,000,000 256,000,000 153,600,000 92,160,000 92,160,000 46,080,000 3 2019 4,830,000,000.00 3,889,006,496.13 940,993,503.87 92,160,000 848,833,503.87 137,399,766.29 711,433,737.58 4 2020 4,105,500,000.00 3,305,655,521.71 799,844,478.29 92,160,000 707,684,478.29 114,552,125.34 593,132,352.96 5 2021 2,463,300,000.00 1,983,393,313.03 479,906,686.97 46,080,000 433,826,686.97 70,223,059.21 363,603,627.77 1 Should we calculate the cashflow in 2016? 2 to calulate the changes in Net working capital, is that just substract the 3 If there is no salvage value, how to calculate the capital expedenture 1606179980 2818482190 king capital, is that just substract the NWC in the current year with the NWC in the prervious year? calculate the capital expedenture