Data Analysis in Excel: Spotting Red Flags

  • Peter Lynch

I still recall my first massive data challenge as a private equity investor. We were exploring the acquisition of a founder-owned dental adhesives manufacturer. It was fun to work on because the owner had an incredible story. He immigrated to the US and used his chemical engineering background to land a job developing adhesives that could withstand the extreme heat experienced by objects reentering Earth’s atmosphere (like the space shuttle).

Per the founder, he never saw himself as an employee, and he started his own company early in his career. I cannot recall the company’s revenue, but by the time I met with him he was paying himself $5 million to $6 million a year selling adhesives to help straighten teeth (which he joked was much easier to do). As it relates to data, the company could provide timely consolidated financials, but there was no detail behind revenue and profitability.

To explore this due diligence challenge, I flew out to meet with the founder and asked if any additional information was available. In response, I was told that they had an old system that generated purchase orders, but that the output was a text file. The exchange went something like this:

PL: Could I review the last three years of purchase orders?

Founder: *chuckles* Sure! But each year is about 700 to 900 pages of text…

PL: *feigning enthusiasm* Not a problem! If I can take it with me, I will follow up next week.

I’ll be honest, I had no plan. I was just confident I could figure it out. The biggest challenge was getting the text data into Excel, but once I had my Excel table, the analysis took less than an hour. And it was wildly eye-opening.

The company had hundreds of products and distributed them across the country, but approximately 90% of the company’s profit came from three variations of the same product, which were largely sold to the same customer.

That was all the information we needed to make a decision. I flew back out to deliver the news that it would not be a great fit for us. This could have been done over the phone, but we had been in discussions for months, and I had deep respect for this individual.

In our meeting, I suggested that the founder cut the majority of his SKU tail to boost profitability. He laughed, stated that he was plenty wealthy already, but then he turned serious and offered me a job. I was flattered and asked who I would be working for since he was about to sell the company (largely to be polite, I loved my job at the time). And he just chuckled again. We shook hands and that was the conclusion of my due diligence on that opportunity.

I have many additional examples, but the point is that you need to know what to focus on in any transaction. In this instance it would have been very easy to build an LBO model with the information originally provided. But the product and customer concentration issue would have gone unnoticed with this analysis.

Fortunately, this skill set is easily developed with an understanding of a few additional Excel functions. Once you have mastered it, the ability to replicate the modeling framework will translate into a tremendous advantage in your due diligence efforts.

RELATED: ASM recently launched a new course on data analysis as part of the ASM+ Tier. The first five lessons are live now and the introductory video is available below.