58|EXCEL – Earnings Management Analysis

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.

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.

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


  • 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


This was pretty powerful financial statement analysis. I used a lot of table references and XLookups to bring in the data. The conditional formatting was good practice for me too. I could tell something was different about company 3 from the beginning, but the Beneish model made it much more apparent. The first time through I used the table formulas (referencing the table and column names) to bring in a lot of the Beneish data, but that didn’t work so well for some reason. I ended up with these answers:

Still, a great challenge!

1 Like

Wow! I feel like I’ve practiced the xlookup formula enough to call myself an expert! It was neat to see how powerful cell references in Excel can be. For example, I was confused by the concept of accruals being the difference between net income and operating cash flows, so I actually subtracted NI from CF. I knew something was off when all my numbers weren’t landing. So, I changed the value to represent NI less CF and then I got the solution. Cool formula at the end that I’ll have to look into more!

Interesting analysis! I now have a lot of respect for Beneish for developing that formula! While the obvious answer is Company 3, I just wanted to draw attention to Company 4. It seems like they had a strange year compared to last year. They had the highest sales growth (21%), but had less-than-average inventory growth (15% vs 17% average), a huge decrease in AR (37% decrease), and a huge decrease in their Day Sales Outstanding (61 days to 32 days). It seems like they really tightened up their receivable process while growing sales in an impressive way. These odd changes may either be red flags or indicators of a good investment opportunity, depending on what accounts for these changes.

Wow, this was a deeper analysis than I have performed in the past and I learned quite a bit! That Beneish Test seems like a pretty handy model to use, especially in this case. While looking with just the firs 6 test, I first noticed comapny 3, but then I also thought company 9 looked odd. I didn’t think it very rational to have inventory growth of 21% with AR decreasing by 17% and have a near 0% growth in sales. I also used data bars at the end to visually see the differences and rankings of each company. Loved the challenge!!
here’s my solution:

That was a great analysis to perform! It felt great working my excel skills. I was thinking that companies 3,4, and 9 looked rather odd, but it’s nice to have a model that points out 3’s suspiciousness so clearly. Here is what I had:

Wow, this is cool because I was just learning about the Beneish model in one of my classes, and I wasn’t sure if it was something that is actually used in the business world. It was fun to be able to do my own analysis with it and see that Company 3 is definitely up to some earnings management! :slight_smile:

@Christian I personally thought that the data bars were a super cool addition–and something that I had no clue Excel could do. So I went ahead and added them to my solution too; thanks for pointing it out to me! It’s always like Christmas has come early whenever I find a new capability in Excel.

Here’s my solution in an Excel file. I did a few things differently, so I think it could be helpful if anyone wants to compare it to theirs! Earnings Management Challenge.xlsx (30.4 KB)

This is a very cool challenge. I have a newfound respect for the Beneish Model. I was cheering Company 3 all the way until the end.

Challenge58_Data.xlsx (26.7 KB)

I am just learning excel and I loved the diversity in this problem. It taught me a lot of new things regarding conditional formatting and especially taught me the importance of parenthesis in the Beneish Model. Love the challenge though!