You are given raw expense transactions and a quarterly forecast table. Your task is to structure the expense data into a quarter-level report that a VP can review quickly. The output should compare posted actual spend against forecasted spend by quarter and department, and include the variance between them.
expense_id
INT
Primary key for each expense row
expense_date
DATE
Date the expense was incurred
department
VARCHAR(50)
Cost center or department name
amount
DECIMAL(12,2)
Expense amount; may be negative for credits
status
VARCHAR(20)
Posting status such as posted, pending, or void
forecast_quarter
DATE
Quarter start date used as the forecast key
department
VARCHAR(50)
Department name
forecast_amount
DECIMAL(12,2)
Planned quarterly spend
| 1 | 2024-01-05 | Marketing | 1200 | posted |
| 4 | 2024-04-02 | Sales | 900 | pending |
| 7 | 2024-06-30 | Finance | -50 | posted |
| 10 | 2024-08-01 | Engineering | 2000 | posted |
| 2024-01-01 | Marketing | 2500 |
| 2024-04-01 | Finance | 500 |
| 2024-07-01 | Engineering | 2200 |
| 2024-10-01 | Legal | 300 |