Business Analytics Interview Questions for Consulting Companies
40 questions weighted to the interview style of consulting companies — 3 easy, 32 medium, 5 hard.
How consulting companies interview for Business Analytics
Consulting firms look for people who can structure messy business problems and explain them simply to clients. Expect case and guesstimate questions, data interpretation, and questions on how you handled stakeholders, along with the technical skills for the role.
This set focuses on Case Studies and Guesstimates (9), Excel for Business Analysts (8), Business Metrics and KPIs (5), SQL for Business Analysis (4).
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
A bank's mobile app logins increased, but digital transactions fell. What could explain this?
Users are coming but not completing transactions, so look at the gap. Possible causes: a release that broke a payment flow, new security steps like extra OTPs adding friction, logins driven by balance checks or offers, or transactions shifting to another channel. Compare the funnel from login to transaction by app version and device, check failure and error rates, and review support complaints before recommending fixes.
02
Describe how you would model an employee leave approval process in BPMN.
Use one pool for the company with lanes for Employee, Manager and HR system. Start event: leave request submitted. Task: system checks leave balance. Exclusive gateway: balance sufficient? If no, end event that notifies the employee. If yes, user task: manager reviews. Add an interrupting timer boundary event of 3 days that escalates to the skip-level manager. Gateway: approved or rejected. Approved path updates the leave balance and calendar, then both paths notify the employee and end.
03
Revenue grew 20 percent this quarter but the number of active customers stayed flat. How do you interpret it?
Growth came from more value per customer, not more customers. Break revenue into customers times orders per customer times average order value to see which moved. It could be price increases, larger baskets, a shift to premium products or a few large accounts. Then check risks: is new customer acquisition stalling, is churn being masked, and is growth concentrated in a small segment that could leave?
CALCULATE evaluates an expression under a modified filter context. You pass a measure plus filter arguments that add, replace or remove filters, for example CALCULATE([Sales], 'Region'[Name] = "North") or CALCULATE([Sales], ALL('Product')). It is the basis of time intelligence, percent-of-total and comparison measures. It also triggers context transition when used inside a row context, which is powerful but a common source of confusion.
Check market size (population, online ordering share), competition, restaurant supply, unit economics per order, CAC and payback period. Propose a pilot with clear success metrics.
07
Write an Excel formula for month-to-date sales up to today.
// Dates in Sales[Date], amounts in Sales[Amount]
=SUMIFS(Sales[Amount],
Sales[Date], ">=" & EOMONTH(TODAY(), -1) + 1,
Sales[Date], "<=" & TODAY())
// EOMONTH(TODAY(),-1)+1 gives the first day of the current month
Decide whether and how to enter a market by evaluating attractiveness, competition, capabilities and entry mode (build, buy, partner), then risks and next steps.
// Excel 365
=COUNTA(UNIQUE(A2:A500))
// unique customers in the North region only
=COUNTA(UNIQUE(FILTER(A2:A500, B2:B500="North")))
// older versions (no blanks in range)
=SUMPRODUCT(1/COUNTIF(A2:A500, A2:A500))
11
A business head wants 25 charts on one dashboard page. How do you respond?
Ask what decisions they make weekly and which three to five KPIs drive them. Propose a summary page with those KPIs, trends and one breakdown, then drill-through or separate pages for detail. Show a quick mock-up of both versions so they see the clutter and the load time. Use bookmarks or a field parameter to let them switch views without adding charts. Agree on the layout before building.
12
What steps would you follow to create a tracking plan for a new 'save for later' feature?
Start from the questions to answer: how many users discover it, use it and later buy saved items. Define events such as save_clicked, saved_item_viewed, saved_item_removed and saved_item_purchased, with properties like product ID, price, source screen and platform. Follow the existing naming convention, assign owners, review with developers, and test events in a staging build. Build the dashboard before launch so data is checked on day one.
13
A client's profits are falling. How do you structure the problem?
Split into revenue and cost, then revenue into price, volume and mix, and cost into fixed and variable; check internal factors and external market factors.
Link your answer to real experience. For example: during my internship I noticed reports were rebuilt manually every week, so I mapped the process and automated it with Power Query, saving the team a day each week. I enjoyed understanding the problem, talking to users and seeing the result used. A BA role combines that problem solving, communication and data work, which is where my strengths are, and I want to build deeper domain knowledge.
15
Write an Excel formula that labels sales as High (100000 or more), Medium (50000 or more) or Low.
Use STAR: Situation, Task, Action, Result. Show that you listened, found the underlying goal, used data to align people and reached a documented decision.
18
Write a SQL query to get each customer's first order date and first order amount.
WITH ordered AS (
SELECT customer_id, order_date, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY order_date, order_id) AS rn
FROM orders
)
SELECT customer_id, order_date AS first_order_date,
amount AS first_order_amount
FROM ordered
WHERE rn = 1;
19
What is the difference between churn rate and retention rate?
Churn rate is the percentage of customers who leave in a period: customers lost divided by customers at the start. Retention rate is the percentage who stay, roughly 100 minus churn for the same cohort. If you start a month with 1,000 subscribers and 50 cancel, churn is 5 percent and retention 95 percent. Count new customers separately, otherwise growth hides churn. Revenue churn can differ from customer churn.
Standardise both first: trim spaces, fix case, convert IDs to the same type and remove duplicates. Use a unique key like customer ID, or a combined key of phone and email if IDs differ. Then use XLOOKUP or COUNTIFS to mark each record as matched, only in sales or only in billing, or a Power Query merge with anti joins. Summarise mismatches by type and share the exception list with both owners.
22
A hospital chain's average patient waiting time has risen from 30 to 50 minutes. How would you approach the case?
Clarify where the wait is measured: registration, consultation, diagnostics or billing. Split the problem into demand and capacity. On demand, check patient volume, peak-hour mix, walk-ins versus appointments and new departments. On capacity, check doctor availability, no-shows of staff, consultation time per patient and process bottlenecks such as manual billing. Identify the biggest stage and suggest fixes like slot-based appointments, triage and digital billing.
23
Write a SQL query to calculate monthly customer churn rate for a subscription business.
WITH monthly AS (
SELECT DISTINCT customer_id,
DATE_TRUNC('month', billing_date) AS month
FROM subscriptions_billed
)
SELECT
prev.month AS month,
COUNT(prev.customer_id) AS customers_at_start,
COUNT(prev.customer_id) - COUNT(nxt.customer_id) AS churned,
ROUND(100.0 * (COUNT(prev.customer_id) - COUNT(nxt.customer_id))
/ COUNT(prev.customer_id), 2) AS churn_pct
FROM monthly prev
LEFT JOIN monthly nxt
ON nxt.customer_id = prev.customer_id
AND nxt.month = prev.month + INTERVAL '1 month'
GROUP BY prev.month
ORDER BY prev.month;
24
What is row-level security in Power BI and how do you set it up?
Row-level security restricts which rows a user can see in the same report. In Desktop you create roles under Manage Roles with a DAX filter, such as 'Region'[Manager Email] = USERPRINCIPALNAME() for dynamic RLS. You test with View as Role, publish, and then map users or groups to roles in the Service. Dynamic RLS with a mapping table scales better than creating one static role per region.
25
CAC is 1,200 rupees, average monthly revenue per customer is 500 rupees and gross margin is 40 percent. What is the CAC payback period?
Monthly gross profit per customer is 500 times 40 percent, which is 200 rupees. Payback period equals CAC divided by monthly gross profit, so 1,200 divided by 200 gives 6 months. If customers churn on average before 6 months, the business loses money on acquisition. Using revenue instead of gross profit would wrongly suggest 2.4 months, which is why margin must be included.
26
Tell me about a time you found an error in a spreadsheet just before it went to management.
Situation: an hour before a quarterly review I noticed regional totals were higher than the company total. Task: find the issue without delaying the meeting. Action: I traced it to a SUMIFS range that stopped at row 5000 while new data went beyond it, plus one duplicated region. I fixed both, converted the data to an Excel Table, and added a check cell comparing totals. Result: the correct deck went out on time and the check became part of our template.
27
How would you improve a company's low app conversion rate?
Confirm the impact and reproduce it quickly. Work with development to estimate a fix and its regression effort. Then present options to the business owner with risks: fix and do targeted retesting before go-live, go live with a temporary workaround or manual process, go live with the affected feature switched off, or delay. The decision belongs to the business, documented at the go or no-go meeting. Never hide a critical defect to protect a date.
29
Write a SQL query for the top 3 products by revenue in each category.
WITH ranked AS (
SELECT category, product_id, SUM(amount) AS revenue,
DENSE_RANK() OVER (PARTITION BY category
ORDER BY SUM(amount) DESC) AS rk
FROM sales
GROUP BY category, product_id
)
SELECT category, product_id, revenue
FROM ranked
WHERE rk <= 3
ORDER BY category, rk;
30
Three departments describe the same order process in three different ways during workshops. How do you arrive at the true AS-IS?
Treat each version as a hypothesis. Walk the process physically or observe real cases end to end, and pull a sample of 20 to 30 recent orders from the system to trace what actually happened. Compare timestamps and status changes with each department's story. Then hold a joint session, show the evidence-based map and mark the variants openly. Often all three are partly right because different order types follow different paths, which should be modelled explicitly.
Usually rounding. Excel may display rounded values while summing the full-precision numbers, or line items were rounded individually in the source system and the total was computed differently. Check whether finance rounds each invoice line before summing, and apply ROUND at the same level in Excel. Other causes include floating-point artifacts, values stored as text being skipped by SUM, or hidden filtered rows. Agree and document one rounding rule with finance.
33
What is an issue tree and how does a hypothesis-driven approach work?
An issue tree breaks the main question into smaller, MECE sub-questions until each can be answered with data, such as 'why are profits down?' into revenue and cost branches. A hypothesis-driven approach starts with a likely answer, for example 'profit fell because delivery costs rose', and uses the tree to decide which data proves or disproves it first. It focuses effort and keeps the case structured and fast.
Log the change, assess impact on scope, cost and timeline, get approval through change control or backlog grooming, and update documents and stakeholders.
35
A product line shows a loss after allocated overheads. Leadership wants to shut it down. How do you advise?
Separate contribution from allocation. If the product's revenue exceeds its variable and directly attributable fixed costs, it contributes to covering shared overheads, which will not disappear if it is closed and will be reallocated to other products. Check whether the overhead allocation key, such as revenue share, is fair. Also consider strategic links, like customers who buy it alongside profitable products. Recommend shutting it only if contribution is negative or the freed capacity has a better use.
SUMIFS is faster and easier for sums with AND conditions on ranges. SUMPRODUCT multiplies arrays and sums the result, so it handles cases SUMIFS cannot: weighted totals like price times quantity, OR logic, conditions on calculated values such as MONTH(date)=4, or comparing two columns row by row. On very large sheets SUMPRODUCT is slower, so prefer SUMIFS or a helper column when possible.
Situation: my manager asked why customer complaints were rising, but complaints were stored as free text with no categories. Task: give an answer within a week. Action: I sampled 300 complaints, created categories, tagged them manually and used keyword rules in Excel to classify the rest, then validated with the support lead. Result: late refunds were 40 percent of complaints, the refund process was fixed, and categorised tagging was added to the support tool.
39
Guesstimate: how many cups of tea are sold in Delhi per day?
Population about 20 million, adult tea drinkers about 50%, cups bought outside per person around 1 on average. This gives roughly 10 million cups, then adjust for offices and stalls.
WITH m AS (SELECT month, SUM(amount) AS rev FROM orders GROUP BY month)
SELECT month, rev,
ROUND(100.0 * (rev - LAG(rev) OVER (ORDER BY month))
/ LAG(rev) OVER (ORDER BY month), 1) AS mom_pct
FROM m;
How are Business Analytics interviews at consulting companies different?
Consulting firms look for people who can structure messy business problems and explain them simply to clients. Expect case and guesstimate questions, data interpretation, and questions on how you handled stakeholders, along with the technical skills for the role.
Which consulting companies are covered?
This page links to 13 consulting companies, including Accenture, Deloitte, EY, KPMG, PwC, Publicis Sapient, Thoughtworks, ZS Associates. Each has its own page with rounds and a separate practice set.
What rounds should I expect?
Common rounds at these companies include: Technical interview; Partner or HR round; Managerial round; Online assessment (aptitude + technical); HR round. The exact process varies by company and role.