MOVING AVERAGE AND EXPONENTIAL SMOOTHING IN FORECASTING DEMAND

Complete the Assignment, name it as GroupXX_Assign3.xlsx (where XX is your Group Number), and upload and submit to the instructor through D2L, using the link named “Group Graded Assignment 3.” Do not enter anything in the spreadsheet cells that are black, labeled “Grader”.

You must complete this assignment without the assistance of persons other than the members of your GA (Graded Assignment) Group. You may use any other resources you deem necessary. Answer the questions below by placing the appropriate graph and/or answers in the designated cells of the spreadsheet.

DO NOT CHANGE THE APPEARANCE, FORMATTING, OR FUNCTIONALITY OF THE SPREADSHEET UNLESS INSTRUCTED TO DO SO.

NOTE: It is expected that you will generate additional spreadsheets when you use StatTools.

Information for Questions 1-3:

The Instant paper Clip Company sells and delivers office supplies to various companies, schools, and agencies within a 30-mile radius of its warehouse. The office supply business is extremely competitive, and the ability to deliver orders promptly is an important factor in customer relations. The manager of the company wants to be certain that enough drivers and delivery vehicles are available so that orders can be delivered promptly. Therefore, the manager wants to be able to forecast the number of orders that will occur during the next month (i.e., to forecast the demand for deliveries).

From records of delivery orders the manager has accumulated data for the past 10 months. These data are shown in the worksheets named MA and ES. The manager wants your help in fitting different models (Moving Average and Exponential Smoothing) to help forecast demand for deliveries.

QUESTION 1: Moving Average Models – Worksheet MA

6 Points: Using the Excel Insert Tab Line Graph and follow-up Chart Tools Tabs, construct an appropriate line chart displaying the time pattern of order deliveries. Please create a professional appearing chart with labeling and titles. A legend is probably not needed. Move the chart so that it starts in cell H4 and fits within the red shaded area. Is the time pattern stationary? Yes or No in cell K3.
4 Points: In column C (starting in the appropriate cell), write a formula using a built-in Excel function to forecast demand through October using an MA (3) model.
4 Points: In column D (starting in the appropriate cell), write a formula using a built-in Excel function to forecast demand through October using an MA (5) model.
2 Points: In cell B17, write a formula using a built-in Excel function to forecast demand for November using the MA (3) model you constructed.
2 Points: In cell B18, write a formula using a built-in Excel function to forecast demand for November using the MA (5) model you constructed.
4 Points: In column E, under the label “MA (3) Error,” write a formula in the appropriate cells calculating the error terms necessary to calculate the MAE for the MA (3) model. Do not construct a column of errors, then another column of absolute, squared, or absolute relative errors. Construct only one set of errors using the appropriate formula to make the errors absolute, squared, or absolute relative (whichever is appropriate for MAE).
4 Point: In column F, under the label “MA (5) Error,” write a formula in the appropriate cells calculating the error terms necessary to calculate the MAE for the MA (5) model. Do not construct a column of errors, then another column of absolute, squared, or absolute relative errors. Construct only one set of errors using the appropriate formula to make the errors absolute, squared, or absolute relative (whichever is appropriate for MAE).
2 Points: In cell B22, write a formula using a built-In Excel function referencing the appropriate cells to determine the MAE for the MA (3) model.
2 Points: In cell B23, write a formula using a built-In Excel function referencing the appropriate cells to determine the MAE for the MA (5) model.
2 Points: In cell B26, write the model that best fits the data according to the MAE calculations. That is, write either “MA (3)” or “MA (5)” in cell B26.
8 Points: Using Stat Tools replicate the MA (3) and MA (5) forecasting processes following the guidelines below:
1. Create the MA (3) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell A1 of the StatToolsMA Worksheet.
1. Create the MA (5) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell G1 of the StatToolsMA Worksheet.
1. Confirm that your StatTools results match the results you obtained in earlier segments of this Question.
2 points: Would your choice of model that best fits the data using MAE be the same if the criteria were MAPE? Place your answer (Yes or No) in cell I24.

QUESTION 2: Exponential Smoothing Models – Worksheet ES

4 Points: In column C (starting in the appropriate cell), write a formula to forecast demand through October using an ES model with smoothing constant alpha given in cell B1. Be sure to reference the cells containing the weights in the formula.
2 Points: In cell B20, write a formula to forecast demand for November using the ES model you constructed.
4 Points: In column D, under the label “ES Error,” write a formula in the appropriate cells calculating the error terms necessary to calculate the MAPE for the ES model. Do not construct a column of errors, then another column of absolute, squared, or absolute relative errors. Construct only one set of errors using the appropriate formula to make the errors absolute, squared, or absolute relative (whichever is appropriate for MAPE).
1 Point: In cell B24, write a formula using a built-In Excel function referencing the appropriate cells to determine the MAPE for the ES model.

Read the following carefully before answering part e. You are going to use Excel’s Solver Add-in Tool to determine the optimal value of alpha (the ES smoothing constant) that minimizes the MAPE for the model. The alpha used above may or may not be optimal, it is just a guess. To use Solver, you must first copy the appropriate parts of the data and your formulas/outputs to another part of the spreadsheet, then edit the references to other cells.

Copy Instructions:

Copy from range A1:B1 to range H1:I1. This copies the alpha value cell.
1. Copy from range A6:D16 to range H6:K16. This copies the data and your formulas.
1. Copy from range A23:B24 to range H23:I24. This copies your MAPE calculation formula.

Edit Formulas Instructions:

Edit any formulas in column J to reference the smoothing constant, alpha, given in cell I1.

Using Solver:

The point of using Solver is to change alpha until the value of MAPE is minimized. This could be accomplished by hand but would take a long time. For example, if you wanted accuracy of alpha to only two decimal places, you would (on average) have to try about 50 values to find the one that minimizes MAPE (and potentially about 100 values). Precision to 4 decimal places would require an average of 5000 values, and possibly as many as 10000. Solver runs through all possible alpha values and selects the one that minimizes the MAPE.

Solver requires three sets of inputs; (1) the cell containing the formula and value to be minimized (MAPE cell in this case), (2) the cell to change to achieve the minimized value (the alpha value cell in this case), and (3) the constraints that must be recognized. The constraints require that the value in the MAPE cell be >= 0, and the value of alpha be between 0 and 1, exclusive (not including 0 and 1). The document named Solver for Optimizing will show you how to use Solver to find the optimal alpha for the ES model. You will find Solver on the Data Sheet under Data Analysis. Be sure the Solver Add-In is enabled.

6 Points: Using Solver, set cell I24 (MAPE) to a minimum (radio button), by changing cell I1 (alpha), subject to the following constraints:

MAPE >= 0, that is cell I24 >= 0
- Alpha >= 0.001, that is cell I1 >= 0.001
- Alpha <= 0.999, that is cell I1 <= 0.999
- Non-negativity (actually redundant here because of the alpha >= 0.001 restriction, but a good practice to get into)

As a result, a new value of alpha will appear in cells I1 that results in the smallest possible MAPE (which will also appear in cell I24).

In each of cells B28 and B29, reference the appropriate cells containing the Solver Solution. For example, in cell B28, enter the formula reference as “=I2.”

2 Points: In cell B30, write a formula to forecast demand for November using the new ES model alpha you found using Solver (cell B28).

8 Points: Using StatTools replicate the ES forecasting processes following the guidelines below:

1. Create the ES (alpha =0.2) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell A1 of the StatToolsES Worksheet.

2. Follow the guidelines in our text to find the optimal alpha (smoothing constant) for the ES model of our data. Incorporate in you process only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell G1 of the StatToolsES Worksheet.

3. Confirm that your StatTools results match the results you obtained in earlier segments of this Question.

h. 3 Points: Which Metric does StatTools use for optimization? [Your textbook has the answer.] Place your answer in cell L29. What value does StatTools give as the optimal alpha? Place your answer in cell L30. What is the value of the MAPE at this alpha value? Place your answer in cell L31.

QUESTION 3

PM Computer Services assembles customized personal computers from generic parts. The company was formed and is operated by two part-time university students, Paul and Bryan. The company has experienced steady growth since started. The computer parts are bought using volume discounts when good deals can be found. As such it is important that they develop a good model to forecast demand for their computers so that they will know how many computer components parts to purchase and stock.

The company has accumulated computer demand data over a 12-month period. The data are shown in the worksheet named PM.

8 Points: Using the Excel Insert Tab Line Graph and follow-up Chart Tools Tabs, construct an appropriate line chart displaying the time pattern of computer demand data. Please create a professional appearing chart with labeling and titles. A legend is probably not needed. Move the chart so that it starts in cell J4 and fits within the red shaded area. Is the time pattern stationary? Yes or No in cell M3.
6 Points: Using StatTools, create the Optimal Parameters Exponential Smoothing (Simple) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell A1 of the StatTools PM Worksheet.
6 Points: Using StatTools, create the Optimal Parameters Exponential Smoothing (Holt’s) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell G1 of the StatTools PM Worksheet.
8 Points: In the blue area designated Analysis, provide a managerial analysis for Paul and Bryan incorporating any patterns you have identified, recommending a forecasting method (with reasons), and providing your best forecast for the upcoming month.

Order Similar Paper