Question

1 Approved Answer

Posted on Jul 17, 2024

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

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, and a 50% increase after the second year.

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.

a. Use Excel to determine the NPV of the project with a 12% cost of capital. Also calculate the IRR of the project using Excels IRR function.

note: Use the 2018 data and the Assumptions, formulas are inputed to complete the panels that allow to estimate free cash flow, NPV and IRR. I already did everything besides a. (see screenshot).

Please show the formula used- thank you!

image text in transcribed

Assumptions Year 1 Sales 3.0% of 2018 Sales 1 1 Revenue Growth Base Case Fixed 0% Foed at 10% 15.0% 15% 0% 10% 10.0% 10% 0% 10% 5.0% 5% 0% 10% 5.0% 5% 0% 10% 2018 Data Sales EBITDA Calculated EBITDA PPE AR Inventory Payables NWC 79,500,000 16,545,000 16.545,000 10.793,000 29.820,000 1,682,000 6.558,000 24,944.000 2 3 EBITDA Sales Cap Ex INWC/des Tax Rale Cost of Capital (WACC) 20.8% from 2018 10.0% 5.0% of 2018 PPE 31.3% from 2018 21.0% 12.0% Year 0 Year 6 NPV First Year Sales 2% 3% 4% Year 3 3,020,441 10% 627,883 323.790 304,093 IRR 0.00% % 0.00% 0.00% - Year 2 2,745,855 15% 570,803 323,790 247,013 51,873 195, 140 323,790 112,248 Year 1 2,387,700 3% 496,350 215,860 280,490 58,903 221,587 215,860 748,320 539,850 (850,523) 1.120x Year 4 3,171,463 5% 659,277 323.790 335,487 70,452 265,035 323,790 47,331 Revenue yoy growth EBITDA Depreciation EBIT Taxes Unlevered Net Income Depreciation Increase in NWC Capex Free Cash Flow Discount Factor Present Value NPY IRR NPV Year 5 3,330,036 5% 692.241 323.790 368,451 77,375 291,076 323.790 49,698 63.859 107,930 (107,930) (22,665) (85,265) 107.930 (1.043,654) 240,233 323,790 88,057 Cost of Capital 10% 12% 15% - IRR 0.00% 0.00% 0.00% NPV (1,079,300) (1,079,300) 1.000x 406,682 1.254 477,966 1.406x 541,494 1.574x 565,168 1.762x 1,066,319 1.974x Revene Growth 1 2 3 IRR 0.00% Base Case 0.00% Fixed at 0% 0.00% Fixed at 10% Year 6 Depreciation Schedule CapEx Yr 0 CapEx Yr 1 Depreciation Capex 1,079,300 539,650 Year 1 215,860 Year 2 215,860 107,930 323,790 Year 3 215 860 107,930 323,790 Year 4 215,860 107.930 323,790 Year 5 215.860 107.930 323.790 107,930 107.930 215,860 Year o Year 6 NWC Requirements NWC (12% of Sales) Increase in NWC Year 1 748,320 748,320 Year 2 880,568 112,248 Year 3 946.626 B6 057 Year 4 993.956 47,331 Year 5 1,043,654 49.699 (1,043,654) Assumptions Year 1 Sales 3.0% of 2018 Sales 1 1 Revenue Growth Base Case Fixed 0% Foed at 10% 15.0% 15% 0% 10% 10.0% 10% 0% 10% 5.0% 5% 0% 10% 5.0% 5% 0% 10% 2018 Data Sales EBITDA Calculated EBITDA PPE AR Inventory Payables NWC 79,500,000 16,545,000 16.545,000 10.793,000 29.820,000 1,682,000 6.558,000 24,944.000 2 3 EBITDA Sales Cap Ex INWC/des Tax Rale Cost of Capital (WACC) 20.8% from 2018 10.0% 5.0% of 2018 PPE 31.3% from 2018 21.0% 12.0% Year 0 Year 6 NPV First Year Sales 2% 3% 4% Year 3 3,020,441 10% 627,883 323.790 304,093 IRR 0.00% % 0.00% 0.00% - Year 2 2,745,855 15% 570,803 323,790 247,013 51,873 195, 140 323,790 112,248 Year 1 2,387,700 3% 496,350 215,860 280,490 58,903 221,587 215,860 748,320 539,850 (850,523) 1.120x Year 4 3,171,463 5% 659,277 323.790 335,487 70,452 265,035 323,790 47,331 Revenue yoy growth EBITDA Depreciation EBIT Taxes Unlevered Net Income Depreciation Increase in NWC Capex Free Cash Flow Discount Factor Present Value NPY IRR NPV Year 5 3,330,036 5% 692.241 323.790 368,451 77,375 291,076 323.790 49,698 63.859 107,930 (107,930) (22,665) (85,265) 107.930 (1.043,654) 240,233 323,790 88,057 Cost of Capital 10% 12% 15% - IRR 0.00% 0.00% 0.00% NPV (1,079,300) (1,079,300) 1.000x 406,682 1.254 477,966 1.406x 541,494 1.574x 565,168 1.762x 1,066,319 1.974x Revene Growth 1 2 3 IRR 0.00% Base Case 0.00% Fixed at 0% 0.00% Fixed at 10% Year 6 Depreciation Schedule CapEx Yr 0 CapEx Yr 1 Depreciation Capex 1,079,300 539,650 Year 1 215,860 Year 2 215,860 107,930 323,790 Year 3 215 860 107,930 323,790 Year 4 215,860 107.930 323,790 Year 5 215.860 107.930 323.790 107,930 107.930 215,860 Year o Year 6 NWC Requirements NWC (12% of Sales) Increase in NWC Year 1 748,320 748,320 Year 2 880,568 112,248 Year 3 946.626 B6 057 Year 4 993.956 47,331 Year 5 1,043,654 49.699 (1,043,654)