Accounting Question

read instructions

Input Area:
Year
units
price/unit
Revenues
cost per unit
COGS
Fixed Costs (advertisement cost)
Year
0
$3.00
$0.50
1
8
$3.15
$25.20
$0.53
$4.20
$10.00
2
10
$3.31
$33.08
$0.55
$5.51
$6.00
Beg BV Dep
End BV
0
10
10
1
10
2.5
7.5
2
7.5
2.5
5
3
5
2.5
2.5
4
2.5
2.5
0
3
12
$3.47
$41.67
$0.58
$6.95
$4.00
4
8
$3.65
$29.17
$0.61
$4.86
$2.00
Output Area:
Pro Forma Income Statements
Year
Revenues
COGS
Fixed Costs
Dep
EBIT
Taxes(40%)
NI
1
2
3
4
Total Cash Flow
1
2
3
4
OCF
Year
OCF
Cap Spending
Total CF
PV @ 12%
NPV =
0
Spreadsheet Problem
Due on 10/15
1. You are working with a consumer product company on estimating the expected
cash flows from a new brand of toothpaste that they plan to introduce. From your
prior experiences with earlier introductions of similar products, you have
collected the following information:
• You are likely to sell 8 million tubes in the first year that you introduce the project,
10 million in the second year, 12 million in the third year and back down to 8
million in the fourth year. At the end of the fourth year, you will have to replace
the brand with a new one.
• You price toothpaste at $ 3 a tube currently, and expect to increase prices at 5% a
year for the next 4 years.
• You will have to advertise most heavily in the first year, spending $ 10 million on
advertising, but you expect this to drop to $ 6 million in year 2, $ 4 million in year
3 and $ 2 million in year 4.
• Your costs associated with producing the toothpaste are $ 0.50 a tube currently, and
you expect this to increase at 5% a year for the next 4 years.
• You will have to invest $ 10 million in new equipment at an old plant to create the
manufacturing facilities and you expect to depreciate this expenditure straight line
over the 4 years down to zero.
• You have a 40% tax rate.
• The firm’s weighted average cost of capital is 12%. This is the required return on
the project.
What is the NPV?