just ration analysis and dupont analysis

TI G H 1 2 3 A B D E FINANCIAL ANALYSIS MODEL Note: Enter data in Blue-coded cells: Black cells are computer generated ANALYST'S NAME NAME OF COMPANY FIRST CALENDAR YEAR OF DATA 1996 = 4 5 NUMBER OF YEARS OF DATA SO 1996 $1,500 $1.000 $500 1997 $1,550 $1.030 $520 1998 $1,700 $1.040 $660 SO SO =IF($B$8>4,F13-F14," ") 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 $155 $275 $2 $12 SO SO SO SO INCOME STATEMENT Sales Revenue Less: Cost of Goods Sold Gross Profits Less: Operating Expenses: Selling Expense General / Admin. Exp. Lease Expense Depreciation Expense Total Operating Expenses Operating Profits Less: Interest Expense Net Profits Before Taxes Less: Taxes Net Profit After Taxes Less: Pref. Stock Divds. Earnings Available for Common Stockholders $140 $260 $5 $10 $415 $85 $23 S62 $10 $52 s1 $150 $270 SO sul $431 $89 $29 $60 $12 $48 SO SO SO $0 SO STE(SB$84,SUM(F17:F20)," =IF($B$84,F15-F21," ") $216 $30 $186 $15 $171 =IF($B$84,F22-F23,"") SO SO =IF($B$8>4,F24-F25," ") SO SO $51 $48 $171 IF($B$8>4,F26-F27,"") 28 29 30 > TET fx A B D E F G H S31 $82 $104 $145 $362 $12 $66 $152 $191 $421 $18 $68 $160 $200 $446 SO SO SO SO $0 SO SO =IF ($B$8>4,SUM(F33:F36),"") BALANCE SHEET Current Assets: Cash Marketable Securities Accounts Receivable Inventories Total Current Assets Gross Fixed Assets (at cost): Land & Buildings Machinery and Equipment Furniture & Fixtures Vehicles Other (Inc. Fin. Leases) Total Gross Fixed Assets Less: Accumulated Depreciation Net Fixed Assets Other Assets Total Assets $180 SO SO SO SO S180 SO SO SO SO $195 SO SO SO SO $195 $63 $132 SO $553 $200 $0 SO SO SO $200 $65 $135 SO $0 SO SO SO =IF($B$84,SUM(F39:F43),"") =IF($B$8>4,F44-F45,"") $128 SO $490 $581 =IF($B$8>4,F37+F46," ") 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 66 67 68 =IF($B$84,F37+F46,"") SO $0 SO SO SO SO so Current Liabilities: Accounts Payable Notes Payable Accruals Taxes Payable Other Current Liabilities Total Current Liabilities L/I Debt (Inc. Financial Leases) Total Liabilities Preferred Stock Common Stock Paid-In Capital In Excess of Par Retained Earnings Total Stockholders' Equity Total Liabs. & Stockhldrs' Equity Reconciliation TA & TL/SE Number of Common Shares End-of-Year Stock Price 1996 $126 $190 $25 SO SO $341 $40 $381 SO $20 $30 $59 $109 $490 = 1997 $136 $200 $27 SO SO $363 $38 $401 SO $20 $30 $102 $152 $553 SO 500 $5.00 1998 $150 $140 $28 SO SO $318 $13 $331 SO $20 $30 $200 $250 $581 SO 500 $5.50 SO SO SO SO SO SO =IF($B$8>4,SUMF5 SO ETE($B$824,F56+F57 SO SO SO SO DIE(SB$824,SUM(F59:F62)," ") =IF($B$8>4,F58+F63,"") =IF(SB$8 0 $0.00 SO 500 $3.00 0 $0.00 fx A 00 D E F G H RATIO ANALYSIS Current Ratio Quick Ratio =IF($B$8-4,F37/F56," ") =IF($B$8>4,(F37-F36)/F56," ") Inventory Turnover Average Collection Period Fixed Asset Turnover Total Asset Turnover =IF($B$8-4,F14/F36," ") =IF($B$8>4,F35/(F13/360)," ") =IF(SB$8>4,F13/F46," ") =IF($B$8>4,F13/F48,"") Debt Ratio Debt-to-Equity Times Interest Earned =IF($B$8-4,F58/F48,"") =IF($B$8-4,F57/F63,"") =IF($B$8>4,F22/F23,"") Gross Profit Margin perating Profit Margin Profit Margin =IF($B$8>4,F15/F13," ") =IF($B$8>4,F22/F13," ") =IF($B$8>4,F26/F13," ") 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 Return on Total Assets (ROA) Return on Equity (ROE) O =IF($B$8>4,F26/F48,"") =IF($B$8>4,F26/F63,"") Earnings Per Share =IF($B$84,F28/F66," ") Price Earnings Ratio =IF(AND(F67>0,F89>0),F67/F89 DUPONT ANALYSIS Net Profit AT/Sales Sales/Total Assets ROA Net Profit AT/Total Assets Total Assets/Stockhldrs. Equity ROE =IF($B$8-4,F26/F13," "') =IF($B$84,F13/F 48,"") =IF($B$8>4F94F95," ") =IF($B$8-4,F26/F48,"") =IF($B$8>4,F48/F63," ") = IF($B$8>4,F97*F98,"") A 00 D E TI G 1996 1997 $48 $11 (548) ($46) SO $10 $2 $0 SO 1998 $171 $12 (58) ($9) SO $14 $1 SO SO S181 04 STATEMENT OF CASH FLOWS CASH FLOW FROM 05 OPERATING ACTIVITIES 06 Net Profit After Taxes 07 Depreciation 08 Decrease in Accts. Receiv. 09 Decrease in Inventories 0 Decrease in Other Assets 1 Increase in Accounts Pay. 2 Increase in Accruals 3 Increase in Taxes Payable 4 Increase in Other Cur. Lia. 5 Cash Flow from Oper. Act. 6 CASH FLOW FROM 7 INVESTMENT ACTIVITIES 8 Increase in Gross Fixed Assets 9 Cash Flow from Investments 20 CASH FLOW FROM 21 FINANCING ACTIVITIES E2 Increase in Notes Payable 3 Increase in Long-Term Debt 24 Changes in Stockholders' Equity 25 Preferred Dividends Paid E6 Common Dividends Paid (-) E7 Activities 28 E9 Net Incr. in Cash /Mark. Securities 30 31 12 33 14 NO ($15) $10 (82) SO SO (560) ($25) SO SO (585 ($35) $91