Ratio - Download as Excel

							A 1

B

C

D

E

F

G

H

I

J

FINANCIAL ANALYSIS MODEL

Note: Enter data in Blue-coded cells; Black cells are computer generated 2 3 ANALYST'S NAME 4 NAME OF COMPANY 5 FIRST CALENDAR YEAR OF 6 DATA 2002 7 8 NUMBER OF YEARS OF DATA 2 9 10 11 12 INCOME STATEMENT 2002 2003 13 Sales Revenue $1,818,500 $1,750,500 14 Less: Cost of Goods Sold $1,005,500 $996,000 15 Gross Profits $813,000 $754,500 16 Less: Operating Expenses: 17 Selling Expense $506,000 $479,000 18 General / Admin. Exp. 19 Lease Expense 20 Depreciation Expense 21 Total Operating Expenses $506,000 $479,000 22 Operating Profits $307,000 $275,500 23 Less: Interest Expense $27,000 $19,000 24 Net Profits Before Taxes $280,000 $256,500 25 Less: Taxes $84,000 $77,000 26 Net Profit After Taxes $196,000 $179,500 27 Less: Pref. Stock Divds. Earnings Available for Common 28 Stockholders $196,000 $179,500 29 30 31 BALANCE SHEET 32 Current Assets: 33 Cash $60,100 $64,200 34 Marketable Securities $54,000 $50,000 35 Accounts Receivable $107,800 $102,800 36 Inventories $123,000 $115,500 37 Total Current Assets $344,900 $332,500 38 Gross Fixed Assets (at cost): 39 Land & Buildings $625,300 $520,300 40 Machinery and Equipment $0 $0 41 Furniture & Fixtures $0 $0 42 Vehicles $0 $0 43 Other (Inc. Fin. Leases) $0 $0 $0.0 $0.0 44 Total Gross Fixed Assets $625,300 $520,300 45 Less: Accumulated Depreciation $0.0 46 Net Fixed Assets $625,300 $520,300 47 Other Assets $0 $0 $0.0 $0.0 48 Total Assets $970,200 $852,800 49 50 Current Liabilities: 2002 2003 51 Accounts Payable 160000 145400 52 Notes Payable 53 Accruals 54 Taxes Payable 43500 42000 55 Other Current Liabilities 0 0 56 Total Current Liabilities 203500 187400 57 L / T Debt (Inc. Financial Leases) 200000 200000 58 Total Liabilities 403500 387400 59 Preferred Stock 0 0 60 Common Stock 280000 300000 61 Paid-In Capital In Excess of Par 62 Retained Earnings 286700 165400 63 Total Stockholders' Equity 566700 465400 64 Total Liabs. & Stockhldrs' Equity 970200 852800 65 0 0 Reconciliation TA & TL/SE 66 Number of Common Shares 57000 500

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

=IF($B$8>4,SUM(F17:F20)," ") =IF($B$8>4,F15-F21," ") =IF($B$8>4,F22-F23," ") =IF($B$8>4,F24-F25," ")

IF($B$8>4,F26-F27," ")

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

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

=IF($B$8>4,SUM(F51:F55)," ") =IF($B$8>4,F56+F57," ")

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

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

C 5

D

E

F

G

H

I

J

1.7 1.1 8.2 21.3 2.9 1.9 0.4 0.4 11.4 0.4 0.2 0.1 0.2 0.3 3.4 0.87

1.8 1.2 8.6 21.1 3.4 2.1 0.5 0.4 14.5 0.4 0.2 0.1 0.2 0.4 359.0 0.01

=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," ")

10.8% 187.4% 20.2% 20.2% 171.2% 34.6%

10.3% 205.3% 21.0% 21.0% 183.2% 38.6%

=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

2002

2003 179500.0 0.0 5000.0 7500.0 0.0 -14600.0 0.0 -1500.0 0.0 175900.0

105000.0 105000.0

0.0 0.0 20000.0 0.0 -5.0 19995.0 300895.0