Concept
1. The four roles at a glance
| Data Analyst | MIS Executive | Finance / FP&A / Accounts | Operations / Supply chain | |
|---|---|---|---|---|
| Main job | find insights, answer business questions | produce accurate reports on time | budgets, P&L, variance, valuation | track stock, orders, schedules, SLAs |
| Excel focus | pivots, lookups, Power Query, charts, dynamic arrays | lookups, SUMIFS, pivots, formatting, speed, macros | SUMIFS, financial functions, scenarios, modelling, consolidation | lookups, dates/workdays, conditional formatting, validation, inventory maths |
| Often also tested | SQL, Power BI/Tableau, basic statistics | VBA/automation, email-ready formatting | accounting basics, Tally/ERP exports | ERP exports, basic forecasting |
| Typical test | "Here's sales data — give 3 insights with a chart" | "Make this daily report from the raw dump in 30 min" | "Build a 3-year projection / budget variance" | "Find items below reorder level and days of stock" |
2. Data Analyst — what to show
- Cleaning messy data (Power Query, TRIM, dates) and explaining your steps.
- Pivots with % of total, YoY, grouping; slicers.
- Clear charts with conclusion titles (Analysis track, Module 5).
- KPIs: growth, conversion, retention, cohort.
- Sample questions: How do you handle missing values? How would you find why sales fell in March? What's the difference between mean and median, and which would you report for order values?
3. MIS Executive — what to show
- Speed and accuracy: XLOOKUP/VLOOKUP, SUMIFS/COUNTIFS, pivots, Paste Special, shortcuts.
- Report formatting: headers, number formats, freeze panes, print setup.
- Repeatability: Tables, Power Query refresh, a simple macro.
- Sample questions: How do you make the same report every day in 10 minutes? How do you check your report total is correct? Which shortcuts do you use most?
4. Finance — what to show
- Financial functions:
| Function | Purpose | Example |
|---|---|---|
PMT |
loan EMI | =PMT(9%/12, 60, -500000) → ₹10,379 per month |
NPV / XNPV |
present value of future cash flows | XNPV when dates are irregular |
IRR / XIRR |
return rate of cash flows | XIRR for SIP/irregular dates |
FV |
future value | SIP maturity |
EDATE / EOMONTH |
month-end schedules | =EOMONTH(A2,0) |
- Budget vs actual with favourable/adverse logic (Dashboards track, Build 2), three-statement links, sensitivity tables (Data Table), Goal Seek.
- Sample questions: Why can a 3% revenue miss become an 18% profit miss? NPV vs IRR? How do you build a scenario switch?
5. Operations — what to show
- Inventory maths: closing = opening + in − out; days of stock = stock ÷ average daily usage; reorder point = daily usage × lead time + safety stock.
- Dates:
WORKDAY,NETWORKDAYS, due-date and SLA ageing buckets (0–7, 8–15, 16–30, >30 days). - Conditional formatting for alerts; data validation for clean entry.
- ABC analysis (Pareto of consumption value).
- Sample questions: How would you flag orders breaching the 3-day SLA? Which items should we reorder today? How do you classify A/B/C items?
6. Read the job description first
Highlight Excel words in the JD (e.g. "Power Query", "dashboards", "VBA", "variance analysis") and match them to this table. If the JD mentions SQL/Power BI, be ready to say what you know honestly — Excel skills transfer well.
7. One-week prep plan
| Day | Do |
|---|---|
| 1 | Revise the Top 50 for your role's sections |
| 2 | Practise 10 whiteboard formulas (Lesson 3) without Excel |
| 3 | Timed practical: one portfolio dataset in 45 minutes |
| 4 | Case-round framework (Lesson 4) on a messy file |
| 5 | Polish one portfolio project + 3 STAR stories |
| 6 | Mock interview with a friend; record and review |
| 7 | Rest, review notes, prepare questions for the interviewer |
STAR stories: Situation, Task, Action, Result — e.g. "Monthly report took 3 days (S/T); I rebuilt it with Power Query (A); now 2 hours, 20 hours saved per month (R)."
Common mistakes
Preparing generic Excel answers for a finance role (no PMT/NPV). Ignoring the JD. No stories with numbers.