Production & Inventory Control
Production & Inventory Control
Part 1: Forecasting Tool
The data in the Excel spreadsheet “flight_data” represents the number of airline tickets purchased each month from 1949-1960. You will create an Excel tool that will have the ability to generate forecasts using all five of the forecasting methods we covered in class:
- Simple moving average
- Weighted moving average
- Simple exponential smoothing
- Winter’s method (assuming exactly two seasons of historical data as initial input)
- Linear regression
Your tool must be able to accept a forecasting method and its associated parameters from the user, as well as the range of historical data that should be used for initialization. For example, I should be able to tell the forecasting tool that I want to forecast the monthly demand for January-December 1950, using the demand data from January-December 1949 and applying Winter’s method with N = 4, α1 = 0.1, α2 = 0.0.5, α3 = 0.2. Note that, although I specified the historical data for January-December 1949, only the data for May-December would actually be used (because we are assuming two seasons, and N = 4). But you want the tool to be flexible enough that it can be given a range of historical data to use and then select from that data appropriately, given the forecasting parameters that are provided by the user.
In addition to generating forecasted demand values for the months specified by the user, the tool must also be able to generate metrics that capture the quality of the forecast, compared to the actual observed demand. This will enable the user to experiment with different forecasting approaches, compare metrics, and then select an approach that provides the best results. Think carefully about what is meant by “best”, in terms of forecast accuracy and bias, as well as the consequences (in terms of cost) of overages and shortages for this system.
Once the forecasting tool is developed, you will use it to answer the following two questions (which you will address in your report):
-
Develop a forecast for airline ticket demand for each of the 12 months in the year 1950. Which forecasting approach is best? Why?
-
Develop a forecast for airline ticket demand for each of the 12 months in the year 1960. Which forecasting approach is best? Why?
DETAILED ASSIGNMENT
