Project Overview
This capstone project brings together everything you have learned in the Data Analytics course, focusing on core financial analysis techniques used by finance professionals worldwide. You will work with realistic P&L (Profit & Loss) statements spanning 3 fiscal years, build budget vs actual variance reports, and create financial forecasting models. Your goal is to deliver professional-grade analysis that helps executives understand past performance and predict future outcomes.
P&L Analysis
Analyze income statements and profitability trends
Variance Analysis
Compare budget vs actual with detailed breakdowns
Forecasting
Build predictive models for future performance
Dashboard
Create executive-ready Power BI reports
Learning Objectives
Technical Skills
- Build P&L analysis models in Excel
- Create budget vs actual variance reports
- Develop financial forecasting models
- Write DAX measures for financial KPIs
- Design interactive Power BI dashboards
Business Skills
- Interpret P&L statements and trends
- Analyze favorable and unfavorable variances
- Present financial forecasts to stakeholders
- Identify cost drivers and profit levers
- Make data-driven budget recommendations
Business Scenario
GlobalTech Industries
You have been hired as a Financial Analyst at GlobalTech Industries, a diversified technology company with operations in Hardware, Software, Cloud Services, and Professional Services. The CFO is preparing for the annual board meeting and needs comprehensive financial analysis.
"We need a deep dive into our P&L performance, understand where we are over or under budget, and forecast next year's financials. The board wants to see trends, variances, and projections that will help us make strategic investment decisions."
Business Questions to Answer
- What are the revenue and expense trends over 3 years?
- Which line items are driving profitability changes?
- How do margins compare across divisions?
- Where are we over or under budget?
- What are the largest favorable/unfavorable variances?
- Which divisions are meeting budget targets?
- What is the projected revenue for next fiscal year?
- What expense growth should we plan for?
- What are the best/worst case scenarios?
- Which divisions should receive more investment?
- Where can we optimize costs?
- What are the key financial risks?
The Dataset
You will work with a comprehensive financial dataset from GlobalTech Industries. The Excel workbook contains multiple sheets with income statements, balance sheets, and supplementary data across 3 fiscal years.
Kaggle Datasets (Recommended)
Download real financial datasets from Kaggle for authentic analysis experience. These datasets require a free Kaggle account to download.
Primary Datasets (Use These)
Additional Datasets
Download Datasets (Real Data)
These are actual datasets from Kaggle - real financial data for your analysis.
Dataset Structure
| File Name | Records | Description | Key Columns |
|---|---|---|---|
Financials.csv |
701 | Kaggle Company Financials - Sales & profit by segment, country, product | Segment, Country, Product, Units Sold, Sales, COGS, Profit, Date, Year |
balance_sheet_IBM.csv |
79 | IBM Balance Sheet data (2015-2023) - Real company financial statements | fiscalDateEnding, totalAssets, totalLiabilities, totalShareholderEquity |
Financials.csv Schema (Company Sales Data)
| Column | Type | Description |
|---|---|---|
Segment | String | Business segment (Government, Midmarket, Enterprise, etc.) |
Country | String | Country of sale (USA, Canada, France, Germany, Mexico) |
Product | String | Product name (Paseo, VTT, Velo, Amarilla, etc.) |
Discount Band | String | Discount tier (None, Low, Medium, High) |
Units Sold | Integer | Number of units sold |
Manufacturing Price | Currency | Cost to manufacture per unit |
Sale Price | Currency | Price sold per unit |
Gross Sales | Currency | Total sales before discounts |
Discounts | Currency | Total discount amount applied |
Sales | Currency | Net sales after discounts |
COGS | Currency | Cost of Goods Sold |
Profit | Currency | Net profit (Sales - COGS) |
Date | Date | Transaction date (MM/DD/YYYY) |
Month Number | Integer | Month number (1-12) |
Month Name | String | Month name (January-December) |
Year | Integer | Year (2014) |
balance_sheet_IBM.csv Schema (Real IBM Data)
| Column | Type | Description |
|---|---|---|
fiscalDateEnding | Date | End of fiscal period (YYYY-MM-DD) |
reportedCurrency | String | Currency (USD) |
totalAssets | Currency | Total company assets |
totalCurrentAssets | Currency | Current assets (cash, receivables, inventory) |
cashAndCashEquivalentsAtCarryingValue | Currency | Cash and cash equivalents |
totalLiabilities | Currency | Total company liabilities |
totalCurrentLiabilities | Currency | Current liabilities (payables, short-term debt) |
longTermDebt | Currency | Long-term debt obligations |
totalShareholderEquity | Currency | Total shareholder equity |
retainedEarnings | Currency | Accumulated retained earnings |
commonStockSharesOutstanding | Integer | Number of shares outstanding |
Sample Data Preview
Understanding the data structure is crucial. Here is what a typical row from each dataset looks like:
Financials.csv (Kaggle Company Sales Data):
| Segment | Country | Product | Units Sold | Sales | COGS | Profit | Date |
|---|---|---|---|---|---|---|---|
| Government | Canada | Carretera | $1,618.50 | $32,370.00 | $16,185.00 | $16,185.00 | 01/01/2014 |
| Government | Germany | Carretera | $1,321.00 | $26,420.00 | $13,210.00 | $13,210.00 | 01/01/2014 |
balance_sheet_IBM.csv (Real IBM Balance Sheet):
| fiscalDateEnding | totalAssets | totalLiabilities | totalShareholderEquity | longTermDebt |
|---|---|---|---|---|
| 2023-12-31 | $135.2B | $112.6B | $22.5B | $54.6B |
| 2022-12-31 | $127.2B | $105.2B | $21.9B | $47.2B |
Project Requirements
Your deliverables must include both an Excel workbook with detailed analysis and a Power BI dashboard for executive presentation. Structure your work with clear documentation and professional formatting.
Excel Financial Model
Build a comprehensive Excel workbook that serves as the foundation for your analysis:
Data Preparation:
- Import and validate all source data sheets
- Create a consolidated data model linking all sheets
- Add calculated columns for derived metrics (margins, growth rates)
- Build a date dimension table for time intelligence
Pivot Table Analysis:
- Create pivot tables for revenue analysis by division, quarter, and year
- Build expense breakdown pivots with drill-down capability
- Design profitability analysis by segment
- Add slicers for interactive filtering
Financial Ratio Calculations
Profitability Ratios:
- Gross Margin: (Revenue - COGS) / Revenue
- Operating Margin: Operating Income / Revenue
- Net Profit Margin: Net Income / Revenue
- Return on Assets (ROA): Net Income / Total Assets
- Return on Equity (ROE): Net Income / Shareholders Equity
Liquidity Ratios:
- Current Ratio: Current Assets / Current Liabilities
- Quick Ratio: (Current Assets - Inventory) / Current Liabilities
- Cash Ratio: Cash / Current Liabilities
- Working Capital: Current Assets - Current Liabilities
Efficiency Ratios:
- Asset Turnover: Revenue / Average Total Assets
- Receivables Turnover: Revenue / Average Accounts Receivable
- Days Sales Outstanding: 365 / Receivables Turnover
- Inventory Turnover: COGS / Average Inventory
Variance Analysis
Year-over-Year Analysis:
- Calculate YoY growth for revenue, gross profit, operating income, net income
- Identify divisions with highest and lowest growth
- Analyze margin expansion or compression trends
- Document significant variances with explanations
Budget vs Actual (if applicable):
- Compare actual results to budget targets
- Calculate favorable and unfavorable variances
- Highlight areas exceeding or missing targets
Power BI Dashboard
Create an interactive executive dashboard with the following pages:
- Executive Summary: KPI cards, trend sparklines, key metrics at a glance
- Revenue Analysis: Revenue by division, trends, growth rates
- Profitability: Margin analysis, waterfall charts, segment performance
- Financial Health: Ratio scorecards, benchmark comparisons, trend indicators
- Division Deep Dive: Drill-through page for division-level details
Dashboard Requirements:
- Professional color scheme and consistent formatting
- Interactive filters (year, quarter, division slicers)
- Drill-through and drill-down capabilities
- Conditional formatting for KPI status indicators
- Tooltips with additional context
DAX Measures
Create calculated measures using DAX for dynamic calculations:
- Total Revenue, Total Expenses, Net Income measures
- YoY Growth % with SAMEPERIODLASTYEAR
- Running totals and cumulative metrics
- Prior period comparisons
- Ratio calculations as measures
Insights and Recommendations
Executive Summary: Create a comprehensive analysis summary with clear, actionable insights.
Key Findings (5-7 insights):
- Financial health assessment based on ratio analysis
- Profitability trends and margin drivers
- Division performance comparison and rankings
- Liquidity position and working capital trends
- Areas of concern or risk identification
Strategic Recommendations (3-5 actionable items):
- Investment priorities by division based on performance
- Cost optimization opportunities identified
- Working capital improvement suggestions
- Growth strategy recommendations
Recommended Workflow
Follow this structured approach to complete your project efficiently and systematically. Each phase builds upon the previous one to create a comprehensive financial analysis dashboard.
Data Import & Exploration
2-3 hours- Download and open the dataset
- Review all 5 sheets and understand relationships
- Validate data integrity and formulas
- Create data dictionary documentation
Excel Analysis
3-4 hours- Build pivot tables for revenue analysis
- Create ratio calculation sheet
- Design variance analysis tables
- Add Excel charts for key metrics
Power BI Data Model
2-3 hours- Import data into Power BI
- Create relationships between tables
- Build date dimension table
- Write DAX measures for KPIs
Dashboard Development
3-4 hours- Create Executive Summary page
- Build Revenue Analysis page
- Design Profitability page
- Add Financial Health page
Analysis & Insights
1-2 hours- Analyze financial health indicators
- Document 5-7 key findings
- Compare ratios to benchmarks
- Develop recommendations
Documentation & Submission
1 hour- Write comprehensive README
- Add dashboard screenshots
- Create executive summary document
- Test all files open correctly
- Upload to Google Drive and submit
Completion Milestone
Once you complete all 6 phases, you will have a professional portfolio piece showcasing your financial analysis skills!
Financial KPI Specifications
Calculate and display the following Key Performance Indicators. Organize your calculations in both Excel (using formulas) and Power BI (using DAX measures).
- Gross Margin: (Revenue - COGS) / Revenue * 100
- Operating Margin: Operating Income / Revenue * 100
- Net Profit Margin: Net Income / Revenue * 100
- EBITDA Margin: EBITDA / Revenue * 100
- Return on Assets: Net Income / Avg Total Assets * 100
- Return on Equity: Net Income / Avg Equity * 100
- Current Ratio: Current Assets / Current Liabilities
- Quick Ratio: (Current Assets - Inventory) / Current Liabilities
- Cash Ratio: Cash & Equivalents / Current Liabilities
- Working Capital: Current Assets - Current Liabilities
- Cash Conversion Cycle: DIO + DSO - DPO
- Asset Turnover: Revenue / Average Total Assets
- Receivables Turnover: Revenue / Avg Accounts Receivable
- Days Sales Outstanding: 365 / Receivables Turnover
- Inventory Turnover: COGS / Average Inventory
- Days Inventory Outstanding: 365 / Inventory Turnover
- Payables Turnover: COGS / Average Accounts Payable
- Revenue Growth YoY: (Current - Prior) / Prior * 100
- Gross Profit Growth: YoY change in gross profit
- Net Income Growth: YoY change in net income
- CAGR (3-Year): (End Value / Start Value)^(1/3) - 1
- Sequential Growth QoQ: Quarter-over-quarter change
KPI Implementation Tips
Required Visualizations
Create the following visualizations in your Power BI dashboard. All charts should be interactive, professionally formatted, and executive-ready.
Business Question: How has revenue changed over the 3-year period?
- X-axis: Time (months/quarters), Y-axis: Revenue ($)
- Multiple lines for each division
- YoY growth % in tooltips, trend line for total revenue
Business Question: How does revenue flow through to net income?
- Start with Revenue, end with Net Income
- Show COGS, Operating Expenses, Interest, Taxes
- Color code: green (positive), red (expenses)
Business Question: What is the revenue contribution from each division?
- Segments for each division with percentages
- Total revenue in center of donut
- Use company brand colors for divisions
Business Question: How do margins compare across divisions?
- Divisions on Y-axis, margins as clusters
- Gross, Operating, Net Margin comparison
- Add industry benchmark reference line
Business Question: What is the overall financial health at a glance?
- 6-8 KPI cards: ROE, ROA, Current Ratio, Quick Ratio
- Color indicators: Green/Yellow/Red for health status
- YoY change with arrow indicators
Business Question: Which metrics are growing fastest?
- YoY % change for Revenue, Gross Profit, Net Income
- Color: green (positive), red (negative)
- Sort by growth rate descending
Business Question: How does performance vary by quarter?
- Rows: Divisions, Columns: Q1-Q4 each year
- Heat map coloring (darker = higher values)
- Enable drill-down by clicking quarters
Business Question: Where is the company spending money?
- Hierarchy: Expense Category → Division
- Size: Expense amount, Color: YoY change
- Interactive drill-down capability
Business Question: How is cash being generated and used across operating, investing, and financing activities?
- X-axis: Time periods (quarters), Y-axis: Cash flow amount
- Stacks: Operating, Investing, Financing cash flows with net cash flow line overlay
- Insight: Cash generation capability, investment patterns, financing activity trends
Visualization Best Practices
Professional Formatting
- Use consistent color palette
- Apply proper number formatting
- Align elements cleanly
- Remove chart clutter
Interactivity
- Enable cross-filtering
- Add slicers for year, quarter, division
- Use drill-through for details
- Include informative tooltips
Executive Focus
- Lead with key insights
- Use traffic light indicators
- Show vs prior period
- Include benchmark comparisons
Submission Requirements
Create a Google Drive folder with the exact name shown below and share the link with view access:
Required Folder Name
Financial-Analysis-Project-[YourName]
Required Project Structure
Financial-Analysis-Project-[YourName]/
├── data/
│ └── financial_data.xlsx # Original dataset (download from above)
├── excel/
│ └── financial_analysis.xlsx # Your Excel analysis workbook
├── powerbi/
│ └── financial_dashboard.pbix # Your Power BI dashboard file
├── screenshots/
│ ├── dashboard_executive.png # Executive summary page screenshot
│ ├── dashboard_revenue.png # Revenue analysis page screenshot
│ └── dashboard_profitability.png # Profitability page screenshot
├── docs/
│ └── executive_summary.pdf # Executive summary document (1-2 pages)
└── README.md # REQUIRED - see contents belowREADME.md Template Structure
Your README should follow this professional structure:
1. Project Header
- Project title: "Financial Analysis Dashboard"
- Your full name
- Submission date
- Course name and project number
2. Business Context
- Brief description of GlobalTech Industries scenario
- Project objectives
- Dataset overview (3 years, 4 divisions, etc.)
3. Tools Used
- Microsoft Excel (version)
- Microsoft Power BI Desktop (version)
- Any additional tools
4. Key Findings
- 5-7 data-driven insights
- Each finding with supporting metric
- Business implications
5. Dashboard Screenshots
- 3-4 screenshots of your Power BI dashboard pages
- Brief caption for each
- Use markdown image syntax
6. Financial Ratios Summary
- Table of key ratios calculated
- Comparison to industry benchmarks
- Traffic light status indicators
7. Recommendations
- 3-5 strategic recommendations
- Priority ranking
- Expected business impact
8. File Descriptions
- Brief description of each deliverable
- How to navigate the Excel workbook
- How to use the Power BI dashboard
9. Contact Information
- Your email
- LinkedIn profile (optional)
Executive Summary Document
Create a 1-2 page PDF executive summary containing:
- Company financial health overview (1 paragraph)
- Key performance highlights (3-4 bullet points)
- Financial ratio scorecard table
- Top 3 strategic recommendations
- Areas requiring attention
Do Include
- Complete Excel workbook with all analysis sheets
- Power BI file (.pbix) with all dashboard pages
- Clear documentation in README
- Dashboard screenshots
- Executive summary PDF
- Original dataset in data folder
Do Not Include
- Power BI Desktop installer files
- Temporary or backup files (~$filename)
- Personal or sensitive information
- Broken file links or formulas
- Unfinished or draft versions
Enter your Google Drive folder link - we will verify your files automatically
Grading Rubric
Your project will be graded on the following criteria. Total: 600 points.
| Criteria | Points | Description |
|---|---|---|
| Excel Analysis | 100 | Comprehensive pivot tables, proper formulas, clean formatting, organized sheets |
| Financial Ratio Calculations | 100 | All required ratios calculated correctly with clear formulas and documentation |
| Power BI Dashboard | 150 | At least 8 professional visualizations, interactive features, clean design |
| DAX Measures | 75 | Well-written DAX measures for KPIs, time intelligence, and calculations |
| Analysis Quality | 75 | Insightful observations, accurate interpretations, benchmark comparisons |
| Documentation | 50 | Complete README, executive summary, clear file organization |
| Recommendations | 50 | Actionable, data-driven business recommendations with clear rationale |
| Total | 600 |
Grading Scale
Excellent
540-60090-100%
Good
480-53980-89%
Satisfactory
420-47970-79%
Needs Work
<420<70%
Ready to Submit?
Make sure you have completed all requirements and reviewed the grading rubric above.
Submit Your ProjectCommon Issues & Solutions
Problem: Data source errors when opening the .pbix file on a different computer
Solution: Use relative paths or include the data file in the same folder. When importing, use "Get Data" > "Excel workbook" and navigate to the data folder within your project.
Verify: Test by moving your entire project folder to a different location and re-opening the .pbix file
Problem: Excel shows circular reference warning when calculating ratios
Solution 1: Check that your formulas do not reference their own cell directly or indirectly
Solution 2: For average calculations (like Average Assets), create separate cells for beginning and ending values
Solution 3: Use the AVERAGE function with explicit cell references rather than calculated ranges
Problem: SAMEPERIODLASTYEAR or other time intelligence functions return errors
Solution: Ensure you have a proper Date table marked as a Date Table in Power BI
Tips:
- Create a dedicated Date table with continuous dates (no gaps)
- Mark it as Date Table: Modeling > Mark as Date Table
- Create relationship between Date table and your fact tables on the date column
- Use the Date table column in your time intelligence functions
Problem: Calculated ratios do not match expected values or seem unrealistic
Solution: Double-check your formula logic and ensure consistent units
Common Issues:
- Mixing monthly and annual figures (multiply monthly by 12 for annual ratios)
- Using point-in-time vs average values (ROA should use average assets)
- Percentage vs decimal format mismatch
- Gross Margin should be around 40-60% for this dataset
Problem: Charts show blank or display error messages
Solution: Check data types and relationships
Steps:
- Verify data loaded correctly in Data view
- Check that numeric columns are typed as numbers (not text)
- Ensure relationships are active (solid line in Model view)
- Try removing and re-adding the visual
Problem: Reviewer cannot access your submitted folder
Solution: Verify sharing settings are correct
Steps:
- Right-click folder > Share > Get link
- Change from "Restricted" to "Anyone with the link"
- Set role to "Viewer"
- Test by opening link in incognito/private browser window
- Verify all files inside folder are also accessible