BYU Student Author: @Jonathan_Weston
Reviewers: @Nate
Estimated Time to Solve: 75 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
You work at an investments firm, and you have been tasked with analyzing the financial health of ten companies. They are in the same industry and are similar in size and scope. With recent economic downturns and management scandals, you are concerned about the likelihood of earnings management within these companies. Earnings management is the manipulation of accounting techniques or assumptions to present an overly positive view of the company’s financial position (e.g., to reach certain forecasts or bonuses).
Management mainly does this either by controlling the real business activities of the company or by changing different accounting estimates. Such changes in and of themselves are not illegal. However, if the adjusted numbers materially mislead investors, the SEC can step in and issue fines. Since these actions are so intertwined with the actual responsibilities of management, it can be hard to prove earnings management. There is no one test to identify earnings management, but you can apply several different analyses that each give you a different perspective. Using some of these tests, you can gain a clearer picture of the ten companies’ true earnings quality.
You have gathered the financial data you think you’ll need into an Excel file and have determined several tests that you want to perform on each of the ten companies to analyze the likelihood of earnings management. The first tab in the file, “Company_Data”, contains financial information for this year and the previous year. The “Analysis” Tab is where you will perform your tests. Column headers that start with “PY” are referencing data from the previous year. Column headers that are in bold and have a thick black border are where you will enter your calculations.
Instructions
In the Analysis tab of the Excel file, perform the following tests. Formulas are included under “Suggestions and Hints”.
1. Valuation Multiples
- For each company, calculate the P/E and P/B ratios.
- Apply a conditional formatting color scale to the calculated fields.
2. Inventory Growth and Days Inventory Held (DIH)
- Calculate the inventory growth from last year.
- Calculate the Days inventory Held for this year and the previous year.
- Apply a conditional formatting color scale to the calculated fields.
3. Sales Growth and Inventory Growth
- Calculate the sales growth from last year.
- Apply a conditional formatting color scale to the calculated fields.
- Compare this to the inventory growth calculated in the last test. What relationship would you expect between the growth in sales and growth in inventory?
4. Accounts Receivable and Days Sales Outstanding
- Calculate the growth in Net Accounts Receivable
- Calculate the Days Sales Outstanding for the current year and previous year.
- Apply a conditional formatting color scale to the calculated fields.
5. Allowance for Doubtful Accounts
- Calculate the Allowance for Doubtful Accounts as a percentage of Net Accounts Receivable for the current and previous year.
- Apply a conditional formatting color scale to the calculated fields.
6. Accruals to Assets
- Accruals refer to revenues earned or expenses charged. Accruals directly affect the net income of a company, but the cash related to those transactions has not changed hands. Accruals are calculated as the difference between Net Income and Cash from Operating Activities. Cash from Operating Activities is labeled as “Operating Cash” in the Company_Data tab.
- Calculate the Accruals as a percentage of Total Assets.
- Apply a conditional formatting color scale to the calculated fields.
7. Reflection
- Compare your results from the 6 tests performed. Is there a company that stands out?
8. The Beneish Model
The Beneish Model is a formula that uses eight different financial ratios with set coefficients to identify the likelihood of earnings manipulation. The model was created by Professor M. Daniel Beneish of the Kelley School of Business at Indiana. For this test, you will calculate each of the eight financial ratios and then apply them to the Beneish model to calculate the M-Score, which describes the degree of earnings manipulation.
- DSRI: Days of Sales in Receivables Index = (Net Receivables t / Sales t) / Net Receivables t-1 / Sales t-1)
- GMI: Gross Margin Index = [(Sales t-1– COGS t-1) / Sales t-1] / [(Sales t – COGS t) / Sales t]
- AQI: Asset Quality Index = [(Non-Current Assets t – PPE t) / Total Assets t] / [(Non-Current Assets t-1 – PPE t-1) / Total Assets t-1]
- SGI: Sales Growth Index = Sales t / Sales t-1
- DEPI: Depreciation Index = (Depreciation t-1/ (PP&E t-1 + Depreciation t-1)) / (Depreciation t / (PP&E t + Depreciation t))
- SGAI: SGA Expense Index = (SG&A Expense t / Sales t) / (SG&A Expense t-1/ Sales t-1)
- LVGI: Leverage Index = (Total Debt t / Total Assets t) / (Total Debt t-1 / Total Assets t-1)
- TATA: Total Accruals to Total Assets Index = Total Accruals t / Total Assets t
With these eight financial ratios calculated, you can plug them into each variable of the Beneish Model to calculate the M-Score. An M-Score greater than -1.78 suggests that the company is likely to be a manipulator.
- M-Score = -4.84 + 0.92DSRI + 0.528GMI + 0.404AQI + 0.892SGI + 0.115DEPI – 0.172SGAI – 0.327LVGI + 4.679TATA
- Apply a conditional formatting color scale to the M-Score Column.
Data Files
Suggestions and Hints
Formulas
- P/E Ratio = Share Price / EPS
- P/B Ratio = Share Price / Book Value per Share
- Growth form last year = (Current year – Previous year) / Previous year
- Days Inventory Held = Inventory / Cost of Goods Sold * Days in a year
- Net Accounts Receivable = (Acc. Rec. Gross – Allowance for Doubtful Accounts)
- Days Sales Outstanding = Net Accounts Receivable / Sales * Days in a year
Solution
