FINANCIAL ANALYSIS MODEL RATIO

123456789 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 A B C D E F G H I J FINANCIAL ANALYSIS MODEL Note: Enter data in Blue-coded cells; Black cells are computer generated ANALYST'S NAME Dr. Glenn L. Stevens NAME OF COMPANY Sample Firm FIRST CALENDAR YEAR OF DATA 1996 NUMBER OF YEARS OF DATA 3 INCOME STATEMENT 1996 1997 1998 Sales Revenue $1,500 $1,550 $1,700 $0 $0 Less: Cost of Goods Sold $1,000 $1,030 $1,040 $0 $0 Gross Profits $500 $520 $660 =IF($B$8>4,F13-F14," ") Less: Operating Expenses: Selling Expense $140 $150 $155 $0 $0 General /Admin. Exp. $260 $270 $275 $0 $0 Lease Expense $5 $0 $2 $0 $0 Depreciation Expense $10 $11 $12 $0 $0 Total Operating Expenses $415 $431 $444 =IF($B$8>4,SUM(F17:F20)," ") Operating Profits $85 $89 $216 =IF($B$8>4,F15-F21," ") Less: Interest Expense $23 $29 $30 $0 $0 Net Profits Before Taxes $62 $60 $186 =IF($B$8>4,F22-F23," ") Less: Taxes $10 $12 $15 $0 $0 Net Profit After Taxes $52 $48 $171 =IF($B$8>4,F24-F25," ") Less: Pref. Stock Divds. $1 $0 $0 $0 $0 Earnings Available for Common Stockholders $51 $48 $171 IF($B$8>4,F26-F27," ") BALANCE SHEET Current Assets: Cash $31 $12 $18 $0 $0 Marketable Securities $82 $66 $68 $0 $0 Accounts Receivable $104 $152 $160 $0 $0 Inventories $145 $191 $200 $0 $0 Total Current Assets $362 $421 $446 =IF($B$8>4,SUM(F33:F36)," ") Gross Fixed Assets (at cost): Land & Buildings $180 $195 $200 $0 $0 Machinery and Equipment $0 $0 $0 $0 $0 Furniture & Fixtures $0 $0 $0 $0 $0 Vehicles $0 $0 $0 $0 $0 Other (Inc. Fin. Leases) $0 $0 $0 $0 $0 Total Gross Fixed Assets $180 $195 $200 =IF($B$8>4,SUM(F39:F43)," ") Less: Accumulated Depreciation $52 $63 $65 $0 $0 Net Fixed Assets $128 $132 $135 =IF($B$8>4,F44-F45," ") Other Assets $0 $0 $0 $0 $0 Total Assets $490 $553 $581 =IF($B$8>4,F37+F46," ") Current Liabilities: 1996 1997 1998 =IF($B$8>4,F37+F46," ") Accounts Payable $126 $136 $150 $0 $0 Notes Payable $190 $200 $140 $0 $0 Accruals $25 $27 $28 $0 $0 Taxes Payable $0 $0 $0 $0 $0 Other Current Liabilities $0 $0 $0 $0 $0 Total Current Liabilities $341 $363 $318 =IF($B$8>4,SUM(F51:F55)," ") L /T Debt (Inc. Financial Leases) $40 $38 $13 $0 $0 Total Liabilities $381 $401 $331 =IF($B$8>4,F56+F57," ") Preferred Stock $0 $0 $0 $0 $0 Common Stock $20 $20 $20 $0 $0 Paid-In Capital In Excess of Par $30 $30 $30 $0 $0 Retained Earnings $59 $102 $200 $0 $0 Total Stockholders' Equity $109 $152 $250 =IF($B$8>4,SUM(F59:F62)," ") Total Liabs. & Stockhldrs' Equity $490 $553 $581 =IF($B$8>4,F58+F63," ") Reconciliation TA & TL/SE $0 $0 $0 =IF($B$8>4,F64-F48," ")66 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 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 A B C D E F G H I J Number of Common Shares 500 500 500 0 0 End-of-Year Stock Price $3.00 $5.00 $5.50 $0.00 $0.00 RATIO ANALYSIS Current Ratio 1.06 1.16 1.40 =IF($B$8>4,F37/F56," ") Quick Ratio 0.64 0.63 0.77 =IF($B$8>4,(F37-F36)/F56," ") Inventory Turnover 6.90 5.39 5.20 =IF($B$8>4,F14/F36," ") Average Collection Period 24.96 35.30 33.88 =IF($B$8>4,F35/(F13/360)," ") Fixed Asset Turnover 11.72 11.74 12.59 =IF($B$8>4,F13/F46," ") Total Asset Turnover 3.06 2.80 2.93 =IF($B$8>4,F13/F48," ") Debt Ratio 0.78 0.73 0.57 =IF($B$8>4,F58/F48," ") Debt-to-Equity 0.37 0.25 0.05 =IF($B$8>4,F57/F63," ") Times Interest Earned 3.70 3.07 7.20 =IF($B$8>4,F22/F23," ") Gross Profit Margin 33.33% 33.55% 38.82% =IF($B$8>4,F15/F13," ") Operating Profit Margin 5.67% 5.74% 12.71% =IF($B$8>4,F22/F13," ") Net Profit Margin 3.47% 3.10% 10.06% =IF($B$8>4,F26/F13," ") Return on Total Assets (ROA) 10.61% 8.68% 29.43% =IF($B$8>4,F26/F48," ") Return on Equity (ROE) 47.71% 31.58% 68.40% =IF($B$8>4,F26/F63," ") Earnings Per Share $0.10 $0.10 $0.34 =IF($B$8>4,F28/F66," ") Price/Earnings Ratio 29.41 52.08 16.08 =IF(AND(F67>0,F89>0),F67/F89," ") DUPONT ANALYSIS Net Profit AT/Sales 3.47% 3.10% 10.06% =IF($B$8>4,F26/F13," ") Sales/Total Assets 306.12% 280.29% 292.60% =IF($B$8>4,F13/F48," ") ROA 10.61% 8.68% 29.43% =IF($B$8>4,F94*F95," ") Net Profit AT/Total Assets 10.61% 8.68% 29.43% =IF($B$8>4,F26/F48," ") Total Assets/Stockhldrs. Equity 449.54% 363.82% 232.40% =IF($B$8>4,F48/F63," ") ROE 47.71% 31.58% 68.40% =IF($B$8>4,F97*F98," ") STATEMENT OF CASH FLOWS CASH FLOW FROM OPERATING ACTIVITIES 1996 1997 1998 Net Profit After Taxes $48 $171 Depreciation $11 $12 Decrease in Accts. Receiv. ($48) ($8) Decrease in Inventories ($46) ($9) Decrease in Other Assets $0 $0 Increase in Accounts Pay. $10 $14 Increase in Accruals $2 $1 Increase in Taxes Payable $0 $0 Increase in Other Cur. Lia. $0 $0 Cash Flow from Oper. Act. ($23) $181 CASH FLOW FROM INVESTMENT ACTIVITIES Increase in Gross Fixed Assets ($15) ($5) Cash Flow from Investments ($15) ($5) CASH FLOW FROM FINANCING ACTIVITIES Increase in Notes Payable $10 ($60) Increase in Long-Term Debt ($2) ($25) Changes in Stockholders' Equity $0 $0 Preferred Dividends Paid $0 $0 Common Dividends Paid (-) ($5) Cash Flow from Financing Activities $3 ($85) Net Incr. in Cash /Mark. Securities ($35) $91