Question

Please give the excel formulas and steps details

Financial ratio data elements (all in $ million except per share data):			Ratio	Ratio Value	Industry Average	Better or worse
Current_assets	14,277		Current		1.3
Current_liabilities	7,687		Quick		0.9
Inventory	4,034		Inventory_turnover		23.6
Cost_of_goods_sold	14,371		DSO		61.6
Accounts_receivable	4,911		Fixed_assets_turnover		3.8
Annual_sales	31,657		Total_assets_turnover		0.7
Net_fixed_assets	8,866		Debt_ratio (%)		75.3%
Total_assets	37,987		Times_interest_earned		15.3
Total_liabilities	26,365		Net_profit_margin (%)		0.2
EBIT	7,333		ROA (%)		0.1
Interest_charges	238		ROE (%)		3.7
Net_income	4,858		P/E		38.9
Common_equity	11,563		M/B		78.8
Market_price_per_share	$ 235.37
Earnings_per_share	$     7.93
Book_value_per_share	$   19.44

Answer 1

Ratio	Ratio Value	Industry Average	Better or worse
Current	1.86	1.3	Better
Quick	1.33	0.9	Better
Inventory_turnover	3.56	23.6	Worse
DSO	56.62	61.6	Better
Fixed_assets_turnover	3.57	3.8	Worse
Total_assets_turnover	0.83	0.7	Better
Debt_ratio (%)	69.41%	75.30%	Better
Times_interest_earned	30.81	15.3	Better
Net_profit_margin (%)	0.15	0.2	Worse
ROA (%)	0.13	0.1	Better
ROE (%)	0.42	3.7	Worse
P/E	29.68	38.9	Worse
M/B	12.11	78.8	Worse

Current ratio

Current ratio = Current assets/ Current liabilities

Current ratio =14,277/7,687

Current ratio = 1.86

Quick Ratio

Quick Ratio = (Current Assets-Inventory)/Current liabilities

Quick Ratio =(14,277-4,034)/7,687

Quick Ratio = 1.33

Inventory Turnover Ratio

Inventory Turnover Ratio= Cost of Goods Sold / Average Inventory

Inventory Turnover Ratio=14,371/4,034

Inventory Turnover Ratio= 3.56

Days Sales Outstanding

Days Sales Outstanding =(Account Receivable/Total credit sales in accounting period)*Days in accounting period

Days Sales Outstanding =(4911/31,657)*365

Days Sales Outstanding = 56.62 days

Fixed Asset Turnover Ratio

Fixed Asset Turnover Ratio = Net Sales/ Average Fixed Assets

Fixed Asset Turnover Ratio = 31,657/8,866

Fixed Asset Turnover Ratio = 3.57 times

Total Asset Turnover Ratio

Total Asset Turnover Ratio = Net Sales/ Average Total Assets

Total Asset Turnover Ratio = 31,657/37,987

Total Asset Turnover Ratio = 0.83

Total debt ratio (%)

Total debt ratio =(Total liabilities/Total assets)*100

Total debt ratio =(26,365/37,987)*100

Total debt ratio = 69.4

Times interest earned

Times interest earned =EBIT/Interest expenses

Times interest earned =7,333/238

Times interest earned =30.81

Net profit margin(%)

Net profit margin= Net Income/ Net Sales

Net profit margin=4,858/31,657

Net profit margin=0.15

Return on Asset Ratio

Return on Asset Ratio = Net Income / Average Total Assets

Return on Asset Ratio = 4,858/37,987

Return on Asset Ratio = 0.13

Return on Equity

Return on Equity = Net Income / average share holders equity

Return on Equity = 4,858/11,563

Return on Equity = 0.42

P/E Ratio

PE Ratio=Share price/Earnings per share

PE Ratio=235.37/7.93

PE Ratio=29.68

M/B Ratio

M/B Ratio =Market value of share/ Book value of share

M/B Ratio =235.37/19.44

M/B Ratio =12.11