Gasoline Sales Forecasting: Exponential Smoothing Analysis

docx

School

IIT Kanpur *

*We aren’t endorsed by this school

Course

100

Subject

Management

Date

Jun 10, 2024

Type

docx

Pages

Uploaded by DeanMantisPerson1021

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. Your assignment submission must be an MS-Excel workbook and all required arithmetic and logic computations must be implemented using MS-Excel formulas and functions . You should not enter results computed using a calculator which would defeat the purpose of using spreadsheet 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 9112 9432 2 11007 10863 3 8848 9380 4 10970 9152 5 10151 8369 6 9851 9558 7 9792 9366 8 9055 8395 9 9078 8084 10 10588 8028 11 9010 8985 12 9790 8046 13 8362 7479 14 9687 6703 15 8647 6949 16 7458 7080 17 8237 6835 18 8405 7231 19 9037 6567 20 8060 5590 Problem 1 (Exponential Smoothing) a) Copy and paste the first two columns (week # and series 2 actuals) 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. Page 1 of 7

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 this cell that contains the alpha value. This will facilitate what-if analysis to see how the accuracy of the forecast changes with respect to changes in alpha value. 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 21 so that you are making a forecast for one week (21) with no actual. In your exponential smoothing formula, make sure you refer to the cell containing the alpha value (see sample layout at end of assignment instructions). You will need to use relative and absolute addresses correctly in order to copy the exponential smoothing formula. Since gallons is a continuous measure, you do not need to round values to a whole number using the MS-Excel round function. For readability, you can just format numbers to display 0 decimal places (arrows under home->number menu). 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). 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 21. 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 by entering the correct X and Y values for each series (e.g. weeks by actual values and weeks by forecasted values). 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. Note that your exponential smoothing forecast formulas should NOT refer to alpha values in this table which is just for record-keeping. Your forecasting formulas should refer to a single separate cell with the alpha value. 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): Alpha MAD 0.00 10.20 0.25 9.80 *Min 0.50 11.43 0.75 15.20 1.00 17.30 Submit the assignment with the best alpha value for time series 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 actuals data into the actual gallons of gas column. Page 2 of 7

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 2. 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. a) For each time series, use Excel functions (INTERCEPT, SLOPE and RSQ) to estimate the intercept, slope and coefficient of determination (R 2 ). b) 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. c) 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 and the week number which is the independent X variable. d) 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). e) For each time series, write in a textbox your interpretation of the slope using computed slope value and problem specific variable names. I am looking for a very specific interpretations of slope and R2 values. You should refer to examples in M1 notes and solutions to exercises and quizzes. f) For each time series, write in a textbox your interpretation of R 2 using computed value and problem specific variable names. g) For which time series is the linear time most appropriate? Explain why. Page 3 of 7

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

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 Maintenance$ 23.5 771.91 21.2 252.43 23.3 610.44 27.7 1093.61 33.7 1073.09 38.0 551.50 39.9 522.77 44.4 1293.03 45.7 988.10 49.0 1517.96 54.2 1408.09 56.4 1179.07 58.7 1506.26 63.2 1228.26 63.9 1683.54 64.3 1894.31 67.2 1756.54 70.0 1150.32 72.2 1946.12 73.0 1928.84 74.6 1585.99 75.4 1519.00 78.4 1985.04 80.5 1495.76 83.5 1744.04 a) Use Excel functions (INTERCEPT, SLOPE and RSQ) to estimate the intercept, slope and coefficient of correlation (R 2 ). b) Write the regression equation in a textbox. Use problem specific variable names (e.g. selling price). c) Create a scatter diagram of miles driven (thousands) by sales price. Do not connect coordinate points with a line. Instead add a linear trend line. d) Interpret the slope using computed values and problem specific variable names. Write you answer in a textbox. e) Interpret R 2 using computed R 2 value and problem specific variable names. Write your answer in a textbox. f) Using the regression results, predict the maintenance expense of a car with 77 K miles . Create a separate area in the spreadsheet for making predictions. Here’s a possible format (values are not correct answers): f) Prediction Miles K Maintenance $ 60 1,674.18 Page 4 of 7

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 your MS-Excel workbook file and send it via Bb. Remember that a filename beginning with ~ is not a valid Excel file. Page 5 of 7

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. Actual (Series 1) vs Forecast Actual (Series 2) vs Forecast Week Actual # Deliveries Series 1 Exp. Smooth Forecast Absolute Deviations Week Actual # Deliveries Series 2 Exp. Smooth Forecast Absolute Deviations 1 85 1 67 2 95 85 10 2 80 67 13 3 82 90 8 3 82 80 2 4 86 86 0 4 86 82 4 5 92 86 6 5 92 86 6 6 88 89 1 6 88 92 4 7 96 89 7 7 96 88 8 8 90 93 3 8 99 96 3 9 110 92 18 9 112 99 13 10 100 101 1 10 120 112 8 11 119 101 18 11 119 120 1 12 113 110 3 12 113 119 6 14 120 112 8 14 122 113 9 15 128 116 12 15 127 122 5 16 128 122 6 16 131 127 4 17 125 17 131 Alpha 0.5 7.21 MAD Alpha 1 6.14 MAD Alpha MAD Alpha MAD 0.00 18.79 0.00 37.79 0.25 9.79 0.25 14.71 0.50 7.21 *Min 0.5 8.57 0.75 7.36 0.75 6.93 1.00 8.64 1.00 6.14 *Min 0 20 40 60 80 100 120 140 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 # Deliveries Week # Forecast vs Actual Actual # Deliveries Series 1 Exp. Smooth Forecast Write your answers in a textbox... 0 20 40 60 80 100 120 140 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 # Deliveries Week # Forecast vs Actual Actual # Deliveries Series 2 Exp. Smooth Forecast Write answers in a textbox... Page 6 of 7

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

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. Time Period t Actual (series 1) b) Forecast F t Time Period t Actual (series 2) b) Forecast F t 1 85 81 1 65 73 2 95 84 2 80 77 3 82 87 3 82 81 4 86 90 4 86 85 5 92 93 5 92 89 6 88 95 6 88 93 7 96 98 7 96 97 8 90 101 8 98 101 9 110 104 9 110 105 10 100 107 10 121 109 11 119 110 11 119 113 12 113 113 12 113 117 13 120 116 13 120 121 14 128 119 14 128 125 15 128 122 15 132 129 16 114 125 16 121 133 17 128 17 137 a) a) a = 77.8 a = 69.2 B = 3.0 B = 4.0 R 2 = 0.79 R 2 = 0.92 d) - f) Put written answers in a textbox d) - f) Put written answers in a textbox 60 70 80 90 100 110 120 130 140 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Deliveries week c) Linear Trend Model Time Series 1 60 70 80 90 100 110 120 130 140 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Deliveries week c) Linear Trend Model Time Series 2 Page 7 of 7

Related Documents