This project is a fully self-designed analytics solution, built entirely from scratch using Microsoft Excel.
I manually created a realistic synthetic UK population dataset and transformed it into a stakeholder-ready dashboard answering real-world questions around:
- demographics
- income and debt
- assets and net worth
- regional and sectoral differences
No pre-made datasets.
No templates.
Just data design → analysis → insight → presentation.
In real analytics roles, the hardest part is not building charts — it’s:
structuring messy reality into decision-ready insight.
This project demonstrates:
- how to design data from zero
- how to ask the right questions
- how to translate raw numbers into insight
- how to present findings clearly to non-technical stakeholders
The dashboard is designed around practical business and policy questions, including:
- 👥 Gender distribution (men vs women)
- 🎂 Average age across the population
- 💷 Average income (overall, by sector, by county)
- 🚗 Average car value per person
- 🧾 Debt risk indicators:
- % of people with debts greater than annual income
- Count of people with debts above a configurable threshold
- Count of people with less than X% of mortgage left
- 💰 Net worth insights:
- Average age of people whose net worth exceeds income
- Asset value vs debt exposure
- 🗺️ Regional insights:
- Income distribution by UK county / territory
- 🏭 Sector insights:
- Income differences across professions
(Health, Construction, Technology, Agriculture, Education, Social Services)
- Income differences across professions
These questions mirror real use cases in:
- financial analysis
- public policy
- risk assessment
- market and population research
The dataset simulates realistic distributions for:
- Demographics
- Gender
- Age
- Education
- Children
- Financials
- Income
- Debts
- Assets
- Net worth
- Assets
- Car value
- House value
- Mortgage remaining
- Professional attributes
- Field of work / sector
- Geography
- UK counties / territories
The structure is analysis-ready, optimised for:
- pivot tables
- KPI calculations
- segmentation
- dashboard visuals
Key metrics were deliberately chosen to tell a story, not just show numbers:
- Gender counts and proportions
- Average age & income (overall and segmented)
- Income by sector and territory
- Debt-to-income risk metrics
- Mortgage completion indicators
- Net worth vs income relationships
Each KPI answers a specific stakeholder question.
The dashboard includes:
- A dedicated dashboard sheet
- Clearly separated:
- Raw data
- Calculations
- Visual outputs
- Charts highlighting:
- Sector distribution
- Regional income variation
- Debt and risk indicators
- Clean formatting:
- Consistent currency & percentage formats
- Logical grouping
- Easy scanning for decision-makers
The focus is clarity, not decoration.
- Formulas
AVERAGE,AVERAGEIF,AVERAGEIFSCOUNTIF,COUNTIFS- Percentage and ratio calculations
- Pivot tables
- Income by sector
- Income by county
- Gender distributions
- Layout & modelling best practices
- Separation of raw data vs calculations
- Scalable structure for future extensions
Exact formulas and structure can be explored directly in the Excel file.
- Download Population Analysis.xlsx
- Open in Microsoft Excel (desktop recommended)
- Navigate to the Dashboard sheet
- Explore:
- KPIs
- Charts
- Sector & territory insights
- Review raw data and calculations to understand the full model
All project files — including the Excel dashboard, raw synthetic dataset, and supporting materials — are available via Google Drive.
- 📊 Population Analysis.xlsx — main Excel dashboard
- 🧮 Raw synthetic population & financial data
- 📐 Supporting calculation sheets and structures
Recommended: Use Microsoft Excel (desktop) for the best performance and full dashboard interactivity.
This dataset is ideal for:
- Power BI or SAP Analytics Cloud migration
- Power Query / Power Pivot enhancement
- Time-series extensions (income or debt over years)
- Scenario modelling (interest rate shocks, policy changes)
- Web-based dashboards (Python / Streamlit)