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 should use 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

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 08, 2023

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

Is to 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 best forecasting 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 should use 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

★★★★★

3.47 Rating (154 Votes )

There are 3 Steps involved in it

Step: 1

To create forecasting models in Excel using Analytic Solver follow these steps 1 Open Excel Open Microsoft Excel and ensure that Analytic Solver is in... blur-text-image