For this assignment, you will be conducting an analysis of 5 years of comparative financial statements. You will need to read the following document: HW 3 – Beneish – 1999 – The Detection of Earnings Manipulation.pdf
Utilize the file HW3_Data.xlsx to complete the assignment. This Excel file contains two worksheets: a 5-year balance sheet and a 5-year income statement.
For each question, please answer in a professional style, as though you were being asked these questions by a boss/client and providing a written response. You can write your response to each question just below the question in this document. When submitting your assignment, please also submit your workbook where you completed the calculations to show your work.
Question #1 (35 points): Create a 5-year horizontal analysis for the financial statement provided. Recall from class – a horizontal analysis (aka variance analysis) shows the percentage change from year-to-year at the line-item level. Excel tip: if you are careful about how you write your formulas, you should be able to copy the same equation down (to more lines) and across (to more years) to save time.
Question #2 (35 points): Create a 5-year vertical analysis for the financial statement provided. Recall from class – a vertical analysis (aka common-size analysis) shows every item as a percent of its total category. Excel tip: this kind of analysis is a great time to use absolute cell references (the “$” sign); remember, you can make absolute references that are only rows, only columns, or both.
Question #3 (30 points): Choose one of the following ratios and calculate it for all 5 years. Explain what the ratio you chose explains about a company, then interpret the calculated values. You should interpret both the individual annual ratios and the trend (how it changed over the 5 years). Ratios you can choose include:
Current Ratio
Quick Ratio
Inventory Turnover
Age of Receivables
Total Asset Turnover
Net Profit Margin
Return on Total Assets
Return on Common Equity
Debt to Equity
Times Interest Earned
EXTRA CREDIT: Question #4 (5 points each): Repeat Question #3, but for a different ratio. Each additional ratio you calculate and explain is worth 5 bonus points.
EXTRA CREDIT: Question #5 (10 points): Create one or more visualization that would be helpful in showing your chosen ratio’s trend over time, and help a decision-maker diagnose what may be driving the trend.
EXTRA CREDIT: Question #6 (50 points): In the electronic reserves for class, you will find a paper by Dr. Messod Beneish where he explains a financial statement analysis that can detect possible financial statement fraud. The details of the calculation are on p. 4 – it involves calculating 7 ratios, then adding them up to get a “Beneish M-Factor Score.” If you really want to push your skills at implementing a cutting-edge analysis from a written description you might find online, use this paper to calculate the Beneish M-Score for 2012. Tips on understanding Dr. Beneish’s formal notations in his equations:
The subscript “t” means “current year”
The subscript “t-1” means “prior year”
∆ (the Greek letter delta) means “change in”, i.e., current year– prior year
You can ignore the numbers in brackets in the equations (e.g.,[2]). Those refer to an older version of XBRL called Compustat.
The Beneish constant (the “intercept” in our regression) is-4.840 (you can find this on p. 6 of his paper, if you wish to look.
This question is hard! It is worth a lot of points for good reason! Do not worry if you find it difficult, just keep at it.
