* You only need to do the part 4 of this project.

* Please follow the introduction carefully.

* The company is CSX Corporation.

Refer to This document

This Financial Statement Analysis Project extends and enriches your knowledge of financial accounting by using the sources, information, and tools that support decision making in the “real” business world.  Activities help you acquire expertise in analyzing and interpreting annual reports and other business information. The project consists of five parts worth a total of 100 points as summarized below:

 

Item	Due Date* and Time/	Format/ Delivery	Point Value	Brief Description
Part 1	Fri, 9/25, 5pm	Show hard copy to instructor and post in BlackBoard Group Journal	5 pts	Verify assigned company, print out the 10-k financial statements, and post required information to Blackboard
Part 2	Fri, 10/16, 5pm	Blackboard Assignment	10 pts	Individually enter financial statement information into an Excel template
Part 3	Fri, 11/06, 5pm	Blackboard Assignment	25 pts	Individually analyze your company’s financial statements using various Excel tools. Must be Reviewed.
Part 4	Mon, 11/23, 5pm	Hard Copy in my office and Blackboard Assignment	25 pts	Individually prepare a written analysis communicating your findings in Part 3
Part 5	Week of Mon, 12/07	Class Presentation / BlackBoard	25 pts	As a group prepare and deliver a presentation that compares and contrasts your companies
Peer Eval	Exam week	Take home	Up to 5 pts	Individually evaluate group members
Professional-ism	Done by professor at the end of the project		5 pts	See criteria later in this document

 

 

Recommendation

* Due dates and times are firm. A penalty of 10% of the points possible for a particular part will be assessed for each calendar day the work for a part is late. Any student not submitting Part 4 by April 15^th (2 days late) will not be permitted to present with his or her group and will receive no points for the presentation.

Part 1:   Individual Company Selection

1. Your industry group and company is assigned by your instructor.

You MUST verify that the company assigned to you is a qualified “public” company.  The first step in verifying your company’s suitability is to locate its most recent 10-K[1] filing. Links to “investor relations” or “shareholder information” at the top or bottom of a company’s main page often provide access to this form. Form 10-K filings can also be found at: http://sec.gov/edgar/searchedgar/companysearch.html .  On this site, search for the company, and then select its most current 10K form.  Using the most recent as well as previous versions of that form, ensure that your company qualifies for this project by verifying that the company:

1. Is the “parent company“ of a group headquartered in the United States (Hint: parent companies use the word “consolidated” in their financial statement titles to indicate inclusion of the combined data of all subsidiaries)
2. Files US GAAP Financial Statements with the SEC on Form 10-K
3. Has its stock traded on one of the major US exchanges (NYSE, NASDAQ, AMEX).
4. Has NOT undergone bankruptcy, merger or significant divestiture within the past 3 years (Hint: read about the company’s recent history and review the financial statements for evidence that the company, for example, changed names or doubled—or halved—in size).
5. Has filed 5 years’ worth of forms 10-K, consistent with items a. through d.), above. (Hint – Find Item 6 in the form 10-K. This is a summary of financial data over the past 5 or 10 years. Does the company have at least 5 years of data? Have any years been split or noted as changed?)

2. Once you have verified items 1-6,

1. Download and save the most recently 10-k. The company’s Form 10-K will be your primary source document for this project. Obtain the report in Adobe portable document file format (.pdf).  The pdf file format preserves formatting that will serve as model for the work you do in Excel.  Note that financial information on internet financial sites such as Yahoo and MSN contain information that is condensed and/or modified and not suitable for this project.
2. PRINT OUT a HARD COPY of your company’s audited financial statements (including the auditor’s opinion letter, the financial statements, and the accompanying footnotes). Do NOT print out the entire 10-k document.  Show your print out to your instructor in order to received credit for Part 1.

3. Create an entry in your group’s journal on Blackboard that includes the following:

1. Your name and contact information
2. The full, registered name of the company you were assigned
3. the year end (day, month and year) for the “current” fiscal or calendar year.
4. The .pdf file of your company’s most recent 10-k. (Upload as attachment.)

Part 2:  Individual – Data Entry In Excel Template

Financial Accounting Workshops related to Part 2 will be held as announced.

Note:  Before you start this part of the project, make sure you are using the complete Form 10-K for your company. Secondary information that has been summarized or condensed on financial sites such as Hoovers, Yahoo, or MSN will NOT provide you with the details needed to prepare this project.

1. Retrieve the project Excel template from Blackboard. Enter your identifying information on the first tab of the workbook and save the file with your first and last names.

2. In the “Introduction” text box outlined in blue on the first tab of the template, in your own words, summarize your company’s business activities, products, and the primary industry or industries in which it operates (200 to 300 words). If you do incorporate any language that is not your own, make sure to use quotation marks and clearly indicate the source. You may find preparing this summary in Word and pasting it into the text box provided on the template an efficient way to accomplish this task.  Resize the text box so that it fits the text you’ve placed in it. This will not entail adding any rows or columns to this tab.

3. Copying your financial statements:

1. Using the company’s pdf version of the financial statements, enter your company’s balance sheet and income statement on the appropriate “Balance Sheet” and “Income Stmt” tabs on the Excel template provided.  You should have two years’ of balance sheet data and three years’ of income statement and cash flow data.  Use all of the exact account names and amounts, headings and subheadings, and years of data contained in your company’s To simplify your data entry, use the same ARIAL 11 font throughout your statements.  However, follow the formatting given in your primary document for indents, capitalization, underlining, dollar signs, decimal points, etc.

1. For all differences, subtotals, and totals in the statements, insert formulas to perform the appropriate calculations. Make sure that all account and line descriptions are in column A and use the “wrap text” feature to keep the column width of these items reasonable. Delete empty columns. Make sure all items have the same font and size.

4. Your work will be evaluated, in part, on the following criteria. Please use this checklist to review your document to see that:
   1. All headings, account names, and dollar amounts are the same as on the audited financial statements included in the Form 10-K.
   2. All statements are consistent with the Form 10-K as to text alignment, alignment of amounts, indentations, use of $ and underlines. (The objective here is a consistent, professional presentation of the data, and replicating the published version is usually a good way to achieve that.)
   3. Empty rows and columns are deleted.
   4. Each account and line description is in a single cell, using “wrap text” to control the column width.
   5. All columns showing “####” have been made wide enough to display the full amount.
   6. There are three years of data for the income statement and two years of data for the balance sheet
   7. An ARIAL 11 font is used throughout,. (The Form 10-K may use multiple fonts, but your project file should be as uncluttered and consistent as possible.)
   8. Every difference, subtotal, and total in every financial statement is the result of a formula using cell references and/or Auto Sum.
   9. Appropriate headings, including indication of year ends and the denomination of amounts used in the statement (thousands, millions, etc.) are used on all statements.

1. Save your work as an Excel file.  Consider making an optional appointment to have your work reviewed by a Learning Assistant. After your work is reviewed, make all corrections necessary.

Submit your file to Blackboard no later than the Part 2 due date listed at the top of this document. DO NOT Email your file – it will not count as being submitted.

Part 3:  Individual – Data Analysis

• Make all necessary revisions and corrections to the Part 2 before beginning Part 3.
• Financial Accounting Workshops related to Part 3 will be held as announced.

1. Read Chapter 12 in your text.

2. Make a copy of your completed Balance Sheet and then a copy of your Income Statement using the Edit/Move or Copy Sheet command. Be sure to check the “Create a copy” box indicating you want to make a copy. Insert the copy of the before the Balance Sheet before the Income Statement tab. Insert the copy of the Income Statement Data Summary tab.

3. Review Illustration 12-2 on page 655 of a horizontal analysis of an income statement. Rename the original “Income Stmt” tab “IS Horiz” and prepare a horizontal analysis of your income statement. Because your income statement contains three years of data, you will have to add a “difference” column and a “percentage change” column for the first two years presented, and then add another two columns that compare the middle year to the current year.  Properly label what is shown in each set of columns. Use a technique similar to that of your balance sheet for formulas, formatting, and copying and pasting. Check for appropriate labels, headings, etc.

4. Based on the Illustration 12-2 on page 655 prepare a horizontal analysis of a balance sheet. Rename the original “Balance Sheet” tab “BS Horiz”. On this tab, add two columns to the right of your balance sheet amounts. These extra columns will be used for a “horizontal analysis,” since they compare amounts horizontally across the years presented.  Label the first column “difference” and the second column “percentage change.”  Prepare formulas for both the amount of increase/decrease and percent of increase/decrease of cash. Format the percent to show the percentage sign and one decimal place. Then copy and paste both formulas to the appropriate remaining cells on your balance sheet. Fix any necessary formatting. Check for appropriate labels, headings, etc.

5. Review Illustration 12-3 on page 656 of a vertical analysis of income statements. Rename the copied “Income Stmt (2)” tab “IS Vert” and prepare a vertical analysis of your income statement. Hint: Insert a column between each of the columns of amounts. Use an absolute cell reference to your net sales (or net revenue) amount in your first formula when calculating your percent. Format the cell. Now that formula can be copied to all other elements of the balance sheet for that year. Make a new formula for each of the other years. Format and copy them as well. Check for appropriate labels, headings, etc.

6. Review Illustration 12-3 on page 656 of a vertical analysis of balance sheets. Rename the copied “Balance Sheet (2)” tab “BS Vert”. With reference to the section in your text entitled “VERTICAL ANALYSIS” that begins on page 700, on this tab, compute component percentages for your balance sheet.  This approach to understanding financial statements is often referred to as a “vertical analysis” since it compares amounts within a given year to a significant base amount from that same year.  To perform this analysis, you will need to insert columns after each year of data.  This column will show the percentage of total assets that each item represents.  Use an absolute cell reference to total assets in your first formula when calculating your percent. Format the cell to show the percentage sign and one decimal place. Now that formula can be copied to all other elements of the balance sheet for that year. Make a new formula for the other year. Format and copy it as well. Check for appropriate labels, headings, etc

7. Fill in the amounts requested on the Data Summary tab by using cell references to your balance sheet and income statements whenever possible.

1. Insert the actual years you are analyzing as column headings, with the current year in the on the right in cell G5 (e.g. 2015).
2. In as many instances as possible, use cell references to your BS Horiz and IS Horiz
3. You will need to calculate amounts (e.g. averages) for the data summary sheet. Use cell references to your BS Horiz and IS Horiz in formulas as much as possible.
4. You will need to enter some information manually. This data almost always is available in your Form 10-K in Item 6 – Selected Financial Data or summaries in other parts of the Form 10-K. If you have problems finding this information, you may need to do additional research on the web and/or talk with a reference librarian at Trexler Library to acquire the information.
5. If an amount for your company is zero, leave the cell blank.
6. Amounts in the data table must be positive except for a net loss or a negative cash flow from operations, an income tax benefit and exceptional situations where the amount referenced is the opposite of the usual sign. Therefore if your financial statement lists a negative amount for an item such as cost of sales or interest expense, since you are using cell references, you need to place a “-” sign in front of the cell reference to make the amount positive.

8. On the Charts tab, prepare a separate chart (graph) for each of the items listed below for the 5 years data on the Data Summary tab. Use cell references to the Data Summary tab when preparing your charts. You will have four separate charts on this tab. You may select the type of graph you think appropriate. Remember that charted chronological data should always be presented earliest to latest, left to right.

1. Net Revenue (or Net Sales)
2. Gross Profit Margin
3. Net Income

1. Free Cash Flow

9. On the ratio tab in the appropriate space, insert the actual years you are analyzing as column headings, just as you did on the Data Summary worksheet. Using cell references to the Data Summary tab, calculate the ratios requested in the space provided.

Part 3, Continued

10. Your work will be evaluated, in part, on the following criteria. Please use this checklist to review your document to see that:

1. Adhered to all relevant checklist items from Part 2 apply to Part 3 (alignment, formatting, totals, formulas, decimal places, ###, etc.)
2. BS and IS Horizontal Analysis – cell references used whenever possible for formulas.
3. Balance Sheet Vertical Analysis – all items are divided by an absolute cell reference to Total Assets or Total Liabilities and Stockholders’ Equity.
4. Income Statement Vertical Analysis –all items are divided by an absolute cell reference to net sales (net revenue).
5. Data Summary and Ratio — cell references to balance sheet or income statement used whenever possible. For all calculations, cell references use when preparing formulas.
6. Data Summary and Ratio — appropriate year headings are entered.
7. Data Summary– amounts are positive. The only exceptions are net cash outflows or a Net Loss.
8. Charts – All charts have titles showing the variable graphed (e.g., Net Income)
9. Charts – The appropriate multiple of dollars appears as the Y-axis title.
10. Charts — “Years” is the X-axis title.
11. Charts — Individual years are shown on the X axis.
12. All percentages formatted with the % sign and display one decimal place.
13. Charts – None of the charts show legends since only one variable (Excel calls them series) is being graphed. The name of that variable or series is the name of the chart.

11. Schedule an appointment with one of the learning assistants to review your work.  Make all necessary corrections.  Your Part 3 submission will not be accepted without a completed appointment with one of the learning assistants.

12. Submit your work no later than the assigned due date via the appropriate assignment in Blackboard.

 

Part 4 Individual – Written Company Evaluation

Make all corrections and revisions to Part 3 before beginning Part 4.

Workshop assistance for Part 4 will be available as announced.

Guidance for Financial Statement Analysis, Part IV

Fall 2015

This year’s financial statement analysis memo requires you to integrate your knowledge of business and economic principles with the data you have compiled for your company.  This document provides examples of how you might meet that requirement.  In general, think about how you could connect the economic environment to the behavior and performance of your company.  Start by understanding supply and demand in the company’s product market, and in the market for the inputs it uses.

In the Introduction, you are required to:  “Use your knowledge of economic principles to describe the macroeconomic environment in general, and how that economic environment affects your company’s industry in particular.”

Identify macroeconomic variables that affect your company.  For example: interest rates, unemployment, GDP, and inflation.  Explain how one or more of these variables have influenced your company, either positively or negatively, over the past three to five years.  For example, “Continued low interest rates have allowed the company to borrow at attractive rates and pursue a strategy of aggressive expansion.”  Or, “Slow growth in the overall economy has caused the company to seek growth through acquiring other companies rather than through launching new products.”  You might go on to say something about weak product demand.

 

Also in the introduction: Use your knowledge of business principles to summarize the business strategy your company has employed in order to accomplish its mission in this environment.

Business principles you might mention could range from the economic maxim of profit maximization to the business strategies of low-margin generic product sales versus high-margin premium branding.  Take your cue from what the company says about itself in its financial reports and on its website.  Relate their business approach to what you have identified in the economy and can see in the financial statements.  For example, “The Company has pursued a strategy of low-cost pricing, in order to generate sales volume that will compensate for lower profit margins.”  Or, “Because of increased competition from similar products, the Company has aggressively expanded its marketing efforts.”  You might go on to say something about an increase in supply of similar products from other producers.

In the Summary:  “Use your knowledge of economic and business principles to evaluate how effective company’s strategy has been in responding to the economic conditions you identified in your introduction.”

In your conclusion, refer back to the economic and business conditions from your introduction, and say something about whether the financial reporting result suggest success, or lack of success.  For example, “In light of the steady decrease in gross profit and net income, the increase in sales volume has not been enough to compensate for discount pricing.  Or, “While interest expense has increased steadily, so has profitability, suggesting that recent debt financing has been an effective strategy in this time of low interest rates.

 

Your task here is not to come up with insights that would impress the talking heads on CNBC.  Rather, it is to connect basic economic and business principles to the behavior and financial reporting of the real-world business you analyze.

Do NOT use analysts’ reports or others’ evaluations. Since the entire report will draw on your work, your ideas and be written using your own words, no language should be quoted.  Borrowing language from another source, published or unpublished will be treated as a violation of the Academic Integrity Code. If you are relying on information contained in a note to the financial statements, you should cite it in your discussion; e.g.: (note 4).

A hard copy (printout) is due in by the due date and time listed at the top of this document.  The print out should include both your report and a printout of each of the worksheets in your Excel workbook.  Make sure you adjust the print format so that each worksheet prints out legibly on a single page. Also submit a copy of your Word file no later than the due date and time via the appropriate assignment link in Blackboard.

 

Outline for the format of your report (copy and paste this outline):

I           Introduction

1.

2.

3.

 

II          Income Statement Analysis

1.

2.

3.

4.

 

III         Balance Sheet Analysis

1.

2.

3.

4.

 

IV         Cash Flows Analysis

1.

2.

3.

4.

 

(Continued on next page)

 

 

V          Ratio and Integrative Analysis

1.

2.

3.

 

VI         Summary

1.

2.

 

_______________

 

Content

 

I           Introduction –

 

1. Explain what this report is about and give your reader a brief description of the company’s products, services, industry and mission.
2. Use your knowledge of economic principles to describe the macroeconomic environment in general, and how that economic environment affects your company’s industry in particular.
3. Use your knowledge of business principles to summarize the business strategy your company has employed in order to accomplish its mission in this environment.

 

II          Income Statement Analysis

1. Briefly explain what information this statement conveys (in general).
2. What are the changes and trends in net income over the past three years? How do changes in significant income statement items, according to your vertical and horizontal analyses, help explain the change in net income?  Explain the changes to the best of your ability.  (100 words maximum.)  Be specific as to amounts and percentages.
3. According to the notes to the financial statements, how does your company determine the timing of revenue recognition? Why is this important to this particular business?
4. What irregular, extraordinary or unusual items were experienced by your company in the three years disclosed in the income statements? How does the disclosure of such items influence your opinion of the company’s performance?
   1. Such items may be “irregular items, such as those described in pages 687 – 689 in your text).
   2. They may also be significant unusual items, such as restructuring charges, loss on debt retirement or extinguishment, acquisition or merger expenses, legal settlements or other items which generally do not recur and are significant enough to warrant disclosure on the face of the financial statements. (Hint – if the item is unfamiliar to you, it is probably “irregular”.)

 

IIII        Balance Sheet Analysis

1. Briefly explain what information this statement conveys (in general).
2. According to the notes to the financial statements, how is the net book value of property, plan and equipment determined? Explain how this approach enhances or impairs the consistency, comparability and relevance of the amounts in the financial statements.
3. According to the notes to the financial statements, what significant legal and financial contingencies or risks does the company face that are not reflected in the financial statements? (Make sure you are referring to the footnotes and not to other parts of the annual report.)
4. What asset categories are particularly important to this company’s business and why? What are the changes and trends in significant balance sheet items, according to your vertical and horizontal analyses?  Are these assets growing as fast as revenues? Explain the changes to the best of your ability.  (150 words maximum.  Be specific as to amounts and percentages.)

IV         Cash Flows Analysis

1. Briefly explain what information this statement conveys (in general).
2. How does the trend in operating cash flow changes compare to the trend in net income changes over the past three years – discuss?
3. Explain the importance of the current year’s free cash flow (or lack thereof); discuss the trend in this item over the past two years.
4. Offer an explanation (using only this statement) for any significant change in your company’s cash balance over the past three years.

V          Ratio and Integrative Analysis

1. Select a measure of liquidity calculated in Part 3 and explain how your balance sheet analysis (above) supports the trend your ratio suggests.   (Your “trend” might be constant, i.e., no upward or downward trend.)
2. Select a measure of profitability calculated in Part 3 and explain how your income statement analysis (above) supports the trend your ratio suggests.   (Your “trend” might be constant, i.e., no upward or downward trend.)
3. Select a measure of solvency calculated in Part 3 and explain how your balance sheet analysis (above) supports the trend your ratio suggests.  (Your “trend” might be constant, i.e., no upward or downward trend.)

VI         Summary

1. Summarize your company’s performance, growth and resources in 150 words,
2. Use your knowledge of economic and business principles to evaluate how effective company’s strategy has been in responding to the economic conditions you identified in your introduction.

 

Part 5:  Group Presentation

 

Make all corrections/revisions to prior parts of this project before working on the presentation.

1. Presentation Content— The purpose of these presentations is to inform/educate your classmates about your industry as well as the company you have analyzed within that industry. Your presentation should last no more than 15 minutes and include, but not be limited to, the following content:

 

1. General information about your industry and about each of the companies studied by your group (no more than three minutes)

1. A summary about the financial strengths and weakness of each of your companies

1. A general comparison of your companies by size (you could compare such things as total assets, net revenues, gross profit, net profit, cash flows, working capital, debt, common stock, etc.)
2. You should also compare key items for your companies on a relative basis.
3. A summary of the similarities and differences in the presentation (format, level of detail, listed accounts) of the companies’ financial statements

1. A comparison of your companies’ profitability, liquidity, and solvency. Using appropriate ratios, compare your companies to each other and to industry averages*.  Explain the significance of these comparisons.  Suggest why your firms may have different profitability, liquidity, and solvency positions.
2. A conclusion ranking the firms by financial strength and your rationale for the ranking

 

Note –  Companies with fiscal year-ends will need to be compared to others based upon the common “current year” adopted in Part 1. E.g., X co has a 1/31/14 fiscal year.  For comparative purposes, this its 2013 year.

 

Use charts for comparisons.  For example show each company’s current ratio and the industry standard on one chart to add visual impact.

 

*A note on industry averages–You can find industry standards for some ratios on numerous financial websites, such as Reuters and MSN Money, For this assignment, you must use such industry averages for the current ratio, gross margin, net income ratio, return on assets, and total debt to assets ratio. For other ratios, you may calculate an average ratio value based on the companies analyzed in your group.  Use a simple average to approximate what an industry average would be and comment on any significant deviations from the average.

2. Presentation Style — The tone and sophistication of your presentation should be appropriate for a professional, business-literate audience. Consult the Grading Rubrics posted under Important Documents on BlackBoard for the specifics of how your presentation will be graded.
3. Material should be presented so that the class remains actively engaged in your message.

1. Handouts, product samples, items displaying company logos—even fashion shows of company products, have been used effectively to keep fellow students interested in presentations. Use your imagination!

1. A collaborative, as well as cooperative, approach to the project is required and each student is expected to participate equally in the actual presenting.
2. “Business casual” dress or dress related to the industry/product is required. (No hats or caps, no jeans, etc.)

1. Each team is required to prepare and use PowerPoint slides.

• Briefly state concepts/ideas on the slides. Generally, less is best—a few slides containing major points is much better than many slides with very detailed information. You should have very few, if any, paragraphs.
• Explain or expand the concepts stated on the slides.
• Use text fonts and backgrounds to establish cohesiveness in your presentation.
• Select a font for text that contrasts well with your background. (Check this on the computer in Ettinger 211—things may look different on your PC.)
• Use color for emphasis. Add images and clip-art for clarification and visual interest.
• Be sure that all slides can be easily read–especially graphs and tables.
• Use the spellchecker!
• Use a consistent style.

3. Personal Presentation Characteristics

• Maintain eye contact with your audience. Be conscious of facing your audience.  Do not talk into the projector screen or the monitor.
• Smile! Appear to have enthusiasm for what your and your team members are doing.
• Determine if you, or another person on your team, will be advancing the slides before the presentation.
• Talk about and/or explain your slides.
• Use notes to help you give your presentation. Do NOT write out what you plan to say in detail. Your notes should only be reminders to help you deliver your presentation.
• Avoid “and a”, “a”, “um”, and other verbal fillers Team members not speaking should be unobtrusive unless they are playing a role in assisting the speaker.

 

4. Practice – Practice your presentation in Ettinger 211 before you present. Consider having someone outside your group, possibly a Learning Assistant, or someone else who has taken this course, critique your practice session using the above criteria.

 

5. Upload the final version of your presentation to the BlackBoard assignment within 5 hours of the actual presentation.

 

Peer Evaluation – After all presentations have been given, each group member will have an opportunity to evaluate the contribution of the group members.

 

Professionalism – How you do your work is often as important as getting the work done. To promote your professional growth, you will be evaluated on your ability to:

 

• Submit work on or before due dates
• Complete revisions/corrections to work in a timely fashion
• Follow directions for completing work
• Display thoroughness in completing work assigned
• Follow directions for submitting work
• Submit work that is neat and well organized
• Submit work that, while not necessarily always accurate, reflects attention to detail
• Research answers to questions independently
• Ask for help when appropriate
• Present questions in a concise, organized manner
• Respond to constructive criticism in a positive manner

[1] 10K is the name of the form public corporations are required to file with the SEC that details their annual report information.  Make sure that the auditors’ report included in that form (“Report of Independent Public Accountants”) indicates that the financial statements that follow were prepared in accordance with Generally Accepted Accounting Principles (GAAP).