Excel Based Case Study Interview Questions
As many Excel users know, one of the most magical features of Excel is the . Cell references allow users to include the values of external cells in formulas dynamically — rather than hard-coding particular values manually.
However, cell references can be confusing when copied and pasted to different locations. By default, Excel uses cell references, which change dynamically as they are copied and pasted around a sheet. For example, if a reference to cell is copied and pasted one row down and one column to the right, the new reference will point to cell . This allows users to perform similar calculations on different ranges of cells quickly and easily.
In contrast, cell references do not change when they are copied and pasted to other locations within a sheet. Absolute cell references can be used on either rows, columns, or both at the same time, and are indicated using the sign. For example, if a reference to cell is copied and pasted one row down and one column to the right, the new reference will point to cell — it won't change at all, because both the row and column are locked. If a reference to cell is copied and pasted one row down and one column to the right, the new reference will point to cell — only the row number will change, because the column letter is locked.
Here's a handy table that will show you what the sign means depending on where you see it in a cell reference:
|$A$1||Row and column locked||Cell reference will not change at all as cell is copied and pasted.|
|$A1||Column locked||Only row reference will change as cell is copied and pasted.|
|A$1||Row locked||Only column reference will change as cell is copied and pasted.|
|A1||Nothing locked||Both row and column will change as cell is copied and pasted.|
So, after writing the perfect resume and cover letter, and preparing at least 6 Hero Stories, it’s time to face the music. Just as there’s always a dragon to slay before rescuing the princess, there’s always a case interview process to master before landing the dream job.
Consulting firms rely heavily on case interviews to find the right candidate and, therefore, you should practice them – practice them well, start practicing them early (2-3 months prior to the interview), and then keep practicing them often. Once you have been requested to come for an interview it won’t be just to discuss your GPA, background, or experience – that ends up being important, but only later.
First, you have to show you have the skills to do the job.
Case Interviews give the interviewer an in-depth view of how you think/function under pressure, solve problems, and understand both the large picture and the smallest aspects of a problem. In addition, they are excellent test scenarios for communication on your feet. Case interviews go beyond just spouting business knowledge – you are tested on how you build and communicate a clear framework, break down problems into small pieces, develop real-world options, and recommend actionable solutions despite the presence of conflicting information.
The 2 case interview styles are Interviewer-led and Interviewee-led cases. In our Consulting Interview Bible, we go into the differences in great detail – but that’s not the point of this article. Within both case styles, there can be a mixture of case interview types.
Today, we focus on 6 types of case interviews. You could see any one of these 6 variations (and probably multiple ones) of a case interview in your final rounds, so it’s important to be familiar with each one of these (see sample case interview questions). We know we could do an entire article on each one, and very well might in the near future (comment below if you’d like to see more on this)! For now, however, let’s just get started.
We’ve ordered these from the most basic to the most complicated – when we work with Black Belt clients, we have them build up through the case types to develop increased ability as they tackle more difficult business problems.
Brainteasers are most commonly logic puzzles or riddles. They are meant to test both your analytical and creative thinking process.
Here are some sample brainteaser questions:
- How many golf balls can fit in a football stadium?
- How many miles of road are there in the United States?
In their purest form, brainteasers are uncommon in management consulting interviews, but we include them for 2 reasons. One, it’s good to be prepared for them in case you have an interviewer who decides to throw them in. Two, you solve them the same way you solve a market sizing question (see below) – by breaking down the solution into component parts.
2. Consulting Math
Consulting is a data-based industry – but where there are no facts, there are well-developed estimates. In regular math drills, you will not have the leisure of having a calculator. We are going back in time to the pen and paper and, your best resource…your smart brain. These drills are commonly elementary math but be prepared for fractions and percentages.
Again, these are not usually stand-alone case interview questions for MBB (although they can be in a time-pressured partner interview), but they are great practice for Problem Solving Tests (PST’s) and as components for longer strategic case interviews. Here’s a sample question:
- About 225 of Smith’s Investments roughly 20,000 employees have the title of partner. What percentage of all Smith’s Investments employees are partners?
- The market for lead pencils has been declining at 4%/year for the last 3 years. The original market was $24M/year. What is the market in year 3 (now)?
Unlike science math, consulting math only expects close-to-correct answers. In science or technology, a tiny margin of error can lead to major accidents. However, in business, decisions can be effectively and efficiently made based on near-perfect data/calculations.
For Problem Solving Tests, multiple questions like these are asked with a time limit – either in spoken form or in written form, and usually with under 2 minutes to answer each question. In written form, they are multiple choice questions. Make sure you know major currency exchange rates of the office you are wanting to join.
GMAT and GRE tests are excellent practice for consulting math.
3. Market Sizing
Market sizing questions are fairly straightforward and should be solved in 8-10 minutes and 4-5 steps with 1 level of segmentation. You are measuring the existing market of an item in total units or total sales ($) – it’s important to clarify up front which one it is. Sample market sizing questions include:
- How many hamburgers are consumed in the U.S. in one year?
- What is the size of the U.K. market ($) for helium balloons?
For market sizing, you are using the size of the market as the baseline to answer strategic questions like:
- How many of X exist in the market?
- How fast is the market for X growing?
- What is the $$ opportunity if a client introduces X into the market?
In most market sizing cases, the interviewer has no clue of the exact solution – nor does she care. The emphasis is more on how you arrived at your conclusion (did you go off on a tangent? could you defend your assumptions?) rather than the conclusion itself.
Also, for market sizing questions, be prepared for adjustments at the end of the case – “What if the % of the population used was this? What is your most sensitive assumption?” Because you are preparing a spreadsheet (a mini-financial model) on the fly, be prepared to adjust your inputs – just like you would on a real-life consulting project.
Profitability is the ultimate business metric, and profitability cases can address a business in any industry. This case type names a company and gives some detailed history and factual data, and poses one of three questions:
- What should Company X do about revenues (prices x volumes)?
- What should Company Y do about costs (fixed costs + variable costs)?
- What should Company Z do about overall profits (including both sides of the profit equation using metrics such as profit/unit or profit/channel)?
In our worldview, there are 4 types of profitability questions:
- Declining profitability
- Profitability – revenue-focused
- Profitability – cost-focused
- Profitability – scenarios
In the first type, profitability questions can cover why a company might be losing profits or what a company should do to increase profits creatively. Your job is to set up a framework to help them find the source of the issue. The framework should be in 2 levels, and beyond revenues and costs, it could highlight a potential internal company or product problem, competition or a global market issue, etc.
Revenue- or cost-focused profit questions aren’t necessarily posed as profit questions (the word profit isn’t used in the set-up). For example, maybe a company is integrating a new software infrastructure and they want to know if it’s a “good idea”. The focus is on quantifying the benefits of new business infrastructure solutions – benefits that translate to increased sales or reduced costs.
Also, revenue and cost questions may be focused on non-financial decision making – for example, “X is deciding whether or not to run for office. Should they or shouldn’t they?” Meaning, they are not referring to financial outcomes, but are still quantifiable – your job is to think about the issue with a 360-degree view and develop a well-structured pro and con list.
Lastly, you might encounter a weighing scenario question. You would be given 2 options and will need to weigh the profitability of one against the other. An example of this might be comparing the use of the U.S. Post Office for mailing needs versus UPS. Or maybe the company is trying 2 different types of packaging for a product; one is 3 oz. and the other is 8 oz. Which packaging would you recommend and why?
5. Market Study
Market study questions come in 3 forms:
- Market Entry
- Revenue Growth
- Market Share
The first type, market entry, addresses a scenario where a company is interested in entering a new market. You are really answering another question – how much money will they make, will they be profitable entering that market, or how should they enter that market?
In the second scenario, a company is trying to grow their revenues. Your job would be to brainstorm how they could obtain this goal. If you look at it through the lens of profitability, would it be by increasing prices or volumes (market study would be the second level of analysis)? If you look at it through the market study lens, you look at market factors (competitors, customers, supply/demand, etc.) that would affect growth plans – and profitability would be the second level of analysis. In either case, your job is to identify any warning signs that might hinder this company from achieving this goal.
A final type of market study question would address a company declining in market share. In this scenario, you need to focus on overall company growth and how it has been affected by competitors and customer loss. You are looking for a way to grow in context to the current market competition.
6. Merger & Acquisition Cases
M&A cases are the mac-daddy of all consulting cases because they include market sizing, profitability, and market study cases – with a fair share of case math thrown in. The cases will either be about whether Company A should merge with Company B or if Company Y should purchase Company Z.
Details of what type of product or services will be included and a limited amount of factual data relating to both the purchasing and the target business. When asking 1-3 clarifying questions, focus 80% on the target and only 20% on the parent/acquirer.
There are 2 types of Merger and Acquisition purchasers – financial and strategic acquirers. Understanding the motivations of both will help you identify how best to evaluate the target. Remember, however, the goal for both types of acquirers is…as always…increased profitability.
After you have a better understanding of the case types, that’s where the real work begins – developing actionable frameworks for each.
In any case type, you will need to recap the question back to the interviewer. This will help you and the interviewer ensure you understand the business problem you are solving. Make sure you include all the factual details given. Your recap should take up to 2 minutes and will reflect your understanding of what the company does, what the company is trying to decide, and how they currently make money. Be confident – it’s your first impression.
When choosing your framework to remember to identify the areas that need further examination in order to come to your conclusions. You are not being asked to conduct a hypothetical analysis.
Now that we have highlighted 6 case interview types, it’s time to get cracking!
Want more help? Grab our most popular combo – the Consulting Case Bank(550+ cases; includes a copy of the Consulting Interview Bible).
Want more reading? Check out these articles from MC on this topic: