Excel automate, Power Query, data mapping, dashboard development

I am looking for an experienced Excel and Power Query specialist to complete a multi-level governance and assurance system for a health and social care organisation.

The organisation has five service divisions. The workbooks, registers and overall structure have already been designed; I am not looking for someone to start again. I need someone who can review the existing files, improve the formulas and functionality, make the registers smarter and create reliable connections across the system.

The required reporting flow is:

Service/Practice Observation → Regional Governance → Divisional Governance → Group Governance/Master Dashboard

Each division requires its own governance workbook and dashboard. Service and regional information must feed into the relevant divisional workbook, with key information from all five divisions feeding into one Group-level Master Dashboard.

Smart governance registers

The project includes a suite of existing registers covering areas such as:

Risk
Safeguarding
Incidents and serious incidents
Complaints and compliments
GDPR and data breaches
Restrictive practice
Governance actions
Service user feedback
Audits and improvement
Regulatory actions
Training and workforce compliance

The exact number of files will be confirmed with the successful freelancer following an initial review.

The registers need to become functional, consistent and easy to use, rather than simply formatted spreadsheets. Depending on the register, this may include:

Unique reference numbers
Automated status calculations
Open, closed and overdue indicators
Due-date and review alerts
Automated risk ratings and RAG indicators
Dropdown lists and controlled data entry
Conditional formatting
Automated totals and summary metrics
Monthly, quarterly and annual reporting
Filtering by service, region, division, category, status and reporting period
Identification of themes, recurring issues and required escalations
Formula protection without restricting data entry
Summary pages or dashboards
Connections to the appropriate regional, divisional and Group dashboards

The registers will remain the source of truth for individual records. Only relevant totals, themes, trends, risk ratings and outstanding actions should feed into higher-level dashboards.

Main requirements

The successful freelancer will be required to:

Review and inventory the existing workbooks and registers
Produce a clear data map showing how information should move through the system
Make the registers smarter, more automated and easier to maintain
Add, correct and protect formulas across multiple files and tabs
Standardise dropdowns, identifiers, categories and reporting periods where required
Create calculated metrics, alerts and RAG indicators
Link Practice Observation data to Regional Governance workbooks
Link Regional Governance workbooks to the relevant Divisional Governance workbook
Complete dashboards for each of the five divisions
Link all five Divisional Governance workbooks to the Group Master Dashboard
Link relevant register data to the correct reporting level
Use Power Query or another reliable Excel-based method where appropriate
Ensure files work reliably within Microsoft 365/SharePoint
Reduce the risk of broken links when files are moved, updated or refreshed
Test the complete data flow from source registers through to the Master Dashboard
Ensure the system is straightforward for managers with varying levels of Excel experience

Deliverables

Smart, functioning governance registers
Functional Practice Observation templates
Regional Governance and Assurance workbooks
Five Divisional Governance workbooks and dashboards
One Group Governance workbook and Master Dashboard
Reliable connections between all relevant files
Working formulas, validation, dropdowns, alerts and RAG ratings
A complete data map/data dictionary
Testing of the full reporting flow
Simple refresh and maintenance instructions
Final handover and correction of any errors identified during testing

Confidentiality

The work relates to confidential organisational systems. A Non-Disclosure Agreement must be signed before any files are shared. Anonymised or test data will be used wherever possible.

Please include in your proposal:

Your experience of creating smart Excel registers and trackers
Your experience of linking Excel files stored within SharePoint/Microsoft 365
Your Power Query experience
How you would minimise broken connections
How you would approach mapping and testing this system
Examples of comparable work
What you can deliver within the approximate £1,000 budget