This is the second in a series of four articles, on applications of advanced analytics to improve business performance. As business is awash in Excel, it is often at a lost to think beyond the limitations of that tool to know what analytics are available and how it can be applied to optimize operations.
The information in the article and more will be covered at JPK Group’s 2019 Analytics Academy. Click here for more information.
What Don’t I Know?
Managers continuously ask their analysts, “what don’t I know that I need to know?”, as they attempt to instigate thinking to find the “land minds” in the road ahead that can damage their business. However, when the only tool in the analytic toolbox is Excel, little more is done than YoY variance reports.
Visualization tools have added help in trending, but these tools are not predictive, which limits the value. Further, visualization requires, well, visual inspection. Imagine having 1000 SKUs, would you really inspect the trend of all 1000?
But, you say, dashboards will monitor the trends we need to track, and you would be right. Alas, dashboards still are only snapshots of the past, and while good eye-candy, lead too often to false positive and negative conclusions.
Since all decisions are about the future, BI, visualization, and spreadsheet tools can only do so much because they’re presenting the past. As such, we need to build the analytic toolbox with desktop statistical and analytics tools to enable a view to the future that can help find “what we don’t know”.
There is a universe of analytics, but a relative few that have systematic and transportable use through all businesses and areas of business. One such is the correlation. This simple and powerful statistic can give insights into the drivers of business performance.
While a readily available calculation, its use in multi-dimensional analysis is limited because it’s hard to deploy in spreadsheets, BI, and visualization/dashboard tools. However, desktop statistical and enterprise analytics tools are equipped to provide this valuable analysis.
Correlations
Correlations are “referential”; i.e. a reference of one variable to another. For example, as unemployment goes down, a correlation with a store’s sales shows it goes up. We say sales are negatively correlated with unemployment; i.e. they go in opposite directions.
The figure below is a simple representation of a correlation chart. The X axis is, say, store sales, and the Y axis unemployment. The chart on the right-hand side, expressed as a series of dots, has a regression line through the dots that represents the correlation. The tilt of the line is the direction of correlation, and the greater the tilt and the tighter the dots around the line the more that sales and unemployment are correlated. The chart on the left-hand side is a positive correlation; i.e. where sales and, say, promotions move in the same direction.
Correlations are important because it can uncover drivers of the business, but correlations are not necessarily causal and need investigation to confirm causality.
Another use of correlations is to find indicators that lead demand. Let’s again use sales and unemployment. As above, there is a relation in the same time period; e.g. when unemployment drops this month so do sales rise. However, from a business perspective, we can’t react in time to take advantage of this knowledge.
Suppose though, unemployment was a “leading” indicator to sales; i.e. if unemployment declined this month it would predict sale increasing four months hence. With this runway, we could adjust inventory and pricing to better take advantage of the opportunity. This is a more powerful use of correlations.
Application of Lead Indicators
The figure that follows, incorporates correlations to find lead indicators of sales, forecast sales, and determine if the company’s sales forecast is itself reasonable. This is a three-mints-in-one value proposition.
The green bars to the left of the dotted vertical black line are the actual sales in the Business Solutions business unit, and to the right are the forecasted sales from the company’s CRM. The blue line is an unbiased statistical forecast that incorporates unemployment as a four-month lead indicator in its calculation, and the red lines are two standard deviations about the forecast line.
The chart reveals that unemployment is indeed a four-month negative correlated leading indicator to sales (note the Correlation Coefficient is -0.835, meaning a “good” and negative correlation). This is a great find as it offers reaction time.
Next, the four-month CRM forecast shows the four bars to the right of the dotted vertical black line are at the bottom of the lower red line, with the November 2017 bar under the red line. This means that the CRM forecast for November 2017 is statistically “unreasonable” (i.e. beyond two standard deviations). This too is an important find, as unless the business manager has specific knowledge of future events, his forecast fails the statistical test.

Application of Predictive Analytics
Business is good, but will the trend continue? But the trend in the West region is bad, so I must fix it. However, do I have the knowledge I need to allocate my time accordingly? Without predictive analytics, the answer is I don’t really know.
While analytical predictions are not perfect, they guide us in the more appropriate direction or give us pause to think more deeply, which itself produces better outcomes.
Let’s imaging a report on the figure below that has YTD variance of sales across several retailer’s city based stores and the Monitor 19 product. If there is nothing but the YoY analysis, the decision would be to “fix” the bad trends (red YoY variance) and ignore the good trends (as nothing is “broken”). However, if a calculation could predict the future trend, then different decisions could be made that result in better business performance.
The 12 Month Predicted Trend Direction utilizes something akin to a first derivative to measure the velocity of sales, which is extended to predict the future trend of sales.
Where YoY sales are down, say Inacom Phoenix Monitor 19, but with an upward green arrow, it is a bad trend predicted to get better, which we might not spend time to fix something already on the mend.
Conversely, Fry’s Phoenix Monitor 19, is a double-digit good trend with the predicted arrow upward, meaning a good trend predicted to get better. Here the product manager might notify his retailer, Fry’s, that the store has pricing power to increase price.
Another instance of this prediction is to find good trends that are predicted to go bad. Here the ROI is 2X more than finding the bad trends that can get worse. In other words, it is more valuable to close the barn door before the horse leaves then finding the hose after he leaves.
There are dozens of additional applications of unbiased forecasting and analytics including those that incorporate AI and ML, all of which can be a boon to business. To get better performance though, you’ll need to expand beyond Excel, BI, and visualization tools to enterprise analytics, but the return is large.
If you are interested in this topic, then join us at the Analytics Academy in San Diego from September 18-19th 2019.
Click here for more information and to register!
Authors
This article is a collaboration between Robert J Zwerling and Jesper H Sorensen from the organization Finance Analytics Institute (www.fainstitute.com) and is an excerpt from their book, Implementing an Analytics Culture for Data Driven Decisions – A Manifesto for Next Generation Finance. Robert and Jesper are the content creators behind the Analytics Academy and will teaching at the Academy in September!
Copyright 2019 Finance Analytics Institute, Robert J Zwerling & Jesper H Sorensen. All rights reserved. No part of this document may be reproduced without this copyright notice.