You have been hired by Internal Business Machines Corporation (IBM) in their capital budgeting division. Your first assignment is to determine the free cash flows and NPV of a proposed new type of tablet computer similar in size to an iPad but with the operating power of a high-end desktop system.
Development of the new system will initially require an initial capital expenditure equal to 10% of IBMs Property, Plant, and Equipment (PPE) at the end of the latest fiscal year for which data is available.
The project will then require an additional investment equal to 10% of the initial in-vestment after the first year of the project, a 5% increase after the second year, and a 1% increase after the third, fourth, and fifth years. The product is expected to have a life of five years.
First-year revenues for the new product are expected to be 3% of IBMs total revenue for the latest fiscal year for which data is available. The new products revenues are expected to grow at 15% for the second year then 10% for the third and 5% annually for the final two years of the expected life of the project.
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 and that depreciation is straight-line for capital budgeting purposes.
Use the template provided in the attached Spreadsheet
4. Perform a sensitivity analysis by varying the project forecasts as follows:
a. Suppose first year sales will equal 2%4% of IBMs revenues.
b. Suppose the cost of capital is 10%15%.
c. Suppose revenue growth is constant after the first year at a rate of 0%10%
Assumptions Year 1 Sales 3.0% of 2018 Sales 79,590,000 16,545.000 10.0% Revenue Growth Base Case Fxed al % Fixed at 10% 1 1 2 2 15.0% 15% 0% 10% 5.0% 5% 0% 10% 5.0% 5% 2018 Data 8 Sales EBITDA Calculated EBITDA PPE AR Inventory Payables NIC 0% TOS 10% % 10,793,000 29.820.000 1,692,000 6,568,000 24,944,000 EBITDA/B Cap Ex NWCASAS Tax Rate Cost of Capital (WACC) 20.8% from 2018 10.0% 5.0% of 2018 PPE 31.3% from 2018 21.0% 12.0% Year o Year 1 2,387,700 Year 6 Year 2 2,745,855 Year 3 3,020,441 Year 4 4 3,171,463 Year 5 3,330,036 First Year Sales 2% 3% 4% NPV 1,089,295 2,741,228 4,393,161 IRR 44.85% 86.36% 124.50% Revenue yoy growth EBITDA Depreciation EBIT Taxes Unlevered Net Income Depreciation Increase in NWC CapEx Free Cash Flow Discount Factor Present Value NPV IRR 496,350 (215,860) 280,490 58,903 221,587 215,860 748,320 (107,930) 861,977 12% 769.622 570,803 (237,446) 333,357 70,005 263,352 237.446 880,566 (113,327) 1,010,593 12% 8D5,639 627,883 (260,111) 367,771 77,232 290,539 260,111 946,625 (114,460) 1,122,704 12% 799,119 659,277 (283,003) 376,274 79,017 297,256 283,003 993,956 (115,604) 1,175,608 12% 747,120 692,241 (306,124) 386,117 81,084 305,032 306,124 1,043,654 (116,760) 1,231,926 12% 699,028 Cost of Capital 10% 12% 15% NPV 2,950,905 2,741,226 2,457,238 IRR 86.35% 96.36% 86.36% (1,079,300) (1,079,300) Revene Growth 1 2 3 NPV 2,741,228 1,896,447 2,698,052 IRR 86.36% Base Case 72.83% Fixed at 0% 84.49% Fixed at 10% (1,079,300) 2,741,228 86.4% Capex Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Depreciation Schedule CapEx Yr 0 CapEx Yr 1 Depreciation Year o Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 NWC Requirements NWC (12% of Sales) Increase in NWC Income Statement al numbers in thousand Breakdown TTM 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Total Revenue 77,131.000 79,890.000 79,199.000 79,620,000 91,742.000 Cost of Herende 41.000.000 -2.600.000 42,613,000 41,025,000 41,007,000 Gos Pront 36,076.000 36,636.000 36,227,000 30,294,000 40,084,000 Oporatra Expens Hesearch Development 5,761,000 5,279,000 5,787,000 5,761,000 5,247,000 Scing General and Administrative 19,764,000 19,863,000 19,665,000 20,479,00C 19,894,000 Total Operating Expenses 24,988,000 23,661,000 24,272,000 25, 102,000 24,763,000 Operating Income or Loss 11,187,000 13,285,000 11,865,000 12,192,000 15,621,000 Inbonest Expense 1,183,000 723,000 615,000 630,000 463,000 Total Other Income Expona Net 578,000 1,482,000 17,000 889,000 421,000 Incomc Balore Tax 10,607,000 11,242,000 11,400,000 12,880,000 15,545,000 Income Tax Excurse 2,888,000 2,619,000 5,642,000 449,000 2,581,000 Income from Continuing Operator 7,719,000 8,723,000 5,753,000 11,881,000 12,864,000 Net Income 7,712,000 8,723,000 5,753,000 11,872,000 13,190,000 Net Income tablato com mana 7,712,000 8,728,000 5,753,000 11,872,000 19,190,000 Regard EPS Real 2.57 9.17 12.49 13.48 9.52 12.38 13.42 Weight tracing Preke $12,000 939,800 855,400 978,700 816,300 937.400 858,700 982,700 FAITDA 18,545.000 18.558,000 17,341,000 20 268,000 FBIT 12,065.000 12,015.000 12,860,000 18413,000 Balance Sheetal numbers in thousands Breakdown 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Morets Current Assets Cash Cash And Cson Eolvalents 11,379,000 11.972,000 7.925,000 7,600.000 Short Term Investments 613,000 600,000 TU 1,000 500.000 Total Cash 11,597,000 12.690,000 8,527,000 3,194.000 Ne Poochables 29,820,000 30.549,000 28,188,000 27,363.000 Inventory 1,682,000 1.533,000 1,552,003 1,551000 Other Our: Asants 1,000 1,000 1,000 D Total Current Assets 49,145,000 45.735,000 43,388,000 42,504.000 Non-cum asets Propaty, plant and cula may Gas property, plant and 9: 39,461,000 92.331,000 80,134,000 29,341.000 Accumulated Depreciation -21,663,000 -21.215,000 -19,309,000 -13,616,000 Net property, plant and equ 10,793,000 11,116,000 10,831,000 10,725 000 Equity and other investments 220,000 122,000 104,000 475.000 Gandwi 36,265,000 36788,000 98,199,000 32,02-.000 Intangiale Ale 3,688,000 4.ARA.CO. 3,485.000 Othe long-term AS 295,000 572,000 729.000 5710 Total non-current als 74,237,000 75A20,000 73,584,000 67,487,000 Total Assets 123.982,000 125 350,000 117,470.000 119,495 can Lidochromily Lisbe Current Lisboa Total Revenue 10,207.000 6988,000 7.513,003 8,481,000 Acounts Payable 8.668.000 8.451,000 6.200,000 8,028,000 Tas payable Accuelables 3,841.000 4.510,000 4.705,000 4,353 090 Delored revenu 11,165,000 11,052,000 11.035,000 11.021,000 Other Ourer: Loties 7,261.000 1.000 1.000 1.000 Total Current Liabilities 30,227,000 37,303,000 36.275,000 34,200,000 Non-cument latinos Long Term Debt 35,COS,000 36,837,000 34.555,000 33,428.000 Detened taxes labies 2,095,000 645,000 424,003 263.000 Dermed revendes 3,445,000 3.745,000 3,500,000 3,771.000 Other long-term lates 1,719,000 1.721,000 1,770,000 2,063.000 Total non-current liabilities 68,225,000 70 258,000 62.002,000 61.002.000 Total Liabilities 106,453,000 107.631,000 59,078,000 93,071.000 Stockholders Equity Comman Brock 55,151,000 54.536,000 53,935,000 53,262.000 Read E 159,200,000 158 126,000 152,759,000 143,124.000 Accumulated other compte 29,490,000 26.592,000 29,398,000 29,607.000 Total stockholders equity 16,799,000 17.594,000 18,246,000 14,262,000 Total liabilities and stockh: 123,882,000 125 354,000 117,470,000 110,495.000 Cash Flow al numbers in thousandel Breakdown TTM 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Cash flows om operating acties Net Income 7.712.000 2.729,000 6.753,000 11,872,000 13,199,000 Deprecation & amorization 0.521,00 4.490,UCO 4,641,000 4,381,00C 3,885,000 Date ad income taxes 853,000 -931,000 -1, 132,000 1,387,000 Stoc based compensation 507.000 610.UCO 634,000 644,000 459,000 Dharga in wordna ephia 504,000 654,000 6.813,000 1,231,000 -2,444,000 Accounts receivable 345,000 419,000 1,215,000 200,000 Inventory 127,000 18,000 -14,000 133,000 Accounts Payak 125,000 47,000 197,000 81,000 other working capital 12,780,COO 11.283.000 12.951,000 12,803,000 12,857,000 Other ron cash Hars 1,000 1,000 544,000 200,000 Net cash provided by operating activites 15,438,000 15.247,000 16.724,000 16,553,000 17,000,000 In cash reporty, alent and equipment 2,858, COO 9.984,000 -8.773,000 -4,150,000 -4,151,000 Acquisto na 92,846,000 - 139,000 -701,000 -6,133,000 -2,750,000 Purchafiranta -7.041,000 -4.984,000 -5,917,000 -3,073,000 Maria alimenta 6.487,000 9.910,000 5,692,000 2,842,000 Othering a -BR2.000 -504,000 -3.020,000 892,000 997,000 Net case used for investing activites -26,402,000 -4.913,000 -7.094,000 -10.974,000 -8 159,000 Cash we caming with the -8.533,000 -6.814,000 -6.995,000 -5.622,000 Caminen seks 204,000 22.000 Cammi rokdar 3434,000 -1614,000 -1,533,000 -3.502,000 -1,600,000 Dvords Paid 6.885.000 - 688,000 - 508,000 -5 258,000 -1897,000 Other incing styles -88,000 112,000 174,000 204,000 321,000 Net cash voed privided by used for financ 10.112.000 -10 470,000 -6 417,000 -5,791,000 -8 165,000 Ned change in tash -1.508,000 -630,000 4 168,000 14 140,000 -190,000 Cachst beginning of period 11.731,000 12 234,000 7 828,000 7,688,000 8,478,000 Cash at end of period 11,604,000 11,972,000 7,828,000 7,688,000 Free Cash Flow Operaing Cash Flow 16.430,000 18,247,000 16.724,000 16,865,000 17,009,000 Capital Expenditure -2.550,000 -3,984,000 -3.773,000 -4,100,000 -4,161,000 Free Cash Flow 12.790,COO 11,293,000 12.951,000 12,809,000 12,857,000 1.741.00 red Bomings Assumptions Year 1 Sales 3.0% of 2018 Sales 79,590,000 16,545.000 10.0% Revenue Growth Base Case Fxed al % Fixed at 10% 1 1 2 2 15.0% 15% 0% 10% 5.0% 5% 0% 10% 5.0% 5% 2018 Data 8 Sales EBITDA Calculated EBITDA PPE AR Inventory Payables NIC 0% TOS 10% % 10,793,000 29.820.000 1,692,000 6,568,000 24,944,000 EBITDA/B Cap Ex NWCASAS Tax Rate Cost of Capital (WACC) 20.8% from 2018 10.0% 5.0% of 2018 PPE 31.3% from 2018 21.0% 12.0% Year o Year 1 2,387,700 Year 6 Year 2 2,745,855 Year 3 3,020,441 Year 4 4 3,171,463 Year 5 3,330,036 First Year Sales 2% 3% 4% NPV 1,089,295 2,741,228 4,393,161 IRR 44.85% 86.36% 124.50% Revenue yoy growth EBITDA Depreciation EBIT Taxes Unlevered Net Income Depreciation Increase in NWC CapEx Free Cash Flow Discount Factor Present Value NPV IRR 496,350 (215,860) 280,490 58,903 221,587 215,860 748,320 (107,930) 861,977 12% 769.622 570,803 (237,446) 333,357 70,005 263,352 237.446 880,566 (113,327) 1,010,593 12% 8D5,639 627,883 (260,111) 367,771 77,232 290,539 260,111 946,625 (114,460) 1,122,704 12% 799,119 659,277 (283,003) 376,274 79,017 297,256 283,003 993,956 (115,604) 1,175,608 12% 747,120 692,241 (306,124) 386,117 81,084 305,032 306,124 1,043,654 (116,760) 1,231,926 12% 699,028 Cost of Capital 10% 12% 15% NPV 2,950,905 2,741,226 2,457,238 IRR 86.35% 96.36% 86.36% (1,079,300) (1,079,300) Revene Growth 1 2 3 NPV 2,741,228 1,896,447 2,698,052 IRR 86.36% Base Case 72.83% Fixed at 0% 84.49% Fixed at 10% (1,079,300) 2,741,228 86.4% Capex Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Depreciation Schedule CapEx Yr 0 CapEx Yr 1 Depreciation Year o Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 NWC Requirements NWC (12% of Sales) Increase in NWC Income Statement al numbers in thousand Breakdown TTM 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Total Revenue 77,131.000 79,890.000 79,199.000 79,620,000 91,742.000 Cost of Herende 41.000.000 -2.600.000 42,613,000 41,025,000 41,007,000 Gos Pront 36,076.000 36,636.000 36,227,000 30,294,000 40,084,000 Oporatra Expens Hesearch Development 5,761,000 5,279,000 5,787,000 5,761,000 5,247,000 Scing General and Administrative 19,764,000 19,863,000 19,665,000 20,479,00C 19,894,000 Total Operating Expenses 24,988,000 23,661,000 24,272,000 25, 102,000 24,763,000 Operating Income or Loss 11,187,000 13,285,000 11,865,000 12,192,000 15,621,000 Inbonest Expense 1,183,000 723,000 615,000 630,000 463,000 Total Other Income Expona Net 578,000 1,482,000 17,000 889,000 421,000 Incomc Balore Tax 10,607,000 11,242,000 11,400,000 12,880,000 15,545,000 Income Tax Excurse 2,888,000 2,619,000 5,642,000 449,000 2,581,000 Income from Continuing Operator 7,719,000 8,723,000 5,753,000 11,881,000 12,864,000 Net Income 7,712,000 8,723,000 5,753,000 11,872,000 13,190,000 Net Income tablato com mana 7,712,000 8,728,000 5,753,000 11,872,000 19,190,000 Regard EPS Real 2.57 9.17 12.49 13.48 9.52 12.38 13.42 Weight tracing Preke $12,000 939,800 855,400 978,700 816,300 937.400 858,700 982,700 FAITDA 18,545.000 18.558,000 17,341,000 20 268,000 FBIT 12,065.000 12,015.000 12,860,000 18413,000 Balance Sheetal numbers in thousands Breakdown 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Morets Current Assets Cash Cash And Cson Eolvalents 11,379,000 11.972,000 7.925,000 7,600.000 Short Term Investments 613,000 600,000 TU 1,000 500.000 Total Cash 11,597,000 12.690,000 8,527,000 3,194.000 Ne Poochables 29,820,000 30.549,000 28,188,000 27,363.000 Inventory 1,682,000 1.533,000 1,552,003 1,551000 Other Our: Asants 1,000 1,000 1,000 D Total Current Assets 49,145,000 45.735,000 43,388,000 42,504.000 Non-cum asets Propaty, plant and cula may Gas property, plant and 9: 39,461,000 92.331,000 80,134,000 29,341.000 Accumulated Depreciation -21,663,000 -21.215,000 -19,309,000 -13,616,000 Net property, plant and equ 10,793,000 11,116,000 10,831,000 10,725 000 Equity and other investments 220,000 122,000 104,000 475.000 Gandwi 36,265,000 36788,000 98,199,000 32,02-.000 Intangiale Ale 3,688,000 4.ARA.CO. 3,485.000 Othe long-term AS 295,000 572,000 729.000 5710 Total non-current als 74,237,000 75A20,000 73,584,000 67,487,000 Total Assets 123.982,000 125 350,000 117,470.000 119,495 can Lidochromily Lisbe Current Lisboa Total Revenue 10,207.000 6988,000 7.513,003 8,481,000 Acounts Payable 8.668.000 8.451,000 6.200,000 8,028,000 Tas payable Accuelables 3,841.000 4.510,000 4.705,000 4,353 090 Delored revenu 11,165,000 11,052,000 11.035,000 11.021,000 Other Ourer: Loties 7,261.000 1.000 1.000 1.000 Total Current Liabilities 30,227,000 37,303,000 36.275,000 34,200,000 Non-cument latinos Long Term Debt 35,COS,000 36,837,000 34.555,000 33,428.000 Detened taxes labies 2,095,000 645,000 424,003 263.000 Dermed revendes 3,445,000 3.745,000 3,500,000 3,771.000 Other long-term lates 1,719,000 1.721,000 1,770,000 2,063.000 Total non-current liabilities 68,225,000 70 258,000 62.002,000 61.002.000 Total Liabilities 106,453,000 107.631,000 59,078,000 93,071.000 Stockholders Equity Comman Brock 55,151,000 54.536,000 53,935,000 53,262.000 Read E 159,200,000 158 126,000 152,759,000 143,124.000 Accumulated other compte 29,490,000 26.592,000 29,398,000 29,607.000 Total stockholders equity 16,799,000 17.594,000 18,246,000 14,262,000 Total liabilities and stockh: 123,882,000 125 354,000 117,470,000 110,495.000 Cash Flow al numbers in thousandel Breakdown TTM 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Cash flows om operating acties Net Income 7.712.000 2.729,000 6.753,000 11,872,000 13,199,000 Deprecation & amorization 0.521,00 4.490,UCO 4,641,000 4,381,00C 3,885,000 Date ad income taxes 853,000 -931,000 -1, 132,000 1,387,000 Stoc based compensation 507.000 610.UCO 634,000 644,000 459,000 Dharga in wordna ephia 504,000 654,000 6.813,000 1,231,000 -2,444,000 Accounts receivable 345,000 419,000 1,215,000 200,000 Inventory 127,000 18,000 -14,000 133,000 Accounts Payak 125,000 47,000 197,000 81,000 other working capital 12,780,COO 11.283.000 12.951,000 12,803,000 12,857,000 Other ron cash Hars 1,000 1,000 544,000 200,000 Net cash provided by operating activites 15,438,000 15.247,000 16.724,000 16,553,000 17,000,000 In cash reporty, alent and equipment 2,858, COO 9.984,000 -8.773,000 -4,150,000 -4,151,000 Acquisto na 92,846,000 - 139,000 -701,000 -6,133,000 -2,750,000 Purchafiranta -7.041,000 -4.984,000 -5,917,000 -3,073,000 Maria alimenta 6.487,000 9.910,000 5,692,000 2,842,000 Othering a -BR2.000 -504,000 -3.020,000 892,000 997,000 Net case used for investing activites -26,402,000 -4.913,000 -7.094,000 -10.974,000 -8 159,000 Cash we caming with the -8.533,000 -6.814,000 -6.995,000 -5.622,000 Caminen seks 204,000 22.000 Cammi rokdar 3434,000 -1614,000 -1,533,000 -3.502,000 -1,600,000 Dvords Paid 6.885.000 - 688,000 - 508,000 -5 258,000 -1897,000 Other incing styles -88,000 112,000 174,000 204,000 321,000 Net cash voed privided by used for financ 10.112.000 -10 470,000 -6 417,000 -5,791,000 -8 165,000 Ned change in tash -1.508,000 -630,000 4 168,000 14 140,000 -190,000 Cachst beginning of period 11.731,000 12 234,000 7 828,000 7,688,000 8,478,000 Cash at end of period 11,604,000 11,972,000 7,828,000 7,688,000 Free Cash Flow Operaing Cash Flow 16.430,000 18,247,000 16.724,000 16,865,000 17,009,000 Capital Expenditure -2.550,000 -3,984,000 -3.773,000 -4,100,000 -4,161,000 Free Cash Flow 12.790,COO 11,293,000 12.951,000 12,809,000 12,857,000 1.741.00 red Bomings
