Part 1. Horizontal and Vertical Financial Statement Analysis with Explanatory Narrative.
General formatting requirements: Please construct visually attractive and easily readable worksheets. Round your calculated percentages to the nearest one tenth of one percent (e.g., 100.0%; 65.2%; 4.3%, etc).
1. Use Excel to prepare a horizontal and a vertical analysis of the following selected income statement components: Net revenues, gross profit, SG&A expenses, and Net Income. The specific terminology and classifications will vary somewhat across companies. Use the three most recent years' income statement data to construct two horizontal income statement analyses and three vertical analyses.
2. From your company's 10-K, locate management's discussion and analysis of year-to-year financial and operating results to identify the main reasons for the changes, trends, and relationships in your horizontal and vertical spreadsheets. Organize your analysis under four headings: Net revenues; inventory and cost of sales; SG&A expenses;, and Net Income. Your narrative should require approximately three pages. In your narrative, please refer to the specific horizontal or vertical percentages you are explaining, describe more than one factor for each income component, identify relevant offsetting factors when applicable, and quantify the impact of those factors.
3. Print and attach a copy of each spreadsheet and a copy of the Excel formulas for each spreadsheet, for a total of four spreadsheet printouts. You may insert the spreadsheets directly into your Word file. Instructions for viewing and printing formulas in Excel:
• Highlight area on spreadsheet
• Select Tools, Select Options, Select View tab
• Under Window Options, place a √ in the Formulas box
• Print
• To return to original document, repeat above steps and uncheck the Formulas box.
Alternatively, use Control and tilde (the ~ key just to the left of the #1 key) to toggle between original document and formulas.
Part 2. Identify Major Sources of Cash and Use Operating Cash Flow to Evaluate the Quality of Earnings.
From your company's Statement of Cash Flows, (a) list the dollar amount and sign (positive or negative) of your company's three or four major investing cash flows and three or four major financing cash flows for the most recent fiscal year reported, and (b) use the operating cash flow section to evaluate the quality of your company's earnings for the most recent fiscal year reported.