Question

Mel & Bud, Inc., is a printing company specializing in the production of coffee bags and frozen food bags. The company’s board has asked you to complete an analysis for their intended expansion into a new production space; a large mill building in close proximity to the current location will be refurbished to accomplish this task. The executive team has provided some information regarding the project, which follows.

About the Machine

The following are base assumptions regarding the equipment. Note that the cost of the building lease is included in the Cost of Goods Sold as indicated. Initial investment outlay is $70 million, with $50 million for machinery and $20 for net working capital (inventory). Life of the equipment is 5 years; the increase in annual revenues is $60 million annually. The company’s gross margin is 60% (excluding depreciation). Use straight line depreciation to calculate the cash flows. Selling, general, and administrative expenses are 3% of sales. The company’s tax rate was negotiated at 30% for the new operations.

Weighted Average Cost of Capital

Weighted average cost of capital is based on the following fact pattern. Capital structure of 40% debt and 60% common equity, there is a 30% tax rate. The cost of debt is 8%, while the company beta is 1.1. In addition, the risk free rate is 1%, and the market return is 11%.

1. Using Excel, create a spreadsheet that calculates all relevant cash flows (i.e., initial investment, periodic cash flows, and terminal value). Round all numbers to the nearest dollar.

POST THE ANSWER IN AN EXCEL SPREADSHEET !!!

Answer 1

cash outflow
purchase of machine		-50000000
investment in working capital		-20000000
total cash outflow		-70000000
Year	0	1	2	3	4	5
Initial investment	-70000000
sales		60000000	60000000	60000000	60000000	60000000
less variable cost -40% pf sales		24000000	24000000	24000000	24000000	24000000
gross profit		36000000	36000000	36000000	36000000	36000000
less depreciation-		10000000	10000000	10000000	10000000	10000000
operating profit		26000000	26000000	26000000	26000000	26000000
less selling expenses		1800000	1800000	1800000	1800000	1800000
profit before tax		24200000	24200000	24200000	24200000	24200000
less tax-30%		7260000	7260000	7260000	7260000	7260000
profit after tax		16940000	16940000	16940000	16940000	16940000
add depreciation		10000000	10000000	10000000	10000000	10000000
add recovery of working capital						20000000
periodic cash flow		26940000	26940000	26940000	26940000	46940000
Terminal value						20000000
weighted average cost of capital
after tax cost of debt	cost of debt*(1-tax rate)	8*(1-.3)	5.6
cost of equity	risk free rate+(market return-risk free rate)*beta	1+(11-1)*1.1	12
WACC
source	weight	cost of source	weight*source
debt	0.4	5.6	2.24
equity	0.6	12	7.2
WACC	sum of present value of cash flow		9.44