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

Answer rating: 100% (QA)

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... View the full answer