Question

In: Statistics and Probability

PLE sells small tractors worldwide, with sales regions including North America, South America, Europe, and the Pacific Rim.

PLEASE SHOW FORMULAS IN EXCEL
Case: Performance Lawn Equipment

PLE sells small tractors worldwide, with sales regions including North America, South America, Europe, and the Pacific Rim. Three years ago a new region was opened to serve China, where a booming market for small tractors has been established. PLE has always emphasized quality and considers the quality it builds into its products as its primary selling point. In the past 2 years, PLE has also emphasized the ease of use of their products.

Before digging into the details of operations, Elizabeth Burke wants to gain an overview of PLE’s overall business performance and market position by examining the information provided in the database.

3. For the EUR data only in the worksheet, Tractor Unit Sales, use the exponential smoothing method to develop the forecasting models with various values of α (α = 0.1, 0.2, …, 0.9), then calculate MAPE to identify the best value of α.

Tractor Unit Sales
Month	NA	SA	Eur	Pac	China	World
Jan-10	570	250	560	212	0	1592
Feb-10	611	270	600	230	0	1711
Mar-10	630	260	680	240	0	1810
Apr-10	684	270	650	263	0	1867
May-10	650	280	580	269	0	1779
Jun-10	600	270	590	280	0	1740
Jul-10	512	264	760	290	0	1826
Aug-10	500	280	645	270	0	1695
Sep-10	478	290	650	263	0	1681
Oct-10	455	280	670	258	0	1663
Nov-10	407	290	888	240	0	1825
Dec-10	360	280	850	230	0	1720
Jan-11	571	320	620	250	0	1761
Feb-11	650	350	760	275	0	2035
Mar-11	740	390	742	270	0	2142
Apr-11	840	440	780	280	0	2340
May-11	830	470	690	290	0	2280
Jun-11	760	490	721	300	0	2271
Jul-11	681	481	680	312	0	2154
Aug-11	670	460	711	305	0	2146
Sep-11	640	460	695	290	0	2085
Oct-11	620	440	650	260	0	1970
Nov-11	570	436	680	250	0	1936
Dec-11	533	420	657	240	0	1850
Jan-12	620	510	610	250	10	2000
Feb-12	792	590	680	250	12	2324
Mar-12	890	610	730	260	20	2510
Apr-12	960	600	820	270	22	2672
May-12	1040	620	810	290	20	2780
Jun-12	1032	640	807	310	24	2813
Jul-12	1006	590	760	340	20	2716
Aug-12	910	600	720	320	31	2581
Sep-12	803	670	660	313	30	2476
Oct-12	730	630	630	290	37	2317
Nov-12	699	710	603	280	32	2324
Dec-12	647	570	570	260	33	2080
Jan-13	730	650	500	287	35	2202
Feb-13	930	680	590	290	50	2540
Mar-13	1160	724	620	300	63	2867
Apr-13	1510	730	730	310	68	3348
May-13	1650	760	740	330	70	3550
Jun-13	1490	800	720	340	82	3432
Jul-13	1460	840	670	350	80	3400
Aug-13	1390	830	610	341	90	3261
Sep-13	1360	820	599	330	100	3209
Oct-13	1340	810	560	320	102	3132
Nov-13	1240	827	550	300	110	3027
Dec-13	1103	750	520	290	114	2777
Jan-14	1250	780	480	200	111	2821
Feb-14	1550	805	523	210	121	3209
Mar-14	1820	830	560	220	123	3553
Apr-14	2010	890	570	230	120	3820
May-14	2230	930	590	253	130	4133
Jun-14	2490	980	600	270	136	4476
Jul-14	2440	1002	580	280	134	4436
Aug-14	2334	970	570	250	132	4256
Sep-14	2190	960	550	230	137	4067
Oct-14	2080	930	530	220	130	3890
Nov-14	2050	920	517	190	139	3816
Dec-14	2004	902	490	190	131	3717

Expert Solution

Month	Eur	Forecast(0.1)	Error(F1)	Absolute Error(F1)	(Sum\|Ai-Fi\|/n)*100
10-Jan	560	#N/A	#N/A	#N/A	#N/A
10-Feb	600	560	-40	40	0.066666667
10-Mar	680	564	-116	116	0.170588235
10-Apr	650	575.6	-74.4	74.4	0.114461538
10-May	580	583.04	3.04	3.04	0.005241379
10-Jun	590	582.736	-7.264	7.264	0.012311864
10-Jul	760	583.4624	-176.5376	176.5376	0.232286316
10-Aug	645	601.11616	-43.88384	43.88384	0.068036961
10-Sep	650	605.504544	-44.495456	44.495456	0.068454548
10-Oct	670	609.9540896	-60.0459104	60.0459104	0.089620762
10-Nov	888	615.9586806	-272.0413194	272.0413194	0.306352837
10-Dec	850	643.1628126	-206.8371874	206.8371874	0.243337868
11-Jan	620	663.8465313	43.84653132	43.84653132	0.070720212
11-Feb	760	659.4618782	-100.5381218	100.5381218	0.132287002
11-Mar	742	669.5156904	-72.48430963	72.48430963	0.097687749
11-Apr	780	676.7641213	-103.2358787	103.2358787	0.132353691
11-May	690	687.0877092	-2.912290802	2.912290802	0.004220711
11-Jun	721	687.3789383	-33.62106172	33.62106172	0.046631154
11-Jul	680	690.7410445	10.74104445	10.74104445	0.015795654
11-Aug	711	689.66694	-21.33305999	21.33305999	0.030004304
11-Sep	695	691.800246	-3.199753995	3.199753995	0.004603963
11-Oct	650	692.1202214	42.1202214	42.1202214	0.064800341
11-Nov	680	687.9081993	7.908199264	7.908199264	0.011629705
11-Dec	657	687.1173793	30.11737934	30.11737934	0.04584076
12-Jan	610	684.1056414	74.1056414	74.1056414	0.121484658
12-Feb	680	676.6950773	-3.304922737	3.304922737	0.00486018
12-Mar	730	677.0255695	-52.97443046	52.97443046	0.072567713
12-Apr	820	682.3230126	-137.6769874	137.6769874	0.167898765
12-May	810	696.0907113	-113.9092887	113.9092887	0.140628751
12-Jun	807	707.4816402	-99.51835981	99.51835981	0.123318909
12-Jul	760	717.4334762	-42.56652383	42.56652383	0.056008584
12-Aug	720	721.6901286	1.690128556	1.690128556	0.002347401
12-Sep	660	721.5211157	61.5211157	61.5211157	0.093213812
12-Oct	630	715.3690041	85.36900413	85.36900413	0.135506356
12-Nov	603	706.8321037	103.8321037	103.8321037	0.172192543
12-Dec	570	696.4488933	126.4488933	126.4488933	0.221840164
13-Jan	500	683.804004	183.804004	183.804004	0.367608008
13-Feb	590	665.4236036	75.42360361	75.42360361	0.127836616
13-Mar	620	657.8812432	37.88124325	37.88124325	0.061098779
13-Apr	730	654.0931189	-75.90688108	75.90688108	0.103982029
13-May	740	661.683807	-78.31619297	78.31619297	0.105832693
13-Jun	720	669.5154263	-50.48457367	50.48457367	0.070117463
13-Jul	670	674.5638837	4.563883696	4.563883696	0.006811767
13-Aug	610	674.1074953	64.10749533	64.10749533	0.105094255
13-Sep	599	667.6967458	68.69674579	68.69674579	0.114685719
13-Oct	560	660.8270712	100.8270712	100.8270712	0.180048341
13-Nov	550	650.7443641	100.7443641	100.7443641	0.183171571
13-Dec	520	640.6699277	120.6699277	120.6699277	0.232057553
14-Jan	480	628.6029349	148.6029349	148.6029349	0.309589448
14-Feb	523	613.7426414	90.74264142	90.74264142	0.173504095
14-Mar	560	604.6683773	44.66837728	44.66837728	0.079764959
14-Apr	570	600.2015396	30.20153955	30.20153955	0.052985157
14-May	590	597.1813856	7.181385598	7.181385598	0.01217184
14-Jun	600	596.463247	-3.536752962	3.536752962	0.005894588
14-Jul	580	596.8169223	16.81692233	16.81692233	0.028994694
14-Aug	570	595.1352301	25.1352301	25.1352301	0.044096895
14-Sep	550	592.6217071	42.62170709	42.62170709	0.077494013
14-Oct	530	588.3595364	58.35953638	58.35953638	0.110112333
14-Nov	517	582.5235827	65.52358274	65.52358274	0.126738071
14-Dec	490	575.9712245	85.97122447	85.97122447	0.175451479
		567.374102
				Total=	6.200944423



		MAPE	10.16548266

The entire table was copied and pasted to excel.As the question demnads answer about the column EUR cell, we go on with deleting other columns.Now we have values of the variable EUR along with the tie series data In time column.Now we need to apply Exponential smoothening method over the values of EUR.Go to DATA>Data Analysis Tool Pack(If there isn' any dat aanlysis toool pack install it in excel)>Click exponentiaal smoothening>Click the range of the variable (B2-B61) in the inpur range.Place 1-alpha in place of damping facto.Suppose alpha=0.1,1-alpha=0.9>click the checkbox chart>click ok.

You will get the exponential smoothjening forecast for alpha 0.1.

In next column execute the absolute error.

apply the formula (=ABS(B2-C2)) and get the absolute error for first valure of EUR.To repeat the same formul;a for rest of the values,double click on the the cell and drag down till 61th row.

Now to calculate MAPE we need (Sum(ABS(actual value-forecast value))/n)*100

We have the ABS(actual value-forecast value) part. In the next column divide this with actual value in every row.

Apply autosum over the obtained values.

To get MAPE divide it by n multiply it by 100.

repeat the same for various values of alpha.

orchestra answered 1 year ago

Young men in North America and Europe, but not in Asia, tend to think they need...

Young men in North America and Europe, but not in Asia, tend to think they need more muscle to be attractive. One study presented 200200 young American men with 100100 images of men with various levels of muscle. Researchers measure level of muscle in kilograms per square meter (kg/m2kg/m2) of fat‑free body mass. Typical young men have about 20 kg/m2.20 kg/m2. Each subject chose two images, one that represented his own level of body muscle and one that he thought...

The continents of Europe (or more exactly, Eurasia) and North America are separating at a speed...

The continents of Europe (or more exactly, Eurasia) and North America are separating at a speed of about 3 cm per year. a) Assuming they were once together and have moved at a constant speed, estimate roughly how long has it taken the continents to reach their current separation (in years)? b) Estimate roughly the kinetic energy of North America moving at 3 cm per year (in erg or J).

How has the African diaspora been critical to the economic development of North America, South America,...

How has the African diaspora been critical to the economic development of North America, South America, and the Caribbean

A chemical manufacturer is setting up capacity in Europe and North America for the next three...

A chemical manufacturer is setting up capacity in Europe and North America for the next three years. The annual demand for each market is 1 million Kilograms (kg) and is likely to stay at that level. The two choices under consideration are building 2 million units of capacity in North America (Option 1) or building 1 million units of capacity in each of the two locations (Option2). Building two plants will insure an additional one time cost of 1.5 million....

In North America and Europe, as countries developed from the late 19th to the late 20th...

In North America and Europe, as countries developed from the late 19th to the late 20th century, social insurance (public pension) schemes, unemployment benefits, and publicly provided health care and welfare schemes became more pervasive. Over that period, the extended family was replaced by the nuclear family. Use the concepts of economies of scope and path dependency to explain why this happened and why the transformation took several generations.

If the states in North America, Western Europe, and East Asia all were to meet the...

If the states in North America, Western Europe, and East Asia all were to meet the target of providing 0.7 percent of GNP in foreign assistance, what might the effects be? How much additional aid would be made available? To whom would it likely go? What effects might it have on the recipient states and on economic development overall?

You are the director of international operations for North and South America for Lenovo. In 2015...

You are the director of international operations for North and South America for Lenovo. In 2015 you developed a five-year marketing plan to aggressively market personal computers in Canada, Mexico and Brazil. A key element of your plan called for meeting the competitive prices of HP and local manufacturers every step of the way. In addition you planned to spend heavily on marketing. Your yearly budget for marketing in the major target markets was set as follows: Canada ...

An economy consists of two regions, the North and the South. The short-run elasticity of labor...

An economy consists of two regions, the North and the South. The short-run elasticity of labor demand in each region is -0.5. Labor supply is perfectly inelastic within both regions. The labor market is initially in an economywide equilibrium, with 600,000 people employed in the North and 400,000 people in the South at wage of $15 per hour. Suddenly, 20,000 people immigrate from abroad and initially settle in the South. They possessed the same skills as the native residents and...

Why did citizens of Europe and North America feel that life would never be the same...

Why did citizens of Europe and North America feel that life would never be the same after WWI? What events during the War and the post-War period gave rise to the feelings of bitterness and betrayal within the civilian populations against the governments of all the warring powers? How did these events and feelings lead to the development of nihilism in postwar society (the rejection of all religious and moral principles, in the belief that life is meaningless)—characterized by the...

Company X is setting up capacity in Europe and North America for the next three years....

Company X is setting up capacity in Europe and North America for the next three years. Annual demand in each market is 2 million kilograms (kg) and is likely to stay at that level. The two choices under consideration are building 4 million units of capacity in North America or building 2 million units of capacity in each of the two locations. Building two plants will incur an additional one-time cost of $2 million. The variable cost of production in...