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
