Vertical Analysis Of Income Statement

Sheet1 vertical Analysis Of Income Statementhorizontal Analysis Of Inco

Sheet1 vertical Analysis Of Income Statementhorizontal Analysis Of Inco

Sheet1 Vertical Analysis of Income Statement Horizontal Analysis of Income Statement All items are presented as a % of Net Sales All items are presented as a % of Net Sales Notice the vertical analysis is analyzin one year only Notice the horizontal analysis is analyzing the change between two years 20X2 What does this mean? 20X2 20X1 % Change from 20X1 to 20X2 What does this mean? From 20X1 to 20X2: Sales $1,300,.0% Sales or Net Sales is the denominator for all accounts below in a Vertical Analysis. Sales $1,300,000 $1,000,% Sales increased 30% from 20X1 to 20X2. The formula is (20X2 Sales - 20X1 Sales) divided by 20X1 Sales.

I memorize the formula like this: (New - Old)/Old Cost of Goods Sold 600,.2% Divide cell B6 by $B$5. The formula is: =SUM(B6/$B$5) Cost of Goods Sold 600,,% COGS increased 50%, which is 20% more than net sales. What would cause this to occur? Did vendors increase prices? Was there inventory shrinkage?

Gross Profit 700,.8% Gross profit is ~ 54% of Net Sales Gross Profit 700,,% Notice you cannot derive Cell J7 by subtracting 50% from 30%. Gross project increased 17%, almost 1/2 of the increase in Net Sales By using $B$5 instead of B5 as the denominator, you can copy and paste the formula down. The $ sign anchors a specific cell; in this case, B5 Total Sales Operating Expenses: Operating Expenses: General & Administration Expenses 125,.6% Divide cell B10 by $B$5 General & Administration Expenses 125,,% Why did G&A expenses increase 47% in one year? Selling Expenses 100,.7% Selling Expenses 100,,% Why did Selling expenses increase 54%? Total Operating Expenses 225,.3% Total Operating Expenses 225,,% Notice again, Cell J12 cannot be derived by subtracting J11 from J10.

Operating Income 475,% Operating Income 475,,% Continuing using the formula (New - Old)/Old Interest Expense 10,.8% Interest Expense 10,,% The company must have borrowed more funds bc interest expense doubled. Why? What was the intent of the borrowing? Income Before Taxes 465,.8% Income Before Taxes 465,,% Income Taxes 139,.7% Income Taxes 139,,% Income taxes are an unavoidable expense, but can be managed through effective tax strategies Net Income After Taxes $325,.0% Net Income is 25% of Net Sales Net Income After Taxes $325,500 $311,% Since Gross Profit is 53.8% and Net Income is 25% of Net Sales, Operating Expenses, Interest Expense, and Income Tax Expense must be 28.8% of Net Sales (53.8% - 25%) OR (9.6% + 7.7% + .8% + 10.7%) = 28.8% 052721Page 1 I.

Corporate and Industry Financial Analysis and Presentation II. Introduction Measurement, analysis, and interpretation (Excel): Accurately computes all ratios and other financial metrics relevant to the industry. Apply financial statement analysis to evaluate a firm’s financial performance Research: research and present current information on the firm and the industry. Decision making: Engage critical thinking skills to select most relevant ratios, identify all data outliers, select 5 qualitative factors impacting the firm, identifies internal weaknesses and external: threats, incorporate relevant current news, accurately identify limitations/shortcomings in the analysis, and highlight data in which the group found questionable/suspicious.

You’re one of the top three candidates for a position at Benson, Cundiff, & Gilbert a financial accounting and brokerage firm in the heart of Washington, DC. You’ve always wanted to live in the District, as locals call it. Sasha, the head of Human Resources at Benson, Cundiff, & Gilbert called this morning. After a brief discussion, Sasha says, “in preparation for your third interview you will be making a presentation to our Board of Directors and at least one of the three principals, but don’t worry about that, they are eager to meet you.†Easy for her to say, you thought. Further, Sasha says, “we also want to learn how well each candidate works as a team member, so you will prepare and make the presentation with the other three candidates.

This will be very useful to those making the hiring decision. Each candidate will be assessed on the process used to create the presentation file and the actual presentation. We’ve done this type of interviewing in the past and sometimes more than one candidate is hired: Levin Page 2 not for the same position but in related jobs. Are you willing to partake in this group interview?†Without giving it a lot of thought because you didn’t want to sound hesitant, you say “Absolutely; what time and where?†III. Steps to Completion Conduct a comprehensive financial analysis of a publicly traded corporation and make a presentation to the Board of Directors (professor).

Financial analyses will be performed in Excel and the presentation will be prepared in PowerPoint. The Excel file must support the figures in your presentation. The results of the financial analysis will be presented in a professional quality PowerPoint presentation, with the presenter's notes under each slide. Step 1: Prepare Excel file All cells requiring computations must have a formula. Cell formulas should reference other sheets where appropriate.

Complete all calculations in one Excel file; multiple sheets are encouraged. Step 2: Prepare PowerPoint presentation A. Introduction to the industry in which the corporation operates - this section may require multiple slides Common issues/risks all corporations face in the industry Critical success factors in the industry Current economic health of the industry B. Introduction to the corporation - this section may require multiple slides A brief history Briefly describe the corporation’s strategy Identify the corporation’s major competitors in Page 3 C. Financial analysis - this section will require multiple slides Compute the accompanying financial ratios for the 5 most recent years for which a Form 10-K has been filed.

In addition, you should obtain the industry average ratios for the most recent year for the SIC code of the industry in which the corporation operates. The corporation’s ratios for the most recent year will be compared to the industry ratios. Compute any ratios or metrics which are industry-specific for the 5-year period Prepare a 5-year trend analysis (also known as a horizontal analysis) of each of the ratios computed in A and C. Note: the corporation data will represent a 5-year trend, the industry data for the most recent period will represent a point in time. Create tables, graphs, or other media to present your findings.

All tables/graphs/etc. included from external sources, those you did not create, must be cited. Be sure to address any data/ratio outliers (red flags) discovered in your analysis. Summarize your findings by these financial ratio categories: Liquidity ratios, Efficiency ratios, Leverage ratios, Profitability ratios, Market Value Indicators. Some ratios may not be applicable to your corporation. For example, inventory ratios may not be applicable for corporations in the service industry corporations.

If a ratio is not applicable and not computed for your corporation, explain the reason the ratio is not applicable for this analysis. D. Qualitative factors Address issues of a qualitative nature that may impact the financial health of the corporation. For example, pending litigation, disclosures in its annual report, presence or lack of a corporate code of ethics/professional behavior, a corporate hotline, foreign operations, legislation, recent employee strikes, political issues, SASB/environmental issues in the annual report, etc. Check for news articles within the past five (5) years.

E. Risks Discuss internal weaknesses and external threats to the financial health the corporation faces. See Michael Porter's Competitive Advantage to learn more about a SWOT analysis. haron Levin Page 4 F. Summary of analysis Summarize your findings. Highlight any items from your analysis for which you are skeptical or have doubt.

Identify any shortcomings in your analysis. Conduct an online search for any late-breaking information/news, which may be available since the filing of the most recent Form 10-K may have been many months ago. Often, this will be in a subsequent filing of Form 8-K. G. References Sources of information used in the project.

Use strict APA Style for the Reference list. IV. Deliverables A. Excel Include as many sheets as necessary to perform the required financial statement analysis. Save the file with the Group name as part of the file name.

Example: Group 1 Project.xlsx. B. PowerPoint Prepare the file in APA Style. You may find this video helpful in determining how to apply APA Style to presentation files: APA formatting for PowerPoint. 0527 Course Outcomes Assessed in this Project:  Measurement, analysis, and interpretation (Excel): Accurately computes all ratios and other financial metrics relevant to the industry. Apply financial statement analysis to evaluate a firm’s financial performance  Research: research and present current information on the firm and the industry.  Decision making: Engage critical thinking skills to select most relevant ratios, identify all data outliers, select 5 qualitative factors impacting the firm, identifies internal weaknesses and external: threats, incorporate relevant current news, ac...  Communication: Create an audio enhanced Power Point presentation and effectively delivers information tailored to the intended audience