Have received a case study from a LMM PE shop. It's a raw spreadsheet of customer data - around 4000 rows (transactions). Customer location, which branch they were served from, when they signed up, how much they paid, churn, etc. They've asked me to analyze the data in whichever way I think makes sense to draw key insights.
What different types of analyses would you recommend I run on this dataset (in Excel)? It's a recycling business that serves businesses, has a bunch of recycling locations/landfills, a bunch of trucks, and a bunch of customers in various cities. Customer contracts are usually annual.
Intuitively I would have thought that this business is a lot about site/location level analysis to determine growth/profitability/effectiveness - which are the locations that have high customer growth, lowest churn, most profitable, quickest at serving customers, how does revenue per customer vary between sites and also how all these metrics have trended over time
Really depends on what data is provided but assuming you're given dates of the transaction:
- Historical revenue broken down by month/year/quarter and further by price and volume
- If you're given the gross profit or contribution profit for transaction - can include that historically as well
- Customer cohort analysis (by count and revenue) for churn / retention, etc.
- Customer concentration and top customer revenue / profit trends over time
- Branch-level historical revenue / profitability / concentration
- If you're given trucks and stuff - maybe some utilization analysis which is probably directly tied to the Company's overall gross profitability
- If there's various geographies - historical revenue / profitability / concentration
- If there's various services - historical revenue / profitability / concentration
That's just off the top of my head. Would have to understand the business drivers better to run any other analyses related to landfill costs / utilization, etc. I'd recommend Googling online for key drivers of recycling businesses
Above is great but pretty fulsome for a case study and is a lot more like data cuts you'll run in actual DD on the job. For a case study, I would assume that whoever is evaluating you probably wouldn't want to see a shit ton of data cuts and is more focused on how you think about the most important drivers, especially if you are a new hire and don't have industry / domain expertise.
Assume the most important ones to focus on first are:
A lot of other stuff is kind of gravy and starts getting into more details than necessary in my opinion. I will tell you if someone ran 20 data cuts I may think "cool" but I don't know if I'd give them an edge over someone who was focused in their analysis, sometimes less is more and demonstrates your ability to prioritize important things.
This is great, thanks. The challenge is unless you do run a bunch of data cuts, you won’t know if an insight or a pattern or a correlation is there or not.
What tools in excel are commonly used for this? Would you just keep using pivot tables or formulas? Pivot tables are easier but since they refresh every time, you can’t store your work such that you could then also ship off the Excel sheet, or is there a way around this?
I don’t like the idea of running a bunch of data just to try to see some type of correlation. There needs to be a reason why you’re looking at that cut — e.g, is a key growth initiative X, and if so, how’s the traction been? Core part of the thesis is low churn — how true is this?
I’ve run enough data cuts in my life to know that nobody gives a flying fuck about 99% of them, but will really scrutinize 1 or 2 of them.
Don’t think anyone cares how you run the cuts, but formulas make it easier for someone to open a file and know immediately what’s going on.
Vel fugit nisi consequatur voluptas quis. Perferendis blanditiis eos explicabo dicta illo esse repudiandae.
Porro ad placeat quae. Quo saepe voluptate possimus quasi et aut. Sunt similique sapiente hic. Cupiditate suscipit explicabo tempore est molestiae voluptas consequatur. Hic voluptatem amet sed vitae officia velit laboriosam. Vitae sed odio ipsa veniam.
Nemo eligendi beatae optio. Iste sed nam quas.
Nulla aperiam natus dolore doloremque quaerat omnis. Ut placeat repellat quis inventore ut et iusto. Porro esse doloremque et id quisquam sint harum.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...