Njosh
Problem Guidelines
Because using spreadsheet software is a basic business skill, the assignments are intended to give you practice in structuring problems as well as simply finding the answers. All problems must be solved using Excel formulas to receive credit. If, for example, you solve a problem on paper and then copy the results to Excel, you will receive no credit for that problem.
Submission Guidelines
All assignments are to be submitted using the Blackboard site for your lab section. Assignments should be uploaded using the upload link in the Assignment folder, not the digital dropbox. It is important that all homework files include the student’s NetID, so they can be easily identified and sorted. The file containing homework assignment 2 submitted by the student with NetID rbaker2 should be named rbaker2hw2.
Questions
Burlington has tracked daily sales of coats for the three locations below:
|
Date |
Chicago |
Seasonal Relatives for Chicago Sales |
California |
New York |
|
1/4/18 |
152 |
0.3 |
640 |
456 |
|
1/5/18 |
195 |
0.4 |
586 |
650 |
|
1/6/18 |
653 |
1.5 |
420 |
543 |
|
1/7/18 |
187 |
0.5 |
645 |
632 |
|
1/8/18 |
240 |
0.6 |
507 |
546 |
|
1/9/18 |
207 |
0.8 |
623 |
531 |
|
1/10/18 |
311 |
0.9 |
511 |
544 |
|
1/11/18 |
373 |
1.2 |
500 |
639 |
|
1/12/18 |
225 |
0.5 |
576 |
570 |
|
1/13/18 |
276 |
0.3 |
540 |
544 |
|
1/14/18 |
475 |
0.7 |
475 |
674 |
|
1/15/18 |
755 |
1.1 |
645 |
711 |
|
1/16/18 |
732 |
1.4 |
534 |
594 |
|
1/17/18 |
975 |
2.2 |
511 |
564 |
|
1/18/18 |
170 |
0.3 |
650 |
693 |
|
1/19/18 |
203 |
0.7 |
699 |
645 |
|
1/20/18 |
288 |
0.8 |
580 |
570 |
|
1/21/18 |
378 |
0.9 |
520 |
456 |
|
1/22/18 |
400 |
1.3 |
546 |
609 |
|
1/23/18 |
798 |
1.6 |
576 |
585 |
|
1/24/18 |
1111 |
1.9 |
649 |
609 |
|
1/25/18 |
176 |
0.5 |
657 |
480 |
|
1/26/18 |
213 |
0.6 |
600 |
544 |
|
1/27/18 |
240 |
0.9 |
703 |
560 |
|
1/28/18 |
311 |
0.7 |
657 |
476 |
|
1/29/18 |
456 |
1 |
546 |
656 |
|
1/30/18 |
|
|
|
|
Question Set 1. Using only the Chicago sales data, generate the following sales forecasts:
1. Naïve forecasts (using the deseasonalized sales data) for January 5th through January 30th. You will need to adjust the sales data to remove the seasonality effects. (6pts)
2. Four-day moving average forecasts for January 8th through January 30th. (3pts)
3. Twelve-day moving average forecasts for January 16th through January 30th. (3pts)
Note that, for example, you will not be able to make a four-day moving average forecast for January 7th since four previous sales figures are required.
Question Set 2. Using only the California sales data:
1. Forecast sales for January 7th through January 30th using a three-day weighted moving average. (5pts)
The weights are:
|
Day |
Weight |
|
1 day previous |
0.50 |
|
2 days previous |
0.35 |
|
3 days previous |
0.15 |
2. Find the mean absolute deviation of the sales forecasts for January 7th through January 29th. You should not include the forecast for January 30th since there is no corresponding sales figure for that date. (3pts)
3. Find the mean squared error of the sales forecasts, again for January 7th through January 29th. (4pts)
4. Create a scatter scatter-chart for the three-day weighted moving average. Label your chart axes properly. (2 pts)
Question Set 3. Using only the New York sales data:
1. Forecast sales for January 5th through January 30th using exponential smoothing with alpha set to 0.6. Assume the sales forecast for January 4th was 456. (6pts)
2. Using your forecasts from Q3.1, find the mean absolute deviation of the sales forecasts for January 5th through January 29th. Again, you should not include the forecast for January 30h since there is no corresponding sales figure for that date. (4pts)
3. Using your forecasts from Q3.1, find the mean squared error of the sales forecasts for January 5th through January 29th. (4pts)
Reminder: Check your work to make sure you have only included forecasts and accuracy measures for the exact date ranges specified. Also, please copy these tables into your excel file while solving.