Forecast Effectively and Efficiently for Informed Decision-Making

In other words, design your forecasting system with the end in mind. This is because forecasting the various domains of a large growing business’s value chain brings new challenges to the data analyst. Let’s look at a few of them and then talk about how to manage the challenges like a pro!

It’s convenient to think of the value chain as running horizontally across the domains of Sales, Purchasing, Production, etc., whereas the master data structure often reflects the vertical structure of the organizations inside the company. This vertical data structure may originate from legacy reasons, accounting requirements, ease of data entry, etcetera. Consequently, the structure does not necessarily follow the horizontal flow from sales to delivery, especially as a successful business frequently reinvents the value chain to best serve its customers. The horizontal and vertical structures must be kept in mind as we design the reports and ultimately – the forecasting system.

Organizations use dashboards and interactive reports to visualize value chain performance. Therefore, to let managers efficiently filter on actionable insights, even the most skilled data analyst needs access to people in the organization that can bring domain knowledge and understand each domain’s interaction with the value chain. Otherwise, the dashboard cannot reflect the reality in the value chain to a sufficiently detailed level. So, what is a sufficiently detailed level? Most likely, this is the level where it’s possible to extract actionable insights from the data. Ensure that you have domain knowledge in your team or have access to people that knows the business internals of the company. A solid dashboard or interactive business report is the first step in your journey toward forecasting some periods into the future. This will be greatly appreciated, not least during the annual budgeting, but also in understanding the short-term need for production resources based on a forecast of demand.

“Ensure that you have domain knowledge in your team or have access to people that knows the business internals of the company.”

As a business grows the data volumes increase and contain a larger variety. Customers, factories, and suppliers generate data in all parts of the value chain. Data continually arrive at a higher velocity and are stored in different data sets – sometimes disconnected from each other. While these are challenges of a more practical nature, here follows some future-looking design requirements that the data analyst should keep in mind when outlining the design of a forecasting system.

Design Requirements

How to efficiently capture and process the data volumes to capitalize on hidden insights?

This can be divided into the following steps.

1. First and foremost, agree with the stakeholders on the scope, time, and resources. In short, clarify expectations to be met by each iterative build of the solution as the work progresses over time.
2. Start on a small scale with a flexible approach. Often a set of well-thought-out CSV files can form a database where we try out ideas and design a first prototype. This demo report or dashboard is great to have when you want feedback from the users. We keep the database design flexible as we iterate the prototype a few times. We should at this stage expect frequent changes in the design to serve our needs in the best way possible.
3. Finally, time to use real business data to identify actionable insights. Proceeding with the CSV files, we download a few years of data from the relevant business domains involved in the value chain. This gives us input also on what to expect from a data quality point of view as this can vary greatly between the domains in business. You may for example see that Production data is more stable than Sales data or vice versa. Some data fields are based on free text input, text formats may vary, or may be missing altogether. This is where the Extract, Transform, and Load (ETL) tool enters the scene. The ETL job is preferably managed using custom-coded software that covers all possible scenarios and ensures an effective data load of your data to the database. As we model the value chain, the data must be extracted from various sources, each representing a company domain. Once the data is accepted and safe in the database, we also know that it is good to use in our dashboard and future analysis. The ETL is coded in parallel with the data loading in an iterative fashion. It also serves as a detailed explanation of the data transformation process. Remember, a smart data transformation before data load may save query time on frequently used report queries. Before proceeding to the next step, validate that the ETL is stable over several recurring data loads, weekly, monthly, or as agreed. Further, confirm with the users that the insights from the data are actionable and realistically can meet the needs of the organization. Confirm also that the master data of the organization holds the required quality for your needs, or else the future steps may not deliver as expected.

How can we take it one step further and forecast the drivers of various value chain KPIs?

A forecast is using historical data to make predictions of the future. Depending on the method used, the variability of the historical data could also be used to give a confidence level on the predicted numbers. Using historical data implies that the past will repeat itself in the future, at least in some predictable ways. For example, traveling increases around the holiday season. This is not always the case, which leads us to the benefits of using an expert panel to add a final touch to the computer-generated forecast. So, with this in mind, let us see how we can forecast the drivers of KPIs.

Companies with large market shares are likely to monitor the share frequently as a large share compared with the competition will provide Economies of Scale as well as being a main driver of Revenue.

Now, assume that Research and Development have developed a new patented consumer technology that Marketing through market research and other market data sets has forecast to increase market share by ten percent in this segment over the coming two years. We could now forecast various KPIs:

We have revenue as:

Total market x Market share x Price

From the revenue side we subtract the outlays like so:

Number of Workers x Wage rate + Units of Capital x Price + Units of Material x Price

As Revenue less Outlays gives us the contribution towards fixed cost we may track and forecast the contribution from this product line as a KPI.

Using the above framework, we make several scenarios by changing the variables. For example, Production may need to invest in more capital equipment to meet demand but plans to use the same workforce. This in turn would postpone the increase in contribution by one year and only in the second year the company foresees an increase of 20 percent in contribution. Both Finance and Product Management are monitoring this KPI, so we can understand from this that being able to accurately forecast the drivers of KPIs may impact other areas in the company as well.

Computer-generated forecasts and market modeling have played a role in the above case but more important is the scenario-building exercise from various parts of the organization to arrive at the final business plan.

How to add intelligence to offload users from information overload?

The answer lies in automating the data processing and continuously implementing enablers for automation. The enablers would typically manage data set extractions, transformations of that data, and loading data sets to databases that permit scalability. Remember, the data structure in place may look in a certain way for legacy or accounting reasons but may not necessarily be optimized for our needs.

The benefits of automation are not only on the cost side, but you will also forecast your business based on more accurate data sets. But let us start looking at the cost of having admin staff process data using spreadsheets. These are the major steps admin staff would conduct using spreadsheet software monthly.

• Extracting data. This means knowing where the data resides, getting access to the data, filtering on the right parameters, and extracting data over the relevant period.
• Transforming the data. This means reformatting dates and keys in key-value tables, finding and managing duplicated data entries, transposing tables, stacking and joining tables, setting columns in the correct order for data uploading, performing basic sanity tests on the data before data upload, handling missing data, etc.
• Loading the data. This means moving the spreadsheet files to a server and asking a database admin to upload new data. Should something go wrong here, manual time-consuming troubleshooting would take place. You are good to go when everything loads and is successfully committed to the DB.

Let us say the above takes 40-80 hours monthly. Compare this with 80 hours to write a customized ETL program. The payback is short, and it brings the added benefits of having a consistent, repeatable, and auditable way of pre-processing the data. The benefits outweigh the cost of the ETL software.

With today’s easier access to machine learning, provided the underlying data sets are large, we can incorporate intelligence into the dashboards to highlight patterns that deviate from the normal or what we would expect. These are so-called data anomalies that are not always easy to spot for a human. A common example of this is credit card fraud detection systems used to analyze the past transaction details of customers and extract behavioral patterns. We can of course also be less fancy and simply add a flag that warns if (using the market share case above) the contribution margin risks dropping below a set threshold. This is accomplished by adding a forecast from HR over the predicted wage rates for the coming three-year period. If HR forecast wages to increase more than expected, then Marketing and Sales, given the company’s strong position in the market, can hopefully compensate for this by increasing the sales price ahead of the predicted increase in costs. Thereby maintaining margins. This latter case may well be the main advantage of building intelligent early pattern detection into the forecast system.

A Forecasting User Story Example

Let’s look at a user story where an intelligent forecast system shows its usefulness.

Jane considers postponing the price change until she is ready with the revised annual price list. However, fearing a decline in market share following an increased sales price, Jane instead has a discussion with Engineering and Sales which led her to the decision to introduce the next product model with new features earlier than planned. Jane also increased the price to reflect the added value of the new features, which at the same time offset the effects of potentially increased wages.

A year later, Jane’s initiative increased the market share and maintained the margin. By receiving the system’s notification, Jane could make an informed decision well ahead of time.

Effective Data Processing

There are many challenges in effectively processing data, so it’s important to focus on a few important requirements as work is planned. The process should be automated to minimize manual processing. The work will be repetitive, and as such is well suited to be automated. The system should be built to handle outliers in the data as well as missing data. The input data should be transformed into new data sets of suitable granularities. Data may come as daily sales for example, but forecasting should be done using monthly aggregated data.

Although the above may sound like a daunting task, it can be achieved by feeding data to an API service that returns a forecast.