Question

***Please show your work with excel formulas****

Billingham Packaging is considering expanding its production capacity by purchasing a new machine, the XC-750. The cost of the XC-750 is $2.78 million. Unfortunately, installing this machine will take several months and will partially disrupt production. The firm has just completed a $46,000 feasibility study to analyze the decision to buy the XC-750, resulting in the following estimates:

• Marketing: Once the XC-750 is operational next year, the extra capacity is expected to generate $10.10 million per year in additional sales, which will continue for the ten-year life of the machine.

• Operations: The disruption caused by the installation will decrease sales by $4.98 million this year. As with Billingham's existing products, the cost of goods for the products produced by the XC-750 is expected to be 70% of their sale price. The increased production will also require increased inventory on hand of $1.12 million during the life of the project, including year 0 and depleted in year 10.

• Human Resources: The expansion will require additional sales and administrative personnel at a cost of $1.94 million per year.

• Accounting: The XC-750 will be depreciated via the straight-line method over the ten-year life of the machine. The firm expects receivables from the new sales to be 15% of revenues and payables to be 11% of the cost of goods sold. Billingham's marginal corporate tax rate is 35%.

a. Determine the incremental earnings from the purchase of the XC-750. (year 0, year 1-10)

b. Determine the free cash flow from the purchase of the XC-750.(year 0, year 1-10)

c. If the appropriate cost of capital for the expansion is 10.1% , compute the NPV of the purchase.

d. While the expected new sales will be $10.10 million per year from the expansion, estimates range from$8.15 million to $12.05 million. What is the NPV in the worst case? In the best case?

e. What is the break-even level of new sales from the expansion? If the firm believes that sales will not increase, but costs would be reduced by purchasing the new machine, what is the break-even level for the cost of goods sold?

f. Billingham could instead purchase the XC-900, which offers even greater capacity. The cost of the XC-900 is $3.94 million. The extra capacity would not be useful in the first two years of operation, but would allow for additional sales in years 3-10. What level of additional sales (above the $10.10 million expected for the XC-750) per year in those years would justify purchasing the larger machine?

Answer 1

		Note:Cost of feasibility stdy is sunk cost
		This cost is not relevant for the analysis
	A	Initialcash flow for Cost of machine in year 0	($2,780,000)
	B	Additional Sales from year2-10	$10,100,000
	C	Decrease in sales in year1	$4,980,000
	D=0.7*B	Increase inCost of sales in year 2-10	$7,070,000
	E=0.7*C	Decrease in Cost of sales in year1	$3,486,000
	F=B-D	Increase in before tax cash flow 2-10	$3,030,000
	G=C-E	Decrease in before tax cash flow in year1	$1,494,000
	H=F*(1-0.35)	Increase in after tax cash flow 2-10	$1,969,500
	I=G*(1-0.35)	Decrease in after tax cash flow in year1	$971,100
		CASH FLOW FROM DEPRECIATION TAX SHIELD
	J=A/10	Annual depreciation Year1-10	$278,000
	K=-J*0.35	Annual depreciation Tax Shield 1-10	$97,300
	X	Incremental Cash flow for Sales and administrative costs Year1-10	($1,940,000)
		INCREMENTAL OPERATING CASH FLOW:
	L=-I+K+X	Incremental Operating cash flow in year1	($2,813,800)
	M=H+K+X	Incremental Operating cash flow in year2-10	$126,800
		CASH FLOW FROM WORKING CAPITAL
	P	Cash flow for increase in inventory in year 0	($1,120,000)
	Q	Cash flow due to release of inventory in year10	$1,120,000
	R=0.15*B	Increase in receivables	$1,515,000
	S=0.11*D	Increase in payables	$777,700
	T=-(R-S)	Incremental cash flow fromreceivable & payable Year1	($737,300)
	U	Cash flow due to release of receivable & payable in year10	$737,300
	a	Incremental Earning
		YEAR1
		Decrease in before tax earning	$1,494,000
		Depreciation	$278,000
		Total decrease in income before tax(after depreciation)	$1,772,000
		Tax saving	$620,200
		Decrase in after tax Income in year 1	$1,151,800
		YEAR 2-10
		Increase in before tax earning2-10	$3,030,000
		Depreciation	$278,000
		Increase in before tax earning (after depreciation)	$2,752,000
		Increase in after tax earning	$1,788,800	(1-0.35)*2752000