Forecasting Sheet (4 Problems)

MGT 465 Production and Operations Management

Chapter 8 Forecasting

Information for Completing the Spreadsheet Shell

Submit completed Excel workbooks by clicking on the assignment name in Blackboard and

attaching your file. Please rename the file with your last name.

The spreadsheet cells that are yellow colored contain the problem data. Enter formulas into the

blue colored cells to complete the spreadsheet.

There are some videos in the Ch 8 Videos folder that show how to enter some of the formulas

you need to enter in order to complete the assignment.

Problem 5

 Don’t forget to answer the question about which forecast to use in the spreadsheet.

 Reference the alpha values stored in cells C2 and C12 when you enter the exponential smoothing formula in column C. Entering cell references in formulas rather than

numerical values makes it easier to change the alpha values; simply enter new values in

cell C2 or C12 and the spreadsheet automatically calculates new forecasts and MAD

values.

 Use the MAD function (Mean Absolute Deviation) to calculate the absolute error values in column E.

 Use the average function to calculate the MAD value.

Problem 7

 The squared error formula in Excel uses ^2 to indicate that the value is to be squared. For example, if you are in cell B1 and you want to square the number stored in cell A1, you

would enter in cell B1 the formula =A1^2.

 Use the FORECAST.LINEAR function to perform the calculations for the linear regression in column C.

o Because this problem uses historical time series data, the x-values (Known_xs) are the days of the week numbers and the y-values (Known_ys) are the demand

values.

o The values in the Linear Reg. column (column C) show the expected demand for corresponding week. For example, the first value in the column shows what the

forecasted demand is for week 1; it is the y-value on the regression line associated

with the x-value equal to one.

o The FORECAST.LINEAR function internally calculates the regression line equation, and then plugs in an x-value into the regression formula and returns the

y-value.

Problem 9

 If the data in this problem did not reflect seasonal changes, we could simply calculate an average and use it to forecast the number of visitors.

 The seasonal index is used to adjust the average to reflect a change in value for a season.

Problem 25

 The previous problems contain time series data. In a time series, data may change as time passes, but why the data is changing is not accounted for in the pattern.

o Data can follow a pattern:  level or horizontal pattern – no change in data values.  trend pattern – progressively increasing or decreasing  seasonal pattern – regular repeating pattern

 Regression can be used to establish a reason for why data is changing. o In this problem, the company believes that the amount they spend on advertising

will cause the retail sales to change.