Select a publicly held company of your choice of interest to you (or you may use the financial statements of your company but you must provide that data source if you use this option). Obtain a copy of the companys core balance sheet, income statement, and statement of cash flows for the most recent 2 years. For this activity, identify the most recent year as Year 2 and the second most recent year as Year 1. Build a new Excel model of the companys balance sheet and income statement for years 1 and 2 using the template provided. Years 1 and 2 dollar data should be provided in two adjacent columns in your Excel model. You may consolidate some line items on the publicly available statements if appropriate. If the statements are too abbreviated, you may have to investigate the firms annual report to gather a bit more detail to make your analysis meaningful. Build a common size balance sheet analysis using the Excel template on the same worksheet page as your balance sheet, in the columns to the right of the dollar value entries for the balance sheet. Include common base year analysis, as well as combined common size common base year analysis. See definitions below from the homework document. Thus, on a single balance sheet worksheet (tab) you should have: 1) the dollar values, 2) the common size values, 3) the common base year values and 3) the combined common size common base year values in columns to the right of the dollar values. Use Excel formulas for the computations. Build a common size income statement analysis using the Excel template provided. Include common base year analysis, as well as combined common size common base year analysis. See definitions below from the homework document. Thus, on a single income statement worksheet (tab) you should have: 1) the dollar values, 2) the common size values, 3) the common base year values and 3) the combined common size common base year values in columns to the right of the dollar values. Use Excel formulas for the computations. Complete the ratio analysis worksheets provided at the end of this document for at least 5 of the 8 ratios, i.e. 5 of the Ratio 1  Ratio 8 worksheets provided. Note: some example industry average data (upper, middle, and lower quartiles) have been provided, but these are just examples. You may gather other industry average quartile data OR you may assume industry values for illustration (if assumed, then note that). Recall, upper, middle and lower quartile values indicate ranges that 25% (i.e. a quarter) of the reporting data fall. Thus, 25% above the upper quartile, 25% between the upper and middle quartile, 25% between the middle and lower quartile, and 25% below the lower quartile. You may consider using the following source for some Key Business Ratios: Ratio 1 Name & Formula: 1.What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4.Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 2.6 Middle quartile 1.8 Lower quartile 1.4 6.What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 2 Name & Formula: Days Cash on Hand = DCH = (Cash & Equivalents) / [(Operating Expenses  Depreciation)/365] 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile n/a days Middle quartile n/a Lower quartile n/a 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 3 Name & Formula: Days in Accounts Receivables = DAR = AR/average daily revenues = AR / (revenue/365) 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 43 days Middle quartile 31 Lower quartile 14 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 4 Name & Formula: Debt to Equity = total debt / total equity 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 2.4 Middle quartile 0.78 Lower quartile 0.33 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11.What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 5 Name & Formula: Operating Profit Margin 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5.Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile n/a %, BUT Net PM = 4.9% Middle quartile n/a, BUT Net PM = 1.5% Lower quartile n/a, BUT Net PM = 0.5% 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 6 Name & Formula: Net Profit Margin = net income / total revenue 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5.Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 4.9% Middle quartile 1.5 Lower quartile 0.5 7. What are some actions that could INCREASE this ratio? 9.What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11.What actions and/or decisions (if any) in your operational area/unit could influence this ratiRatio Analysis Activity Ratio 7 Name & Formula: Return on Assets 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 10% Middle quartile 3.3 Lower quartile 1.3 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 8 Name & Formula: Return on Equity = net income / total equity 1. What does this ratio tell you? 2. In general, would you like this ratio to be higher or lower? Why? 3. Compute this ratio for Year 2: 4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse from year 1 to year 2? 5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry? Upper quartile 12.5% Middle quartile 5.2 Lower quartile 3.5 6. What are some risks or concerns if this ratio is TOO LOW? 7. What are some actions that could INCREASE this ratio? 8. What are some risks or concerns if this ratio is TOO HIGH? 9. What are some actions that could DECREASE this ratio? 10. Why would this ratio be commonly considered as a key ratio at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence this ratio? Ratio Analysis Activity Ratio 9 Name & Formula: Cash Flow Statement Ratios = Cash Flow from Operations (CFO) relative to other items, such as: Note: CFO = top category of CF Statement. Year 2 Year 1 CFO / Revenue = CFO / Operating Income = CFO / Total Assets = CFO / Total Equity = CFO / Total Debt = 1.In general terms, what do these ratios & ratios like these tell you? 2. In general, would you like these ratios to be higher or lower? Why? 3. Compute these ratios for Years 1 & 2 in the columns above: 4. Trend Analysis: In general terms, did the ratios generally get better or worse? 5. Industry Analysis: Why would it be valuable to evaluate how a firms CFO ratios (such as the ones above) compare with the industry averages for the ratios? 6. What are some risks or concerns if these ratios are TOO LOW? 7. What are some actions that could INCREASE these ratios? 8. What are some risks or concerns if these ratios are TOO HIGH? 9. What are some actions that could DECREASE these ratios? 10. Why would these ratios be commonly considered as key ratios at your organization, and in organizations in general? 11. What actions and/or decisions (if any) in your operational area/unit could influence these ratios? 

3 1 Balance Sheet Common Size Analysis 4 2 B Yr2$value C Yr1$value D Yr2CS E Yr1CS F 5 3 Dollar Values Common Size (CS) (% of TA) 6 4 7 5 Balance Sheet Accounts Year 2 Year 1 enter date 2 here enter date 1 here Year 2 (% of TA) enter date 2 here Year 1 (%of TA) enter date 1 here 8 6 Asset Accounts 9 7 10 8 11 9 12 10 13 11 14 12 15 13 16 14 17 15 18 16 19 17 20 18 21 22 23 24 25 26 27 2222222 19 20 21 22 Note: Add or delete lines as appropriate 23 24 25 Liabilities, Debt, and Equity Accounts Balance Sheet Analysis Income Statement Analysis Other as desired + CBY G Common Base Year (CBY) Yr2$value / Yr1$value Combinded CS & CBY H Combined CS & CBY Yr2CS/Yr1CS