A deep dive into Actuals, Budgets & Forecasts
Today, the market is flooded with numerous BI or Data Visualization tools (viz. Tableau, Power BI, QuickSight, Looker, Google Data Studio, QlikSense, etc and not to forget Excel as well). Businesses have to deal with the decision of choosing the right tool based on their need by looking at the costs, maintenance, ease of use, development efforts, capabilities & functionalities, etc. Talking about Development, various learning platforms offer courses around how to use the tool and ways of building complex charts/graphs. Some of the above mentioned products also have custom visualizations capabilities and an extensive marketplace with pre-built additional visuals. But, creating Reports/Dashboards that are self-explanatory and shout insights at the first glance, is the need of the hour!
When it comes to financial modeling, comparing and analyzing your budget vs actuals is extremely important. You might know how much revenue you’ll generate, but things don’t always go as planned. Hence, it is important to compare & understand Actuals with the initially Budgeted & Forecasted sales. Having said that, revenue/sales is not the only business KPI that can be analyzed against Budget/Forecast. It can be anything from Number of Units sold to Customer Acquisition Count, Marketing Leads brought in to Funds Raised, etc.
Generally these are the high level steps of using the data to drive business decisions:
- Planning & finalizing the Budget.
- Forecasting based on past available data.
- Recording & Storing Actuals as they happen.
- Visualizing the deviations from initial plan/expectation.
- Analyzing or deriving insights from the above pieces of information.
- Course correcting or deciding next steps.
In this article, we will be deep-diving into point 4 and laying out the foundation of step 5 from the above. Let’s first understand what the 3 variables mean:
- Budget : What the business expects/wants to achieve; this can be treated as the “targets”. This is generally done by the senior management based on the direction business wants to lead in.
- Forecast : What could be possibly achieved if the business continues to perform like in the past; based on data. Generally ML models are used to predict the future using historical time-series information.
- Actuals : What we really achieved; current situation recorded over time.
In a common scenario, analysts receive data aggregated at a Day level, showing Actuals, Budget & Forecast values. The simplest way to visualize the same is to plot a Line Chart, with each line denoting the 3 variables (actuals in the future will be blank, because budget & forecast are predefined/calculated).
It does give the information as to on which day we achieved/missed the Budget & Forecast. On a closer look, one can even figure out the deviation. The problem with such visualizations is that they don’t speak for themselves!
Following are a few tested & widely used ways to visualize Actuals, Budgets & Forecasts data on Tableau.
Bullet Chart : With Bullets showing Budget and Gray bars showing Forecast, the Size & Colour of the thinner bars depict how much did we actually achieve or miss in terms of the planned numbers. Size can be compared with the Budget Bullets & Color with Forecast.
Variance Water-Fall : Missing out targets in some months, can be considered okay if for the entire year together business was able to remain in line of the Budget & Forecast. In such scenarios, it is helpful to track & cumulate the variations over months to get a sense of direction we are at.
Metric Table : One among the simplest, this gives a numerical sense of deviations from plan. It is of the utmost importance to orgs within a business that track their primary KPIs on a weekly or more frequently basis (eg. Number of successful deliveries made in a day).
Scatter Plot : Variation of Budget & Forecast on each axis and size of the bubble being Actuals. Instead of Months, if this is prepared for each department in a company, it helps in identifying focus areas quickly. It says, the big bubble farthest from origin (0,0) in the chart is problematic!
Old-Fashioned Line Chart : This is always helpful in bringing out Budget/Forecast variations across months. In case, there is a need for comparison between departments/products within a business, it can be modified to a bar or column chart as well.
Variance Call-Outs : Budget or Forecast deviations shown as Colour & Callouts respectively; a combination of 2 of the above charts. Depending on the use case, budget & forecast can be swapped. One quick glance at the Colors & Arrows are enough to gauge the business health.
Note : Deviations/Variance is always calculated as Actuals - Budget OR Actuals - Forecast.
Till now we have only judged Actuals against Budget and/or Forecast. Further to the above analysis, at times there is a need of comparing Forecast with Budget, to determine:
- If we are Budgeting/Targeting unrealistic goals.
- If our historical performance is close to the Targets we have set for ourselves.
- If we are using the right model to predict Forecast values.
Budgets are generally decided for a full financial year, irrespective of the industry, whereas, it is a good practice to adjust Forecasts on a Quarterly/Monthly basis (depending on the need & nature of business).
