Question

Bourque Enterprises is considering a new project. The project will generate sales of $1.3 million, $1.8 million, $1.7 million, and $1.2 million over the next four years, respectively. The fixed assets required for the project will cost $1.7 million and will be eligible for 100 percent bonus depreciation. At the end of the project, the fixed assets can be sold for $185,000. Variable costs will be 20 percent of sales and fixed costs will be $440,000 per year. The project will require NWC equal to 15 percent of sales that must be accumulated in the year prior to sales. The required return on the project is 11 and the tax rate is 21 percent.

What is the NPV of the project?

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I	J	K
2
3		To calculated NPV, free cash flow of the project can be calculated as follows:
4		Free Cash Flow = Operating Cash Flow - Capital Expenditures - Change in working capital
5		Operating Cash Flow = EBIT*(1-Tax Rate)+Depreciation
6		Free cash flow of the project can be calculated as follows:
7		(All amount in million $)
8		Year	0	1	2	3	4
9		Investment in Fixed Assets	=-1.7
10		Sales		1.3	1.8	1.7	1.2
11		Variable Cost		=-E10*20%	=-F10*20%	=-G10*20%	=-H10*20%
12		Fixed Cost		-0.44	-0.44	-0.44	-0.44
13		Depreciaton		=D9
14		EBIT		=SUM(E10:E13)	=SUM(F10:F13)	=SUM(G10:G13)	=SUM(H10:H13)
15		Tax Expense (21%)		=-E14*21%	=-F14*21%	=-G14*21%	=-H14*21%
16		After Tax operating income (EBIT*(1-T))		=E14+E15	=F14+F15	=G14+G15	=H14+H15
17		Add Depreciation		=-E13
18		Operating Cash Flow		=E16+E17	=F16+F17	=G16+G17	=H16+H17
19		Working Capital Required	=E10*15%	=F10*15%	=G10*15%	=H10*15%	0
20		Investment in working capital	=0-D19	=-(E19-D19)	=-(F19-E19)	=-(G19-F19)	=-(H19-G19)
21		Free Cash Flow	=D9+D20	=E18+E20	=F18+F20	=G18+G20	=H18+H20
22
23		Calculation of NPV:
24		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
25		Given the following cash flow and WACC, NPV for the project can be calculated as follows:
26		Year	0	1	2	3	4
27		Free Cash Flow (FCF)	=D21	=E21	=F21	=G21	=H21
28		MARR (i)	0.11
29		(P/F,i,n) for each year		=1/((1+$D28)^E26)	=1/((1+$D28)^F26)	=1/((1+$D28)^G26)	=1/((1+$D28)^H26)
30		Present Value of cash flows = FCF*(P/F,i,n)		=E27*E29	=F27*F29	=G27*G29	=H27*H29
31		Present value if future cash flows	=SUM(E30:H30)	=SUM(E46:H46)
32
33		NPV for Project	=Present value fo future cash flows - Initial investment
34			=D31+D27	=D31+D27
35
36		Hence NPV of the project is	=D34	million
37