Assume you are an analyst for an investment firm, and you have been asked to prepare a report on a particular company
In support of this report:
- prepare a horizontal analysis and a vertical analysis. The horizontal analysis should show two years of changes (e.g., the increases/decreases by dollar and percentage from 2016 to 2017 and, then, from 2017 to 2018). The vertical analysis should should the percentages for two years (e.g., for the 2017 and 2018 year-ends).
- calculate the full list of ratios for a two-year period. Per the Excel spreadsheet referred to below, show the numbers that are used for each ratio.
- write a 3-4 page narrative that discusses the key trends. Include three dimensions to the narrative: 1) what you see in the horizontal analysis; 2) what you see in the vertical analysis; and 3) what the list of ratios tells you. Bottom line, how is the company performing?
Present the calculations in an organized Excel document. Ideally, have one tab for the horizontal analysis, one tab for the vertical analysis, and one tab for the ratios. For the ratios, list the components used in the calculation. An Excel template is provided in the Modules section to use as a guide (you will probably need to add or subtract lines on the horizontal and vertical analysis tabs), primarily to ensure you provide the full range of information.
Some ratios can be discussed in tandem (e.g, Inventory turnover and days sales in inventory). The focus should be: What do the numbers and trends tell us about your company? Is it performing well with liquidity, solvency, and/or profitability? Have the trends been improving or declining? Can you identify factors that support the trends?
To complete the above, you will need three years of financial statements for your company (to complete the horizontal analysis and also to get some beginning balances for ratios using the balance sheet). Be alert to the fact that your company might not have made dividend payments. We had talked in class about how to find historical, year-end stock prices for a couple of the ratios, such as this site: https://www.marketwatch.com/tools/quotes/historical.asp. (Links to an external site.)
The narrative should be prepared in Word using Times New Roman, 12 pt. or Calibri 11 pt, double-spaced. Upload the Word document narrative, the Excel calculations, and a copy of your company’s financial statements.
The company is name is Allegion