Excel Question

Overview

Financial statement analysis helps us to gain insights into a companys operations. In this assignment, we will take a companys income and balance sheet data and develop a spreadsheet model that helps us to interpret this raw financial data. We will first generate financial ratios and comparative statements. Then we will prepare graphs to give us a pictorial overview of the companys situation. In the process, we will employ many basic concepts of spreadsheet design.

Data

Each of you has been assigned to a new company Your task is to analyze the companys financial statements to get a good understanding of the companys operation. You can find the data in the Financial Statement Data for project 3 module on They include the financial statements (both balance sheets and income statements) and the industry comparison report from Mergent Online.

What to do:

• Follow the Excel worksheet guideline provided below to prepare the accounting statements. You should use separate worksheets for balance sheet and income statement, and name each worksheet appropriately (i.e., HD_Income). Format the statements to enhance readability by using these formatting tools: number formats, border lines, shade (fill color), and indents. You must use at least three.
• Generate a common size balance sheet and a common size income statement on the same worksheet where each statement is.
• Make comments in the common-size income statement about the level and trend of the following items: COGS, Depreciation expenses, EBIT (operating income before interest expenses and taxes), and NI. Do the same in the common-size balance sheet about Cash, Inventory, Current Assets, Net Fixed Assets, Account Payable, Current Liabilities, Long- term Debt, and Common Stock.
• On a separate worksheet named Ratios, calculate the following ratios for each year. Refer to the note for the ratio definitions, i.e., Ch 7_8 note in the lecture note folder. Ratios in () refers to the corresponding ratios in Mergent Online, if they differ.1. Liquidity: Current ratio and Quick ratio.
  2. Asset management: Inventory Turnover, net fixed asset turnover (net PPEturnover), and total asset turnover ratio.
  3. Debt: Total debt to equity ratio (use this definition to compute, = (Current debt +long-term debt & leases ) / total equity), times interest earned (interest coverageratio), long-term Debt-to-Equity (LT debt to equity)
  4. Profitability: profit margin, BEP (operating margin), ROA, ROE.
• Find the industry averages for the ratios that you computed above, using Mergent Onlines comparison report. Be sure to control outliers when computing the averages.

When there is no industry average available, put NA in the industry column on the ratio

table.

• Comment on the calculated ratios 1) compared to the industry average (; 2) trend for theperiod. See an example attached.
• Provide a summary of your analysis of the companys performance by identifying itsweaknesses/strengths in no more than 2-3 sentences.
• Create separate comparative bar graphs for each of the four categories. Make sure to putall the ratios in a category together in a chart. Be sure to use the secondary axis for ratios that are very high/low compared to others in the category. You will have four charts. See a sample chart attached.
• Name the file with your last name, i.e., Moon_Excel 3.xlsx. Excel worksheet design:
  • Label clearly so that a user can see what are inputs and outputs.
  • Use at least four formatting tools (number formats, underlines, indent, and fillcolor) to enhance readability of the financial statements and your outputs.
  • In the output area (i.e., ratios computed), use only formulas with references to inputcells. Do not type in numbers in your formula!
  • Use different fonts and font sizes, as well as features such as bold and italicize, toenhance your worksheets appearance and readability.
  • Your graphs should have titles, and the series should have legends.
  • Appearance matters!Grading Rubrics Points Original and Common-sized statements and comments 8 Formats and appearance 4 Ratio analysis and interpretations 8 Industry Benchmark, outlier control 5 Ratio Graphs 3 Summary of analysis 2