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.

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 hardcode 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).

 

20. 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).

 

20. e) Create a scatter chart to plot 2 series: 1) actual gallons (Y) by week (X)and 2) forecasted 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.
21. 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.

 

2. 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 actuals 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 actuals 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 (R²).
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 actuals weeks and 1 forecasted 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 actuals and insert a scatter chart (do not connect the points with a line). Right click one coordinate point and choose add trendline (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 R².
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.

milesK	Maintenancce$
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 (R²).
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 R² 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 milesK 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 MilesK input.

Submission

Congratulations, you are now an adept forecaster and a power MS Excel user.  Now you just need to upload the file and send it via Bb. Remember that a filename beginning with ~ is not a valid Excel file.

 

 

Sample Spreadsheet Layout for Problem 1 – For formatting purposes only.  You will not get the same answers. You are not required to use this format.

 

 

 

Sample Spreadsheet Layout for Problem 2 – For formatting purposes only.  You will not get the same answers. You are not required to use this format.