30-Day Analyst Portfolio Challenge — Project Bank for Every Profession

Retail Sales Performance Dashboard

Suitable for: any background · Industry: Retail / FMCG · Tools: Excel + Power BI

Business problem: A retail chain with 25 stores reports total revenue every month, but the regional manager cannot see which stores, categories or salespeople are actually driving growth versus which are dragging the average down.

Objective: Build a dashboard that lets the regional manager identify top and bottom performing stores/products in under 30 seconds, and decide where to focus next month.

Dataset to Build

Data Cleaning Tasks

• Remove duplicate OrderIDs and blank rows
• Standardise Region/City spellings (e.g., "Bangalore" vs "Bengaluru")
• Handle negative or zero Qty values (returns vs data errors)
• Convert Date to a proper date type; build a Calendar table
• Create a calculated Revenue = Qty × UnitPrice × (1 − Discount) column

Data Analysis Questions

1. Which stores are above/below their monthly target?
2. Which product category contributes the most revenue and margin?
3. Is revenue growth accelerating or slowing month over month?
4. Which salesperson has the best conversion of footfall to revenue?
5. Which day of the week consistently underperforms?

KPIs to Calculate

• Total Revenue
• YoY / MoM Growth%
• Target Achievement%
• Average Order Value (AOV)
• Revenue per Store

Formulas & Queries

Excel: =SUMIFS(Sales[Revenue],Sales[StoreID],A2,Sales[Date],">="&StartDate)

SQL:

SELECT s.StoreName, SUM(f.Qty*f.UnitPrice*(1-f.Discount)) AS Revenue
FROM Sales f
JOIN Stores s ON f.StoreID = s.StoreID
GROUP BY s.StoreName
ORDER BY Revenue DESC;

DAX: Total Revenue = SUMX(Sales, Sales[Qty] * Sales[UnitPrice] * (1 - Sales[Discount]))

Dashboard Pages & Visuals

• Page 1 — Executive Summary: KPI cards (Revenue, Growth%, Target%), trend line chart, region map
• Page 2 — Store Performance: Ranked bar chart of stores, table with conditional formatting
• Page 3 — Product Analysis: Category treemap, top-10 products table

Key Insights & Business Recommendations

Example insight: "3 stores in the South region are consistently 20%+ below target, all in the same city — pointing to a local competitor issue rather than an execution problem." Recommendation: investigate competitive pricing in that city before assuming a staffing or training problem.

Final Deliverables

• Cleaned Excel workbook with PivotTables
• Published Power BI dashboard (3 pages)
• One-page insight summary (PDF)

Proof of Work

Resume bullet: "Built a 3-page Power BI dashboard analysing 12,000+ retail transactions across 25 stores, identifying a ₹40L regional revenue gap and presenting 3 data-backed recommendations."

LinkedIn hook: "I gave a struggling retail chain something they didn't have — visibility. Here's the dashboard I built and what it revealed. 🧵"

60-second pitch: "I analysed retail sales data for a fictional 25-store chain to find out why regional performance varied so much. I cleaned the transaction data, built a data model connecting stores, products and a calendar table, then built a Power BI dashboard with KPI cards and drill-downs. The key finding was that three underperforming stores were clustered in one city — suggesting a local, not systemic, issue. I recommended a targeted pricing review for that city instead of a blanket retraining programme."

Possible Interview Questions

• "Walk me through how you cleaned this data — what problems did you find?"
• "Why did you choose a treemap for category revenue instead of a pie chart?"
• "If revenue is up but margin is down, what would you check next?"

Customer Onboarding Process Optimisation

Suitable for: MBA, BBA, any background · Industry: SaaS / BFSI · Tools: Excel + Power BI + process mapping

Business problem: A B2B SaaS company signs a new customer, but 30% of them never complete onboarding within the target 14 days — increasing churn risk before the customer has even used the product properly.

Objective: Map the current onboarding process, quantify where customers drop off, and propose a redesigned process with clear requirements the product team can build.

Stakeholders

• VP Customer Success (process owner)
• Onboarding Specialists (process executors)
• Product Team (system dependencies)
• New Customers (end users of the process)

Current-State Process & Problems

Signup → Welcome Email → Manual KYC Document Upload → Manual Verification (2–4 days) → Account Setup Call → Training Session → Go-Live. Problems found: manual verification is the single biggest delay (avg. 3.2 days), training sessions get rescheduled 40% of the time, and there's no automated nudge for customers who stall.

Proposed Future-State Process

Signup → Automated Welcome + Self-Serve KYC Upload → System-Assisted Verification (target: <24 hrs) → Self-Paced Training Module + Optional Live Call → Automated Go-Live Checklist with reminders.

Business, Functional & Non-Functional Requirements

• Business requirement: Reduce average onboarding time from 14 to 7 days.
• Functional requirement: System must send automated reminders at day 3, 7 and 10 of stalled onboarding.
• Non-functional requirement: Reminder system must handle 500+ concurrent onboarding journeys without delay.

User Story & Acceptance Criteria

User story: "As an onboarding specialist, I want to see which customers are stalled at which step, so I can proactively follow up before they disengage."

Acceptance criteria: Given a customer has not progressed in 3 days, the dashboard flags them in red; the specialist can filter by stalled stage and days-stalled.

Data, KPIs & Dashboard

Dataset: OnboardingEvents table (CustomerID, Stage, StageStartDate, StageEndDate, Status) — approx. 2,000–4,000 rows. KPIs: Onboarding Completion%, Average Time-in-Stage, Drop-off Rate by Stage, Time-to-Go-Live. Dashboard pages: Funnel view of all active onboardings, stage-wise bottleneck chart, stalled-customer watchlist table.

Key Insights & Recommendations

Insight: "68% of total onboarding delay sits in one stage — manual KYC verification." Recommendation: automate document verification first; it alone could cut average onboarding time by ~40%, before touching the training step at all.

Final Deliverables

• Current-state and future-state process flow diagrams
• Requirements document (BRD-style)
• Power BI onboarding funnel dashboard

Proof of Work

Resume bullet: "Mapped a 6-stage customer onboarding process, identified the single largest bottleneck (manual KYC verification, 3.2-day avg. delay) and proposed a future-state process projected to cut onboarding time by 40%."

LinkedIn hook: "30% of new customers were dropping off before they even used the product. Here's how I diagrammed the problem — and what I'd fix first."

60-second pitch: "I picked a common SaaS problem — customers abandoning onboarding — and treated it like a real BA engagement. I interviewed (fictional) stakeholders, mapped the current process, and used data to prove where the actual bottleneck was, rather than guessing. The finding was that a single manual step caused most of the delay, so my recommendation focused there first instead of a broader, more expensive redesign."

Possible Interview Questions

• "How did you decide which stage was the real bottleneck versus just the slowest-looking one?"
• "What's the difference between a functional and non-functional requirement here?"
• "How would you validate this recommendation before it's built?"

Budget vs Actual Analysis for a Multi-Branch Retail Chain

Suitable for: B.Com, MBA Finance, Commerce · Industry: Retail · Tools: Excel + SQL + Power BI

Business problem: Finance has just closed the quarter and total spend is 15% over budget across 20 cost centres — but the CFO doesn't know if this is broad overspending or a few extreme outliers.

Objective: Build a Budget vs Actual dashboard that pinpoints exactly which cost centres and expense categories are driving the overspend, and by how much.

Dataset to Build

Data Cleaning Tasks

• Match Budget and Actuals on CostCentreID + Category + Month (watch for mismatched category naming)
• Handle cost centres with no budget entered (treat as 100% variance, flag separately)
• Convert all amounts to a consistent currency/unit

KPIs to Calculate

• Budget Variance% = (Actual − Budget) / Budget
• Operating Expenses (total & by category)
• Gross Profit / Net Profit / Profit Margin%
• EBITDA
• Overspend Contribution% by cost centre

Formulas & Queries

Excel: =(Actual-Budget)/Budget with conditional formatting: red if >10%, amber if 5–10%, green if within budget.

SQL:

SELECT cc.CostCentreName,
       SUM(a.ActualAmount) AS Actual,
       SUM(b.BudgetAmount) AS Budget,
       (SUM(a.ActualAmount)-SUM(b.BudgetAmount))*1.0/SUM(b.BudgetAmount) AS VariancePct
FROM Actuals a
JOIN Budget b ON a.CostCentreID=b.CostCentreID AND a.Category=b.Category AND a.Month=b.Month
JOIN CostCentres cc ON a.CostCentreID=cc.CostCentreID
GROUP BY cc.CostCentreName
HAVING (SUM(a.ActualAmount)-SUM(b.BudgetAmount))*1.0/SUM(b.BudgetAmount) > 0.1
ORDER BY VariancePct DESC;

DAX: Variance % = DIVIDE([Total Actual]-[Total Budget],[Total Budget])

Dashboard Pages & Visuals

• Page 1: KPI cards (Total Budget, Total Actual, Variance%), waterfall chart of category-wise contribution to overspend
• Page 2: Cost-centre heatmap table with conditional formatting, drill-through to category detail

Key Insights & Recommendations

Insight: "82% of the total overspend comes from just 3 of 20 cost centres, concentrated entirely in the 'Marketing' and 'Repairs & Maintenance' categories." Recommendation: a targeted review of 3 cost centres, not an org-wide budget freeze.

Final Deliverables

• Budget vs Actual Excel model
• Power BI variance dashboard with drill-through
• One-page CFO summary memo

Proof of Work

Resume bullet: "Built a Budget vs Actual dashboard across 20 cost centres, isolating 82% of a 15% quarterly overspend to 3 cost centres and 2 expense categories, enabling a targeted rather than org-wide budget review."

LinkedIn hook: "Everyone assumed the whole company was overspending. The data said otherwise. Here's how I found the real 3 cost centres responsible."

60-second pitch: "I built a Budget vs Actual model for a fictional 20-branch retail chain that was 15% over budget. Instead of stopping at the headline number, I broke variance down by cost centre and category, and found the overspend was concentrated in just 3 cost centres. That changes the recommendation completely — from a blanket cost-cutting drive to a focused investigation, which saves the business time and avoids unnecessary cuts elsewhere."

Possible Interview Questions

• "How would you handle a cost centre with no budget entry at all?"
• "What's the difference between Gross Profit and EBITDA, and why does it matter here?"
• "If Marketing is over budget but drove higher revenue, is that still a 'problem'?"

Patient Wait-Time & Readmission Analysis

Suitable for: Nursing, Hospital Administration, Pharmacy · Industry: Hospital · Tools: SQL + Power BI · Use fictional/anonymised data only

Business problem: A 200-bed hospital's patient satisfaction scores are falling. Two suspected causes: long OPD wait times and a rising 30-day readmission rate — but nobody has quantified either.

Objective: Quantify wait times and readmission patterns by department, and recommend where operational changes will have the biggest satisfaction impact.

Dataset to Build

Data Cleaning Tasks

• Anonymise/replace any real patient identifiers before use
• Compute WaitTime = ConsultTime − CheckInTime; remove negative/erroneous durations
• Flag readmissions where a new AdmitDate falls within 30 days of a prior DischargeDate for the same patient

Analysis Questions

1. Which department has the longest average wait time?
2. Does wait time correlate with lower satisfaction scores?
3. Which diagnosis categories have the highest 30-day readmission rate?
4. Is readmission linked to length of the original stay?

KPIs to Calculate

• Average Wait Time (by department)
• 30-Day Readmission Rate%
• Average Length of Stay
• Bed Occupancy%

Formulas & Queries

SQL (readmission flag):

SELECT a1.PatientID, a1.AdmitDate, a1.Diagnosis
FROM Admissions a1
JOIN Admissions a2
  ON a1.PatientID = a2.PatientID
  AND a2.AdmitDate > a1.DischargeDate
  AND a2.AdmitDate <= DATEADD(day,30,a1.DischargeDate);

DAX: Readmission Rate % = DIVIDE([Readmitted Patients],[Total Discharged Patients])

Dashboard Pages & Visuals

• Page 1: Wait-time by department bar chart, trend line over months
• Page 2: Readmission rate by diagnosis category, length-of-stay vs readmission scatter plot

Key Insights & Recommendations

Insight: "Cardiology has both the longest average wait time (48 min) and the highest 30-day readmission rate (14%) — well above the hospital average of 7%." Recommendation: prioritise a discharge-planning review in Cardiology before expanding OPD capacity elsewhere.

Final Deliverables

• Anonymised, cleaned dataset
• Power BI clinical operations dashboard
• One-page findings brief for hospital administration

Proof of Work

Resume bullet: "Analysed 15,000+ (anonymised, fictional) patient visit records to identify Cardiology as the department with both the highest wait time and 30-day readmission rate, informing a targeted discharge-planning recommendation."

LinkedIn hook: "Patient satisfaction scores were falling and nobody could say exactly why. So I went looking — here's what the wait-time and readmission data actually showed."

60-second pitch: "I used a fictional hospital dataset to investigate falling patient satisfaction. Rather than assume wait time alone was the cause, I also looked at readmissions, and found both problems concentrated in the same department — Cardiology. That let me recommend a focused intervention there instead of a hospital-wide, expensive fix."

Possible Interview Questions

• "How did you define a 'readmission' in SQL, and why does the definition matter?"
• "How would you protect patient privacy if this were real data?"
• "Is correlation between wait time and readmission the same as causation here?"

Employee Attrition Analysis

Suitable for: MBA HR, Psychology, any background · Industry: IT Services · Tools: Excel + SQL + Power BI

Business problem: Attrition has crossed 22% this year, well above the industry benchmark of 15%. Leadership wants to know if this is company-wide or concentrated in specific teams and tenure bands.

Objective: Identify where attrition is concentrated and recommend retention actions targeted at the highest-risk group, not a blanket policy change.

Dataset to Build

Data Cleaning Tasks

• Calculate Tenure = ExitDate (or Today) − JoinDate, in months
• Bucket Tenure into bands: 0–6mo, 6–12mo, 1–2yr, 2–5yr, 5yr+
• Standardise Department and ExitReason category naming

KPIs to Calculate

• Attrition Rate% (overall and by department/tenure band)
• Retention Rate%
• Average Tenure at Exit
• Regretted vs Non-Regretted Attrition%

Formulas & Queries

Excel: =COUNTIFS(Employees[Department],A2,Employees[ExitDate],"<>")/COUNTIF(Employees[Department],A2)

SQL:

SELECT Department,
       DATEDIFF(month, JoinDate, COALESCE(ExitDate, GETDATE())) AS TenureMonths,
       COUNT(*) AS Headcount,
       SUM(CASE WHEN ExitDate IS NOT NULL THEN 1 ELSE 0 END) AS Exits
FROM Employees
GROUP BY Department, DATEDIFF(month, JoinDate, COALESCE(ExitDate, GETDATE()));

DAX: Attrition Rate % = DIVIDE([Exited Employees],[Average Headcount])

Dashboard Pages & Visuals

• Page 1: Attrition rate by department bar chart, trend line by month
• Page 2: Tenure-band attrition funnel, top exit reasons (from survey)

Key Insights & Recommendations

Insight: "58% of all exits happen within the first 12 months, concentrated in one department, with 'lack of growth clarity' as the top exit reason." Recommendation: a structured 90-day and 6-month career check-in for new joiners in that department, rather than a company-wide compensation review.

Final Deliverables

• Cleaned attrition dataset
• Power BI attrition dashboard
• One-page HR leadership summary

Proof of Work

Resume bullet: "Analysed attrition across 1,500+ employee records, found 58% of exits occurred within the first year in one department, and recommended a targeted early-career retention programme."

LinkedIn hook: "22% attrition sounds like a company-wide crisis. The data said it was actually a first-year problem in one department. Here's the dashboard that changed the conversation."

60-second pitch: "I built an attrition dashboard using fictional HR data for a company with attrition running well above benchmark. Instead of treating it as a blanket problem, I segmented by tenure and department, and found most exits were early-tenure and concentrated in one team. That reframes the fix from an expensive company-wide compensation review to a focused onboarding and career-clarity intervention."

Possible Interview Questions

• "How would you distinguish regretted from non-regretted attrition with this data?"
• "What's a confounding factor that could explain the department-tenure pattern besides culture?"
• "How would you measure if your recommended fix actually worked?"

Instagram Growth & Reel Performance Analysis

Suitable for: Media, Visual Communication, Marketing, any background · Industry: any brand · Tools: Excel + AI tools

Business problem: A brand's Instagram account posts consistently but follower growth has been flat for two months, and the team doesn't know which content format is actually working.

Objective: Identify which content type, posting time and format drives the highest engagement, and recommend a content calendar shift.

Dataset to Build

Data Cleaning Tasks

• Export data from Instagram Insights (or use a fictional dataset built to match its structure)
• Standardise PostTime into hour-of-day buckets
• Remove duplicate/test posts from the dataset

KPIs to Calculate

• Engagement Rate% = (Likes+Comments+Shares+Saves) / Reach
• Follower Growth Rate%
• Reach and Impressions by Post Type
• Best-performing posting time band

Formulas & Queries

Excel: =(Likes+Comments+Shares+Saves)/Reach, then =AVERAGEIFS(EngagementRate,PostType,"Reel") to compare formats.

AI-assisted step: feed the top 10 and bottom 10 posts' captions into an AI tool and ask it to summarise common themes — always cross-check its output against the raw engagement numbers yourself before using it in your insight.

Dashboard Pages & Visuals

• Page 1: Engagement rate by post type (bar chart), follower growth trend line
• Page 2: Heatmap of engagement by day-of-week × hour-of-day

Key Insights & Recommendations

Insight: "Reels get 3.2× the engagement rate of static posts, but only make up 20% of the content calendar. Engagement also peaks 7–9 PM, a slot used for only 1 in 5 posts." Recommendation: shift the content mix toward 50% Reels and reschedule more posts into the 7–9 PM window.

Final Deliverables

• Cleaned engagement dataset
• Excel dashboard with PivotCharts
• One-page content strategy recommendation

Proof of Work

Resume bullet: "Analysed 90 days of Instagram post performance data, found Reels drove 3.2× the engagement rate of static posts, and recommended a content-mix and posting-time shift."

LinkedIn hook: "Same brand, same effort, wildly different results by content type. Here's what 90 days of Instagram data taught me about what actually works."

60-second pitch: "I analysed 90 days of Instagram performance data for a brand whose follower growth had stalled. I calculated engagement rate by post type and posting time, and found Reels massively outperformed static posts, but were underused. I used an AI tool to help summarise caption themes, but validated every number myself before recommending a specific content-mix and scheduling change."

Possible Interview Questions

• "Why measure engagement rate instead of just raw likes?"
• "How did you make sure the AI tool's caption summary wasn't just noise?"
• "What would you test next to confirm the Reels effect isn't a fluke?"