Complete the IBM Data Case for Chapter 8 (pp. 277-278). This case requires you to conduct a Capital Budgeting analysis of the cash flows for a project the firm is evaluating. Use the information in Chapters 7 and 8 to do your analysis. Your final cash flow should be structured similarly to the example on Page 247 of your Corporate Finance text. Instruction and values to be used are below. This is based on 2020 IBM numbers provided below.

AutoSave Us Wk 3 Case Study Ch 8 Template.xlsx - Last Modified: 5m ago Search Taylor File Home Insert Draw Page Layout Formulas Data Review View M-Files (Z:) M-Files (M:) Share Comme Arial AutoSum 10 A === a Wrap Text General 1 ti Normal 2 Normal 2 2 Normal 4 Normal 5 Normal 6 X Cut LECopy Format Painter AB O Fill Paste BIU BV MA 35 3 Merge & Center Normal 9 $ % Normal Good Bad Insert Delete Format Conditional Format as Normal 8 Formatting Table Clear Sort & Find & Filter Select Editing Clipboard Font Alignment Number Styles Cells F6 X fic J L Y Z AB AC 5 C. B D E F G H 1 K M N 0 Q R s T U V w X 1 Case Study on IBM 2 tep process to solve 3 1. Find your data from the 2020 financials on IBM and add it to the data box. This data will be based on the company as a whole. 4 2020 Data THE DATA YOU SHOULD USE IS PROVIDED 6 Sales FOR YOU ON THE NEXT PAGE. USE THE 7 EBITDA Use the EBIT + the line for Depreciation & Amortization PROVIDED DATA 8 Calculated EBITDA Same as above 9 PPE Use net PPE value 10 AR Use Receivables - allowance for doubtful accounts 11 Inventory 12 Payables 13 NWC Net working capital = (A/R + Inventory) - Payables 14 15 2. Determine your revenues each year for the project. 16 a. Revenue for Year 0 = 0 17 b. Revenue for Year 1 = Sales for company as a whole x 3% 18 Revenue for Year 2 is 15% more than year 1 = Year 1 Revenue x 1.15 19 d. Revenue for Year 3 is 10% more than year 2 = Year 2 Revenue x 1.10 20 Revenue for Year 4 is 5% more than year 3 = Year 3 Revenue x 1.05 21 f. Revenue for Year 5 is 5% more than year 4 = Year 4 Revenue x 1.05 22 23 3. Calculate EBITDA using the formula provided in the problem of EBITDA/Sales = 17.1%. 24 Work backwards with the info we have, since you know the sales for the project (calculated in step 2). Calculate for each year of the project. This would change the formula to be 25 26 EBITDA = Sales x 17.1% 27 28 4. Calculate deprecation, which is based on the outlay of cash for the project from Year 0 and Year 1. This is based on your invested capital. 29 30 A. Calculate your capital expenses for year 0. From the problem data, this is based on 10% of your property, Plant and Equipment (PPE) for the company as a whole. Use the formula of PPE x 10% = Year O Capital 31 B. Calculate your capital expenses for year 1. From the problem data, this is based on 5% of your Property, Plant and Equipment (PPE) for the company as a whole. Use the formula of PPE x 5% = Year 1 Capital. 32 Calculate depreciation for Year O capital using the straight line depreciation method. This will be the total capital for Year O divided by 5 years. Depreciation will start in Year 1 and completed in Year 5. 33 D. Calculate depreciation for Year 1 capital using the straight line depreciation method. This will be the total capital for Year 1 divided by 5 years. Depreciation will start in Year 2 and completed in Year 6, after the project completes. 34 E. Calculate the total depreciation for each year. 35 36 Calculate the EBIT for each year. Use the formula of EBIT = EBITDA - total deprecation. 37 38 6. Calculate the taxes for the project earnings. This would be Taxes = EBIT x 21% tax rate (given in problem). 39 40 7. Calculate the unlevered net income. This would be UNI = EBIT - Taxes. e. C. 5 Assumptions Year 1 Sales 3.0% of 2020 Sales Revenue Growth Base Case Fixed at 0% Fixed at 10% 1 1 2 3 15.0% 15% 0% 10% 10.0% 10% 0% 10% 5.0% 5% 0% 10% 5.0% 5% 0% 10% 2020 Data Sales EBITDA Calculated EBITDA DE PPE AR Inventory Payables NWC 73,621,000 12,620,000 12,620,000 14,726,000 18,387,000 1,839,000 4,908,000 15,318,000 EBITDA/Sales | NWC/Sales Tax Rate Cost of Capital (WACC) 17.1% from 2020 10.0% 5.0% of 2018 PPE 20.8% from 2020 21.0% 12.0% USE THIS DATA PROVIDED ABOVE INSTEAD OF LOOKING UP ON YAHOO FINANCE. Year 0 Year 3 Year 4 Year 5 Year 6 Year 1 2,208,630 Year 2 2,539,925 Complete years 2 through 6. Formulas curently showing as #VALUE or #NUM will be update once all fields are entered First Year Sales 2% 3% 4% #VALUE! #VALUE! #VALUE! #VALUE! #NUM! #NUM! #NUM! #NUM! 378,600 378,600 79,506 299,094 Revenue yoy growth EBITDA Depreciation EBIT Taxes Unlevered Net Income Depreciation Increase in NWC CapEx Free Cash Flow Discount Factor Present Value NPV IRR Cost of Capital 10% % 12% 15% #VALUE! #VALUE! #VALUE! #VALUE! #NUM! #NUM! #NUM! #NUM! 459,540 1,472,600 736,300 (1,472,600) (896,746) 1.000x 1.120x #VALUE! #VALUE! #VALUE! #NUM! Revene Growth 1 2 3 #VALUE! #VALUE! #VALUE! #VALUFI #NUM! #NUM! #NUM! #NUM! Base Case Fixed at 0% Fixed at 10% Year 2 Year 3 Year 4 Year 5 Year 6 Depreciation Schedule CapEx Yr 0 CapEx Yr 1 Depreciation Capex 1,472,600 736,300 Year 1 294,520 Year 0 Year 2 Year 3 Year 4 Year 5 Year 6 NWC Requirements NWC (12% of Sales) Increase in NWC Year 1 459,540 459,540 AutoSave Us Wk 3 Case Study Ch 8 Template.xlsx - Last Modified: 5m ago Search Taylor File Home Insert Draw Page Layout Formulas Data Review View M-Files (Z:) M-Files (M:) Share Comme Arial AutoSum 10 A === a Wrap Text General 1 ti Normal 2 Normal 2 2 Normal 4 Normal 5 Normal 6 X Cut LECopy Format Painter AB O Fill Paste BIU BV MA 35 3 Merge & Center Normal 9 $ % Normal Good Bad Insert Delete Format Conditional Format as Normal 8 Formatting Table Clear Sort & Find & Filter Select Editing Clipboard Font Alignment Number Styles Cells F6 X fic J L Y Z AB AC 5 C. B D E F G H 1 K M N 0 Q R s T U V w X 1 Case Study on IBM 2 tep process to solve 3 1. Find your data from the 2020 financials on IBM and add it to the data box. This data will be based on the company as a whole. 4 2020 Data THE DATA YOU SHOULD USE IS PROVIDED 6 Sales FOR YOU ON THE NEXT PAGE. USE THE 7 EBITDA Use the EBIT + the line for Depreciation & Amortization PROVIDED DATA 8 Calculated EBITDA Same as above 9 PPE Use net PPE value 10 AR Use Receivables - allowance for doubtful accounts 11 Inventory 12 Payables 13 NWC Net working capital = (A/R + Inventory) - Payables 14 15 2. Determine your revenues each year for the project. 16 a. Revenue for Year 0 = 0 17 b. Revenue for Year 1 = Sales for company as a whole x 3% 18 Revenue for Year 2 is 15% more than year 1 = Year 1 Revenue x 1.15 19 d. Revenue for Year 3 is 10% more than year 2 = Year 2 Revenue x 1.10 20 Revenue for Year 4 is 5% more than year 3 = Year 3 Revenue x 1.05 21 f. Revenue for Year 5 is 5% more than year 4 = Year 4 Revenue x 1.05 22 23 3. Calculate EBITDA using the formula provided in the problem of EBITDA/Sales = 17.1%. 24 Work backwards with the info we have, since you know the sales for the project (calculated in step 2). Calculate for each year of the project. This would change the formula to be 25 26 EBITDA = Sales x 17.1% 27 28 4. Calculate deprecation, which is based on the outlay of cash for the project from Year 0 and Year 1. This is based on your invested capital. 29 30 A. Calculate your capital expenses for year 0. From the problem data, this is based on 10% of your property, Plant and Equipment (PPE) for the company as a whole. Use the formula of PPE x 10% = Year O Capital 31 B. Calculate your capital expenses for year 1. From the problem data, this is based on 5% of your Property, Plant and Equipment (PPE) for the company as a whole. Use the formula of PPE x 5% = Year 1 Capital. 32 Calculate depreciation for Year O capital using the straight line depreciation method. This will be the total capital for Year O divided by 5 years. Depreciation will start in Year 1 and completed in Year 5. 33 D. Calculate depreciation for Year 1 capital using the straight line depreciation method. This will be the total capital for Year 1 divided by 5 years. Depreciation will start in Year 2 and completed in Year 6, after the project completes. 34 E. Calculate the total depreciation for each year. 35 36 Calculate the EBIT for each year. Use the formula of EBIT = EBITDA - total deprecation. 37 38 6. Calculate the taxes for the project earnings. This would be Taxes = EBIT x 21% tax rate (given in problem). 39 40 7. Calculate the unlevered net income. This would be UNI = EBIT - Taxes. e. C. 5 Assumptions Year 1 Sales 3.0% of 2020 Sales Revenue Growth Base Case Fixed at 0% Fixed at 10% 1 1 2 3 15.0% 15% 0% 10% 10.0% 10% 0% 10% 5.0% 5% 0% 10% 5.0% 5% 0% 10% 2020 Data Sales EBITDA Calculated EBITDA DE PPE AR Inventory Payables NWC 73,621,000 12,620,000 12,620,000 14,726,000 18,387,000 1,839,000 4,908,000 15,318,000 EBITDA/Sales | NWC/Sales Tax Rate Cost of Capital (WACC) 17.1% from 2020 10.0% 5.0% of 2018 PPE 20.8% from 2020 21.0% 12.0% USE THIS DATA PROVIDED ABOVE INSTEAD OF LOOKING UP ON YAHOO FINANCE. Year 0 Year 3 Year 4 Year 5 Year 6 Year 1 2,208,630 Year 2 2,539,925 Complete years 2 through 6. Formulas curently showing as #VALUE or #NUM will be update once all fields are entered First Year Sales 2% 3% 4% #VALUE! #VALUE! #VALUE! #VALUE! #NUM! #NUM! #NUM! #NUM! 378,600 378,600 79,506 299,094 Revenue yoy growth EBITDA Depreciation EBIT Taxes Unlevered Net Income Depreciation Increase in NWC CapEx Free Cash Flow Discount Factor Present Value NPV IRR Cost of Capital 10% % 12% 15% #VALUE! #VALUE! #VALUE! #VALUE! #NUM! #NUM! #NUM! #NUM! 459,540 1,472,600 736,300 (1,472,600) (896,746) 1.000x 1.120x #VALUE! #VALUE! #VALUE! #NUM! Revene Growth 1 2 3 #VALUE! #VALUE! #VALUE! #VALUFI #NUM! #NUM! #NUM! #NUM! Base Case Fixed at 0% Fixed at 10% Year 2 Year 3 Year 4 Year 5 Year 6 Depreciation Schedule CapEx Yr 0 CapEx Yr 1 Depreciation Capex 1,472,600 736,300 Year 1 294,520 Year 0 Year 2 Year 3 Year 4 Year 5 Year 6 NWC Requirements NWC (12% of Sales) Increase in NWC Year 1 459,540 459,540