For this assignment, please submit one Excel file. Use a separate worksheet for
ID: 345575 • Letter: F
Question
For this assignment, please submit one Excel file. Use a separate worksheet for each question set and label the worksheets. To rename a worksheet, right-click the worksheet tab located at the bottom of your current sheet, and use the rename option. Label the tabs as “Q Set 1”, “Q Set 2”, and “Q Set 3”, respectively. Be sure to label all results clearly.
.
Questions
Dairy Queen has tracked daily sales of Blizzards for the three locations below:
Date
Union Station
Lincoln Park
Lakeview
8/31/2015
526
485
463
9/1/2015
499
531
537
9/2/2015
461
540
482
9/3/2015
438
586
519
9/4/2015
436
484
481
9/5/2015
420
632
550
9/6/2015
381
562
496
9/7/2015
390
495
559
9/8/2015
347
607
546
9/9/2015
312
490
566
9/10/2015
285
511
624
9/11/2015
285
611
569
9/12/2015
340
543
546
9/13/2015
333
625
616
9/14/2015
279
563
630
9/15/2015
242
546
572
9/16/2015
236
520
584
9/17/2015
247
544
567
9/18/2015
175
676
593
9/19/2015
142
641
590
9/20/2015
166
566
575
9/21/2015
164
692
585
9/22/2015
185
585
660
9/23/2015
152
639
642
9/24/2015
169
674
690
9/25/2015
202
709
659
Question Set 1. Using only the Union Station sales data, generate the following sales forecasts:
1. Naive forecasts for September 1st through September 26th. (4pts)
2. Two-day moving average forecasts for September 2nd through September 26th. (4pts)
3. Five-day moving average forecasts for September 5th through September 26th. (4pts)
Note that, for example, you will not be able to make a five-day moving average forecast for September 4th since five previous sales figures are required.
Question Set 2. Using only the Lincoln Park sales data:
1. Forecast sales for September 5th through September 26th using a five-day weighted moving average. (6pts)
The weights are:
Day
Weight
1 day previous
0.30
2 days previous
0.25
3 days previous
0.20
4 days previous
0.15
5 days previous
0.10
2. Find the mean absolute deviation of the sales forecasts for September 5th through September 25th. You should not include the forecast for September 26th since there is no corresponding sales figure for that date. (4pts)
3. Find the mean squared error of the sales forecasts, again for September 5th through September 25th. (4pts)
Question Set 3. Using only the Lakeview sales data:
1. Forecast sales for September 1st through September 26th using exponential smoothing with alpha set to 0.6. Assume the sales forecast for August 31st was 463. (6pts)
2. Using your forecasts from Q3.1, find the mean absolute deviation of the sales forecasts for September 1st through September 25th. Again, you should not include the forecast for September 26th 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 September 1st through September 25th. (4pts)
Reminder: Check your work to make sure you have only included forecasts and accuracy measures for the exact date ranges specified.
Date
Union Station
Lincoln Park
Lakeview
8/31/2015
526
485
463
9/1/2015
499
531
537
9/2/2015
461
540
482
9/3/2015
438
586
519
9/4/2015
436
484
481
9/5/2015
420
632
550
9/6/2015
381
562
496
9/7/2015
390
495
559
9/8/2015
347
607
546
9/9/2015
312
490
566
9/10/2015
285
511
624
9/11/2015
285
611
569
9/12/2015
340
543
546
9/13/2015
333
625
616
9/14/2015
279
563
630
9/15/2015
242
546
572
9/16/2015
236
520
584
9/17/2015
247
544
567
9/18/2015
175
676
593
9/19/2015
142
641
590
9/20/2015
166
566
575
9/21/2015
164
692
585
9/22/2015
185
585
660
9/23/2015
152
639
642
9/24/2015
169
674
690
9/25/2015
202
709
659
Explanation / Answer
1.
Formula Ft=At-1 Ft = Average(At-1,At-2) Ft = Average(At-1...At-5) Ans 1.1 Ans 1.2 Ans 1.3 Forecast Naïve Forecast two day MA Forecast five day MA Date Union Station Lincoln Park Lakeview Union Station Lincoln Park Lakeview Union Station Lincoln Park Lakeview Union Station Lincoln Park Lakeview 8/31/2015 526 485 463 09-01-2015 499 531 537 526 485 463 09-02-2015 461 540 482 499 531 537 512.5 508 500 09-03-2015 438 586 519 461 540 482 480 535.5 509.5 09-04-2015 436 484 481 438 586 519 449.5 563 500.5 09-05-2015 420 632 550 436 484 481 437 535 500 472 525.2 496.4 09-06-2015 381 562 496 420 632 550 428 558 515.5 450.8 554.6 513.8 09-07-2015 390 495 559 381 562 496 400.5 597 523 427.2 560.8 505.6 09-08-2015 347 607 546 390 495 559 385.5 528.5 527.5 413 551.8 521 09-09-2015 312 490 566 347 607 546 368.5 551 552.5 394.8 556 526.4 09-10-2015 285 511 624 312 490 566 329.5 548.5 556 370 557.2 543.4 09-11-2015 285 611 569 285 511 624 298.5 500.5 595 343 533 558.2 09-12-2015 340 543 546 285 611 569 285 561 596.5 323.8 542.8 572.8 9/13/2015 333 625 616 340 543 546 312.5 577 557.5 313.8 552.4 570.2 9/14/2015 279 563 630 333 625 616 336.5 584 581 311 556 584.2 9/15/2015 242 546 572 279 563 630 306 594 623 304.4 570.6 597 9/16/2015 236 520 584 242 546 572 260.5 554.5 601 295.8 577.6 586.6 9/17/2015 247 544 567 236 520 584 239 533 578 286 559.4 589.6 9/18/2015 175 676 593 247 544 567 241.5 532 575.5 267.4 559.6 593.8 9/19/2015 142 641 590 175 676 593 211 610 580 235.8 569.8 589.2 9/20/2015 166 566 575 142 641 590 158.5 658.5 591.5 208.4 585.4 581.2 9/21/2015 164 692 585 166 566 575 154 603.5 582.5 193.2 589.4 581.8 9/22/2015 185 585 660 164 692 585 165 629 580 178.8 623.8 582 9/23/2015 152 639 642 185 585 660 174.5 638.5 622.5 166.4 632 600.6 9/24/2015 169 674 690 152 639 642 168.5 612 651 161.8 624.6 610.4 9/25/2015 202 709 659 169 674 690 160.5 656.5 666 167.2 631.2 630.4 9/26/2016 202 709 659 185.5 691.5 674.5 174.4 659.8 647.2Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.