Budget vs. Actual Dashboard Power BI Report

Requirements Document: Budget vs. Actual Dashboard Power BI Report

1. Project Overview

The Budget vs. Actual Dashboard provides financial insights by comparing budgeted revenue and expenses against actual figures. The dashboard highlights key financial variances, trends, and departmental performance to support decision-making.

2. Objectives

• Compare budgeted vs. actual revenue and expenses.
• Identify variance percentages and trends.
• Provide insights into departmental financial performance.
• Enable time-based revenue analysis (MTD, QTD, YTD, YoY Growth).
• Offer dynamic filtering options for deeper analysis.

3. Data Sources & Structure

Tables and Fields

1. Financial Data Table
   • Year
   • Quarter
   • Month
   • Budgeted Revenue
   • Actual Revenue
   • Revenue Variance (%)
   • Budgeted Expenses
   • Actual Expenses
   • Expenses Variance (%)
   • MTD Revenue
   • QTD Revenue
   • YTD Revenue
   • YoY Growth
2. Departmental Performance Table
   • Department Name
   • Actual Revenue
   • Budgeted Revenue
   • Revenue Variance (%)
   • Actual Expenses
   • Budgeted Expenses
   • Expenses Variance (%)
3. Time Series Data Table
   • Year
   • Month
   • 3M Rolling Revenue
   • Actual Revenue
4. Filters & Slicers
   • Year Filter
   • Region Filter
   • Department Filter

4. Data Processing & Transformations

SQL Queries for Data Extraction

SELECT 
    Year, Quarter, Month, 
    SUM(Budgeted_Revenue) AS Budgeted_Revenue,
    SUM(Actual_Revenue) AS Actual_Revenue,
    (SUM(Actual_Revenue) - SUM(Budgeted_Revenue)) / SUM(Budgeted_Revenue) * 100 AS Revenue_Variance,
    SUM(Budgeted_Expenses) AS Budgeted_Expenses,
    SUM(Actual_Expenses) AS Actual_Expenses,
    (SUM(Actual_Expenses) - SUM(Budgeted_Expenses)) / SUM(Budgeted_Expenses) * 100 AS Expenses_Variance
FROM Financial_Data
GROUP BY Year, Quarter, Month;

Power Query Transformations

• Data Cleaning: Remove nulls, format dates, and correct data types.
• Merging Tables: Combine financial and departmental performance data.
• Creating Time Intelligence Columns: Add MTD, QTD, YTD, and YoY Growth fields.

5. DAX Measures for Power BI

Revenue Variance (%)

Revenue Variance % = 
DIVIDE(
    SUM(Financial_Data[Actual_Revenue]) - SUM(Financial_Data[Budgeted_Revenue]),
    SUM(Financial_Data[Budgeted_Revenue]),
    0
)

Expenses Variance (%)

Expenses Variance % = 
DIVIDE(
    SUM(Financial_Data[Actual_Expenses]) - SUM(Financial_Data[Budgeted_Expenses]),
    SUM(Financial_Data[Budgeted_Expenses]),
    0
)

Year-over-Year Growth (%)

YoY Growth % = 
VAR CurrentYear = SUM(Financial_Data[YTD Revenue])
VAR LastYear = CALCULATE(SUM(Financial_Data[YTD Revenue]), SAMEPERIODLASTYEAR(Financial_Data[Year]))
RETURN 
IF(LastYear = 0, BLANK(), (CurrentYear - LastYear) / LastYear)

6. Visuals & Dashboard Elements

1. KPI Cards:
   • Budgeted Revenue vs. Actual Revenue
   • Budgeted Expenses vs. Actual Expenses
   • Revenue Variance (%)
   • Expenses Variance (%)
2. Line Charts:
   • Actual and 3M Rolling Revenue by Year & Month
3. Bar Charts:
   • Revenue by Department
   • Expenses Variance by Department
4. Tables:
   • Quarterly Revenue & Expenses Comparison
   • MTD, QTD, YTD Revenue Breakdown
5. Slicers:
   • Year, Region, and Department filters

7. Deployment & Performance Optimization

• Scheduled Data Refresh: Refresh financial data daily/weekly.
• Optimized DAX Queries: Reduce complex calculations in visuals.
• Aggregated Tables: Improve performance using summary tables for high-level insights.

8. Security & Access Control

• Row-Level Security (RLS): Restrict access based on department and region.
• Role-based Access: Define user roles for financial analysts, managers, and executives.

9. Project Completion Method

1: Data Extraction (SQL Queries)

• Extract budget and actual financial data.
• Pre-process and clean the data using SQL queries.

2: Data Transformation (Power Query)

• Import the SQL-extracted data into Power BI.
• Clean, merge, and shape data.
• Create additional calculated columns.

3: Data Modeling & Relationships

• Establish relationships between financial, departmental, and time series tables.
• Ensure correct joins and indexing for performance optimization.

4: DAX Measures Development

• Create calculated measures for key financial metrics.
• Implement Year-over-Year, Quarter-over-Quarter, and Moving Average calculations.

5: Visualization & Dashboard Building

• Design KPI cards, charts, and slicers.
• Optimize visual hierarchy for better readability.

6: Testing & Validation

• Validate SQL results against Power BI outputs.
• Cross-check totals and trends.
• Ensure filtering and time intelligence functions work correctly.

7: Deployment & Optimization

• Publish the report to Power BI Service.
• Set up data refresh schedules.
• Implement security access controls.

10. Expected Outcomes

• Clear financial variance insights.
• Improved decision-making for budget management.
• Enhanced visibility into department-wise financial performance.
• Actionable intelligence for revenue growth strategies.

Budget vs Actual Financial Data Power Bi Report in CSV format. You can download it here:

Developed by Muhammad Zahid