In this blog post, we will outline:
1. The reasoning for creating a predictive sales model (PSM) at a global diversified chemical company such as Ferro
2. Our approach to selecting the right data and building out a regression model to forecast sales.
The Business Case
The volume and velocity of data has grown exponentially in the past decade. Economic indicator data that used to be unreliable and hard to find is now ubiquitous and commoditized. A range of providers offer data feeds and forecasts of the world’s economic indicators, varying in frequency, depth and breadth of data – financial firms such as Bloomberg, Factset or Thomson Reuters inform the desks of traders from New York to Hong Kong, and specialized players such as IHS Global Insight or Oxford Economics help tie strategy to economic reality for clients ranging from hedge fund managers to CFOs. Other providers such as the IMF, World Bank, and CEIC publish their data and forecasts for free online.
The challenge of implementing data-driven decision making in an enterprise arises due to the fact that there is too much data with too little structure, hindering the ability of managers to identify what data is relevant and how to build a process around data analysis that will sustainably generate value.
Ferro Corp was experiencing such a challenge. As a manufacturer of the glass, ceramics and other performance materials that supply the automobile, construction and appliances industries, Ferro had strong reason to believe that sales fluctuated with the end-use markets of its customers. Ie. If automobile demand in Brazil were to go up, Ferro’s sales would increase with a certain lag factor depending on the length of time it takes the automobile manufacturers to demand more inputs. If this hypothesis were true, a correct predictive forecast could improve supply chain planning in the near term (1-2 years) and capital investment planning in the longer-term (3-5 years).
By applying insights of the PSM in the quarterly budgeting and planning cycle, Ferro was able to reduce the total cost of production and meet market demand by aligning its strategy to economic trends.
Furthermore, Capgemini’s familiarity with Ferro’s ERP systems, specifically the SAP Business Points Consolidation (BPC) environment, has enabled Ferro to roll out the PSM as a dashboard tool that will become an integral part of the budget cycle. Through this integration, the model has been a platform for additional analytics projects, such as Monte Carlo Simulation in line with “recession”, “realistic” and base data. This helped to adjust our forecast numbers to try and get various scenarios of the PSM, based on varying macroeconomic conditions from current events.
Creating and automating an external-data driven sales forecast doesn’t happen overnight.
Our process followed the steps of an economic hypothesis test, setting up an experiment with dependent (sales) and independent (economic indicator) variables and testing if there was correlation between the time series across 15 quarters of historical data (Q1 2010 – Q3 2013). If the movements of economic indicators was correlated with Ferro’s sales performance historically, we would calculate the optimal linear regression to document the historical relationship using least squares regression:
Setting up this economic thought experiment correctly took some steps and verification checks along the way.
First, a Capgemini team interviewed Ferro stakeholders to document the end-use markets that managers believed drove sales in each product line and region. 16 product lines, ranging from container glass to performance coatings for electronics, were selected and their historical sales were pulled from SAP for each of Ferro’s four regions – North America, Latin American, EMEA and Asia Pacific.
Using the product map developed from the stakeholder interviews, we researched the most relevant economic indicator data for the end-use markets identified by Ferro. We issued a short RFP to five data providers and let companies propose which indicators best fit our client’s business requirements. In the end, we advised Ferro to sign a contract with a provider based out of the UK which provided reliable industrial output and national accounts indicator data across 20 major countries on a quarterly basis and also generated 5-year rolling forecasts.
Our next step was to confirm if our economic hypothesis held true using multivariable linear regression. And not surprisingly, the micro and macro level data correlated.
Controlling for multicollinearity and variance inflation factors, we found strong correlation (R-Sq 80+%) between historical sales in product lines with low to moderate volatility (mean / st. dev > 10) with high accuracy using just one or two economic indicators (IPI, GDP, Disposable Income, Auto Demand, etc.). Smaller product lines (<5 MM in quarterly sales) or those facing competitive pressure were more problematic to predict, as their sales generally did not correlate with economics. In these cases, we assigned a historical average growth rate and verified the approach with the business to confirm assumptions. The resulting model applied forecasts of economic indicators from the data-provider to our formulae to predict future sales using coefficients derived through regression analysis.
For the final step, Capgemini integrated the PSM with Ferro’s existing SAP system, building the forecasts into visual elements that could be compared side by side in capital planning, production planning and financial consolidation modules in SAP.
Ferro Predictive Sales Model (PSM) – Quarterly Sales Prediction vs. Actual
Using only economic data and a list of regression formulae, Ferro’s PSM predicted
each of the 15 historical quarters and 3 future quarters to within +/- 5%.
The Impact of Data Analytics
With the increasing integration of the world economy, businesses are increasingly impacted by external economic factors which can be forecasted. Economic events and trends have historical precedents, and their effects on the economic output of different industries can be accurately forecasted. Therefore, firms have the opportunity to predict how different macroeconomic scenarios will impact their sales.
By conducting a predictive sales forecasting exercise to identify historical correlation between sales and economic indices, firms such as Ferro can apply historical patterns to forecast future results.
Data analytics will never replace the core insights on future demand of a ground-level sales manager. But, it can enable the elimination of bias, errors, and delays, to allow management to operate more efficiently and effectively.
It’s a great pleasure to be invited to speak at our first IBF conference, Predictive Business Analytics Forecasting & Planning Conference taking place in Atlanta April 22-24, 2015. We were impressed by the solid list of industry experts and analytics initiative owners in the conference biographies, and we look forward to driving new thought and discussion on our work developing a macroeconomic sales forecast model at Ferro Corp. We look forward to meeting you at the conference.
David Henison, Head of Corporate FP&A
Ferro Corporation (NYSE: FOE)
Gleb Drobkov, Senior Consultant, Business Information Management