MGMT 426 Assignment #1 – Forecasting

by

MGMT 426 Assignment #1 – Forecasting

 

It is okay to help each other by asking and answering questions about the assignment. If one person (A) understands the material better than another person (B), it is A’s responsibility to explain the material to B and it is B’s responsibility to pose questions to A needed to fully understand the material. However, all work must be completed individually. You should never transmit (receive) workbook files electronically to (from) other students or submit a workbook file that has been completed or partially completed by others.  This is a misrepresentation of work completed and will be considered plagiarism.

 

In your submitted assignment, all computations must be completed using excel formulas. You should not enter results computed using a calculator which would defeat the purpose of using this software.

For problems 1 and 2, assume you work for a gasoline service station and want to forecast the gallons of gasoline sold. The forecast will be used to contract for future gasoline purchases.

Week

# (t)

 Series 1

Actual Gasoline Sales

Gallons

 Series 2

Actual Gasoline Sales

Gallons
1 9113 9435
2 11005 10860
3 8849 9379
4 10972 9150
5 10150 8369
6 9851 9557
7 9790 9366
8 9054 8392
9 9077 8084
10 10589 8030
11 9001 8985
12 9780 8045
13 8363 7477
14 9688 6701
15 8646 6951
16 7458 7080
17 8235 6835
18 8404 7213
19 9035 6565
20 8068 5592

 

Problem 1 (Exponential Smoothing)

  1. a) Copy and paste the first two columns (series 1 data) from the above table into a MS-Excel worksheet. Name worksheet “Problem 1 Series 1” by right-clicking the worksheet tab at bottom of screen. See attached sample layout for this problem but note that the attachments are for formatting purposes only and that you will not get the same numbers).
  2. b) Place the alpha value in a clearly labeled cell. Set the starting alpha value to .5. Your formulas should not hard code the alpha values in the formulas but should instead refer to the cell that contains the alpha value. This will facilitate what-if analysis (e.g. to see how the accuracy of the forecast changes with respect to changes in alpha value).

 

  1. c) Start with a naïve forecast for week 2 (use a formula or cell reference). Enter an exponential smoothing formula to make a forecast for week 3 and copy to weeks 4 through 20. Again, make sure you refer to the cell containing the alpha value. You will need to use relative an absolute addresses correctly in order to copy the exponential smoothing formula.

 

  1. d) We also want to calculate the Mean Absolute Deviation (MAD) to gauge forecast accuracy. In a separate column, calculate the absolute deviation for all periods for which you have both a forecast and actual value. In a clearly labeled cell, calculate the MAD score (the average of the absolute deviations).

 

  1. e) Create a scatter chart to plot 2 series: 1) actual gallons (Y) by week (X)and 2) forecaster gallons (Y) by week (X). Select the week number, actual and forecast column headers and the corresponding data for weeks 1 through 20. Choose insert->scatter chart. If you have trouble creating the chart, try inserting a blank scatter chart (e.g. without first selecting data) and then right-click chart and choose “Select Data”.  You will then need to manually add the two series.
  2. f) Change alpha values to 0, .25, .5, .75 and 1. Observe how chart and MAD scores and the scatter chart changes. In separate cells, keep track of the MAD score for each of five alpha values. Highlight which alpha value gives the most accurate forecast for time series 1. For example, create a table that looks like (your results will be different):

 

 

Submit the assignment with the best alpha value for time series 1.

 

  1. g) Now you need to do the same analysis for time series2 (but thanks to MS-Excel, it is not twice as much work). Right click the worksheet tab, choose copy worksheet and rename the new worksheet to “Problem1 Series 2.” Then copy and paste times series 2 actual data into the actual gallons of gas column.

 

  1. h) As with time series 1 data, change alpha values to 0, .25, .5, .75 and 1. Observe changes in chart and MAD scores. In separate cells, keep track of the MAD score for each of five alpha values. Highlight which alpha value gives the most accurate forecast time series 2. Submit the assignment with the best alpha value for time series

 

  1. i) In a textbox, explain why different alpha values provide better forecasts for the two different time series. Don’t just tell me “because they are more accurate” or just explain the MAD formula. The answer has to do with differences between actual time series. Slides 10 and 25 in the forecasting notes should help you answer this question.

Problem 2 (Linear Time Trend) – Create a new worksheet named “Problem 2”. You will now create a linear time trend model for each of the series. See attached sample layout for this problem. Enter week numbers and paste actual for each of the two time series used in problem 1.

  1. For each time series, use Excel functions (INTERCEPT, SLOPE and RSQ) to estimate the intercept, slope and coefficient of determination (R2).
  2. For each time series, write the regression equation in a textbox. Use problem specific variable names (e.g. do NOT use X and Y) and estimated intercept and slope parameter values.
  3. For each time series, make a forecast for all weeks 1-21 (e.g. prior 20 actual weeks and 1 forecaster future week). Your forecasting formulas should refer to the cells which contain the estimated intercept and slope parameters.
  4. For each time series, select the week number and actual and insert a scatter chart (do not connect the points with a line). Right click one coordinate point and choose add trend line (linear).
  5. For each time series, write in a textbox your interpretation of the slope.
  6. For each time series, write in a textbox your interpretation of R2.
  7. For which time series is the linear time most appropriate?  Explain why.

 

Problem 3 (Associative Regression) – A car rental firm wants to estimate an automobile’s annual maintenance expense on the basis of thousands of miles driven. Copy the data below into a worksheet named “Problem 3” and create an associative regression model.

miles K Maintenance$
23.5 774.14
21.2 254.20
23.3 611.45
27.7 1095.28
33.7 1071.26
38.0 555.03
39.9 526.84
44.4 1290.02
45.7 988.54
49.0 1518.32
54.2 1409.44
56.4 1177.05
58.7 1507.05
63.2 1224.88
63.9 1681.50
64.3 1892.78
70.0 1148.66
72.2 1943.40
73.0 1926.59
74.6 1581.53
75.4 1521.37
78.4 1984.71
80.5 1491.40
83.5 1744.04

 

  1. Use Excel functions (INTERCEPT, SLOPE and RSQ) to estimate the intercept, slope and coefficient of correlation (R2).
  2. Write the regression equation in a textbox. Use problem specific variable names (e.g. selling price).
  3. Create a scatter diagram of miles driven (thousands) by sales price. Do not connect coordinate points with a line. Add a linear trend line.
  4. Interpret the slope using computed values and problem specific variable names. Write you answer in a textbox.
  5. Interpret R2 using computed values and problem specific variable names. Write your answer in a textbox.
  6. Using the regression results, predict the maintenance expense of a car with 75 K miles. Create a separate area in the spreadsheet for making predictions. Here’s a possible format (values are not correct answers):

Create a separate clearly labeled cell for the miles K input value.  For maintenance expense, you should enter a formula in a clearly labeled cell and refer to cells which contain the values with estimated intercept, estimated slope and Miles K input.