gokulrnair7/sankalp-industries-budget-vs-actual-variance-analysis

Purpose and Entry Points

This repository is an end-to-end operating-expense budget-versus-actual variance analysis for a synthetic enterprise. Its pipeline generates a star-schema dataset, loads and cleans it in SQL Server, then presents the governed results in Excel and Power BI.

Primary execution order:

• src/generate_dataset.py

• sql/01_create_and_load.sql

• sql/02_data_cleaning.sql

• sql/03_analysis.sql

Synthetic Data Generation

src/generate_dataset.py is the upstream source of the analytical dataset. It produces:

• data/fact_budget_actuals.csv

• data/dim_account.csv

• data/dim_department.csv

The fact records monthly budget and actual spend; dimensions define accounts/cost categories and organizational attributes. Generation intentionally injects realistic ERP-like conditions: null actuals for open periods, duplicates, zero-budget spend, credit notes, renamed accounts, and isolated spikes. These are inputs to downstream data-governance and variance logic, not generation failures.

SQL Server Raw-Landing Boundary

sql/01_create_and_load.sql defines the SQL Server physical schema before CSV import. This is an important boundary: typed database tables protect date, text-code, nullable-actual, and numeric semantics from import-wizard inference.

The source CSVs are imported into raw tables after schema creation. Raw data is retained as the audit-preserving landing layer; downstream consumers should not depend on it for cleaned totals.

Cleaning and Governed Fact View

sql/02_data_cleaning.sql creates vw_clean_fact, the canonical analytical fact layer. It is a view, preserving raw records while centralizing transformations.

The view:

• removes exact business-record duplicates while excluding the surrogate key from duplicate matching;

• repairs consistently swapped date parts;

• marks incomplete/open reporting periods;

• retains null actuals, credit notes, and zero-budget actual spend as meaningful financial events.

vw_clean_fact is the main contract between data preparation and reporting. In particular, open-period filtering must be applied by every aggregate consumer to avoid treating missing/unclosed actuals as a completed-period variance.

Analysis Layer

sql/03_analysis.sql contains the management-analysis queries over the cleaned view and dimensions. Its CTE-based stages support independent inspection of intermediate logic.

The analytical flow calculates headline net variance and absolute variance, then slices results by department, business unit, and cost category. It additionally identifies:

• one-off spend by comparing a cost line to its own monthly baseline;

• material variances requiring both percentage and rupee thresholds;

• unbudgeted spend;

• cumulative year-to-date variance.

This separates persistent control or planning issues from exceptional events and prevents small high-percentage misses or large low-significance values from dominating review.

Reporting Consumers

excel/Sankalp_Variance_Analysis.xlsx is a formula-driven executive-analysis artifact. Its budget and actual inputs feed reconciled variance, bridge, and summary views from a single source area.

powerbi/Sankalp_Variance_Dashboard.pbix is the interactive reporting layer. It connects directly to SQL Server’s cleaned fact view and the two dimensions, rather than reimplementing cleaning in Power BI. The model uses one-to-many dimension-to-fact filtering and supports fiscal/calendar and organization slicers.

The Power BI file depends on a local SQL Server connection for refresh, although cached data is embedded.

Runtime and Infrastructure

Material runtimes are Python with pandas/numpy for generation and Microsoft SQL Server/SSMS for storage, import, cleaning, and querying. Excel and Power BI are downstream desktop BI clients.

The primary operational dependency is the SQL Server database connection: it hosts raw tables, vw_clean_fact, and the dimensional model consumed by Power BI. Data files under data/ are reproducible seed artifacts, while the Excel workbook and .pbix are presentation endpoints.