Question

You are the financial analyst for a tennis racket manufacturer. The company is considering using a graphitelike material in its tennis rackets. The company has estimated the information in the following table about the market for a racket with the new material. The company expects to sell the racket for 4 years. The equipment required for the project will be depreciated on a straight-line basis and has no salvage value. The required return for projects of this type is 12 percent and the company has a 21 percent tax rate.

		Pessimistic				Expected				Optimistic
	Market size		126,000				136,000				148,000
	Market share		20	%			24	%			26	%
	Selling price	$	144			$	149			$	153
	Variable costs per unit	$	97			$	93			$	90
	Fixed costs per year	$	969,000			$	914,000			$	884,000
	Initial investment	$	1,330,000			$	1,180,000			$	1,160,000

Calculate the NPV for each case for this project. Assume a negative taxable income generates a tax credit. (A negative amount should be indicated by a minus sign. Do not round intermediate calculations and round your answers to 2 decimal places, e.g., 32.16.)

Answer 1

Unit sale = market size* market share
Depreciation = Initial investment/Number of years
	Unit Sales	Price	Variable costs	Fixed costs
Pessimistic	25200	144	97	969000
Expected	32640	149	93	914000
Optimistic	38480	153	90	884000
Operating cashflow = (Sales - variable Costs)*(1-Tax) + Tax*Depreciation
Pessimistic:
= ((144-97) *25200-96900)) (1-0.21)+ 0.21* 332500
=(47*25200-969000)*0.79 +0.21*332500
=215400*0.79+69825
=239991.00
Expected
= ((149-93 )* 32640- 914000)) (1-0.21) +0.21*295000
= (56*32640 -914000) (0.79) +0.21*295000
= 913840*0.79+ 61950
= 783883.60
Optimistic
= ((153-90)*38480 - 884000 ) *(1-0.21) + 0.21*290000
= (63*38480 -884000) *0.79 + 60900
=1540240*0.79+60900
=1277689.60
Pessimistic:	H	I	J	K
	N	cash flow	Discount factor = (1/(1+r)^n	Present value
77	Year		12%
78
79	0	1330000.00	1.000000000	1330000.00
80	1	239991.00	0.892857143	214277.68
81	2	239991.00	0.797193878	191319.36
82	3	239991.00	0.711780248	170820.85
83	4	239991.00	0.635518078	152518.62
NPV				2058936.51
Expected	H	I	J	K
	N	cash flow	Discount factor = (1/(1+r)^n	Present value
89	Year		12%
90
91	0	1180000.00	1.000000000	1180000.00
92	1	783883.60	0.892857143	699896.07
93	2	783883.60	0.797193878	624907.21
94	3	783883.60	0.711780248	557952.86
95	4	783883.60	0.635518078	498172.20
NPV				3560928.34
Optimistic	H	I	J	K
	N	cash flow	Discount factor = (1/(1+r)^n	Present value
101	Year		12%
102
103	0	1160000.00	1.000000000	1160000.00
104	1	1277689.60	0.892857143	1140794.29
105	2	1277689.60	0.797193878	1018566.33
106	3	1277689.60	0.711780248	909434.22
107	4	1277689.60	0.635518078	811994.84
NPV				5040789.67

FORMULA:

	G	H	I	J	K	L
49		Unit Sales	Price	Variable costs	Fixed costs	Depreciation
50	Pessimistic	=126000*20%	144	97	969000	=1330000/4
51	Expected	=136000*24%	149	93	914000	=1180000/4
52	Optimistic	=148000*26%	153	90	884000	=1160000/4
	Pessimistic:	H	I	J	K
		N	cash flow	Discount factor = (1/(1+r)^n	Present value
	77	Year		0.12
	78
	79	0	1330000	=1/(1+$J$77)^H79	=I79*J79
	80	1	239991	=1/(1+$J$77)^H80	=I80*J80
	81	2	239991	=1/(1+$J$77)^H81	=I81*J81
	82	3	239991	=1/(1+$J$77)^H82	=I82*J82
	83	4	239991	=1/(1+$J$77)^H83	=I83*J83
	NPV				=SUM(K79:K83)
	Expected	H	I	J	K
		N	cash flow	Discount factor = (1/(1+r)^n	Present value
	89	Year		0.12
	90
	91	0	1180000	=1/(1+$J$89)^H91	=I91*J91
	92	1	783883.6	=1/(1+$J$89)^H92	=I92*J92
	93	2	783883.6	=1/(1+$J$89)^H93	=I93*J93
	94	3	783883.6	=1/(1+$J$89)^H94	=I94*J94
	95	4	783883.6	=1/(1+$J$89)^H95	=I95*J95
	NPV				=SUM(K91:K95)
	Optimistic	H	I	J	K
		N	cash flow	Discount factor = (1/(1+r)^n	Present value
	101	Year		0.12
	102
	103	0	1160000	=1/(1+$J$101)^H103	=I103*J103
	104	1	1277689.6	=1/(1+$J$101)^H104	=I104*J104
	105	2	1277689.6	=1/(1+$J$101)^H105	=I105*J105
	106	3	1277689.6	=1/(1+$J$101)^H106	=I106*J106
	107	4	1277689.6	=1/(1+$J$101)^H107	=I107*J107
	NPV				=SUM(K103:K107)