HEC WooD

Ratio Analysis
Input Worksheet
2/13/2012

Gray cells will be calculated for you. You do not need to enter anything into them.

Beginning
Line Item
of Year
Inventory $12,500
Total assets $120,000
Owners' equity $29,000
Number of common shares 25,000

Line Item Q1 Q2 Q3 Q4 Annual

Current assets 45,000 46,000 46,500 43,000 $43,000
Fixed assets 80,000 80,000 80,000 80,000 $80,000
Total assets 125,000 126,000 126,500 123,000 $123,000
Average total assets 122,500 123,000 123,250 121,500 $121,500
Cash and cash equivalents 15,000 18,000 16,500 14,350 $14,350
Inventory 15,000 18,000 16,500 14,350 $14,350
Average inventory 13,750 15,250 14,500 13,425 $13,425
Current liabilities 23,000 25,000 22,500 25,600 $25,600
Total liabilities 125,000 125,000 125,000 125,000 $125,000
Owners' equity 28,000 30,900 32,000 28,000 $28,000
Number of common shares 25,000 25,000 25,000 25,000 25,000
Average number of common shares 25,000 25,000 25,000 25,000 25,000
Average owners' equity 28,500 29,950 30,500 28,500 $28,500
Market price per share 10.00 10.00 10.00 10.00 $10.00
Cash flow 175,000 186,000 169,000 155,000 $685,000
Cash flow per share 7.00 7.44 6.76 6.20 $27.40
Dividends paid 5,000 5,000 5,000 5,000 $20,000

Total sales 145,000 156,000 135,600 125,000 $561,600

Operating expenses 68,000 68,000 68,000 68,000 $272,000
Operating income 77,000 88,000 67,600 57,000 $289,600
Advertising expense 18,000 18,000 18,000 18,000 $72,000
Marketing expense 11,000 11,000 11,000 11,000 $44,000
Earnings before interest and taxes 132,000 127,000 114,500 98,000 $471,500
Interest expense 24,000 24,000 24,000 24,000 $96,000
Net income 89,000 87,000 95,000 65,000 $336,000
HEC WooD
Ratio Analysis

Liquidity Ratios Q1 Q2 Q3 Q4 Annual

Definition:

Current Ratio = Current Assets 1.96 = $45,000 1.84 = $46,000 2.07 = $46,500 1.68 = $43,000 1.68 = $43,000
Current Liabilities $23,000 $25,000 $22,500 $25,600 $25,600
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance (0.04) (0.16) 0.07 (0.32) (0.32)
Quarterly Growth/Decline (0.12) 0.23 (0.39)

Quick Ratio = Current Assets – Inventory 1.30 = $45,000 - $15,000 1.12 = $46,000 - $18,000 1.33 = $46,500 - $16,500 1.12 = $43,000 - $14,350 1.12 = $43,000 - $14,350
Current Liabilities $23,000 $25,000 $22,500 $25,600 $25,600
Industry Average 7.00 7.00 7.30 7.20 2.00
Variance (5.70) (5.88) (5.97) (6.08) (0.88)
Quarterly Growth/Decline (0.18) 0.21 (0.21)

Net Working Capital = Current Assets – Current Liabilities 0.18 = $45,000 - $23,000 0.17 = $46,000 - $25,000 0.19 = $46,500 - $22,500 0.14 = $43,000 - $25,600 0.14 = $43,000 - $25,600
Ratio Total Assets $125,000 $126,000 $126,500 $123,000 $123,000

Industry Average 2.00 2.00 2.00 2.00 2.00

Variance (1.82) (1.83) (1.81) (1.86) (1.86)
Quarterly Growth/Decline (0.01) 0.02 (0.05)

Current Liabilities to = Current Liabilities 1.53 = $23,000 1.39 = $25,000 1.36 = $22,500 1.78 = $25,600 1.78 = $25,600
Inventory Ratio Inventory $15,000 $18,000 $16,500 $14,350 $14,350

Industry Average 2.00 2.00 2.00 2.00 2.00

Variance (0.47) (0.61) (0.64) (0.22) (0.22)
Quarterly Growth/Decline (0.14) (0.03) 0.42

Cash Ratio = Cash and Cash Equivalents 0.65 = $15,000 0.72 = $18,000 0.73 = $16,500 0.56 = $14,350 0.56 = $14,350
Current Liabilities $23,000 $25,000 $22,500 $25,600 $25,600
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance (1.35) (1.28) (1.27) (1.44) (1.44)
Quarterly Growth/Decline 0.07 0.01 (0.17)

Operating Ratio = Operating Expenses 0.88 = $68,000 0.77 = $68,000 1.01 = $68,000 1.19 = $68,000 0.94 = $272,000
Operating Income $77,000 $88,000 $67,600 $57,000 $289,600
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance (1.12) (1.23) (0.99) (0.81) (1.06)
Quarterly Growth/Decline (0.11) 0.23 0.19

The following calculations can be used for any expense line item or grouping of expense line items:

Advertising Expense = Advertising Expense 0.12 = $18,000 0.12 = $18,000 0.13 = $18,000 0.14 = $18,000 0.13 = $72,000
to Sales Ratio Total Sales $145,000 $156,000 $135,600 $125,000 $561,600

Industry Average 2.00 2.00 2.00 2.00 2.00

Variance (1.88) (1.88) (1.87) (1.86) (1.87)
Quarterly Growth/Decline (0.01) 0.02 0.01

Marketing Expense = Marketing Expense 0.08 = $11,000 0.07 = $11,000 0.08 = $11,000 0.09 = $11,000 0.08 = $44,000
to Sales Ratio Total Sales $145,000 $156,000 $135,600 $125,000 $561,600

Industry Average 2.00 2.00 2.00 2.00 2.00

Variance (1.92) (1.93) (1.92) (1.91) (1.92)
Quarterly Growth/Decline (0.01) 0.01 0.01
HEC WooD
Ratio Analysis

Asset Ratios Q1 Q2 Q3 Q4 Annual

Definition:

Inventory Turnover Ratio = Total Sales 10.55 = $145,000 10.23 = $156,000 9.35 = $135,600 9.31 = $125,000 41.83 = $561,600
Average Inventory $13,750 $15,250 $14,500 $13,425 $13,425

Industry Average 6.00 6.00 6.00 6.00 2.00

Variance 4.55 4.23 3.35 3.31 39.83
Quarter Growth/Decline (0.32) (0.88) (0.04)

Fixed Assets Turnover = Total Sales 1.81 = $145,000 1.95 = $156,000 1.70 = $135,600 1.56 = $125,000 7.02 = $561,600
Ratio Fixed Assets $80,000 $80,000 $80,000 $80,000 $80,000

Industry Average 2.00 2.00 2.00 2.00 2.00

Variance (0.19) (0.05) (0.31) (0.44) 5.02
Quarter Growth/Decline 0.14 (0.26) (0.13)

Total Assets Ratio = Total Sales 1.16 = $145,000 1.24 = $156,000 1.07 = $135,600 1.02 = $125,000 4.57 = $561,600
Total Assets $125,000 $126,000 $126,500 $123,000 $123,000
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance (0.84) (0.76) (0.93) (0.98) 2.57
Quarter Growth/Decline 0.08 (0.17) (0.06)

Asset to Equity Ratio = Total Assets 4.46 = $125,000 4.08 = $126,000 3.95 = $126,500 4.39 = $123,000 4.39 = $123,000
Owners' Equity $28,000 $30,900 $32,000 $28,000 $28,000
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance 2.46 2.08 1.95 2.39 2.39
Quarter Growth/Decline (0.39) (0.12) 0.44
HEC WooD
Ratio Analysis

Profitability Ratios Q1 Q2 Q3 Q4 Annual

Definition:

Return on Assets Ratio = Net Income 0.73 = $89,000 0.71 = $87,000 0.77 = $95,000 0.53 = $65,000 2.77 = $336,000
Average Total Assets $122,500 $123,000 $123,250 $121,500 $121,500
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance (1.27) (1.29) (1.23) (1.47) 0.77
Quarter Growth/Decline (0.02) 0.06 (0.24)

Return on Equity Ratio = Net Income 3.12 = $89,000 2.90 = $87,000 3.11 = $95,000 2.28 = $65,000 11.79 = $336,000
Average Owners' Equity $28,500 $29,950 $30,500 $28,500 $28,500
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance 1.12 0.90 1.11 0.28 9.79
Quarter Growth/Decline (0.22) 0.21 (0.83)

Profit Margin Ratio = Net Income 0.61 = $89,000 0.56 = $87,000 0.70 = $95,000 0.52 = $65,000 0.60 = $336,000
Total Sales $145,000 $156,000 $135,600 $125,000 $561,600
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance (1.39) (1.44) (1.30) (1.48) (1.40)
Quarter Growth/Decline (0.06) 0.14 (0.18)

Basic Earnings = Earnings Before Interest and Taxes 1.06 = $132,000 1.01 = $127,000 0.91 = $114,500 0.80 = $98,000 3.83 = $471,500
Power Ratio Total Assets $125,000 $126,000 $126,500 $123,000 $123,000

Industry Average 2.00 2.00 2.00 2.00 2.00

Variance (0.94) (0.99) (1.09) (1.20) 1.83
Quarter Growth/Decline (0.05) (0.10) (0.11)

Earnings per Share Ratio = Net Income 3.56 = $89,000 3.48 = $87,000 3.80 = $95,000 2.60 = $65,000 13.44 = $336,000
Average Number of Common Shares $25,000 $25,000 $25,000 $25,000 $25,000
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance 1.56 1.48 1.80 0.60 11.44
Quarter Growth/Decline (0.08) 0.32 (1.20)
HEC WooD
Ratio Analysis

Debt Ratios Q1 Q2 Q3 Q4 Annual

Definition:

Total Debt Ratio = Total Liabilities 1.00 = $125,000 0.99 = $125,000 0.99 = $125,000 1.02 = $125,000 1.02 = $125,000
Total Assets $125,000 $126,000 $126,500 $123,000 $123,000
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance (1.00) (1.01) (1.01) (0.98) (0.98)
Quarter Growth/Decline (0.01) (0.00) 0.03

Interest Coverage Ratio = Earnings Before Interest and Taxes 5.50 = $132,000 5.29 = $127,000 4.77 = $114,500 4.08 = $98,000 4.91 = $471,500
Interest Expense $24,000 $24,000 $24,000 $24,000 $96,000

Industry Average 2.00 2.00 2.00 2.00 2.00

Variance 3.50 3.29 2.77 2.08 2.91
Quarter Growth/Decline (0.21) (0.52) (0.69)

Debt/Equity Ratio = Total Liabilities 4.46 = $125,000 4.05 = $125,000 3.91 = $125,000 4.46 = $125,000 4.46 = $125,000
Owners' Equity $28,000 $30,900 $32,000 $28,000 $28,000
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance 2.46 2.05 1.91 2.46 2.46
Quarter Growth/Decline (0.42) (0.14) 0.56

Loan to Value Ratio = Total Loan 0.38 = $25,000 0.37 = $24,000 0.35 = $23,000 0.34 = $22,000 0.34 = $22,000
Value of Collateral or Property $65,000 $65,000 $65,000 $65,000 $65,000
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance (1.62) (1.63) (1.65) (1.66) (1.66)
Quarter Growth/Decline (0.02) (0.02) (0.02)
HEC WooD
Ratio Analysis

Market Ratios Q1 Q2 Q3 Q4 Annual

Definition:

Earnings per Share = Net Income $3.56 = $89,000 $3.48 = $87,000 $3.80 = $95,000 $2.60 = $65,000 $13.44 = $336,000
(EPS) Ratio Average Number of Common Shares 25,000 25,000 25,000 25,000 25,000

Industry Average 2.00 2.00 2.00 2.00 2.00

Variance 1.56 1.48 1.80 0.60 11.44
Quarter Growth/Decline (0.08) 0.32 (1.20)

Price to Earnings Ratio = Market Price per Share 2.81 = $10.00 2.87 = $10.00 2.63 = $10.00 3.85 = $10.00 0.74 = $10.00
Earnings per Share $3.56 $3.48 $3.80 $2.60 $13.44
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance 0.81 0.87 0.63 1.85 (1.26)
Quarter Growth/Decline 0.06 (0.24) 1.21

Price to Cash Flow = Market Price per Share 1.43 = $10.00 1.34 = $10.00 1.48 = $10.00 1.61 = $10.00 0.36 = $10.00
Ratio Cash Flow per Share $7.00 $7.44 $6.76 $6.20 $27.40

Industry Average 2.00 2.00 2.00 2.00 2.00

Variance (0.57) (0.66) (0.52) (0.39) (1.64)
Quarter Growth/Decline (0.08) 0.14 0.13

Payout Ratio = Dividends Paid 0.06 = $5,000 0.06 = $5,000 0.05 = $5,000 0.08 = $5,000 0.06 = $20,000
Net Income $89,000 $87,000 $95,000 $65,000 $336,000
Industry Average 2.00 2.00 2.00 2.00 2.00
Variance (1.94) (1.94) (1.95) (1.92) (1.94)
Quarter Growth/Decline 0.00 (0.00) 0.02