Predictive Analytics – Budgeting
About 1 out of every 5 customers we talk with nowadays is interested in using their data to predict the future and from our perspective, 5 out of 5 customers we talk to have the opportunity to do so.
So what is Predictive Analytics ? It is simply visualizing the future based on the rich data that you already own.
‘Numbers have an important story to tell and it’s up to you to give them a voice’ – Stephen Few
For CEOs out there I’d interpret this as ‘Your data have an important story to tell and it’s up to you to employ the best tools and teams to give them a voice’
But how do we give our data a voice into the future? How about we start with some simple business examples…..
Inventory Requirements: Inventory Centric companies experience a big impact from optimal inventory holdings and predicting inventory requirements is a significant success driver. Quantity on Hand, Purchase Orders, Sales Orders, Lead Times and Expected Sales are all predictors of inventory requirements.
Cash Flow: Cash movements can be predicted through Budgeting business activity like Sales, Expenses and Capex. More acutely, Payroll, Sales Orders, Purchase Orders, Invoices and Payment Terms are fast moving predictors of Cash Flow.
Budgeting: This post is related to Budgeting so I will draw most of your attention to this topic. By way of intro, budgeting for this purpose is financial planning at an aggregated level, usually for the next 12 months. So how do we predict our future expenses for say ‘Factory Maintenance’? One way is to expect it to be the same as last year and another is that it is a function of projected factory capacity or of maintenance cycles, or all of the above.
We’ll discuss 2 broad ways of predicting: Common Sense Data Modelling and Statistical Smarts. Machine Learning is another method for predicting the future based on historical data but we will cover this in another post.
Common Sense Data Modelling
Below we forecasted year end actual amounts by using DAX Measures to compare current YTD amounts with prior YTD amounts. Once this projected year end amount is found, previous year trends are applied to the remaining amounts for the year.
Pros: -Slicing can be made to be extremely effective and allow for back tracking and observations.
-Intuitive and simple implementation grants easy understanding of data.
Cons:-Less advanced and accurate than more rigorous statistical methods.
-Data can appear skewed when outlining actuals come through.
Below is an interactive example of this model, which we have aptly named Energized Forecast
Below we have a report which utilizes Power BI’s custom visual Time Series Decomposition Visual. The visual employs rigorous statistical methods to find seasonal repeating trends and overall data trends. Once it finds those movements it decomposes the data into each of the 4 representation lines:
- Actual Data Line
- Seasonal Representation which is a the recurring movement of the best fit for the seasonality revealed by the data
- Overall Trend which shows the directional trend of the data
- Randomness which shows the deviations from the Seasonal Representation of the data
Interestingly, if one were to aggregate #2,3 and 4 above then one would note that they all add up to #1.
Pros: -Much more accuracy and reliability.
-Decomposition leads to more understanding and revelation in our data.
Cons:-More complex methods makes it difficult to follow behind-the-scenes
-Utilizing more advanced visuals leaves less room for customization and
Below is a list of the salient DAX measures we used in the ‘Common Sense Data Modelling’ approach above….
Actuals Prior Year End =
PY YTD % Spent = DIVIDE([Actuals Prior Year to Date (Using period table Max)], [Actuals Prior Year End],0)