Capstone 5 — Financial Analytics Reporting System¶
A self-directed capstone building an automated financial reporting system — P&L, margin analysis, and budget variance. The most "business serious" capstone, ideal for analysts targeting finance, FP&A, or corporate strategy roles.
The Brief¶
A finance team produces monthly board reports manually, taking days and risking errors. Build an automated financial reporting system that produces the P&L, analyses profitability by segment/product, and tracks actuals against budget.
Tools¶
Excel (advanced) · SQL · Power BI
Difficulty¶
Intermediate — requires basic financial literacy (revenue, COGS, margin, variance) alongside analytics.
What You Must Deliver¶
- A clean financial dataset with validated math (numbers must tie out)
- A P&L summary and monthly trend
- Profitability analysis by segment, product, and region
- Budget vs actual variance analysis
- An automated, refreshable dashboard
- Financial recommendations
Suggested Datasets¶
- Microsoft Financial Sample
- Any financial dataset with revenue, cost, and ideally budget
- A synthetic P&L you build (revenue, COGS, budget by month/segment)
Financial Concepts You'll Apply¶
| Metric | Formula |
|---|---|
| Gross Profit | Revenue − COGS |
| Gross Margin % | Gross Profit / Revenue |
| Variance | Actual − Budget |
| Variance % | (Actual − Budget) / Budget |
See Projects/Financial-Performance-Analysis/business-problem for the full framework.
Business Questions¶
- What is the P&L (revenue, COGS, gross profit, margin)?
- Which segments/products drive profit (not just revenue)?
- How do actuals compare to budget? Where are the biggest variances?
- How much revenue is given away in discounts?
- What's the projected year-end result?
Requirements Checklist¶
- [ ] Financial math validated (recomputed and reconciled)
- [ ] P&L summary and monthly trend
- [ ] Profitability by segment AND comparison of revenue share vs profit share
- [ ] Budget variance analysis with favourable/unfavourable flags
- [ ] Automated refresh capability (Power Query or Power BI)
- [ ] Time intelligence (YTD, YoY, MoM)
- [ ] Portfolio README
The Reconciliation Requirement¶
In finance, numbers must tie out
A strong submission validates that Profit = Revenue − COGS and that subtotals sum to the total. Finance stakeholders reject any report whose numbers don't reconcile. Demonstrate this validation explicitly. See Projects/Financial-Performance-Analysis/data-cleaning.
Evaluation Criteria¶
| Criterion | Weight |
|---|---|
| Financial accuracy & reconciliation | 25% |
| P&L and profitability analysis | 20% |
| Budget variance analysis | 20% |
| Automation & time intelligence | 20% |
| Recommendations | 15% |
Stretch Goals¶
- A driver-based year-end forecast with a best/base/worst range
- A P&L waterfall chart
- What-if scenario modelling (discount cap, price change)
- Customer/account-level profitability