MGMT $426-$ Assignment #$1$ Forecasting

Purpose of Assignment

In this assignment, you will develop forecasting skills which are widely used in industry and highly valuable since a forecast is the only estimate of the future that an organization has and is the basis for all organizational planning. You will practice using two different techniques $--$ exponential smoothing and simple least squares regression $--$ and learn how to evaluate different forecasting techniques through analysis of historical data.

General Procedures

It is okay to help each other by asking and answering questions about the assignment. However, all work must be completed individually nother. 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. 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 and$/$or functions. You should not enter results computed using a calculator which would defeat the purpose of using this software. You should also not enter values in formulas $($e$.$g$.=3*4)$; instead you should refer to labeled cells which contain those values $($e$.$g$.=$ A$1*$ A$2).$ Otherwise, you are also defeating a major purpose of using spreadsheet software which is automatic recalculation. You also need to use relative and absolute cell addressing correctly. If you are unfamiliar or need a review of these design concepts, you should watch the optional Introduction to Excel video in the main assignment folder. All written answers should be in a textbox to control wrapping.

You should watch the software demonstrations videos $1-3$ to help with the first problem and video $4$ to help with problems $2$ and $3.$ These videos are in the Assignment $1$ folder. In addition, there is an optional Introduction to Excel video in the main assignment folder.

Detail Instructions

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. You will use the two different time series in the table below which each represent a hypothetical history of actual gallons of gasoline sold for the last $24$ weeks $($the $24$th week is the most recent$).$

Problem $1($Exponential Smoothing$)$

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.

b$)$ Place the alpha value in a clearly labeled cell. Set the starting alpha value to $\mathrm{.}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$).$

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 $24.$ Again, make sure you refer to the cell containing the alpha value. You will need to use relative and absolute addresses correctly in order to copy the exponential smoothing formula.

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 $24.$ 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 XY series.

f$)$ Change alpha values to $0,\mathrm{.}25,\mathrm{.}5,\mathrm{.}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. Your forecasting formulas should not be referring to cells in this table which is for mental record$-$keeping only. Highlight the alpha value which gives the most accurate forecast for time series $1.$ For example, create a table that looks like this $($your results will be different$)$:

Submit the assig