Business Intelligence Blog from arcplan

Planning Visualizations: Forecasting Charts


My series on planning visualizations has so far explained how to use the right chart types to tell your plan's story. Today I'll wrap up with a focus on using visualizations to show how your plan will hold up as the fiscal year progresses. The charts below display forecasts that are based off of 12-24 months of historical data used to predict future results.

Plan vs. Actual Spend: Bullet Visualization with Linear Regression
Good planners know that a plan shouldn't simply be created, approved and then left to rot on a shelf somewhere. Plans should be managed and updated throughout the year. Even the best plans require changes, especially when it looks like you might be getting off track.

Say you're a marketing director for a retailer and your fiscal year begins in January. In March, you're starting to wonder if there will be enough funds in the budget by September to do your holiday season advertising. There's a way to predict this information, even if you're only a few months into the fiscal year. A finance professional might run a linear regression and stick a table in Excel to show the progressions of the budget over time. But as a marketer, you're a visual person and might better understand a bullet chart. Plotting the actual data (in yellow) against the plan data (in gray), it's easy to see that marketing expenses were understated in the plan from the beginning, or you simply overspent early on and won't recover without making adjustments to spending. The red bars show just how far off spending will be vs. the plan if you don't take corrective action:


This chart was created in arcplan, whose purpose as a BI and planning software vendor is to shield people from the math behind their visualizations. But if you're interested, I'll break down how we created this chart (also check out our video about regression analysis). It takes a combination of linear projection and cumulative regression using 12 months of past data to predict spending habits based on previous spending habits. The first step is to perform a month to month cumulative calculation on the plan data, then overlay the actual cumulative performance. Since the actual data will only take us through part of the year, the next step is to apply a simple regression model to project the outcome for the remaining periods.

The red bars below the bullet chart reveal how much the actual spend will stray from the budget if the marketing team does not curtail expenses. This example shows us that spending was over budget right away and will get progressively worse throughout the year. As a whole, this visualization clearly answers the question of whether or not the budget will suffice for the year.

Revenue Scenario Comparison: Line Graph with Seasonal Regression
Organizations with sophisticated planning usually do some form of forecasting, or predicting future scenarios for revenue, gross margin, and net income based off data from prior years. Let's take a look at revenue modeling using seasonal regression. The example above used straight line or linear regression (change in y over change in x...very close to slope analysis), but the example below uses seasonal regression. It's more of a predictive tool that shows where something will be in a particular period based on certain factors – in this case, where revenue will be based on current data plus 24 months of historical data. You need that much history to get proper upper and lower bounds.

Why are we interested in predicting things like how much revenue will increase next year or how much we should increase our department's plan next year? What I tell my customers is this: tribal knowledge or gut instinct assumes that your budget should be increased by, say, 5% in relation to the previous year, but why not see if that should actually be the case? We can employ regression analysis to show you a more accurate number that's based on real company data. And then by visualizing it in charts and graphs, we can make it easy to understand for everyone.

In the example below, the grey line displays data from 2 years prior, the yellow line represents data from the previous year, and the green dotted line visualizes the predicted values based on the seasonal regression model.


I know I got into the mechanics of calculating these forecasts, but I want to stress that using charts and graphs to display your forecast data is all about making the information easier to understand and more digestible than tables of numbers. Some finance professionals like tables of numbers and that's fine, but your department heads may respond better to planning dashboards that include some of the visualizations I've shown in this blog article series. For certain planning managers, visualizations will help them better understand their budgets and plans and therefore make better business decisions.

Check out more information on planning visualizations:

Dwight deVera

About Dwight deVera

I'm Senior VP responsible for Solutions Delivery at arcplan in North America. I also present on a lot of arcplan webinars, so you can sign up to hear me - the events listing on our website is located here: You can also follow me on Twitter: @dwightdevera.
Comments (0) Trackbacks (1)

Leave a comment