Complete this spreadsheet by calculating all the gray boxes.
This is a giant retailer that pays no dividends and has no preferred stock.
Calculate all ratios and figures in the boxes provided C22:E33 and state whether the ratios are getting better or worse or about the same.
INCOME STATEMENTS	2011	2012	2013		Factor CS (Over Sales)				CAGR	Industry	2014 Factors	2014E	Justification/2014 Factors
					2011	2012	2013	Avg Rates	2011-13	2013
Sales	$3,369.50	$3,493.25	$3,665.05						2.75%	3.50%	$3,765.69	$3,848.3	Assume Growth is 5.00%
Costs except depreciation	$3,002.70	$3,120.60	$3,289.40		89.11%	89.33%	89.75%	89.40%		90.00%	91.85%	93.87%	Use average over three years
Depreciation	$104.20	$106.55	$107.10		3.09%	3.05%	2.92%	3.02%		5.00%	3.01%	3.07%	Use industry rates
Total operating costs	$3,106.90	$3,227.15	$3,396.50		92.21%	92.38%	92.67%	92.42%		95.00%
EBIT	$262.60	$266.10	$268.55							5.00%
Less interest (INT)	$37.85	$43.30	$38.10							1.00%			Find using targets and information below
Earnings before taxes (EBT)	$224.75	$222.80	$230.45							4.00%
Taxes	$78.75	$76.35	$80.50		2.34%	2.19%	2.20%	2.24%		35.00%			Use industry rates
Net income for common (NI)	$146.00	$146.45	$149.95
Shares of common stock	100	100	100									100.0
Earnings per share (EPS)	$1.46	$1.46	$1.50
		2012	2013	2014E	Comparisons
Net operating profit after taxes					Is it getting Better/Worse/About Same
Net operating working capital
Total operating capital
FCF = NOPAT op. capital
Return on invested capital
EPS
Return on equity (ROE)
Return on assets (ROA)
Inventory turnover
Days sales outstanding
Total liabilities / TA
Times interest earned
Shares outstanding		100.0	100.0	100.0
AFN				-$0.03
BALANCE SHEETS	2011	2012	2013		2011	2012	2013	Avg Rates	2011-13	Industry	2014 Factors	2014E	Justification/2014 Factors
Assets
Cash	$85.60	$39.70	$39.20							2.00%			Use 2.50%
Accounts receivable	$307.65	$296.35	$292.05							10.00%			Use average over three years
Inventories	$379.80	$395.90	$395.15							12.00%			Use average over three years
Other Current Assets	$87.60	$90.50	$93.00							2.00%			Use average over three years
Total current assets	$860.65	$822.45	$819.40
Net plant and equip.	$1,324.60	$1,509.05	$1,588.75							42.50%			Use industry rate
Total assets	$2,185.25	$2,331.50	$2,408.15
Liabilities and equity
Accounts payable	$497.55	$525.05	$551.85							12.50%			Use industry rate
Accruals	$80.35	$81.70	$80.45							3.00%			Use average over three years
Notes payable	$5.95	$189.30	$149.70									$153.47	Find
Total current liab.	$583.85	$796.05	$782.00
Long-term bonds	$827.05	$744.40	$798.25									$805.72	Find
Total liabilities	$1,410.90	$1,540.45	$1,580.25
Common stock	$139.45	$143.10	$170.15
Retained earnings	$634.90	$647.95	$657.75
Total common equity	$774.35	$791.05	$827.90									$959.19	Find
Total liab. & equity	$2,185.25	$2,331.50	$2,408.15
												Inv. Cap.
Invested Capital											Targets
Notes Payable % of IC											8.00%
Long Term Bonds% of IC											42.00%
Common Stock% of IC											50.00%
Int. Rate on Notes Payable	3.00%	3.00%	3.00%								3.00%
Int. Rate on Long Term Bonds	4.56%	5.05%	4.21%								4.60%