population-insights-dashboard

# 📊 Population & Financial Insights Dashboard ### Synthetic UK demographics & financial analytics — built end-to-end in Excel

✨ Overview

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.

🎯 Why this project matters

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

🧠 What the dashboard answers

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)

These questions mirror real use cases in:

financial analysis
public policy
risk assessment
market and population research

🧱 Data design & modelling

Synthetic dataset (created manually)

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

📐 Analytical thinking & KPIs

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.

📊 Excel dashboard design

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.

🛠️ Excel techniques used

Formulas
- AVERAGE, AVERAGEIF, AVERAGEIFS
- COUNTIF, 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.

🖥️ How to use this project

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

📂 Project Files & Dataset Access

All project files — including the Excel dashboard, raw synthetic dataset, and supporting materials — are available via Google Drive.

What you’ll find in the folder

📊 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.

🚀 Extension ideas

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)

### Built by **Neil Sable** _Data Modelling · Financial Analytics · Insight Design_