OM300: Homework #8

For problems 1 and 2, construct an EXCEL spreadsheet to do the calculations

. Submit two

“screenshots” of your Excel spreadsheet

. The first printout should show your solution, and the

second should display the formulas, gridlines, and the row and column headings

.Do problem 3 using Excel and Excel’s SOLVER

. Submit two “screen snapshots” of your

EXCEL spreadsheet

. The first printout should show your solution and the Solver dialog settings,

while the second should display the formulas, gridlines, and the row and column headings

.1

. The following data are monthly sales of jeans at a local department store

. Use this data to

answer the following questions

.Month Sales

January 55

February 47

March 50

April 40

May 30

June 45

a

. (10) What are the naïve forecasts for February through July?

b

. (15) What are the two-month moving average forecasts for March through July?

c

. (15) What are the exponential smoothing forecasts for February through July using

? = 0

.20? Hint: Use the naïve forecast to start the exponential smoothing process

.2

. Compute the MAD, MSE, and Tracking Signal for the

a

. (9) Naïve forecasts for February through June,

b

. (9) Two-month moving average forecasts for March through June, and

c

. (9) Exponential smoothing forecasts for February through June

.d

. (8) Based on these results, which forecasting method would you recommend that the

store use to forecast the future monthly sales of jeans and why?

3

. Construct a spreadsheet model to calculate the exponential smoothing forecasts for

February through June and the MSE of these forecasts

.Use Excel Solver to find the value of ? that minimizes the MSE of these forecasts

.