**

please include excel formulas for all yellow boxes, there is no part c... ill give the numbers in a different format if this is hard to read, the information below is the same as the worksheet from above

Income Statement for the Year Ending December 31 (Millions of Dollars)
	2012
Net Sales	$ 800.0
Costs (except depreciation)	$ 576.0
Depreciation	$ 60.0
Total operating costs	$ 636.0
Earning before int. & tax	$ 164.0
Less interest	$ 32.0
Earning before taxes	$ 132.0
Taxes (40%)	$ 52.8
Net income before pref. div.	$ 79.2
Preferred div.	$ 1.4
Net income avail. for com. div.	$ 77.9
Common dividends	$ 31.1
Addition to retained earnings	$ 46.7
Number of shares (in millions)	10
Dividends per share	$ 3.11
Balance Sheets for December 31 (Millions of Dollars)
Assets	2012		Liabilities and Equity		2012
Cash	$ 8.0		Accounts Payable		$ 16.0
Marketable Securities	20.0		Notes payable		40.0
Accounts receivable	80.0		Accruals		40.0
Inventories	160.0		Total current liabilities		$ 96.0
Total current assets	$ 268.0		Long-term bonds		$ 300.0
Net plant and equipment	600.0		Preferred stock		$ 15.0
Total Assets	$ 868.0		Common Stock (Par plus PIC)		$ 257.0
			Retained earnings		200.0
			Common equity		$ 457.0
			Total liabilities and equity		$ 868.0
Projected ratios and selected information for the current and projected years are shown below.
Inputs	Actual	Projected	Projected	Projected	Projected
	2012	2013	2014	2015	2016
Sales Growth Rate		15%	10%	6%	6%
Costs / Sales	72%	72%	72%	72%	72%
Depreciation / Net PPE	10%	10%	10%	10%	10%
Cash / Sales	1%	1%	1%	1%	1%
Acct. Rec. / Sales	10%	10%	10%	10%	10%
Inventories / Sales	20%	20%	20%	20%	20%
Net PPE / Sales	75%	75%	75%	75%	75%
Acct. Pay. / Sales	2%	2%	2%	2%	2%
Accruals / Sales	5%	5%	5%	5%	5%
Tax rate	40%	40%	40%	40%	40%
Weighted average cost of capital (WACC)	10.5%	10.5%	10.5%	10.5%	10.5%
a. Forecast the parts of the income statement and balance sheets necessary to calculate free cash flow.
Partial Income Statement for the Year Ending December 31 (Millions of Dollars)
	Actual	Projected	Projected	Projected	Projected
	2012	2013	2014	2015	2016
Net Sales	$ 800.0
Costs (except depreciation)	$ 576.0
Depreciation	$ 60.0
Total operating costs	$ 636.0
Earning before int. & tax	$ 164.0
Partial Balance Sheets for December 31 (Millions of Dollars)
	Actual	Projected	Projected	Projected	Projected
Operating Assets	2012	2013	2014	2015	2016
Cash	$ 8.0
Accounts receivable	$ 80.0
Inventories	$ 160.0
Net plant and equipment	$ 600.0
Operating Liabilities
Accounts Payable	$ 16.0
Accruals	$ 40.0
b. Calculate free cash flow for each projected year. Also calculate the growth rates of free cash flow each year to ensure that there is constant growth (i.e., the same as the constant growth rate in sales) by the end of the forecast period.
							Actual	Projected	Projected	Projected	Projected
						Calculation of FCF	2012	2013	2014	2015	2016
Operating current assets
Operating current liabilities
Net operating working capital
Net PPE
Net operating capital
NOPAT
Investment in operating capital	na
Free cash flow	na
Growth in FCF	na	na
Growth in sales
d. Calculate the value of operations and MVA. (Hint: first calculate the horizon value at the end of the forecast period, which is equal to the value of operations at the end of the forecast period. Assume that the annual growth rate beyond the horizon is 6 percent.)
							Actual	Projected	Projected	Projected	Projected
							2012	2013	2014	2015	2016
						Free cash flow
Long-term constant growth in FCF
Weighted average cost of capital (WACC)	10.5%	10.5%	10.5%	10.5%	10.5%
Horizon value
FCF in Years 1-3 and FCF4 + horizon value in Year 4
Value of operations (PV of FCF + HV)
Operating capital
Market value added (MVA=Market value of company - book value of company = Value of operations - Operating capital)
e. Calculate the price per share of common equity as of 12/31/2012.
	Actual
	2012
Value of Operations
Plus Value of Mkt. Sec.
Total Value of Company
Less Value of Debt
Less Value of Pref.
Value of Common Equity
Divided by number of shares
Price per share

The Henley Corporation privately held company specialii predunts and services. for the Year Endi 31 (lillien Net Sales ming before Earning before before pre Number h (in millions) Dividends pel Balance Sheets for December illions of Dollast Lkablicks and Equicy Total ntliabilitiet Longterm bonds Net plac and equipment Retrined earningn equity Projected rojectad years Actaal Projected Projected Projeclad Projecoed Sales Grorth Rate Net PPE 10s 10s. Aert Rec Nee PPE Sal Weighted average cest ef (MAC) the panes ofthe income statement essary calculate free the Year Ending Dec Projected Projected Projected Prejetted Co (except depreciation Depreciation ming before Partial Balance Sheuts for December 11 Projected Prejected Projected Projetoed Net plant and equipment erating Liabilites Payable B1 b Calculate free cash fow for each projected ye of free cash flow lesl by att period, acteal Projected Prejected Projeclad Projeuned Net operating working capital Neeopenting capital Invesment in operating capital ath Calculata the value operati lee operations the end of the forecast period. Assume that erizen is 6 percent) rowth rate bey acteal Projected Prejecad Projeclad Praiened 2014 2015 2015 nstant growth in FCF Long-term average of operati operating capital added Mnrket of of company Valve of epelations-Operatina capital the price per share of of Operati quity Divided by mmber shy per shi The Henley Corporation privately held company specialii predunts and services. for the Year Endi 31 (lillien Net Sales ming before Earning before before pre Number h (in millions) Dividends pel Balance Sheets for December illions of Dollast Lkablicks and Equicy Total ntliabilitiet Longterm bonds Net plac and equipment Retrined earningn equity Projected rojectad years Actaal Projected Projected Projeclad Projecoed Sales Grorth Rate Net PPE 10s 10s. Aert Rec Nee PPE Sal Weighted average cest ef (MAC) the panes ofthe income statement essary calculate free the Year Ending Dec Projected Projected Projected Prejetted Co (except depreciation Depreciation ming before Partial Balance Sheuts for December 11 Projected Prejected Projected Projetoed Net plant and equipment erating Liabilites Payable B1 b Calculate free cash fow for each projected ye of free cash flow lesl by att period, acteal Projected Prejected Projeclad Projeuned Net operating working capital Neeopenting capital Invesment in operating capital ath Calculata the value operati lee operations the end of the forecast period. Assume that erizen is 6 percent) rowth rate bey acteal Projected Prejecad Projeclad Praiened 2014 2015 2015 nstant growth in FCF Long-term average of operati operating capital added Mnrket of of company Valve of epelations-Operatina capital the price per share of of Operati quity Divided by mmber shy per shi