mangament homework and excel

QUESTION: How do we figure out what the “actual” sales are? I am asking this question as calculating bias and mean absolute deviation require actual data. ANSWER: You are already given the actual sales data that had been collected between March of Year 1 and July of Year 5. The data you see here are the same as in the Excel file and these are the actual sales. You need to organize the data yourself to do forecasting. How can you do that? Copy and paste all of the data into one column and call it the actual sales column (Please follow what numbers I use by looking at the assignment sheet): Index of the observations Actual Sales 1 4566.4 2 6941.89 3 7047.23 4 6784.37 5 5814.23 6 6906.38 7 8075.13 8 7913.56 9 9054.18 10 14114.58 This is December sales of Year 1 11 6381.07 This is January sales of Year 2 12 4405.83 This is February sales of Year 2 13 9126.95 ... .... ... .... QUESTION: Is the second question an opinion question or is there one right answer in this case? ANSWER: There is one right answer in this case. Please remember the discussion on the selection of the forecasting technique with the minimum error, i.e., mean absolute deviation (MAD). Therefore, you should calculate MAD for every technique given in question 1 and suggest the one with minimum MAD as the solution of question 2. QUESTION: Is the mean absolute deviation same as the standard deviation? ANSWER: NO. Please see the section of FORMULA REVIEW at the back of the “forecasting” chapter of your textbook. Use the formula under the title of standard error of estimate. To calculate the standard error of estimate, we first calculate the error square and then take the sum of squares over the number of observations minus 2. That is,

Alternatively, you can use the approximate relation between MAD and standard deviation in the lecture slides.

QUESTION: The last part of question 1 asks us to use exponential smoothing. How can we do this if we do not have the exponentially smoothed forecast made for the prior month? Should we just make up one? ANSWER: You can take the first forecast as equal to the actual observation. What you see below will get you started on the application of exponential smoothing method. Year 1 Index Actual sales Forecast March 1 4566.4 (D28) (K28) 4566.4 = D28 April 2 6941.89(D29) (K29) 4566.4 =K28+0.3*(D28-K28) May 3 7047.23(D30) (K30) 5279.047 =K29+0.3*(D29-K29) QUESTION: What is the unit of time that should be used in the forecasting calculations? ANSWER: You should use the monthly data while doing the forecasting calculations. You must be working with 53 data points. QUESTION: How can we construct a confidence interval? Confidence interval is given by forecast+- z*std. dev. of error, where z is the standard normal value corresponding to the desired significance level. QUESTION: For question 1, it asks to forecast using three-month simple moving average. To do this, do we set the forcasted values for March, April,and May of Year 1 same as the actual values and start forecasting from June of Year 1 and continue? If this is the case, forecast for June will be (4566.4+6941.89+7047.23)/3 and for July will it be (6941.89+7047.23+6784.37)/3?