Question
Question - Isto create forecasting models for the sales data of a company's different product families and regions. The company operates primarily in the United
Question - Isto create forecasting models for the sales data of a company's different product families and regions. The company operates primarily in the United States but also has sales in Europe, South America, and the Pacific Rim, including China. The sales data is presented in two tables, one for the Industry Mower product family and one for the Industry Tractor product family. The data includes monthly sales figures for each region from January 2017 to December 2022
Guideline - Use bestforecasting model that is:- For TREND - BASED models, the last 18 months of data should be used as validation data. The models should be optimized by adjusting their parameters.
For SIMPLE EXPONENTIAL SMOOTHING, the smoothing constant should be set to 0.05, and the first month's actual sales data should be rounded to the nearest hundred to seed the forecast column.
For MOVING AVERAGE, it shoulduse a three-month window. Seasonality indices are also provided to forecast demand by region.
Finally, the question asks to use Microsoft Excel and Analytic Solver to develop the forecasting models, evaluate the models, and create final forecasts for 2022 for each product family and region.
Tutors Answers - step by step of the solutions, excel sheet and the graph.
QUESTION
He focus of sales is on the eastern seaboard, California, the Southeast, and the south-central states, which have the greatest concentration of customers. Outside the United States, sales include a European market, a growing South American market, and developing markets in the Pacific Rim and China. The market is cyclical, but the different products and regions balance some of this, with just less than 30% of total sales in the spring and summer (in the United States), about 25% in the fall, and about 20% in the winter. Annual sales are approximately $180 million.
Use the following guidelines when creating forecasts:
Evaluate potential methods that align with your data.
Use the last 18 months as validation data with trend-based models.
Optimize parameters in trend-based models.
When using simple exponential smoothing, set = 0.05 and round the actual data for the first month to the nearest hundred to seed your forecast column.
For moving averages, use 3 months
Use the following table of yearly forecasted demand for seasonality indices
NORTH AMERICA | SOUTH AMERICA | EUROPE | PACIFIC | |
MOWERS | 95000 | 4500 | 11000 | 3700 |
INDUSTRY MOWERS | 87000 | 9000 | 255000 | 25000 |
INDUSTRY TRACTORS | 156000 | 43000 | 81000 | 16000 |
DATA - MOWER UNIT SALES
Month | NA | SA | Europe | Pacific | World |
Jan-17 | 6000 | 200 | 720 | 100 | 7020 |
Feb-17 | 7950 | 220 | 990 | 120 | 9280 |
Mar-17 | 8100 | 250 | 1320 | 110 | 9780 |
Apr-17 | 9050 | 280 | 1650 | 120 | 11100 |
May-17 | 9900 | 310 | 1590 | 130 | 11930 |
Jun-17 | 10200 | 300 | 1620 | 120 | 12240 |
Jul-17 | 8730 | 280 | 1590 | 140 | 10740 |
Aug-17 | 8140 | 250 | 1560 | 130 | 10080 |
Sep-17 | 6480 | 230 | 1590 | 130 | 8430 |
Oct-17 | 5990 | 220 | 1320 | 120 | 7650 |
Nov-17 | 5320 | 210 | 990 | 130 | 6650 |
Dec-17 | 4640 | 180 | 660 | 140 | 5620 |
Jan-18 | 5980 | 210 | 690 | 140 | 7020 |
Feb-18 | 7620 | 240 | 1020 | 150 | 9030 |
Mar-18 | 8370 | 250 | 1290 | 140 | 10050 |
Apr-18 | 8830 | 290 | 1620 | 150 | 10890 |
May-18 | 9310 | 330 | 1650 | 130 | 11420 |
Jun-18 | 10230 | 310 | 1590 | 140 | 12270 |
Jul-18 | 8720 | 290 | 1560 | 150 | 10720 |
Aug-18 | 7710 | 270 | 1530 | 140 | 9650 |
Sep-18 | 6320 | 250 | 1590 | 150 | 8310 |
Oct-18 | 5840 | 250 | 1260 | 160 | 7510 |
Nov-18 | 4960 | 240 | 900 | 150 | 6250 |
Dec-18 | 4350 | 210 | 660 | 150 | 5370 |
Jan-19 | 6020 | 220 | 570 | 160 | 6970 |
Feb-19 | 7920 | 250 | 840 | 150 | 9160 |
Mar-19 | 8430 | 270 | 1110 | 160 | 9970 |
DATA- INDUSTRY MOWER TOTAL SALES
Month | NA | SA | Eur | Pac | World |
Jan-17 | 60000 | 571 | 13091 | 1045 | 74662 |
Feb-17 | 77184 | 611 | 17679 | 1111 | 96585 |
Mar-17 | 77885 | 658 | 22759 | 1068 | 102369 |
Apr-17 | 86190 | 778 | 27966 | 1237 | 116171 |
May-17 | 96117 | 886 | 27895 | 1313 | 126210 |
Jun-17 | 97143 | 882 | 30566 | 1176 | 129768 |
Jul-17 | 84757 | 848 | 29444 | 1359 | 116409 |
Aug-17 | 79804 | 735 | 28364 | 1238 | 110141 |
Sep-17 | 64800 | 657 | 28393 | 1215 | 95065 |
Oct-17 | 59307 | 595 | 24444 | 1154 | 85500 |
Nov-17 | 52157 | 553 | 18000 | 1262 | 71972 |
Dec-17 | 45049 | 462 | 12453 | 1386 | 59349 |
Jan-18 | 58627 | 553 | 12778 | 1443 | 73401 |
Feb-18 | 76200 | 615 | 18214 | 1515 | 96545 |
Mar-18 | 82871 | 658 | 23889 | 1373 | 108791 |
Apr-18 | 84904 | 784 | 29455 | 1442 | 116584 |
May-18 | 93100 | 846 | 29464 | 1215 | 124625 |
Jun-18 | 93000 | 838 | 27414 | 1333 | 122585 |
Jul-18 | 83048 | 763 | 27368 | 1415 | 112594 |
Aug-18 | 74854 | 694 | 27321 | 1296 | 104164 |
Sep-18 | 60769 | 625 | 29444 | 1402 | 92241 |
Oct-18 | 55619 | 610 | 23774 | 1468 | 81470 |
Nov-18 | 48155 | 571 | 17308 | 1351 | 67386 |
Dec-18 | 42647 | 512 | 12941 | 1389 | 57489 |
Jan-19 | 57885 | 537 | 10962 | 1509 | 70892 |
Feb-19 | 77647 | 595 | 15273 | 1402 | 94917 |
Mar-19 | 81845 | 659 | 20556 | 1524 | 104583 |
Apr-19 | 86095 | 756 | 26786 | 1574 | 115211 |
May-19 | 91776 | 878 | 24828 | 1468 | 118949 |
Jun-19 | 100680 | 825 | 24737 | 1560 | 127801 |
Jul-19 | 86190 | 756 | 24828 | 1441 | 113216 |
Aug-19 | 71887 | 714 | 25179 | 1545 | 99325 |
Sep-19 | 60000 | 651 | 24545 | 1667 | 86863 |
Oct-19 | 55566 | 643 | 19286 | 1698 | 77193 |
Nov-19 | 50857 | 619 | 15273 | 1810 | 68558 |
Dec-19 | 42596 | 548 | 9107 | 1731 | 53982 |
Jan-20 | 58095 | 581 | 8571 | 1887 | 69135 |
Feb-20 | 75566 | 614 | 13158 | 1845 | 91182 |
Mar-20 | 80286 | 622 | 19655 | 1923 | 102486 |
Apr-20 | 85140 | 727 | 25179 | 1981 | 113027 |
May-20 | 90093 | 826 | 23103 | 1810 | 115832 |
Jun-20 | 95472 | 783 | 24286 | 1942 | 122482 |
Jul-20 | 87308 | 681 | 24737 | 1961 | 114686 |
Aug-20 | 74476 | 646 | 26607 | 2000 | 103729 |
Sep-20 | 61698 | 625 | 22982 | 2075 | 87381 |
Oct-20 | 57238 | 617 | 16897 | 2019 | 76771 |
Nov-20 | 50673 | 587 | 13750 | 2095 | 67105 |
Dec-20 | 51238 | 591 | 7818 | 2150 | 61797 |
Jan-21 | 59712 | 563 | 7547 | 1852 | 69673 |
Feb-21 | 77961 | 571 | 13889 | 1743 | 94165 |
Mar-21 | 83725 | 625 | 18302 | 1892 | 104544 |
Apr-21 | 90297 | 723 | 25192 | 2037 | 118250 |
May-21 | 91143 | 848 | 24706 | 1887 | 118583 |
Jun-21 | 99320 | 792 | 25306 | 1944 | 127363 |
Jul-21 | 93922 | 745 | 27083 | 2170 | 123919 |
Aug-21 | 73143 | 739 | 26042 | 2037 | 101961 |
Sep-21 | 66699 | 667 | 26304 | 2018 | 95688 |
Oct-21 | 56476 | 660 | 22558 | 2072 | 81766 |
Nov-21 | 51068 | 625 | 14773 | 2182 | 68648 |
Dec-21 | 46893 | 608 | 6977 | 2035 | 56510 |
DATA - INDUSTRY TRACTOR TOTAL SALES
Month | NA | SA | Eur | Pac | World |
Jan-17 | 8143 | 984 | 5091 | 987 | 15205 |
Feb-17 | 8592 | 1051 | 5310 | 1090 | 16042 |
Mar-17 | 8630 | 1016 | 6071 | 1127 | 16844 |
Apr-17 | 8947 | 1027 | 5856 | 1209 | 17039 |
May-17 | 8442 | 1057 | 5273 | 1221 | 15992 |
Jun-17 | 7500 | 1019 | 5315 | 1327 | 15161 |
Jul-17 | 6145 | 977 | 7170 | 1324 | 15616 |
Aug-17 | 5882 | 1057 | 5926 | 1268 | 14132 |
Sep-17 | 5595 | 1086 | 6075 | 1209 | 13965 |
Oct-17 | 5233 | 1045 | 6321 | 1168 | 13766 |
Nov-17 | 4494 | 1078 | 8381 | 1127 | 15080 |
Dec-17 | 3913 | 1029 | 7944 | 1085 | 13971 |
Jan-18 | 5938 | 1172 | 5688 | 1185 | 13983 |
Feb-18 | 6633 | 1273 | 7037 | 1286 | 16228 |
Mar-18 | 7327 | 1423 | 6981 | 1286 | 17017 |
Apr-18 | 8077 | 1612 | 7500 | 1346 | 18535 |
May-18 | 7830 | 1728 | 6571 | 1388 | 17517 |
Jun-18 | 7103 | 1815 | 6990 | 1449 | 17357 |
Jul-18 | 6239 | 1776 | 6667 | 1490 | 16172 |
Aug-18 | 6036 | 1685 | 6762 | 1449 | 15932 |
Sep-18 | 5664 | 1679 | 6635 | 1394 | 15371 |
Oct-18 | 5345 | 1618 | 6311 | 1256 | 14529 |
Nov-18 | 4831 | 1564 | 6476 | 1214 | 14084 |
Dec-18 | 4454 | 1522 | 6250 | 1171 | 13396 |
Jan-19 | 5299 | 1835 | 5922 | 1208 | 14264 |
Feb-19 | 6529 | 2115 | 6667 | 1214 | 16524 |
Mar-19 | 7120 | 2202 | 7228 | 1256 | 17806 |
Apr-19 | 7619 | 2151 | 8200 | 1311 | 19280 |
May-19 | 8387 | 2214 | 7941 | 1415 | 19957 |
Jun-19 | 8110 | 2278 | 7921 | 1520 | 19828 |
Jul-19 | 7752 | 2100 | 7677 | 1675 | 19203 |
Aug-19 | 6894 | 2128 | 7200 | 1584 | 17806 |
Sep-19 | 6015 | 2367 | 6735 | 1527 | 16644 |
Oct-19 | 5368 | 2211 | 6495 | 1422 | 15495 |
Nov-19 | 4964 | 2483 | 6061 | 1366 | 14873 |
Dec-19 | 4444 | 1986 | 5816 | 1262 | 13509 |
Jan-20 | 5000 | 2257 | 5051 | 1373 | 13680 |
Feb-20 | 6284 | 2353 | 6082 | 1436 | 16155 |
Mar-20 | 7785 | 2457 | 6327 | 1478 | 18046 |
Apr-20 | 9934 | 2517 | 7604 | 1512 | 21568 |
May-20 | 10645 | 2612 | 7789 | 1642 | 22688 |
Jun-20 | 9491 | 2749 | 7347 | 1667 | 21254 |
Jul-20 | 9182 | 2887 | 6979 | 1733 | 20781 |
Aug-20 | 8528 | 2833 | 6489 | 1700 | 19550 |
Sep-20 | 8293 | 2789 | 6316 | 1642 | 19039 |
Oct-20 | 8221 | 2765 | 5833 | 1576 | 18395 |
Nov-20 | 7470 | 2746 | 5789 | 1493 | 17498 |
Dec-20 | 6509 | 2534 | 5591 | 1450 | 16084 |
Jan-21 | 7267 | 2635 | 5106 | 1010 | 16019 |
Feb-21 | 8807 | 2703 | 5474 | 1045 | 18028 |
Mar-21 | 10168 | 2795 | 6022 | 1106 | 20089 |
Apr-21 | 11044 | 2997 | 6064 | 1150 | 21254 |
May-21 | 12120 | 3131 | 6344 | 1244 | 22839 |
Jun-21 | 13459 | 3311 | 6593 | 1357 | 24720 |
Jul-21 | 13048 | 3390 | 6304 | 1421 | 24164 |
Aug-21 | 12275 | 3277 | 6064 | 1263 | 22879 |
Sep-21 | 11347 | 3232 | 5789 | 1173 | 21542 |
Oct-21 | 10667 | 3131 | 5699 | 1128 | 20625 |
Nov-21 | 10459 | 3087 | 5604 | 974 | 20125 |
Dec-21 | 10082 | 3030 | 5444 | 979 | 19536 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started