1

M3 Group Assignment: Data Case (Chapter 8)

INSTRUCTIONS

1. Fill in all blanks of the template provided in M3DataCase.xlsx using the information

and addressing the questions in the text below

2. Report the results of the NPV and IRR for the base case and alternative assumptions

on first year sales, cost of capital, and revenue growth answering the questions below.

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 IBM’s

Net Property, Plant, and Equipment (PPE) at the end of the latest fiscal year for which data is available

(year 0). The project will then require an additional investment equal to 50% of the initial capital

expenditure in the first year of the project (year 1).

The product is expected to have a life of five years.

First-year revenues for the new product are expected to be 3% of IBM’s total revenue for the latest fiscal

year for which data is available. The new product’s 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.

Compute the Free Cash Flow for each year.

a. Assume that the project’s profitability will be similar to IBM’s existing projects in the latest fiscal year

and estimate (revenues – costs) each year by using the latest EBITDA/Sales profit margin. Calculate

EBITDA as EBIT + Depreciation expense from the cash flow statement.

b. Determine the annual depreciation by assuming IBM depreciates these assets by the straight-line

method over a five-year life.

c. Calculate the net working capital required each year by assuming that the level of NWC will be a

constant percentage of the project’s sales. Use IBM’s NWC/Sales for the latest fiscal year to estimate the

required percentage. (Use only accounts receivable, accounts payable, and inventory to measure working

capital. Other components of current assets and liabilities are harder to interpret and not necessarily

reflective of the project’s required NWC—for example, IBM’s cash holdings.)

d. To determine the free cash flow, deduct the additional capital investment and the change in net working

capital each year.

2

Determine the NPV and the IRR of the project calculated using Excel’s IRR function under

different assumptions about first year sales, cost of capital, and revenue growth (sensitivity

analysis). Report your results by choosing the option presented in the following multiple

choice questions

(Base case) First year sales are 3%, the cost of capital is 12%, and revenue growth is option 1 in the C6

cell, The NPV and IRR are closest to:

[A] 51,543; 13.2%

[B] 30,767; 12.5%

[C] 41,643; 15.2%

[D] 63,496; 16.8%

First year sales are 2%, the cost of capital is 12%, and revenue growth is option 1 in the C6 cell. The NPV

and IRR are closest to:

[A] -421,080; 4.2%

[B] -361,280; 5.3%

[C] -241,286; 6.1%

[D] -31,423; 7.3%

First year sales are 4%, the cost of capital is 12%, and revenue growth is option 1 in the C6 cell. The NPV

and IRR are closest to:

[A] 343,678; 16.8%

[B] 482,614; 18.8%

[C] 550,856; 20.8%

[D] 601,678; 22.4%

First year sales are 3%, the cost of capital is 10%, and revenue growth is option 1 in the C6 cell. The NPV

and IRR are closest to:

[A] 201,453; 13.6%

[B] 165,383; 12.5%

[C] 101,423; 11.6%

[D] 95,622; 9.8%

First year sales are 3%, the cost of capital is 15%, and revenue growth is option 1 in the C6 cell. The NPV

and IRR are closest to:

[A] -145,516; 12.5%

[B] 103,060; 11.1%

[C] 99,301; 10.0%

[D] 81,345; 8.7%

3

First year sales are 3%, the cost of capital is 12%, and revenue growth is option 2 in the C7 cell (fixed at

0%). The NPV and IRR are closest to:

[A] -200,305; 8.3%

[B] -136,657; 9.6%

[C] -103,567; 7.9%

[D] – 45,356; 5.1%

First year sales are 3%, the cost of capital is 12%, and revenue growth is option 3 in the C8 cell (fixed at

10%). The NPV and IRR are closest to:

[A] 21,115; 12.9%

[B] 18,957; 12.3%

[C] 11,652; 10.4%

[D] 8,456; 7.3%

Rubric

a. Complete spreadsheet (3 points)

b. Multiple choice questions (7 points)

4

Notes on the Excel File

1. All Assumptions are collected in the panel below

2. The data to retrieve from the Statements worksheet need to be reported in

Assumptions

Year 1 Sales 3.0% of 2018 Sales

Revenue Growth 1 15.0% 10.0% 5.0% 5.0%

Base Case 1 15% 10% 5% 5%

Fixed at 0% 2 0% 0% 0% 0%

Fixed at 10% 3 10% 10% 10% 10%

EBITDA/Sales 20.8% from 2018

Cap Ex 10.0% 5.0% of 2018 PPE

NWC/Sales 31.3% from 2018

Tax Rate 21.0%

Cost of Capital (WACC) 12.0%

2018 Data

Sales From Statements

EBITDA From Statements

Calculated EBITDA same as EBITDA in K4

PPE Net property, plant and equipment

AR Net receivables I9

Inventory Inventory

Payables Accounts payable

NWC AR+Inventory-Payables

5

3. Using the 2018 data and the Assumptions, formulas are inserted to complete the panels

that allow to estimate free cash flow, NPV and IRR

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6

Revenue

yoy growth

EBITDA

Depreciation

EBIT

Taxes

Unlevered Net Income

Depreciation

Increase in NWC

CapEx

Free Cash Flow

Discount Factor

Present Value

NPV

IRR

Depreciation Schedule CapEx Year 1 Year 2 Year 3 Year 4 Year 5 Year 6

CapEx Yr 0

CapEx Yr 1

Depreciation

NWC Requirements Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6

NWC (12% of Sales)

Increase in NWC