SharePoint Audit Automation & Power BI Dashboard
Budget: $250 – $750 USD
SPECIFICATION FOR SHAREPOINT-BASED AUDIT PACK AUTOMATION & POWER BI DASHBOARD WITH ESCALATION
Version: 1.4
Prepared for: Wynleigh ICS
1. PROJECT OVERVIEW
Wynleigh ICS requires an automated Microsoft 365–based system to:
Generate audit packs based on the existing audit documentation matrix.
Prepopulate forms with client and audit metadata sourced from the existing client spreadsheet.
Replace the current audit tracking spreadsheet with SharePoint lists + Power BI dashboard.
Track audit progress through predefined workflow steps.
Send automated email notifications / escalations when planned audit dates are missed.
Provide dashboards showing progress, workload, bottlenecks, and audit status.
2. EXISTING DATA SOURCES
2.1 Client Metadata
Stored in a spreadsheet for all certified clients.
Contains: Client Name, Client ID#, Address, Contact Info, Standards, Multi-site flag, Certificate cycle, etc.
Must be imported into SharePoint or Dataverse as the master data source.
2.2 Audit Tracking
Existing spreadsheet contains scheduled audits with planned dates, assigned auditors, audit type, and status.
To be replaced by:
SharePoint Audit List (workflow tracking)
Power BI Dashboard (visualisation, KPI reporting, escalations)
3. SHAREPOINT STRUCTURE
Existing repository retained:
Policies
Procedures
Forms
Other Documents
Audit Document Matrix
Existing matrix used to determine which documents to include per audit type.
Generated Audit Packs Library
Stores ZIP files of audit packs.
Tracks who generated each pack and timestamps.
4. AUDIT METADATA AND WORKFLOW TRACKING
4.1 Audit List Metadata
Audit ID
Client Name / Client ID (lookup from client metadata)
Audit Type (Stage 1, Stage 2, Surveillance, Recertification)
Audit Team Leader
Audit Team Members
Audit Start / End Dates
Standard(s) in scope
Audit Status (workflow stage)
Planned vs Actual Dates
4.2 Example Workflow Stages
Audit Scheduled
Audit Pack Generated
Audit Pack Distributed
Audit Plan Prepared
Audit Plan Approved
Audit Plan Distributed
Audit Conducted
NCs Raised
NCs closed
Report reviewed
Audit Report Submitted
Audit Report Approved
Board Approval scheduled
Board approval concluded
Certificate Issued
4.3 Escalation Features
Power Automate will trigger email notifications to the Audit Team Leader and Administrator for example when:
Audit pack not generated by scheduled date
Audit plan not prepared or approved on time
Audit report submission overdue
Emails include client info, audit type, responsible staff, planned vs actual date, and escalation instructions.
5. AUDIT PACK GENERATION USING EXISTING MATRIX
Select audit type in SharePoint or Power Apps form.
Power Automate uses audit documentation matrix to identify required documents from existing folders.
Copy documents to a temporary audit folder.
Prepopulate templates using metadata (client spreadsheet / SharePoint fields):
Client Name / ID
Address
Audit Type
Audit Dates
Audit Team Leader / Auditors
Standards
Certificate cycle
Compress into ZIP file.
Store in “Generated Audit Packs” library and optionally email Audit Team Leader.
6. POWER BI DASHBOARD REQUIREMENTS
6.1 Key Functions
Replace current audit tracking spreadsheet.
Real-time audit progress tracking per workflow stage.
KPI reporting: audits completed, audits pending, overdue audits.
Drilldowns by auditor, client, audit type, eacxh of 4 standard, and site.
Visualisation of workflow bottlenecks.
6.2 Escalation Integration
Highlight overdue actions using traffic-light visualisation. e.g. responses to NC not received.
Trigger automatic emails via Power Automate when planned dates are missed.
7. SECURITY AND ACCESS CONTROL
Role-based permissions:
Admin: full access
Audit Team Leader: assigned audits
Auditors: assigned audits only
Master templates remain read-only.
Version control for all templates and audit pack documents.
Full audit trail of document generation, workflow updates, and escalations.
8. DELIVERABLES
SharePoint lists and libraries integrated with existing client metadata.
Power Automate flows:
Audit pack generation
Document prepopulation
ZIP packaging
Email notifications and escalations
Power BI dashboard with KPIs, visualisation, and escalations.
Training for:
Admins (3 hours)
Auditors / Team Leaders (2 hours)
Technical documentation:
Metadata mapping
Workflow configuration
Power Automate flows
Power BI setup
Version: 1.4
Prepared for: Wynleigh ICS
1. PROJECT OVERVIEW
Wynleigh ICS requires an automated Microsoft 365–based system to:
Generate audit packs based on the existing audit documentation matrix.
Prepopulate forms with client and audit metadata sourced from the existing client spreadsheet.
Replace the current audit tracking spreadsheet with SharePoint lists + Power BI dashboard.
Track audit progress through predefined workflow steps.
Send automated email notifications / escalations when planned audit dates are missed.
Provide dashboards showing progress, workload, bottlenecks, and audit status.
2. EXISTING DATA SOURCES
2.1 Client Metadata
Stored in a spreadsheet for all certified clients.
Contains: Client Name, Client ID#, Address, Contact Info, Standards, Multi-site flag, Certificate cycle, etc.
Must be imported into SharePoint or Dataverse as the master data source.
2.2 Audit Tracking
Existing spreadsheet contains scheduled audits with planned dates, assigned auditors, audit type, and status.
To be replaced by:
SharePoint Audit List (workflow tracking)
Power BI Dashboard (visualisation, KPI reporting, escalations)
3. SHAREPOINT STRUCTURE
Existing repository retained:
Policies
Procedures
Forms
Other Documents
Audit Document Matrix
Existing matrix used to determine which documents to include per audit type.
Generated Audit Packs Library
Stores ZIP files of audit packs.
Tracks who generated each pack and timestamps.
4. AUDIT METADATA AND WORKFLOW TRACKING
4.1 Audit List Metadata
Audit ID
Client Name / Client ID (lookup from client metadata)
Audit Type (Stage 1, Stage 2, Surveillance, Recertification)
Audit Team Leader
Audit Team Members
Audit Start / End Dates
Standard(s) in scope
Audit Status (workflow stage)
Planned vs Actual Dates
4.2 Example Workflow Stages
Audit Scheduled
Audit Pack Generated
Audit Pack Distributed
Audit Plan Prepared
Audit Plan Approved
Audit Plan Distributed
Audit Conducted
NCs Raised
NCs closed
Report reviewed
Audit Report Submitted
Audit Report Approved
Board Approval scheduled
Board approval concluded
Certificate Issued
4.3 Escalation Features
Power Automate will trigger email notifications to the Audit Team Leader and Administrator for example when:
Audit pack not generated by scheduled date
Audit plan not prepared or approved on time
Audit report submission overdue
Emails include client info, audit type, responsible staff, planned vs actual date, and escalation instructions.
5. AUDIT PACK GENERATION USING EXISTING MATRIX
Select audit type in SharePoint or Power Apps form.
Power Automate uses audit documentation matrix to identify required documents from existing folders.
Copy documents to a temporary audit folder.
Prepopulate templates using metadata (client spreadsheet / SharePoint fields):
Client Name / ID
Address
Audit Type
Audit Dates
Audit Team Leader / Auditors
Standards
Certificate cycle
Compress into ZIP file.
Store in “Generated Audit Packs” library and optionally email Audit Team Leader.
6. POWER BI DASHBOARD REQUIREMENTS
6.1 Key Functions
Replace current audit tracking spreadsheet.
Real-time audit progress tracking per workflow stage.
KPI reporting: audits completed, audits pending, overdue audits.
Drilldowns by auditor, client, audit type, eacxh of 4 standard, and site.
Visualisation of workflow bottlenecks.
6.2 Escalation Integration
Highlight overdue actions using traffic-light visualisation. e.g. responses to NC not received.
Trigger automatic emails via Power Automate when planned dates are missed.
7. SECURITY AND ACCESS CONTROL
Role-based permissions:
Admin: full access
Audit Team Leader: assigned audits
Auditors: assigned audits only
Master templates remain read-only.
Version control for all templates and audit pack documents.
Full audit trail of document generation, workflow updates, and escalations.
8. DELIVERABLES
SharePoint lists and libraries integrated with existing client metadata.
Power Automate flows:
Audit pack generation
Document prepopulation
ZIP packaging
Email notifications and escalations
Power BI dashboard with KPIs, visualisation, and escalations.
Training for:
Admins (3 hours)
Auditors / Team Leaders (2 hours)
Technical documentation:
Metadata mapping
Workflow configuration
Power Automate flows
Power BI setup