DFX Solutions Inc: Budget vs Variance Analysis
Money, a financial resource, is used to meet ends; such a vital resource can be scarce, and it proves challenging because our needs and wants never end! Individuals, families, companies, and governments often need more financial support.
Managing money can be tricky because when there is no plan for it, there is a tendency to waste it. Luckily, there is a tool/document to save us from confusion and financial recklessness. It is called a budget.
A budget is a detailed statement of items and proposed expenditures against expected income/revenue. The significant advantage of having a budget is tracking when you spend more or less, known as budget vs variance analysis.
A budget is a detailed statement of items and proposed expenditures against expected income/revenue.
Business Use Case
My partner company, DFX Solutions, contacted me to assist them with the analysis. The Finance department gave the necessary files, which captured the estimated budget and amount spent for 2023 for various departments and other line items.
I explored the data to understand how the estimated budget varied with the actual amount spent.
My Approach
I studied the data provided. It had the following columns
- Date — this column represents the specific month and year when the budget and actual spending figures apply
- Budget — This column shows the planned or expected amount of money allocated for a specific subcategory within a department for that particular month
- Actual spending — Reflects the actual amount spent in the specific subcategory within a department for that month
- Department — this column represents the major functional areas within the organisation
- Subcategory — This column provides a further breakdown within each department, specifying the particular activities or areas of focus
After thoroughly reviewing the data, I found no errors or anomalies. For the analysis, I used Power BI to design a dashboard that allows users to filter and obtain answers to further questions.
I identified some problem statements which would be of interest to management.
- What is the trend of budget vs actual amount spent in the fiscal year?
- How did the values of the estimated amount and the actual amount spent compare by department?
- How did the estimated amount and actual amount spent vary by subcategory?
- How is the variance distributed among the departments?
- What are the top five categories by variance?
DFX Solutions estimated the budget to be about $7M, while the actual amount spent for the fiscal year was almost $8.5M; this represents an increase of 21%, about $1.4M.
The line chart above shows a comparison of the estimated budget and actual spending during the months of the fiscal year 2023. Notably, the amount spent in July was at an all-time $871,721; it had a 52-week low of $488,686 in June. For the majority of the year, the company exceeded their estimated budget except in January and June.
There are five departments in the company. The Research & Development spent way more ($1,959,150) than estimated ($1,313,756) in comparison to other departments, representing a significant increase of 49%, while the IT services department spent the least ($1,636,840) compared to what amount estimated ($1,572,141) a very slight increase of 4%.
The increase was quite significant for other departments: General & Administration (15%) and Customer Support (33%), while Sales & Marketing had a slight increase (7%) similar to IT services.
The chart above shows significant overspending in Technical Support ($168,825 over budget) and substantial excesses in other subcategories. These variances highlight the need for more accurate forecasting and tighter budget management across most departments.
Variance measures how much the estimated budget deviates from the actual budget. The formula is
Variance = Actual amount spent — estimated amount
- A positive variance indicates overspending (actual spending exceeds the budget).
- An unfavourable variance indicates under-spending (actual spending is less than the budget)
This report has affirmed that all the departments exceeded their estimated budget. The total variance was $1,444,374. The doughnut chart above shows the breakdown among the departments.
The table shows the top five subcategories in terms of variance. The top three subcategories are units under the R&D department. The previous section affirms it as the department with the highest variance ($654,394), backed by the breakdown of the variance among the three units.
You can unlock more insights by applying the filter on the dashboard
Conclusion
DFX Solutions spent way more than they estimated, an overall 21% increase for the fiscal year. There is consistent overspending; there may be a need to allocate more resources budgeted. Management should consider revising future budgets to reflect actual needs.
It is crucial to remember that overspending can yield a positive or negative result, depending on the result. If the company provides more data, it will be possible to understand better the impacts of overspending on the organisation.
Some visuals have been updated on the dashboard after originally publishing this report