1.1 Purpose
The Internal Audit Analysis Dashboard provides a comprehensive view of risk assessments, audit outcomes, and expense tracking to assist management in monitoring compliance, financial risk, and operational efficiency. The goal is to enhance decision-making with real-time insights into audit performance and financial expenditures.
1.2 Scope
This dashboard is designed for internal auditors, finance teams, compliance officers, and senior management. It enables users to analyze audit risks, track financial transactions, and monitor audit responsibilities across various departments and cities.
1.3 Audience
- Internal Auditors
- Compliance Officers
- Finance Teams
- Senior Leadership
- Risk Management Teams
2. Dashboard Features & Functionalities
2.1 Risk Analysis
- Risk Rating by Audit Manager
- Risk Rating by Audit Outcome
- Risk Rating by Department
- Total Amount at Risk by Department & Risk Type
- Risk Rating by Responsibilities (Management vs. Board of Directors)
2.2 Expense Analysis
- Total Spending by Department
- Breakdown of Expenses by Category (Hotel, Taxi, Meals, Personal Car Mileage, etc.)
- Expenses by Employee Name & Country
- Spending Trends Over Time (Q1, Q2, Q3, Q4)
- Approval Status Analysis
2.3 Filtering & Segmentation
- Year Selection (2018, 2020, etc.)
- City Selection
- Department Selection
- Risk Type Selection (High, Medium, Low)
- Expense Category Selection
- Approval Status Selection
3. Data Sources & Integration
- Audit Management System
- Finance & Expense Tracking System
- Risk & Compliance Database
- Enterprise Resource Planning (ERP) System
4. User Requirements
4.1 Functional Requirements
- Ability to filter by year, city, department, risk type, and expense category
- Interactive visualizations for risk and expense tracking
- Exportable reports for audit summaries
- Automated data refresh for up-to-date insights
4.2 Non-Functional Requirements
- Performance: Dashboard should load within 5 seconds
- Security: Role-based access control to protect sensitive data
- Scalability: Ability to handle increasing audit and expense data
5. Key Performance Indicators (KPIs)
- Total Risk Amount by Department & Risk Type
- Audit Risk Ratings (High, Medium, Low) by Manager & Department
- Audit Outcomes & Resolution Rates
- Total Expense Breakdown by Category
- Spending Trends Over Time
- Percentage of Approved vs. Pending Expenses
6. Assumptions & Constraints
- Data should be regularly updated from connected systems
- User access must be managed based on audit roles
- Historical data may be limited for trend analysis
7. Project Completion Method
7.1 SQL Queries
- Extract audit and expense data from multiple sources.
- Transform and clean data for accuracy and consistency.
- Example SQL Query to retrieve audit risk data:
SELECT AuditID, Department, RiskType, RiskRating, Manager, AmountAtRisk FROM AuditData WHERE Year = 2024; - Queries to calculate total risk by department, audit outcomes, and spending trends.
7.2 Power Query
- Connect Power BI to SQL databases and expense management systems.
- Perform ETL (Extract, Transform, Load) operations:
- Data cleansing (removing duplicates, handling missing values)
- Data merging from multiple sources
- Creating calculated columns
- Example Power Query transformation:
let Source = Sql.Database("ServerName", "DatabaseName"), AuditTable = Source{[Schema="dbo", Item="AuditData"]}[Data], FilteredRows = Table.SelectRows(AuditTable, each [Year] = 2024) in FilteredRows
7.3 DAX (Data Analysis Expressions)
- Create calculated measures and columns for deeper insights.
- Example DAX formula for Total Risk Amount:
Total Risk Amount = SUM(AuditData[AmountAtRisk]) - DAX measures to analyze risk distribution, expenses by department, and trends.
8. Conclusion
The Internal Audit Analysis Dashboard provides a comprehensive view of audit risks, financial expenditures, and compliance insights. It enables management to take proactive measures in risk mitigation and financial control. The dashboard will evolve based on business needs and user feedback.
sample data files for the Internal Audit Analysis Dashboard, including:
- Audit Risk Data (CSV) – Audit risk ratings by department, manager, and risk type.
- Audit Outcomes (CSV) – Audit results, resolution status, and financial impact.
- Expense Analysis (CSV) – Department-wise expenses, categories, and approval status.
Developed by Muhammad Zahid
