1) 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). 2) Obtain a...

1) 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).

2) Obtain a copy of the company’s 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”.

3) Build a new Excel model of the company’s balance sheet and income statement for years 1 and 2. For each of your statements, 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 firm’s annual report to gather a bit more detail to make your analysis meaningful. For example, your Balance Sheet should include the main sub-categories of Current Assets and should NOT simply list the total current assets without including the basic main components.

4) Build a common size balance sheet analysis in Excel on the same worksheet page as your balance sheet, in the columns to the right of the dollar value entries for the balance sheet. Also include a common base year analysis, as well as a combined common size and common base year analysis in Excel. See definitions below from detailed 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.

5) Build a common size income statement analysis in Excel. Also include a common base year analysis, as well as a combined common size and common base year analysis in Excel. See definitions below from detailed 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.

6) 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. Recall, a upper, middle and lower quartiles indicate ranges such that 25% (i.e. a quarter) of the reporting data fall above the upper quartile, 25% (i.e. a quarter) of the reporting data fall between the upper and middle quartile, 25% (i.e. a quarter) of the reporting data fall between the middle and lower quartile, and finally, 25% (i.e. a quarter) of the reporting data fall below the lower quartile. You may consider using the following source for some Key Business Ratios: http://libguides.bellevue.edu/az.php?a=k.

7) Complete the ratio analysis worksheet RATIO 9 (Cash Flow Ratios) provided at the very end of this document.

8) Provide a cover page to your report, with all appropriate professional details (i.e. name, date, project title, etc.)

9) Provide an approximately 2 page (double spaced) professional summary of your work and include it with your project.

10) Submission: Post your completed work to the Financial Statements Analysis Activity DB. Copy and paste your Word report (cover page, professional summary, and ratio worksheets) into a new post & attach both your Word report and Excel workbook. All documents should CLEARLY identify what company you are reviewing and your sources for all data.

Recall: Ratio Analysis – Common Size Statement Definitions

Recall, from the “Other Questions” section of the Ch. 2 Ratios Detailed Assignment:

Ratios 3. Common Size Analysis: Definitions: Common size balance sheet = all balance sheet entries are divided by total assets and thus expressed as a percent of total assets. Common size income statement = all income statement entries are divided by total sales and thus expressed as a percent of total assets. Explain & briefly illustrate how one would compute the common size balance sheet and common size income statement for the most current year for the Global Petroleum Corporation. Explain the value of common size balance sheets and income statements.

Ratios 4. Common Base Year Analysis: Definition = each entry for a particular statement or report for a given year is divided by its amount from the base year. Explain & briefly illustrate how one would compute the common base year balance sheet for Global Petroleum Corporation. Explain the value of common base year balance sheets and income statements.

Ratios 5. Combined Common Size and Common Base Year Analysis: Definition: A combined common size and common base year balance sheet is essentially the same as the common base year, except one uses the common size statements (i.e. the percent of total asset results) for the inputs, rather than the raw dollar entries. Thus, one takes each common size entry for a given year and divides by its common size entry for the base year. Explain & briefly illustrate how one would compute the combined common size and common base year balance sheet. Explain the value of combined common size and common base year balance sheets and income statements.

Ratios 6. What is 1. trend analysis versus 2. industry (a.k.a.cross sectional) analysis?

Ratio Analysis Activity

Ratio 1 Name & Formula:

Current Ratio = CR = CA/CL

1. What does this ratio tell you?

2. Compute this ratio for Year 2:

3. In general, would you like this ratio to be higher or lower? Why?

4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse?

5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry?

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. Compute this ratio for Year 2:

3. In general, would you like this ratio to be higher or lower? Why?

4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse?

5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry?

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. Compute this ratio for Year 2:

3. In general, would you like this ratio to be higher or lower? Why?

4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse?

5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry?

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. Compute this ratio for Year 2:

3. In general, would you like this ratio to be higher or lower? Why?

4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse?

5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry?

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 = (revenues – total operating expenses) / total revenues

1. What does this ratio tell you?

2. Compute this ratio for Year 2:

3. In general, would you like this ratio to be higher or lower? Why?

4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse?

5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry?

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 6 Name & Formula:

Net Profit Margin = net income / total revenues

Aka Return on Sales

1. What does this ratio tell you?

2. Compute this ratio for Year 2:

3. In general, would you like this ratio to be higher or lower? Why?

4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse?

5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry?

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 7 Name & Formula:

Return on Assets = net income / total assets

1. What does this ratio tell you?

2. Compute this ratio for Year 2:

3. In general, would you like this ratio to be higher or lower? Why?

4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse?

5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry?

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. Compute this ratio for Year 2:

3. In general, would you like this ratio to be higher or lower? Why?

4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse?

5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry?

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:

1. What does this ratio tell you?

2. Compute this ratio for Year 2:

3. In general, would you like this ratio to be higher or lower? Why?

4. Trend Analysis: Compute this ratio for Year 1. Did the ratio get better or worse?

5. Industry Analysis: Estimates of industry averages for this ratio: How does the ratio compare with the industry?

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?