Historical Balance Sheets

Instructions Instructions for Using HISTORY "Word has come down from the dean That with the aid of a teaching machine Ole' Oedipus Rex could have learned about sex Without ever disturbing the Queen." Lewis Spitz HISTORY is intended as a companion to ANALYSIS FOR FINANCIAL MANAGEMENT, 6th edition, by Robert C. Higgins. Some basic familiarity with Microsoft Excel will be helpful, but not necessary, to use this program. HISTORY produces a financial analysis of up to five years of user-supplied, historical financial data. Results appear in four, convenient tables of one page each. Balance sheet and income statement entries can be customized to a limited degree to reflect the reporting practices of individual companies. HISTORY can be used with its companion program, PROFORMA, to produce up to five-year forecasts of future financial performance and possible funding needs. Input Table A B Output C D E F Contents Historical balance sheets 1-5 years Historical income statements 1-5 years Change in balance sheet accounts Common-size balance sheets Common-size income statements Ratio and sustainable growth analysis INSTRUCTIONS FOR USE All of the worksheets in HISTORY are tied to the Main Menu, from which you selected the Instructions button. To launch the other worksheets, simply choose the appropriate button from the Main Menu. You can return to the Main Menu at any time by selecting the Main Menu button from any worksheet. You may want to begin by printing a copy of these instructions. To do so, use the regular Print command in your software. You can print any of the Output Tables this way. Enter Data Leave these instructions and return to the main menu by pressing the button at the top of this page. At the main menu, select Input Data. A dialog box will appear and you can enter the information about your company. After entered your data and hit "OK", you will go automatically to the Historical Balance Sheets so you can begin your analysis. Input your numbers in the blue areas. Don't worry about formatting, that is done automatically. You may also customize any label in blue simply by typing over the field name. When you've finished, click the button for the Income Statement and continue entering data in the blue areas until you have completed the historical income statements and balance sheets. Page 1 Instructions Perform Calculations When you have entered your data, press the Analyze button and you will return to the main menu. View Results To view the results of the analysis, press the View Output button. You may then select from the three output tables which you want to view. If at any time you want to make changes to the data in the historical tables, you can also view those by pressing the View Output button and selecting the input table. Once you are in the table, change the data, return to the Income Statement (unless you are there already), and press Analyze again. Clear The Clear button on the Main Menu clears all the tables so you can analyze a new company. If you want to save your work, use the Save As command in your software and rename this program. Page 2 Instructions will be helpful, lance sheet and ce and possible riate button from the field name. d balance sheets. Page 3 Instructions the data, return Page 4 Definitions DEFINITIONS OF RATIOS APPEARING IN TABLE F Return on equity = net income/total equity Return on assets = net income/total assets Return on invested capital = [(income before tax + interest expense) (1 - provision for tax/income before tax)]/(short term debt + other s.t. debt + current portion long term debt + long term debt & capital leases + minority interest + total equity) Profit margin = net income/net sales Gross margin = gross profit/net sales Assets to equity = total assets/total equity Tot. liabilities to assets = total liabilities/total assets Tot. liabilities to equity = total liabilities/total equity Long term debt to equity = (l.t. debt & capital leases + currrent portion l.t. debt)/total equity Times interest earned = (income before tax + interest expense)/interest expense Times burden covered = (income before tax + interest expense)/ interest expnse + current portion l.t. debt/[1 - provision for tax/income before tax]) Current ratio = current assets/current liabilities Acid test = (current assets - inventory)/current liabilities Asset turnover = net sales/total assets Inventory turnover = cost of goods sold/inventory Collection period = accounts receivable/(net sales/365) Days sales in cash = cash/(net sales/365) Payables period = accounts payable/(cost of goods sold/365) Fixed asset turnover = net sales/net property, plant, etc. Retention ratio = [(1 - common dividends)/net income]*100 Page 5 Definitions est + total equity) t)/total equity Page 6 MainMenu MAIN MENU  Copyright 2000, Irwin/McGraw-Hill. This computer program is protected by copyright law. Page 7 MainMenu History Page 8 DData Month Date January Februrary March April May June July August September October November December 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Analysis Years 1 2 3 4 5 Page 9 HistoryDialog Input Company Data Company Name Currency, Scale (e.g. $ in Millions) Fiscal Year End Spencer Sporting Goods (000 omitted) January Februrary 1994 1 2 1 2 OK Cancel First Analysis Year 1 Number of Years to Analyze Page 10 TableViewer HISTORY Tables Select The Table You Wish to View Input Tables Table A: Historical Balance Sheets Table B: Historical Income Statements Output Tables Table C: Change in Balance Sheet Accounts Table D: Common-size Balance Sheets Table E: Common-size Income Statements Table F: Ratio and Sustainable Growth Analysis OK Cancel Page 11 1 2 3 4 5 6 7 8 9 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 A B Table A. Historical Balance Sheets C D E F Spencer Sporting Goods (000 omitted) December 31 ASSETS Cash Securities Accounts receivable Inventory Other current assets Total current assets Net property, plant, etc. Other investments Other long-term assets Total Assets $ 1994 36 402 $ 1995 33 1996 $ 25 $ 1997 15 438 33 25 15 - $ 438 $ 33 $ 25 $ 15 - LIABILITIES AND SHAREHOLDERS' EQUITY Short term debt Other short-term debt Accounts payable Accrued taxes Other accruals Current portion l-t. debt Total current liabilities L-t. debt and capital leases Deferred taxes Other deferrals Minority interests Other long-term liabilities Total liabilities Preferred stock Owners' equity Total equity Total liabilities & equity - $ $ - $ $ - $ $ - $ $ - - 8/13/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill 1 2 3 4 5 6 7 8 9 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 A B Table B. Historical Income Statements C D E F Spencer Sporting Goods (000 omitted) December 31 1994 Net sales Cost of goods sold Gross profit Operating expenses Operating income Non-op. income (expense) Interest income Interest expense Income before tax Provision for tax Net income Ex. gains (loss) net tax Other gains (losses) Preferred dividends Available for common Common dividends Added to retained earnings $ $ $ $ $ - 1995 1996 1997 - - - - - - - - - - - - - $ - $ - $ - - - $ - $ - - - $ - $ - - 8/13/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill Table C. Changes in Balance Sheet Accounts* Spencer Sporting Goods (000 omitted) December 31 ASSETS Cash Securities Accounts receivable Inventory Other current assets Total current assets Net property, plant, etc. Other investments Other long-term assets Total Assets $ 1995 1996 1997 (3) $ (8) $ (10) (402) (405) (8) $ (8) (10) (10) (15.00) (15) (15.00) $ (405) $ LIABILITIES AND SHAREHOLDERS' EQUITY Short term debt Other short-term debt Accounts payable Accrued taxes Other accruals Current portion l-t. debt Total current liabilities L-t. debt and capital leases Deferred taxes Other deferrals Minority interests Other long-term liabilities Total liabilities Preferred stock Owners' equity Total equity Total liabilities & equity $ $ $ - - *ASSETS: + (Positive) = Use and - (Negative) = Source ; LIABILITIES: + = Source, - = Use 8/13/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill Table D. Common Size Balance Sheets Spencer Sporting Goods (000 omitted) December 31 ASSETS Cash Securities Accounts receivable Inventory Other current assets Total current assets Net property, plant, etc. Other investments Other long-term assets Total Assets 1994 8.2% 0.0% 91.8% 0.0% 0.0% 100.0% 0.0% 0.0% 0.0% 100.0% 1995 100.0% 0.0% 0.0% 0.0% 0.0% 100.0% 0.0% 0.0% 0.0% 100.0% 1996 100.0% 0.0% 0.0% 0.0% 0.0% 100.0% 0.0% 0.0% 0.0% 100.0% 1997 100.0% 0.0% 0.0% 0.0% 0.0% 100.0% 0.0% 0.0% 0.0% 100.0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! LIABILITIES AND SHAREHOLDERS' EQUITY Short term debt #DIV/0! #DIV/0! Other short-term debt #DIV/0! #DIV/0! Accounts payable #DIV/0! #DIV/0! Accrued taxes #DIV/0! #DIV/0! Other accruals #DIV/0! #DIV/0! Current portion l-t. debt #DIV/0! #DIV/0! Total current liabilities L-t. debt and capital leases Deferred taxes Other deferrals Minority interests Other long-term liabilities Total liabilities Preferred stock Owners' equity Total equity Total liabilities & equity #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 8/13/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill Table E. Common Size Income Statements Spencer Sporting Goods (000 omitted) December 31 Net sales Cost of goods sold Gross profit Operating expenses Operating income Non-op. income (expense) Interest income Interest expense Income before tax Provision for tax Net income Ex. gains (loss) net tax Other gains (losses) Preferred dividends Available for common Common dividends Added to retained earnings 1994 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1995 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1996 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 1997 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 8/13/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill Table F. Ratio and Sustainable Growth Analysis Spencer Sporting Goods 1994 PROFITABILITY RATIOS: Return on equity (%) Return on assets (%) Return on inv. captial (%) Profit margin (%) Gross margin (%) LEVERAGE & LIQUIDITY RATIOS: Assets to equity (%) Total liabilities to assets (%) Total liabilities to equity (%) Long term debt to equity (%) Times interest earned (x) Times burden covered (x) Current ratio (x) Acid test (x) TURNOVER-CONTROL RATIOS: Asset turnover (x) Inventory turnover (x) Collection period (days) Days sales in cash (days) Payables period (days) Fixed asset turnover (x) #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! 1995 #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! 1996 #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! 1997 #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Pro Forma Sustainable Growth Analysis 1994 REQUIRED RATIOS: Profit margin (P) Retention ratio (R) Asset turnover (A) Financial leverage# (T) Sustainable growth rate (g*) Actual growth rate (g) #DIV/0! #DIV/0! 0.0% n/a n/a n/a 1995 #DIV/0! #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! 1996 #DIV/0! #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! 1997 #DIV/0! #DIV/0! 0.0% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #Defined as assets divided by beginning-of-period equity 8/13/2008 Analysis for Financial Management, 6th Ed. by Robert C. Higgins Published by Irwin/McGraw-Hill