Question

To solve the bid price problem presented in the text, we set the project NPV equal to zero and found the required price using the definition of OCF. Thus the bid price represents a financial break-even level for the project. This type of analysis can be extended to many other types of problems.

Romo Enterprises needs someone to supply it with 130,000 cartons of machine screws per year to support its manufacturing needs over the next five years, and you’ve decided to bid on the contract. It will cost you $970,000 to install the equipment necessary to start production; you’ll depreciate this cost straight-line to zero over the project’s life. You estimate that, in five years, this equipment can be salvaged for $80,000. Your fixed production costs will be $335,000 per year, and your variable production costs should be $11.30 per carton. You also need an initial investment in net working capital of $85,000. Assume your tax rate is 30 percent and you require a 11 percent return on your investment.

  

a.	Assuming that the price per carton is $18.00, what is the NPV of this project? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.)

  

NPV

$

  

b.	Assuming that the price per carton is $18.00, find the quantity of cartons per year you need to supply to break even. (Do not round intermediate calculations and round your answer to nearest whole number.)

  

Quantity of cartons

  

c.	Assuming that the price per carton is $18.00, find the highest level of fixed costs you could afford each year and still break even. (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.)

  

Answer 1

A1	B	C	D	E	F	G	H	I	J	K
2
3
4		For NPV to be calculated 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
8		Number of units sold	130,000
9		Price Per unit	$18.00
10		Variable cost per unit	$11.30
11		Life of the project	5	years
12		MARR	11%
13		Fixed production cost	$335,000
14		Initial investment in Net Working Capital	$85,000.00
15		Initial Investment in equipments	$970,000.00
16		Tax Rate	30%
17
18		Using Straight line depreciation,
19		Depreciation per year	=(Investment - Salvage Value)/Expected life of Project
20			$194,000	=(D15-0)/D11
21
22		Market Value of the equipment at the end of year 5	80,000
23		Book value of the equipment at the end of year 5	0
24		Gain on sale of equipment	80,000	=D22-D23
25		Tax on capital gain	24,000	=D24*D16
26		After-Tax Proceed from sale of equipment at Year 5	56,000	=D24-D25
27		a)
28		Free cash Flow calculation:
29		Free cash flow can be calculated as followed:
30		Year	0	1	2	3	4	5
31		Units Sold		130,000	130,000	130,000	130,000	130,000	=$D$8
32		Selling Price		$18.00	$18.00	$18.00	$18.00	$18.00	=$D$9
33		Revenue		$2,340,000	$2,340,000	$2,340,000	$2,340,000	$2,340,000	=I31*I32
34		Variable Cost		($1,469,000)	($1,469,000)	($1,469,000)	($1,469,000)	($1,469,000)	=-I31*$D$10
35		Fixed Production Cost		($335,000)	($335,000)	($335,000)	($335,000)	($335,000)	=-$D$13
36		Depreciation Expense		($194,000)	($194,000)	($194,000)	($194,000)	($194,000)	=-$D$20
37		EBIT		342,000	342,000	342,000	342,000	342,000	=SUM(I33:I36)
38		Tax Expense		(102,600)	(102,600)	(102,600)	(102,600)	(102,600)	=-I37*$D$16
39		EBIT*(1-Tax rate)		239,400	239,400	239,400	239,400	239,400	=I37+I38
40		Add depreciation		194,000	194,000	194,000	194,000	194,000	=-I36
41		Operating cash Flow		342,000	342,000	342,000	342,000	342,000	=I37
42		Initial investment	($970,000.00)
43		Investment in working capital	($85,000.00)
44		After-tax Salvage Value						56,000	=D26
45		Free Cash Flow	($1,055,000.00)	342,000	342,000	342,000	342,000	398,000	=SUM(I41:I44)
46
47		NPV Calculation:
48		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
49		Given the following cash flow and MARR, NPV for the project can be calculated as follows:
50		Year	0	1	2	3	4	5
51		Free Cash Flow (FCF)	($1,055,000)	$342,000	$342,000	$342,000	$342,000	$398,000
52		MARR (i)	11%
53
54		NPV	=-$1,055,000 + $342,000*(P/A,11%,5)+$56,000*(P/F,11%,5)
55			$242,230.05	=D45+E41*PV(D52,D11,-1,0)+I44*(1/((1+D52)^D11))
56
57		Hence NPV is	$242,230.05
58

Goal Seek Settings for (b)

Goal Settings for (c)