Question

Consider a project to supply 105 million postage stamps per year to the U.S. Postal Service for the next five years. You have an idle parcel of land available that cost $1,730,000 five years ago; if the land were sold today, it would net you $1,805,000 aftertax. The land can be sold for $1,753,000 after taxes in five years. You will need to install $5.6 million in new manufacturing plant and equipment to actually produce the stamps; this plant and equipment will be depreciated straight-line to zero over the project’s five-year life. The equipment can be sold for $700,000 at the end of the project. You will also need $600,000 in initial net working capital for the project, and an additional investment of $55,000 in every year thereafter. Your production costs are .53 cents per stamp, and you have fixed costs of $1,100,000 per year. If your tax rate is 21 percent and your required return on this project is 11 percent, what bid price should you submit on the contract?

Answer 1

Formula sheet

A1	B	C	D	E	F	G	H	I	J
2
3		Bid Price will be the price at which NPV of cash flows of the project is zero.
4		To calculated NPV, Free cash flow can be calculated as follows:
5		Free Cash Flow = Operating Cash Flow - Capital Expenditures - Change in working capital
6		Operating Cash Flow = EBIT*(1-Tax Rate)+Depreciation
7		Tax Rate	0.21
8		MARR	0.11
9		Bid Price	0.550545335444076
10		Immediate investment in plant and Equipment	5600000
11		Opportunity cost for land	1805000
12		Initial investment in working capital	600000
13		Annual Investment in working capital	55000
14		Production cost per stamp	0.53
15		Number of stamps sold per year	105000000
16		b)
17		Depreciation each year can be calculated as follows:
18		Machine Cost	=D10
19		Life of Machine	5	years
20		Salvage value	0
21		Depreciation per year	=(Investment - Salvage Value)/Expected life of equipment
22			=(D18-D20)/D19	=(D18-D20)/D19
23
24		Free cash flow can be calculated as followed:
25		Year	0	1	2	3	4	5
26		Revenue		=$D$15*$D$9	=$D$15*$D$9	=$D$15*$D$9	=$D$15*$D$9	=$D$15*$D$9
27		Production cost		=-$D$15*$D$14	=-$D$15*$D$14	=-$D$15*$D$14	=-$D$15*$D$14	=-$D$15*$D$14
28		Depreciation		=-$D$22	=-$D$22	=-$D$22	=-$D$22	=-$D$22
29		Operating Income Before Tax (EBIT)		=SUM(E26:E28)	=SUM(F26:F28)	=SUM(G26:G28)	=SUM(H26:H28)	=SUM(I26:I28)
30		Tax (@21%)		=-E29*$D$7	=-F29*$D$7	=-G29*$D$7	=-H29*$D$7	=-I29*$D$7
31		After Tax operating income (EBIT*(1-T))		=E29+E30	=F29+F30	=G29+G30	=H29+H30	=I29+I30
32		Add Depreciation		=-E28	=-F28	=-G28	=-H28	=-I28
33		Operating Cash Flow		=E31+E32	=F31+F32	=G31+G32	=H31+H32	=I31+I32
34		Initial investment in plant and equipment	=-D10
35		Opportunity cost for land	=-D11
36		Net Proceed from sales of land						1753000
37		Investment in working capital	=-D12	=-$D$13	=-$D$13	=-$D$13	=-$D$13	=-$D$13
38		Free Cash Flow	=SUM(D33:D37)	=SUM(E33:E37)	=SUM(F33:F37)	=SUM(G33:G37)	=SUM(H33:H37)	=SUM(I33:I37)
39
40		c)
41		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
42		Given the following cash flow and WACC, NPV for the project can be calculated as follows:
43		Year	0	1	2	3	4	5
44		Free Cash Flow (FCF)	=D38	=E38	=F38	=G38	=H38	=I38
45		MARR (i)	=D8
46		(P/F,i,n) for each year		=1/((1+$D45)^E43)	=1/((1+$D45)^F43)	=1/((1+$D45)^G43)	=1/((1+$D45)^H43)	=1/((1+$D45)^I43)
47		Present Value of cash flows = FCF*(P/F,i,n)		=E44*E46	=F44*F46	=G44*G46	=H44*H46	=I44*I46
48		Present value if future cash flows	=SUM(E47:I47)	=SUM(E47:I47)
49
50		NPV for Project	=Present value fo future cash flows - Initial investment
51			=D48+D44	=D48+D44
52
53		Hence NPV of the project is	=D51
54		Using goal seek function in excel, the bid price can be found at which NPV will be zero.
55		Thus Bid Price should be	=D9
56

Goal Seek setting