Calculate project cash flows, NPV and IRR

Extracted Content from Question Files:

Corporate Finance Spreadsheet Templates
MAIN MENU - Chapter 7

Problem 7-10 Problem 7-15 Problem 7-18
Problem 7-21 Problem 7-28 Problem 7-30

Fundamentals of Corporate Finance by Brealey, Myers, and Marcus -- Third Edition
Copyright © 2001 Irwin/McGraw-Hill and KMT Software, Inc. (www.kmt.com)

File: 18827.xls Copyright © 1999 Irwin/McGraw-Hill Printed: 9/10/2012
Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 3rd Edition

Problem 7-10 Objective
Calculate operating cash flows

Student Name:
Course Name:
Student ID:
Course Number:

Laurel's Lawn Care, Ltd., has a new mower line that can generate revenues of $120,000 per year. Direct
production costs are $40,000 and the fixed costs of maintaining the lawn mower factory are $15,000 a
year. The factory originally cost $1 million and is being depreciated for tax purposes over 25 years using
straight-line depreciation. Calculate the operating cash flows of the project if the firm's tax bracket is 35
percent.

Solution
Problem 7-10
Instructions
Enter formulas to calculate depreciation and taxes.

Revenues $120,000
Variable costs 40,000
Fixed costs 15,000
Depreciation FORMULA
Pretax profit $65,000
Taxes FORMULA
Net income $65,000
Depreciation
Cash flow $65,000

File: 18827.xls Copyright © 2001 Irwin/McGraw-Hill Printed: 9/10/2012
Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 3rd Edition

Problem 7-15 Objective
Calculate changes in net working capital

Student Name:
Course Name:
Student ID:
Course Number:

A firm's balance sheets for year-end 2000 and 2001 contain the following data. What happened to
investment in net working capital during 2001? All items are in millions of dollars.

Dec. 31, 2000 Dec. 31, 2001
Accounts receivable 32 35
Inventories 25 30
Accounts payable 12 25

Solution
Problem 7-15
Instructions
Enter formulas to calculate the NWC for both 2000 and 2001.
NWC 2000 FORMULA
NWC 2001 FORMULA
Change 0

File: 18827.xls Copyright © 2001 Irwin/McGraw-Hill Printed: 9/10/2012
Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 3rd Edition

Problem 7-18 Objective
Calculate cash flows and NPV

Student Name:
Course Name:
Student ID:
Course Number:

Bottoms Up Diaper Service is considering the purchase of a new industrial washer. It can purchase the washer
for $6,000 and sell its old washer for $2,000. The new washer will last for 6 years and save $1,500 in expenses.
The opportunity cost of capital is 15 percent, and the firm's tax rate is 40 percent.
a. If the firm uses straight-line depreciation to an assumed salvage value of zero over a 6-year life, what are
the cash flows of the project in Years 0-6? The new washer will in fact have a zero salvage value after 6 years,
and the old washer is fully depreciated.
b. What is the project NPV?
c. What will NPV be if the firm uses ACRS depreciation with a 5-year tax life.

Solution
Problem 7-18
Instructions
Enter formulas to calculate cash flow and use the MS Excel NPV function to calculate Net Present Value.
a. If the firm uses straight-line depreciation to an assumed salvage value of zero over a 6-year life, what are
the cash flows of the project in years 0-6? The new washer will in fact have a salvage value after 6 years,
and the old washer is fully depreciated.
Earnings before depreciation $1,500
Depreciation FORMULA
Taxable income $1,500
Taxes FORMULA
Net income $1,500
Depreciation
Operating cash flow $1,500 Years 1-6

Net cash flow at time 0 FORMULA

b. What is the project NPV?
Project NPV FORMULA
c. What will NPV be if the firm uses ACRS depreciation with a 5-year tax life.
Washer cost $6,000
Year ACRS% Depreciation Cash Flow
1 20.00% FORMULA $900.00
2 32.00% FORMULA 900.00
3 19.20% FORMULA 900.00
4 11.52% FORMULA 900.00
5 11.52% FORMULA 900.00
6 5.76% FORMULA 900.00
NPV FORMULA

File: 18827.xls Copyright © 2001 Irwin/McGraw-Hill Printed: 9/10/2012
Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 3rd Edition

Problem 7-21 Objective
Calculate project cash flows, NPV, and IRR

Student Name:
Course Name:
Student ID:
Course Number:

Revenues generated by a new fad product in each of the next 5 years are forecasted as follows:
Year Revenues
1 $40,000
2 30,000
3 20,000
4 10,000
Thereafter 0
Expenses are expected to be 40 percent of revenues, and working capital required in each year is expected
to be 20 percent of revenues in the following year. The product requires an immediate investment of
$50,000 in plant and equipment.
a. What is the initial investment in the product? Remember working capital.
b. If the plant and equipment are depreciated over 4 years to a salvage value of zero using straight-line
depreciation, and the firm's tax rate is 40 percent, what are the project cash flows in each year?
c. If the opportunity cost of capital is 10 percent, what is the project NPV?
d. What is the project IRR?

Solution
Problem 7-21
Instructions
Use formulas and the MS Excel NPV and IRR functions to solve this problem.
a. What is the initial investment in the product? Remember working capital.
Initial outlay $50,000
Working capital FORMULA
Initial investment $50,000

b. If the plant and equipment are depreciated over 4 years to a salvage value of zero using straight-line
depreciation, and the firm's tax rate is 40 percent, what are the project cash flows in each year?
Working Cash
Year Sales Expenses Capital Depreciation Flow
0 ($50,000)
1 $40,000 FORMULA FORMULA FORMULA $24,000
2 $30,000 FORMULA FORMULA $18,000
3 $20,000 FORMULA FORMULA $12,000
4 $10,000 FORMULA FORMULA $6,000
c. If the opportunity cost of capital is 10 percent, what is the project NPV?
($191.93)
NPV
d. What is the project IRR?
FORMULA
IRR

File: 18827.xls Copyright © 2001 Irwin/McGraw-Hill Printed: 9/10/2012
Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 3rd Edition

Problem 7-28 Objective
Calculate cash flows and NPV

Student Name:
Course Name:
Student ID:
Course Number:

Better Mousetraps has developed a new trap. It can go into production for an initial investment in equipment
of $6 million. The equipment will be depreciated straight-line over 5 years to a value of zero, but in fact
it can be sold after 5 years for $500,000. The firm believes that working capital at each date must be
maintained at a level of 10 percent of next year's forecasted sales. The firm estimates production costs
equal to $1.50 per trap and believes that the traps can be sold for $4 each. Sales forecast are given in the
following table. The project will come to an end in 5 years, when the trap becomes technologically obsolete.
The firm's tax bracket is 35 percent, and the required rate of return on the project is 12 percent. What is the
project's NPV?
Year: 1 2 3 4 5 Thereafter
Sales (millions) 0.5 0.6 1 1 0.6 0

Solution
Problem 7-28
Instructions
Using whenever possible enter formulas to calculate the unknown values.
0 1 2 3 4 5
Sales (Traps) 0.000 0.500 0.600 1.000 1.000 0.600
Revenue FORMULA FORMULA FORMULA FORMULA FORMULA
Working capital 0.000 0.000 0.000 0.000 0.000 0.000
FORMULA FORMULA FORMULA FORMULA FORMULA FORMULA
Change in WC
Revenues 0.000
Expense FORMULA FORMULA FORMULA FORMULA FORMULA
Depreciation FORMULA 1.200 1.200 1.200 1.200
Pretax profit 0.000 0.000 -1.200 -1.200 -1.200 -1.200
Tax 0.000 0.000 -0.420 -0.420 -0.420 -0.420
After-tax profit 0.000 0.000 -0.780 -0.780 -0.780 -0.780
Cash flow from op. 0.000 0.000 0.420 0.420 0.420 0.420
Cash flow
CF: capital invest. -6.000 0.000 0.000 0.000 0.000 0.325
CF from WC 0.000 0.000 0.000 0.000 0.000 0.000
CF from op. 0.000 0.000 0.420 0.420 0.420 0.420
Total -6.000 0.000 0.420 0.420 0.420 0.745
PV@ 12% -6.000 0.000 0.335 0.299 0.267 0.423
-4.677
Net present value

File: 18827.xls Copyright © 2001 Irwin/McGraw-Hill Printed: 9/10/2012
Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 3rd Edition

Problem 7-30 Objective
Calculate cash flows, NPV, and IRR

Student Name:
Course Name:
Student ID:
Course Number:

PC Shopping Network may upgrade its modem pool. It last upgraded two years ago when it spent $115 million
on equipment with an assumed life of 5 years and an assumed salvage value of $15 million for tax purposes.
The firm uses straight line depreciation. The old equipment can be sold today for $80 million. A new
modem pool can be installed today for $150 million. This will have a 3-year life and will be depreciated to zero
using straight line depreciation. The new equipment will enable the firm to increase sales by $15 million per
year and decrease operating costs by $20 million per year. At the end of three years, the new equipment will
be worthless. Assume the firm's tax rate is 35% and the discount rate for a project of this type is 12%.
a. What is the net cash flow at time 0 if the old equipment is replaced?
b. What are the incremental cash flows in years 1,2, and 3?
c. What are the NPV and IRR of the replacement project?

Solution
Problem 7-30
Instructions
Use the assumptions shown below to solve the requirements of this problem. Use the NPV and IRR functions
to solve part 2.

Assumptions
Book value of old machine $75
Tax rate 35%
Selling price of old machine $80
Estimated life of new equipment (years) 3
Incremental sales per year $15
Incremental cost savings per year $20
Discount rate 12%

a. What is the net cash flow at time 0 if the old equipment is replaced?

Cash flow from sale of old machine FORMULA
Cost of new machine
Net cash flow at time 0 $0.00

b. What are the incremental cash flows in years 1,2, and 3?

Year 0 Year 1 Year 2 Year 3
After-tax incremental revenue FORMULA FORMULA FORMULA
After-tax incremental cost savings FORMULA FORMULA FORMULA
Depreciation tax savings FORMULA FORMULA FORMULA
Annual cash flow $0.00 $0.00 $0.00 $0.00

c. What are the NPV and IRR of the replacement project?
NPV FORMULA
IRR FORMULA

File: 18827.xls Copyright © 2001 Irwin/McGraw-Hill Printed: 9/10/2012