Interview Guides

Budget vs Actual Variance Report | Dataford Interview Questions - Dataford - Ace your Interview

Budget vs Actual Variance Report

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsCase WhenData Wrangling

Also asked at

Problem

Task

FinSight tracks department spending in a single budgeting table. Write a SQL query to report departments where actual spending differs from projected budget.

Requirements

Return each department with its total projected budget, total actual spend, and the variance (actual_spend - projected_budget).
Include only departments where the totals do not match.
Add a status column that shows Over Budget when actual spend is greater than projected budget, otherwise Under Budget.
Order the results by absolute variance descending, then by department name.

Table Definition

department_budget_actuals

Column	Type	Description
id	INT	Primary key
department	VARCHAR(100)	Department name
projected_budget	DECIMAL(12,2)	Planned budget amount for the entry
actual_spend	DECIMAL(12,2)	Actual spend amount for the entry
report_month	DATE	Month of the budget record
cost_center	VARCHAR(50)	Cost center label

Sample Data

id	department	projected_budget	actual_spend	report_month	cost_center
1	Engineering	5000.00	5200.00	2024-01-01	Platform
2	Marketing	3000.00	2800.00	2024-01-01	Growth
3	Finance	2000.00	2000.00	2024-01-01	Accounting
4	Engineering	4500.00	4300.00	2024-02-01	Platform
5	Marketing	3200.00	3500.00	2024-02-01	Brand
6	HR	1500.00	1400.00	2024-01-01	People Ops

Expected Output

department	total_projected_budget	total_actual_spend	variance	status
Sales	0.00	500.00	500.00	Over Budget
Operations	1800.00	1400.00	-400.00	Under Budget
Engineering	9500.00	9700.00	200.00	Over Budget
Marketing	6200.00	6300.00	100.00	Over Budget
HR	1500.00	1400.00	-100.00	Under Budget

Schema

department_budget_actuals

Column	Type	Description
idPK	INT	Primary key for each budget record
department	VARCHAR(100)	Department name
projected_budget	DECIMAL(12,2)	Projected budget amount for the record
actual_spend	DECIMAL(12,2)	Actual expenditure amount for the record
report_month	DATE	Reporting month for the budget entry
cost_center	VARCHAR(50)	Cost center associated with the record

department_budget_actuals

id	department	projected_budget	actual_spend	report_month	cost_center
1	Engineering	5000.00	5200.00	2024-01-01	Platform
2	Marketing	3000.00	2800.00	2024-01-01	Growth
3	Finance	2000.00	2000.00	2024-01-01	Accounting
4	Engineering	4500.00	4300.00	2024-02-01	Platform
5	Marketing	3200.00	3500.00	2024-02-01	Brand
6	HR	1500.00	1400.00	2024-01-01	People Ops
7	Operations	1000.00	900.00	2024-01-01	Logistics
8	Operations	800.00	500.00	2024-02-01	Facilities
9	Sales		500.00	2024-01-01	Field
10	Finance	0.00	0.00	2024-02-01
11	Support	1200.00	1200.00	2024-01-01
12	Engineering	0.00	200.00	2024-03-01	Infrastructure
1	Finance	4869	-549	2024-02-05	Product Ops
2	Security	2113	1957	2024-03-02	Logistics
3	Marketing	null	1689	2024-01-26	Payroll
4	Administration	4578	1465	2024-02-05	Enterprise
5	Data Science	2983	1695	2024-02-21	Logistics
6	Procurement	-731	-866	2024-01-26	Product Ops
7	Customer Success	-877	437	2024-01-08	SMB
8	Customer Success	1935	4334	2024-01-31	Research Lab
9	Marketing	1746	5033	2024-02-21	Content
10	Quality Assurance	null	4122	2024-02-05	Product Ops
11	HR	4495	1338	2024-01-17	Warehouse
12	Procurement	4818	57	2024-02-23	Product Ops
13	Supply Chain	5029	5320	2024-01-07	Compliance
14	Customer Success	-660	5224	2024-02-15	null
15	Internal Audit	4567	4626	2024-01-26	Logistics

Expected Output

department	total_projected_budget	total_actual_spend	variance	status
Customer Success	-660.00	5224.00	5884.00	Over Budget
Sales	0	500.00	500.00	Over Budget
Operations	1800.00	1400.00	-400.00	Under Budget
Supply Chain	5029.00	5320.00	291.00	Over Budget
Engineering	9500.00	9700.00	200.00	Over Budget
HR	1500.00	1400.00	-100.00	Under Budget
Marketing	6200.00	6300.00	100.00	Over Budget
Internal Audit	4567.00	4626.00	59.00	Over Budget

Problem

Task

FinSight tracks department spending in a single budgeting table. Write a SQL query to report departments where actual spending differs from projected budget.

Requirements

Return each department with its total projected budget, total actual spend, and the variance (actual_spend - projected_budget).
Include only departments where the totals do not match.
Add a status column that shows Over Budget when actual spend is greater than projected budget, otherwise Under Budget.
Order the results by absolute variance descending, then by department name.

Table Definition

department_budget_actuals

Column	Type	Description
id	INT	Primary key
department	VARCHAR(100)	Department name
projected_budget	DECIMAL(12,2)	Planned budget amount for the entry
actual_spend	DECIMAL(12,2)	Actual spend amount for the entry
report_month	DATE	Month of the budget record
cost_center	VARCHAR(50)	Cost center label

Sample Data

id	department	projected_budget	actual_spend	report_month	cost_center
1	Engineering	5000.00	5200.00	2024-01-01	Platform
2	Marketing	3000.00	2800.00	2024-01-01	Growth
3	Finance	2000.00	2000.00	2024-01-01	Accounting
4	Engineering	4500.00	4300.00	2024-02-01	Platform
5	Marketing	3200.00	3500.00	2024-02-01	Brand
6	HR	1500.00	1400.00	2024-01-01	People Ops

Expected Output

department	total_projected_budget	total_actual_spend	variance	status
Sales	0.00	500.00	500.00	Over Budget
Operations	1800.00	1400.00	-400.00	Under Budget
Engineering	9500.00	9700.00	200.00	Over Budget
Marketing	6200.00	6300.00	100.00	Over Budget
HR	1500.00	1400.00	-100.00	Under Budget

Schema

department_budget_actuals

Column	Type	Description
idPK	INT	Primary key for each budget record
department	VARCHAR(100)	Department name
projected_budget	DECIMAL(12,2)	Projected budget amount for the record
actual_spend	DECIMAL(12,2)	Actual expenditure amount for the record
report_month	DATE	Reporting month for the budget entry
cost_center	VARCHAR(50)	Cost center associated with the record

department_budget_actuals

id	department	projected_budget	actual_spend	report_month	cost_center
1	Engineering	5000.00	5200.00	2024-01-01	Platform
2	Marketing	3000.00	2800.00	2024-01-01	Growth
3	Finance	2000.00	2000.00	2024-01-01	Accounting
4	Engineering	4500.00	4300.00	2024-02-01	Platform
5	Marketing	3200.00	3500.00	2024-02-01	Brand
6	HR	1500.00	1400.00	2024-01-01	People Ops
7	Operations	1000.00	900.00	2024-01-01	Logistics
8	Operations	800.00	500.00	2024-02-01	Facilities
9	Sales		500.00	2024-01-01	Field
10	Finance	0.00	0.00	2024-02-01
11	Support	1200.00	1200.00	2024-01-01
12	Engineering	0.00	200.00	2024-03-01	Infrastructure
1	Finance	4869	-549	2024-02-05	Product Ops
2	Security	2113	1957	2024-03-02	Logistics
3	Marketing	null	1689	2024-01-26	Payroll
4	Administration	4578	1465	2024-02-05	Enterprise
5	Data Science	2983	1695	2024-02-21	Logistics
6	Procurement	-731	-866	2024-01-26	Product Ops
7	Customer Success	-877	437	2024-01-08	SMB
8	Customer Success	1935	4334	2024-01-31	Research Lab
9	Marketing	1746	5033	2024-02-21	Content
10	Quality Assurance	null	4122	2024-02-05	Product Ops
11	HR	4495	1338	2024-01-17	Warehouse
12	Procurement	4818	57	2024-02-23	Product Ops
13	Supply Chain	5029	5320	2024-01-07	Compliance
14	Customer Success	-660	5224	2024-02-15	null
15	Internal Audit	4567	4626	2024-01-26	Logistics

Expected Output

department	total_projected_budget	total_actual_spend	variance	status
Customer Success	-660.00	5224.00	5884.00	Over Budget
Sales	0	500.00	500.00	Over Budget
Operations	1800.00	1400.00	-400.00	Under Budget
Supply Chain	5029.00	5320.00	291.00	Over Budget
Engineering	9500.00	9700.00	200.00	Over Budget
HR	1500.00	1400.00	-100.00	Under Budget
Marketing	6200.00	6300.00	100.00	Over Budget
Internal Audit	4567.00	4626.00	59.00	Over Budget

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Monthly Budget Variance Analysis ProcessEasy

Budget vs Actual Variance AnalysisEasy

Flag Discrepant M-Pathways Report TotalsEasy

Next question

Budget vs Actual Variance Report

Easy

SQL & Data Manipulation

Asked at 1 company1AggregationsCase WhenData Wrangling

Also asked at

Problem

Task

FinSight tracks department spending in a single budgeting table. Write a SQL query to report departments where actual spending differs from projected budget.

Requirements

Return each department with its total projected budget, total actual spend, and the variance (actual_spend - projected_budget).
Include only departments where the totals do not match.
Add a status column that shows Over Budget when actual spend is greater than projected budget, otherwise Under Budget.
Order the results by absolute variance descending, then by department name.

Table Definition

department_budget_actuals

Column	Type	Description
id	INT	Primary key
department	VARCHAR(100)	Department name
projected_budget	DECIMAL(12,2)	Planned budget amount for the entry
actual_spend	DECIMAL(12,2)	Actual spend amount for the entry
report_month	DATE	Month of the budget record
cost_center	VARCHAR(50)	Cost center label

Sample Data

id	department	projected_budget	actual_spend	report_month	cost_center
1	Engineering	5000.00	5200.00	2024-01-01	Platform
2	Marketing	3000.00	2800.00	2024-01-01	Growth
3	Finance	2000.00	2000.00	2024-01-01	Accounting
4	Engineering	4500.00	4300.00	2024-02-01	Platform
5	Marketing	3200.00	3500.00	2024-02-01	Brand
6	HR	1500.00	1400.00	2024-01-01	People Ops

Expected Output

department	total_projected_budget	total_actual_spend	variance	status
Sales	0.00	500.00	500.00	Over Budget
Operations	1800.00	1400.00	-400.00	Under Budget
Engineering	9500.00	9700.00	200.00	Over Budget
Marketing	6200.00	6300.00	100.00	Over Budget
HR	1500.00	1400.00	-100.00	Under Budget

Schema

department_budget_actuals

Column	Type	Description
idPK	INT	Primary key for each budget record
department	VARCHAR(100)	Department name
projected_budget	DECIMAL(12,2)	Projected budget amount for the record
actual_spend	DECIMAL(12,2)	Actual expenditure amount for the record
report_month	DATE	Reporting month for the budget entry
cost_center	VARCHAR(50)	Cost center associated with the record

department_budget_actuals

id	department	projected_budget	actual_spend	report_month	cost_center
1	Engineering	5000.00	5200.00	2024-01-01	Platform
2	Marketing	3000.00	2800.00	2024-01-01	Growth
3	Finance	2000.00	2000.00	2024-01-01	Accounting
4	Engineering	4500.00	4300.00	2024-02-01	Platform
5	Marketing	3200.00	3500.00	2024-02-01	Brand
6	HR	1500.00	1400.00	2024-01-01	People Ops
7	Operations	1000.00	900.00	2024-01-01	Logistics
8	Operations	800.00	500.00	2024-02-01	Facilities
9	Sales		500.00	2024-01-01	Field
10	Finance	0.00	0.00	2024-02-01
11	Support	1200.00	1200.00	2024-01-01
12	Engineering	0.00	200.00	2024-03-01	Infrastructure
1	Finance	4869	-549	2024-02-05	Product Ops
2	Security	2113	1957	2024-03-02	Logistics
3	Marketing	null	1689	2024-01-26	Payroll
4	Administration	4578	1465	2024-02-05	Enterprise
5	Data Science	2983	1695	2024-02-21	Logistics
6	Procurement	-731	-866	2024-01-26	Product Ops
7	Customer Success	-877	437	2024-01-08	SMB
8	Customer Success	1935	4334	2024-01-31	Research Lab
9	Marketing	1746	5033	2024-02-21	Content
10	Quality Assurance	null	4122	2024-02-05	Product Ops
11	HR	4495	1338	2024-01-17	Warehouse
12	Procurement	4818	57	2024-02-23	Product Ops
13	Supply Chain	5029	5320	2024-01-07	Compliance
14	Customer Success	-660	5224	2024-02-15	null
15	Internal Audit	4567	4626	2024-01-26	Logistics

Expected Output

department	total_projected_budget	total_actual_spend	variance	status
Customer Success	-660.00	5224.00	5884.00	Over Budget
Sales	0	500.00	500.00	Over Budget
Operations	1800.00	1400.00	-400.00	Under Budget
Supply Chain	5029.00	5320.00	291.00	Over Budget
Engineering	9500.00	9700.00	200.00	Over Budget
HR	1500.00	1400.00	-100.00	Under Budget
Marketing	6200.00	6300.00	100.00	Over Budget
Internal Audit	4567.00	4626.00	59.00	Over Budget

Problem

Task

FinSight tracks department spending in a single budgeting table. Write a SQL query to report departments where actual spending differs from projected budget.

Requirements

Return each department with its total projected budget, total actual spend, and the variance (actual_spend - projected_budget).
Include only departments where the totals do not match.
Add a status column that shows Over Budget when actual spend is greater than projected budget, otherwise Under Budget.
Order the results by absolute variance descending, then by department name.

Table Definition

department_budget_actuals

Column	Type	Description
id	INT	Primary key
department	VARCHAR(100)	Department name
projected_budget	DECIMAL(12,2)	Planned budget amount for the entry
actual_spend	DECIMAL(12,2)	Actual spend amount for the entry
report_month	DATE	Month of the budget record
cost_center	VARCHAR(50)	Cost center label

Sample Data

id	department	projected_budget	actual_spend	report_month	cost_center
1	Engineering	5000.00	5200.00	2024-01-01	Platform
2	Marketing	3000.00	2800.00	2024-01-01	Growth
3	Finance	2000.00	2000.00	2024-01-01	Accounting
4	Engineering	4500.00	4300.00	2024-02-01	Platform
5	Marketing	3200.00	3500.00	2024-02-01	Brand
6	HR	1500.00	1400.00	2024-01-01	People Ops

Expected Output

department	total_projected_budget	total_actual_spend	variance	status
Sales	0.00	500.00	500.00	Over Budget
Operations	1800.00	1400.00	-400.00	Under Budget
Engineering	9500.00	9700.00	200.00	Over Budget
Marketing	6200.00	6300.00	100.00	Over Budget
HR	1500.00	1400.00	-100.00	Under Budget

Schema

department_budget_actuals

Column	Type	Description
idPK	INT	Primary key for each budget record
department	VARCHAR(100)	Department name
projected_budget	DECIMAL(12,2)	Projected budget amount for the record
actual_spend	DECIMAL(12,2)	Actual expenditure amount for the record
report_month	DATE	Reporting month for the budget entry
cost_center	VARCHAR(50)	Cost center associated with the record

department_budget_actuals

id	department	projected_budget	actual_spend	report_month	cost_center
1	Engineering	5000.00	5200.00	2024-01-01	Platform
2	Marketing	3000.00	2800.00	2024-01-01	Growth
3	Finance	2000.00	2000.00	2024-01-01	Accounting
4	Engineering	4500.00	4300.00	2024-02-01	Platform
5	Marketing	3200.00	3500.00	2024-02-01	Brand
6	HR	1500.00	1400.00	2024-01-01	People Ops
7	Operations	1000.00	900.00	2024-01-01	Logistics
8	Operations	800.00	500.00	2024-02-01	Facilities
9	Sales		500.00	2024-01-01	Field
10	Finance	0.00	0.00	2024-02-01
11	Support	1200.00	1200.00	2024-01-01
12	Engineering	0.00	200.00	2024-03-01	Infrastructure
1	Finance	4869	-549	2024-02-05	Product Ops
2	Security	2113	1957	2024-03-02	Logistics
3	Marketing	null	1689	2024-01-26	Payroll
4	Administration	4578	1465	2024-02-05	Enterprise
5	Data Science	2983	1695	2024-02-21	Logistics
6	Procurement	-731	-866	2024-01-26	Product Ops
7	Customer Success	-877	437	2024-01-08	SMB
8	Customer Success	1935	4334	2024-01-31	Research Lab
9	Marketing	1746	5033	2024-02-21	Content
10	Quality Assurance	null	4122	2024-02-05	Product Ops
11	HR	4495	1338	2024-01-17	Warehouse
12	Procurement	4818	57	2024-02-23	Product Ops
13	Supply Chain	5029	5320	2024-01-07	Compliance
14	Customer Success	-660	5224	2024-02-15	null
15	Internal Audit	4567	4626	2024-01-26	Logistics

Expected Output

department	total_projected_budget	total_actual_spend	variance	status
Customer Success	-660.00	5224.00	5884.00	Over Budget
Sales	0	500.00	500.00	Over Budget
Operations	1800.00	1400.00	-400.00	Under Budget
Supply Chain	5029.00	5320.00	291.00	Over Budget
Engineering	9500.00	9700.00	200.00	Over Budget
HR	1500.00	1400.00	-100.00	Under Budget
Marketing	6200.00	6300.00	100.00	Over Budget
Internal Audit	4567.00	4626.00	59.00	Over Budget

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Monthly Budget Variance Analysis ProcessEasy

Budget vs Actual Variance AnalysisEasy

Flag Discrepant M-Pathways Report TotalsEasy

Next question