Improving Forecast Accuracy for Client Courier – Final Report

Client: Client Courier Ltd.

Capstone Consulting Group,

Prepared For: Ignacio Castillo

April 15, 2003

Final Report
April 15, 2003 /

Authors

This report was prepared by:

Adam Crowe

Andrea Denney

David Fath

Elaine Siu

Linda Tarshahani

Table of Contents

Executive Summary

Introduction

Background

Problem Definition

Objectives

Scope and Deliverables

Assumptions

Methodology

Phase One: Data Analysis

Current Methodology

Data Patterns and Trends

Phase Two: Research & Modeling

Research

Modeling

Our Modifications to the Triple Exponential Smoothing Model

Results

Phase Three: Build Forecast Tool

Tool Requirements

How it works

Managerial Discussion

Cost analysis

Forecasting Alternatives

Future considerations

Recommendations

Integration with other tools

Conclusion

Exhibits

Exhibit 1 – Forecast activity areas

Exhibit 2 – Example Impacts of Current Forecasting Methodology

Exhibit 3 – MAPE comparison for tested models

Exhibit 4 – TES model in-depth – Initialization

Exhibit 5 – TES model in-depth – Learning Phase

Exhibit 6 – TES model in-depth - Forecasting

Exhibit 7 – Solution and Modifications

Exhibit 8 – Monthly forecasting errors (1 month out)

Exhibit 9 – Daily forecasting errors (4 weeks/20 working days out)

Exhibit 10 – Capstone vs. Forecast Pro Error rates

Exhibit 11 – Tool Screenshot - The Welcome Splash Page

Exhibit 12 – Tool Screenshot - The Forecast Menu

Exhibit 13 – Tool Screenshot - Reporting Structure

Exhibit 14 - Revisiting our Objectives for Client

Appendices

Appendix 1: Detailed description of tested forecasting models

References

Final Report
April 15, 2003 /

Executive Summary

Client Courier Ltd. has hired Capstone Consulting Group to support its efforts in improving forecast accuracy and efficiency in its Western Canadian distribution centres. Many of the Client’s employees have lost confidence in the current corporate forecasts and are relying on their own personal judgment to make important decisions. As a result, Client feels that inaccurate forecasts are contributing to poor decisions on both strategic and tactical levels.

Client makes many forecasts that represent different regions, activities and employee groups, and makes monthly, weekly, and daily forecasts for each of them. Capstone has agreed to provide a comprehensive tool that will enable managers and front-line staff alike to produce forecasts so that they can be easily used to make important decisions

Capstone considered several new forecasting methods that could increase forecasting accuracy, including: ARIMA, Time Series Decomposition, the Theta model, and Triple Exponential Smoothing (TES). It was decided that TES was the best method to use for Client’s monthly and daily forecasts as it produced the best forecasts for 2002 when data from this year was held back.

To further improve Client’s forecasts, Capstone has customized the TES model to incorporate several unique features of the company’s historical data. The accuracy of this new model brought the average forecasting error down 56% from Client’s previous forecast error. This difference was measured using mean absolute percent error, and is an average of all forecasts.

Once a suitable methodology was agreed upon, Capstone began creating a user friendly VBA tool that could be used across the organization. The tool is capable of incorporating new data and modifying forecasts to reduce errors. Capstone feels this tool will be incredibly useful for Client’s Western Canadian region and hopes that its success will contribute to Client’s competitive advantage in the courier industry.

1

Final Report
April 15, 2003 /

Introduction

Background

Client Courier Ltd. (Client) is Canada’s leader in overnight package delivery, and is a major provider of integrated distribution solutions in North America. Client is interested in formalizing and automating the volume forecasting process for its distribution centers in western Canada. According to the Company, improving forecast accuracy and efficiency is important because it will allow a more organized approach to staff scheduling and resource allocation. As a result of this new approach, Client feels that it can enhance its competitive advantage in the Canadian courier industry by using their resources more effectively. Client Courier Ltd. has hired Capstone Consulting Group (Capstone) to analyze its historical volume, research mathematical methods for forecasting its demand, and recommend a forecast method that improves forecast accuracy. Capstone will also build a flexible and dynamic software application that will allow users to forecast volume demand using a customized forecasting approach.

Problem Definition

The major problem that Client faces is that inaccurate forecasts are leading to poor strategic and tactical decisions within the company. Front line employees and regional managers have lost confidence in the corporate forecasts and are relying on their own personal judgment to make important decisions. Client recognizes that more sophisticated forecasts are needed to create buy-in among its employees and allow them to make informed decisions.

Objectives

Capstone Consulting outlined several objectives that would address Client’s needs:

  • Formalize a forecasting method by providing a mathematical basis from which forecasts can be produced. We planned to exploit the relationships and patterns that exist in historical data and use them to predict future demand.
  • Improve forecasting accuracy and efficiency for forecasts relating to all regional distribution centres, business types and carriers as listed in Exhibit I.
  • Provide a user interface that will allow a wide variety of people within the company to access forecast information to make decisions. This would enable:
  • Senior managers to use long-range forecasts to make strategic decisions.
  • Operational managers to use month-to-month forecasts
  • Enable tactical decision making based on changes to day-to-day forecasts

Scope and Deliverables

Capstone Consulting and Client Courier agreed the scope of this project would be as follows:

  • Investigate:
  • Underlying patterns and trends in daily, weekly and monthly data
  • Forecast methods suitable for Client’s business
  • Produce daily, weekly and monthly forecasts using the most suitable method for:
  • Seven regions within Western Canada
  • Four business activities (Exhibit 1)
  • Two types of carriers (Exhibit 1)
  • Develop:
  • A user-friendly, stand-alone forecasting tool
  • A descriptive user manual for the tool
  • Evaluate:
  • The performance of the forecasts for all regions, all types
  • The strengths and limitations of the model and tool

Our scope was limited to generating forecasts and did not include:

  • Integration of the delivered forecasting tool with other decision support tools
  • Providing recommendations based on our findings about operational activities, such as staff scheduling
  • Implementation of our tool into Client’s corporate structure, which includes training of front-line staff

Assumptions

Capstone Consulting made the following assumptions in developing the forecast models and tool:

  • Each business type in each region has its own percentage allocation of work between PCL and Agents but these percentages remain relatively stable; therefore, forecasting for total volume and then breaking the forecast down by carrier type is acceptable.
  • The model must use historical data to generate forecasts
  • Forecasts produced by Forecast Pro provide an adequate benchmark by which forecast results can be compared.
  • Mean Absolute Percent Error is the most suitable forecast error to measure since it normalizes the errors across demand volumes of different sizes and is the measurement currently used by Client.
  • All the data provided by Client Courier Ltd is accurate, reliable and complete.

Methodology

The methodology undertaken by Capstone Consulting can be broken into three distinct phases. The initial phase was to analyze historical data to find recurring patterns and trends. The second phase was to research forecast models, analyze their respective properties and error rates and then build a forecast model that produced the smallest forecast errors. The third phase was to develop a forecasting tool based on the forecast model.

Phase One: Data Analysis

Current Methodology

Client is utilizing a last point method of forecasting which is then adjusted based on managerial judgment. However, Client has noticed the following symptoms from using this approach (see Exhibit 2 for visual explanation):

  • Consistently lower forecasts – Management’s adjustments to the forecasts are typically conservative
  • High degree of variation in forecast errors – Forecasts rely on a single historical data point; therefore, Client is assuming that each data point will represent future demand
  • Unreasonable forecasts – Special causes of variation that affected last year’s demand such as severe snowstorms, terrorist attacks, and other unpredictable events, are not adjusted for in predicting future demand.

Data Patterns and Trends

Capstone Consulting utilized forecasting tools, data set graphing, and percent errors to test characteristics in Client’s historic data. The following patterns were found:

  1. Seasonality - Historical volume demand for Client Courier contained daily, weekly and monthly seasonality for most regions and activities. Since many of Client’s clients are retailers, monthly seasonality is closely related to the annual consumer purchasing cycles that exist within the industry. Weekly and daily seasonality can be attributed to a variety of factors that are specific to each region and type of activity.
  2. Insignificant trend - Capstone also observed that there has been no significant increase or decrease in annual demand volume for Client over the last four years for most regions. However, some regions did exhibit annual trends, but these fluctuations were small and inconsistent.
  3. Weekly groupings – We also discovered that Client would group months by number of weeks (4 or 5). This meant that the last two days of a month could be considered part of the first week of the following month. When compared to the methodology of grouping dates to compile a month (eg. Feb.1-Feb.28), we found that that Client’s weekly method produced lower errors and showed a higher seasonality, thus this method was chosen for future modeling.
  4. Working days in a month – We found that the number of working days in a particular month had a high correlation to that month’s volume. For example, if January 2001 had 19 working days and January 2002 had 20, the monthly volume difference would be expected to differ by one working day’s volume.

Phase Two: Research & Modeling

Research

Capstone Consulting’s research centered around four forecasting models. Three of these, ARIMA, Time Series decomposition and Triple Exponential Smoothing (TES) were chosen due to their proven capabilities and widespread acceptance as forecasting tools. The fourth, the Theta model, is relatively new and had an intriguing premise. Detailed descriptions of these four models are listed in Appendix 1.

Modeling

The modeling component combined a qualitative and quantitative analysis of the four models chosen to test. The qualitative analysis comprised a suitability test to the data series provided by Client Courier based on model characteristics. The quantitative analysis was conducted by holding back a period of 12 months from our original data set of 4 years. Forecasts were then prepared for those 12 months based on the remaining 3 years of data. Next, the forecasts were compared to the actual monthly figures and a Mean Absolute Percent Error (MAPE) was generated. This performance measure was chosen because the percentage component of it standardizes errors, which are based on different sample sizes of n. Since each month had a different demand volume, this measure was ideal. In addition, Client uses MAPE on a corporate-wide basis for benchmarking purposes.

Modeling analysis and comparative results

The characteristics of Client’s data were such that most of the forecasting models tested could be eliminated. Two characteristics in particular eliminated the practicality of the models:

  1. Limited data - Certain regions had very limited amounts of data because of their recent change to a distribution centre.
  2. Outliers - All regions contained outliers, which were explained by weather problems, loss or gains of clients and other factors that are not accounted for.

These reasons made the ARIMA Box Jenkins model an obvious choice for omission. ARIMA does not work well with outliers, as they violate the stationary assumption. As well, ARIMA requires lengthy time-series data, which was not available for some regions. The ARIMA model would not be effective without extensive data cleaning, which would have enlarged the scope of the project immensely. Time series decomposition proved to be effective in identifying components present in the series but was also quite susceptible to outliers and ultimately did not forecast well. The Theta model produced relatively accurate results but required another forecasting method to forecast the Theta data series. This data series exhibited many of the same basic features of the original series, therefore the ARIMA and decomposition models were not appropriate. This left the Triple Exponential Smoothing model, which worked quite well to forecast the Theta data series. Our last test, and model of choice, was the Triple Exponential Smoothing (TES) model independently. TES does not have the strict basic assumptions of the ARIMA model and it is not as susceptible to outliers, therefore large scale data cleaning is not necessary. Comparing the mean average percent error results from the 4 models validated our choice to use TES (see Exhibit 3). The basic components (Initialization, Learning and Forecasting) of the Triple Exponential Smoothing model can be found in Exhibits 4, 5 and 6.

Our Modifications to the Triple Exponential Smoothing Model

Initial trend smoothing parameter

After some initial analysis it was noticed that the initial trend component in the TES model was not always indicative of future trend and was skewing our forecast. The initial trend in the basic Triple Exponential Smoothing Model is calculated by subtracting the first term of the second period from the first term of the first period. To make our forecasts more accurate, we multiplied the calculated trend to a value between zero and one. This trend smoothing parameter is then calculated using non-linear programming to minimize the fitted errors of the data series. If a value of zero was computed, it meant that the calculated trend was highly skewed and would not be value added for future forecasts. A value of one would indicate that the calculated trend was accurate in determining future forecasts. We also found that when dealing monthly totals there was a high correlation between the number of working days in a month relative to that month’s average number of working days (see Exhibit 7).

Number of working days index

As discussed previously, the number of working days in a particular month fluctuates slightly from year to year depending on where the weekends fall and on holidays such as Easter. To take this into account for our forecasts, we created an index of the number of working days in a particular month divided by that month’s average number of working days (see Exhibit 7). This index was multiplied by a smoothing parameter and then by the forecast. This smoothing parameter was adjusted using non-linear programming as with the other parameters. These modifications greatly improved our forecast accuracy relative to the traditional Triple Exponential Smoothing Model.

Holiday Adjustments

Holidays pose a number of problems for our daily forecasts. First, because our daily forecasts use Triple Exponential Smoothing with five seasons, it is impossible for our model to automatically pick up annual holiday patterns. Second, because the actual demand for holidays is often zero, the seasonality index for that day of the week will be drastically affected, and can severely impact the forecasts for subsequent weeks. For example, if the volume on the first Monday of August (Victoria day) is zero, our model will assign the subsequent Monday a seasonality index that is very low. However, actual demand for that Monday will most likely be far higher than such an index would predict. Our model does not know that the first Monday was a holiday that represents an irregular demand volume.

Tocombat these problems, we make two important adjustments: first, we adjust the series of data that represents historical demand. The volume on holidays is changed to represent the average volume that would be expected on that day of the week if a holiday had not taken place. We do this by taking an average of the same day of the week over the preceding two weeks. This prevents our model from adjusting the seasonality parameter to account for the irregular demand that is experienced on holidays. The second adjustment that is made reduces the forecasted demand that would be expected on a particular day to zero if that day is a holiday. We do this by attaching a binary variable to each future day in the forecast, and assigning a value of 0 to days that are holidays. We then multiply the binary variable to the Triple Exponential Smoothing forecast, which gives us our final forecast for that day.