Module 10 — Business analytics projects
Five briefs, five domains, five portfolio dashboards. Each one gives you a realistic dataset, a named stakeholder with a real decision to make, the questions the dashboard must answer, KPI definitions, the traps specific to that data, and a scoring rubric.
What each one does not give you is a click-by-click walkthrough. By this point in the course you have the technique; these projects are where you prove you can apply it to a problem nobody has solved for you.
You have joined a multi-channel retailer as their first data analyst. The commercial director has two years of order-line data and no reporting beyond a monthly spreadsheet that takes a day to assemble. She wants a single dashboard she can open before her Monday leadership meeting and answer any revenue question that comes up.
Questions the dashboard must answer
- How is revenue tracking against last year, by month and cumulatively?
- Which categories and products drive revenue, and which drive profit? They are not the same.
- Which regions and channels are growing, and which are flat?
- How much revenue is discounted, and what does discounting cost us in margin?
- Who are our highest-value customers, and what share of revenue do the top 20% represent?
What you are working with
- 15,067 order lines across 7,607 orders
- 896 customers who actually purchased
- Total net revenue of about $6.31M across the two years
- Blended profit margin of about 19.5%
- Electronics is the largest category by revenue but carries the thinnest margin (~13%)
Analyse
KPI definitions
Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.
| KPI | Definition | Why it matters / watch out for |
|---|---|---|
| Total Revenue | SUM of NetSales | The headline number. Everything else contextualises it. |
| Total Profit | SUM of Profit | Revenue minus COGS. Watch this diverge from revenue. |
| Profit Margin % | DIVIDE(Total Profit, Total Revenue) | The number that reveals mix problems. |
| Total Orders | DISTINCTCOUNT of OrderID | Not COUNTROWS — the fact table is at line level. |
| Average Order Value | DIVIDE(Total Revenue, Total Orders) | Rising AOV with flat orders means upsell is working. |
| Active Customers | DISTINCTCOUNT of CustomerKey | Customers who bought in the period. |
| Revenue YoY % | Compare to SAMEPERIODLASTYEAR | Requires a marked date table. |
| Discount Rate | Weighted average discount | Weight by revenue, not a simple average of the Discount column. |
Traps specific to this dataset
These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.
COUNTROWS(Sales) counts lines, not orders. Use DISTINCTCOUNT(Sales[OrderID]) for order count, or every average-order-value figure you produce will be wrong by a factor of about two.OrderDate and ShipDate. Only one relationship to the date table can be active. Decide which one the business means by "sales in March" and be explicit about it on the report.bwb-products.csv exists. This is the point of Module 4.This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.
bwb-retail-sales.csv, bwb-products.csv, bwb-customers.csv and bwb-stores.csv. In Power Query, check column quality on every column before you load. Confirm the row count matches the file.CALENDAR or in Power Query, covering 2024-01-01 to 2025-12-31 with no gaps. Mark it as a date table. Time intelligence will silently misbehave if you skip this.Deliverable
A .pbix file with at least two report pages: an executive summary and a detail page reachable by drill-through. The executive page must answer all five business questions above without the reader needing to change any filter.
How this is scored
Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.
- Add a Pareto analysis showing what share of revenue the top 20% of products generate
- Build a discount-band dimension (0%, 1–10%, 11–20%, 21%+) in Power Query and show margin by band
- Add a customer cohort view using SignupDate to show whether newer cohorts spend more or less
The People Director has a retention problem and a board meeting in three weeks. She knows the headline attrition number and nothing else — not which departments are worst, not whether it is a pay problem or a management problem, and not whether it is getting better or worse. She needs evidence she can act on, not a wall of charts.
Questions the dashboard must answer
- What is our attrition rate overall, and how does it vary by department and job level?
- Does satisfaction score predict attrition better than salary does?
- What is the tenure profile of leavers — are we losing people early or late?
- Is there a pay gap by level once department is controlled for?
- Which specific group should we intervene with first, and what is the evidence?
What you are working with
- 1,200 employee records
- 200 leavers — an overall attrition rate of about 16.7%
- Customer Support has the highest departmental attrition at about 24%
- Average salary across all levels is about $82,351
Analyse
KPI definitions
Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.
| KPI | Definition | Why it matters / watch out for |
|---|---|---|
| Headcount | COUNTROWS of employees where Attrition = No | Current employees only. |
| Total Employees | COUNTROWS of all rows | Includes leavers — needed as the attrition denominator. |
| Attrition Rate % | DIVIDE(Leavers, Total Employees) | Be explicit about the denominator on the report. |
| Average Tenure (years) | Average of DATEDIFF(HireDate, today or ExitDate) | Different for leavers and stayers. |
| Average Salary | AVERAGE of AnnualSalary | Always segment this — the blended figure hides everything. |
| Average Satisfaction | AVERAGE of SatisfactionScore | 1–5 scale. Track by department. |
| Leavers | COUNTROWS where Attrition = Yes | The numerator. |
Traps specific to this dataset
These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.
This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.
bwb-hr-employees.csv. Check column quality — pay particular attention to the blank ExitDate column and confirm the blanks are where you expect them.Deliverable
A .pbix file with a single board-ready page plus a supporting detail page, and a written recommendation of no more than 200 words naming one specific intervention and the evidence behind it.
How this is scored
Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.
- Build a satisfaction-vs-attrition scatter at department level to show the relationship visually
- Add a survival-style view: what proportion of each hire cohort is still employed after 1, 2 and 3 years
- Calculate the estimated cost of attrition using a replacement-cost assumption you state openly
The CFO needs a monthly performance pack that replaces the current 40-slide deck. She wants actual versus budget by account and cost centre, with variances that are immediately readable as favourable or unfavourable — which is harder than it sounds, because a negative variance on a cost line is good news.
Questions the dashboard must answer
- What is revenue, cost and profit for the period, versus budget?
- Which cost centres are consistently over budget, and by how much?
- What is the gross margin trend, and is it improving?
- Which single account drives the largest absolute variance?
- Does favourable revenue variance offset unfavourable cost variance overall?
What you are working with
- 722 ledger rows across 24 months
- Revenue accounts are stored positive; COGS and OpEx are stored negative
- Ten accounts across five cost centres
- Both 2024 and 2025 are present, so year-over-year comparison is possible
Analyse
KPI definitions
Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.
| KPI | Definition | Why it matters / watch out for |
|---|---|---|
| Total Revenue | SUM of ActualAmount where AccountType = Revenue | Positive sign convention. |
| Total Costs | SUM of ActualAmount where AccountType in (COGS, OpEx) | Stored negative — decide how to display. |
| Gross Profit | Revenue + COGS | Plus, not minus — COGS is already negative. |
| Gross Margin % | DIVIDE(Gross Profit, Revenue) | The trend line the CFO watches. |
| Net Profit | SUM of all ActualAmount | Works because of the sign convention. |
| Budget Variance | Actual − Budget | Raw arithmetic difference. |
| Variance % | DIVIDE(Variance, ABS(Budget)) | ABS in the denominator, or negative budgets flip the sign. |
| Favourable / Unfavourable | SWITCH on AccountType and variance sign | The measure that makes the report readable. |
Traps specific to this dataset
These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.
SUM(ActualAmount) across everything gives net profit, which is correct and probably not what your "Total Costs" card should show. Decide whether to display costs as positive magnitudes and apply that consistently.DIVIDE(Variance, Budget) on a cost account flips the sign. Use ABS(Budget) in the denominator.This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.
bwb-finance-actuals-budget.csv. Before writing anything, sum ActualAmount by AccountType and confirm you understand which are positive and which are negative.Deliverable
A .pbix with an executive summary page, a P&L matrix page with correct favourable/unfavourable formatting, and a variance analysis page including a waterfall bridging budget to actual.
How this is scored
Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.
- Add a rolling 12-month view so seasonality does not distort month-on-month comparison
- Build a cost centre scorecard ranking centres by consistency of budget adherence, not just size of variance
- Add a what-if parameter letting the CFO model a percentage cost reduction and see the profit impact
A hospital operations director needs to understand where capacity and cost are going. Bed days are the constraint, readmissions are a quality signal the regulator watches, and cost per encounter varies far more between departments than anyone can currently explain.
Questions the dashboard must answer
- How many encounters and distinct patients did we handle, and how is that trending?
- Which department has the highest cost per encounter, and does severity explain it?
- What is our 30-day readmission rate, and does it vary by admission type or department?
- How does length of stay distribute — and is the mean misleading?
- Where are bed days concentrated, and which department is the capacity constraint?
What you are working with
- 3,000 encounters across one calendar year
- Overall 30-day readmission rate of about 5.5%
- Average length of stay of about 3.4 days
- Seven departments with genuinely different cost and stay profiles
Analyse
KPI definitions
Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.
| KPI | Definition | Why it matters / watch out for |
|---|---|---|
| Total Encounters | COUNTROWS | One row per encounter. |
| Distinct Patients | DISTINCTCOUNT of PatientID | Patients can appear more than once — this gap matters. |
| Average Length of Stay | AVERAGE of LengthOfStayDays | Report the median alongside it. |
| Total Bed Days | SUM of LengthOfStayDays | The real capacity measure. |
| Total Cost | SUM of TotalCost | Whole-encounter cost. |
| Cost per Encounter | DIVIDE(Total Cost, Total Encounters) | Compare across departments. |
| Readmission Rate % | DIVIDE(Readmitted encounters, Total Encounters) | The quality signal. |
| Average Severity | AVERAGE of SeverityScore | Context for cost comparisons. |
Traps specific to this dataset
These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.
COUNTROWS gives encounters; DISTINCTCOUNT(PatientID) gives people. Confusing them makes "patients treated" look far higher than it is. Show both and label them precisely.This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.
bwb-healthcare-encounters.csv. Examine the distribution of LengthOfStayDays before deciding how to summarise it.MEDIAN, and showing it beside the mean is the point.Deliverable
A .pbix with three pages — operations overview, cost analysis with severity context, and quality/readmissions — plus a written note on which single department you would investigate first and why.
How this is scored
Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.
- Add a length-of-stay histogram with the mean and median marked, to demonstrate the skew explicitly
- Build a payer-mix view showing cost recovery profile by payer type
- Model the bed-day impact of reducing average length of stay by half a day in the highest-volume department
A supply chain manager is renegotiating supplier contracts next quarter. She needs a supplier scorecard grounded in delivery data rather than relationships, and she needs to know which warehouses and categories are most exposed to late delivery.
Questions the dashboard must answer
- Which suppliers miss their promised lead time most often, and by how much?
- Does a higher supplier quality score actually correlate with on-time delivery?
- Which warehouse absorbs the most late deliveries?
- What is the fill-rate distribution, and which category is worst affected?
- Which three suppliers should we renegotiate first, and what is the evidence?
What you are working with
- 2,200 purchase orders across one year
- About 48% of orders arrive later than promised
- Average delay of about 1.2 days across all orders
- Seven suppliers across four warehouses
Analyse
KPI definitions
Define every one of these as a measure, and write the definition down. A KPI whose definition is not documented will be interpreted three different ways within a month.
| KPI | Definition | Why it matters / watch out for |
|---|---|---|
| Total Purchase Orders | COUNTROWS | One row per PO. |
| On-Time Delivery % | DIVIDE(On-time POs, Total POs) | The headline supplier metric. |
| Average Delay Days | AVERAGE of DelayDays | Negative means early — do not clamp to zero. |
| Fill Rate % | DIVIDE(SUM QuantityReceived, SUM QuantityOrdered) | Weighted, not an average of ratios. |
| Total Purchase Cost | SUM of PurchaseCost | Spend, for weighting supplier importance. |
| Severely Late % | DIVIDE(Severely Late POs, Total POs) | More than 7 days late. |
| Supplier Quality Score | AVERAGE of SupplierQualityScore | Stated score, to test against actual performance. |
Traps specific to this dataset
These are the mistakes that actually get made on this data. Read them before you start, not after your numbers disagree with the source file.
This is one sensible order, not the only one. If you already know how you want to approach it, do that instead — the deliverable is what matters.
bwb-supply-chain.csv. Check the DelayDays distribution — note the negative values and the long right tail from disrupted orders.Deliverable
A .pbix with a supplier scorecard page, a delivery performance page and a warehouse exposure page, plus a one-page written recommendation naming three suppliers to renegotiate with the evidence for each.
How this is scored
Do not start these until the core dashboard answers every question above. A complete simple dashboard beats an incomplete clever one, in a portfolio and at work.
- Add a lead-time reliability measure using standard deviation of delay — consistency matters as much as average
- Build a spend-at-risk measure: total purchase cost flowing through suppliers below a chosen on-time threshold
- Add a what-if parameter for an acceptable on-time threshold and let the scorecard reclassify suppliers live