Ratio - Excel by BrittanyGibbons

VIEWS: 970 PAGES: 2

									A 1

B

C

D

E

F

G

H

I

J

FINANCIAL ANALYSIS MODEL
generated

Note: Enter data in Blue-coded cells; Black cells are computer 2 3 ANALYST'S NAME Dr. Glenn L. Stevens 4 NAME OF COMPANY Sample Firm 5 FIRST CALENDAR YEAR OF 6 DATA 1996 7 8 NUMBER OF YEARS OF DATA 3 9 10 11 12 INCOME STATEMENT 1996 1997 1998 13 Sales Revenue $1,500 $1,550 $1,700 14 Less: Cost of Goods Sold $1,000 $1,030 $1,040 15 Gross Profits $500 $520 $660 16 Less: Operating Expenses: 17 Selling Expense $140 $150 $155 18 General / Admin. Exp. $260 $270 $275 19 Lease Expense $5 $0 $2 20 Depreciation Expense $10 $11 $12 21 Total Operating Expenses $415 $431 $444 22 Operating Profits $85 $89 $216 23 Less: Interest Expense $23 $29 $30 24 Net Profits Before Taxes $62 $60 $186 25 Less: Taxes $10 $12 $15 26 Net Profit After Taxes $52 $48 $171 27 Less: Pref. Stock Divds. $1 $0 $0 Earnings Available for Common 28 Stockholders $51 $48 $171 29 30 31 BALANCE SHEET 32 Current Assets: 33 Cash $31 $12 $18 34 Marketable Securities $82 $66 $68 35 Accounts Receivable $104 $152 $160 36 Inventories $145 $191 $200 37 Total Current Assets $362 $421 $446 38 Gross Fixed Assets (at cost): 39 Land & Buildings $180 $195 $200 40 Machinery and Equipment $0 $0 $0 41 Furniture & Fixtures $0 $0 $0 42 Vehicles $0 $0 $0 43 Other (Inc. Fin. Leases) $0 $0 $0 44 Total Gross Fixed Assets $180 $195 $200 45 Less: Accumulated Depreciation $52 $63 $65 46 Net Fixed Assets $128 $132 $135 47 Other Assets $0 $0 $0 48 Total Assets $490 $553 $581 49 50 Current Liabilities: 1996 1997 1998 51 Accounts Payable $126 $136 $150 52 Notes Payable $190 $200 $140 53 Accruals $25 $27 $28 54 Taxes Payable $0 $0 $0 55 Other Current Liabilities $0 $0 $0 56 Total Current Liabilities $341 $363 $318 57 L / T Debt (Inc. Financial Leases) $40 $38 $13 58 Total Liabilities $381 $401 $331 59 Preferred Stock $0 $0 $0 60 Common Stock $20 $20 $20 61 Paid-In Capital In Excess of Par $30 $30 $30 62 Retained Earnings $59 $102 $200 63 Total Stockholders' Equity $109 $152 $250 64 Total Liabs. & Stockhldrs' Equity $490 $553 $581 65 $0 $0 $0 Reconciliation TA & TL/SE 66 Number of Common Shares 500 500 500 0

$0 $0

$0 $0 =IF($B$8>4,F13-F14," ")

$0 $0 $0 $0

$0 $0 $0 $0 =IF($B$8>4,SUM(F17:F20)," ") =IF($B$8>4,F15-F21," ")

$0 $0 $0

$0 =IF($B$8>4,F22-F23," ") $0 =IF($B$8>4,F24-F25," ") $0 IF($B$8>4,F26-F27," ")

$0 $0 $0 $0

$0 $0 $0 $0 =IF($B$8>4,SUM(F33:F36)," ")

$0 $0 $0 $0 $0 $0 $0

$0 $0 $0 $0 $0 =IF($B$8>4,SUM(F39:F43)," ") $0 =IF($B$8>4,F44-F45," ") $0 =IF($B$8>4,F37+F46," ") =IF($B$8>4,F37+F46," ")

$0 $0 $0 $0 $0 $0 $0 $0 $0 $0

$0 $0 $0 $0 $0 =IF($B$8>4,SUM(F51:F55)," ") $0 =IF($B$8>4,F56+F57," ") $0 $0 $0 $0 =IF($B$8>4,SUM(F59:F62)," ") =IF($B$8>4,F58+F63," ") =IF($B$8>4,F64-F48," ") 0

67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104

A End-of-Year Stock Price RATIO ANALYSIS Current Ratio Quick Ratio Inventory Turnover Average Collection Period Fixed Asset Turnover Total Asset Turnover Debt Ratio Debt-to-Equity Times Interest Earned Gross Profit Margin Operating Profit Margin Net Profit Margin Return on Total Assets (ROA) Return on Equity (ROE) Earnings Per Share Price/Earnings Ratio DUPONT ANALYSIS Net Profit AT/Sales Sales/Total Assets ROA Net Profit AT/Total Assets Total Assets/Stockhldrs. Equity ROE

B $3.00

C $5.00

D $5.50

E $0.00

F $0.00

G

H

I

J

1.06 0.64 6.90 24.96 11.72 3.06 0.78 0.37 3.70 33.33% 5.67% 3.47% 10.61% 47.71% $0.10 29.41

1.16 0.63 5.39 35.30 11.74 2.80 0.73 0.25 3.07 33.55% 5.74% 3.10% 8.68% 31.58% $0.10 52.08

1.40 0.77 5.20 33.88 12.59 2.93 0.57 0.05 7.20 38.82% 12.71% 10.06% 29.43% 68.40% $0.34 16.08

=IF($B$8>4,F37/F56," ") =IF($B$8>4,(F37-F36)/F56," ") =IF($B$8>4,F14/F36," ") =IF($B$8>4,F35/(F13/360)," ") =IF($B$8>4,F13/F46," ") =IF($B$8>4,F13/F48," ") =IF($B$8>4,F58/F48," ") =IF($B$8>4,F57/F63," ") =IF($B$8>4,F22/F23," ") =IF($B$8>4,F15/F13," ") =IF($B$8>4,F22/F13," ") =IF($B$8>4,F26/F13," ") =IF($B$8>4,F26/F48," ") =IF($B$8>4,F26/F63," ") =IF($B$8>4,F28/F66," ") =IF(AND(F67>0,F89>0),F67/F89," ")

3.47% 306.12% 10.61% 10.61% 449.54% 47.71%

3.10% 280.29% 8.68% 8.68% 363.82% 31.58%

10.06% 292.60% 29.43% 29.43% 232.40% 68.40%

=IF($B$8>4,F26/F13," ") =IF($B$8>4,F13/F48," ") =IF($B$8>4,F94*F95," ") =IF($B$8>4,F26/F48," ") =IF($B$8>4,F48/F63," ") =IF($B$8>4,F97*F98," ")

STATEMENT OF CASH FLOWS CASH FLOW FROM 105 OPERATING ACTIVITIES 106 Net Profit After Taxes 107 Depreciation 108 Decrease in Accts. Receiv. 109 Decrease in Inventories 110 Decrease in Other Assets 111 Increase in Accounts Pay. 112 Increase in Accruals 113 Increase in Taxes Payable 114 Increase in Other Cur. Lia. 115 Cash Flow from Oper. Act. 116 CASH FLOW FROM 117 INVESTMENT ACTIVITIES 118 Increase in Gross Fixed Assets 119 Cash Flow from Investments 120 CASH FLOW FROM FINANCING 121 ACTIVITIES 122 Increase in Notes Payable 123 Increase in Long-Term Debt 124 Changes in Stockholders' Equity 125 Preferred Dividends Paid 126 Common Dividends Paid (-) Cash Flow from Financing 127 Activities 128 129 Net Incr. in Cash / Mark. Securities

1996

1997 $48 $11 ($48) ($46) $0 $10 $2 $0 $0 ($23)

1998 $171 $12 ($8) ($9) $0 $14 $1 $0 $0 $181

($15) ($15)

($5) ($5)

$10 ($2) $0 $0 ($5) $3 ($35)

($60) ($25) $0 $0

($85) $91


								
To top