Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

International Marketing And Export Management

Authors: Gerald Albaum , Alexander Josiassen , Edwin Duerr

8th Edition

1292016922, 978-1292016924

More Books

Students also viewed these General Management questions

Question

Under what conditions is the MantelHaenszel test appropriate?

Answered: 1 week ago