excel 2

Forecasting

Redstone Foods M&M Sales Forecasting Tool
Data Section	2017 Total Sold (Cases)	2018 Total Sold (Cases)	2019 Total Sold (Cases)	2020 Total Sold (Cases)		2019 - 2020 Actual Growth	MODEL #1 2020 Forecast 3% Growth Over 2019 Only	MODEL #1 2020 Forecast Growth Over 2019 Only - Absolute Deviations	Squared Errors	Absolute Percent Error	MODEL #2 2020 Forecast 3-month Moving Average	MODEL #2 2020 Forecast 3-month Moving Average - Absolute Deviations	Squared Errors	Absolute Percent Error	MODEL #3 2020 Forecast 3-Month Weighted Moving Average	MODEL #3 2020 Forecast 3-Month Weighted Moving Average - Absolute Deviations	Squared Errors	Absolute Percent Error	MODEL #4 2020 Forecast Exp. Smoothing	MODEL #4 2020 Forecast Exp. Smoothing - Absolute Deviations	Squared Errors	Absolute Percent Error	MODEL #5 2020 Forecast Exp. Smoothing with Trend	MODEL #5 2020 Forecast Exp. Smoothing with Trend - Absolute Deviations	Squared Errors	Absolute Percent Error		Season Adjusment Factors
January	52598	57487	57342	58012		1.17%	59062	1050	1102500	1.81%	71352	13340	177955600	23.00%	71907	13895	193071025	23.95%	64148	6136	37650496	10.58%	65129	7117	50651689	12.27%		0.89
February	58908	59513	58236	59456		2.09%	59983	527	277729	0.89%	67689	8233	67782289	13.85%	65312	5856	34292736	9.85%	66534	7078	50098084	11.90%	68077	8621	74321641	14.50%		0.91
March	58800	62834	56843	57007		0.29%	58548	1541	2374681	2.70%	63473	6466	41809156	11.34%	61722	4715	22231225	8.27%	68459	11452	131148304	20.09%	70156	13149	172896201	23.07%		0.89
April	69056	75115	71343	59582		-16.49%	73483	13901	193237801	23.33%	58158	1424	2027776	2.39%	57943	1639	2686321	2.75%	65325	5743	32982049	9.64%	65089	5507	30327049	9.24%		1.11
May	64884	67409	65875	58209		-11.64%	67851	9642	92968164	16.56%	58682	473	223729	0.81%	58784	575	330625	0.99%	63564	5355	28676025	9.20%	62719	4510	20340100	7.75%		1.03
June	55465	55863	59351	52850		-10.95%	61132	8282	68591524	15.67%	58266	5416	29333056	10.25%	58381	5531	30591961	10.47%	61597	8747	76510009	16.55%	60303	7453	55547209	14.10%		0.93
July	59143	61869	60050	53021		-11.71%	61852	8831	77986561	16.66%	56880	3859	14891881	7.28%	55804	2783	7745089	5.25%	60993	7972	63552784	15.04%	59974	6953	48344209	13.11%		0.94
August	61171	62850	63259	55895		-11.64%	65157	9262	85784644	16.57%	54693	1202	1444804	2.15%	54007	1888	3564544	3.38%	60158	4263	18173169	7.63%	59212	3317	11002489	5.93%		0.99
September	64173	63224	62518	56022		-10.39%	64394	8372	70090384	14.94%	53922	2100	4410000	3.75%	54424	1598	2553604	2.85%	57965	1943	3775249	3.47%	56521	499	249001	0.89%		0.98
October	68265	67351	69002			--	71072				54979				55384				56482				55022					1.08
November	72885	75195	72102			--	74265				ERROR:#N/A				ERROR:#N/A				ERROR:#N/A				ERROR:#N/A					1.10
December	69865	72488	72952			--	75141				ERROR:#N/A				ERROR:#N/A				ERROR:#N/A				ERROR:#N/A					1.14
				Forecast Accuracy			MODEL #1				MODEL #2	MODEL #1	MODEL #1	MODEL #1	MODEL #3	MODEL #1	MODEL #1	MODEL #1	MODEL #4				MODEL #5
Apply Seasonality Adjustment	NO			2020 YTD Accuracy	MAD		6823				4724				4276				6521				6347
Forecast Growth Rate (Target):	3.00%				MSE		65823776				37764255				33007459				49174019				51519954
Forecast Weights:	20%	30%	50%		MAPE		12.13%				8.31%				7.53%				11.57%				11.21%
YTD Aggregate Growth Rate:	-8.07%
				1st Quarter 2020 Accuracy	MAD		1039				9346				8155				8222				9629
					MSE		1251637				95849015				83198329				72965628				99289844
					MAPE		1.80%				16.06%				14.02%				14.19%				16.61%
				2nd Quarter 2020 Accuracy	MAD		10608				2438				2582				6615				5823
					MSE		118265830				10528187				11202969				46056028				35404786
					MAPE		18.52%				4.48%				4.73%				11.80%				10.36%
				3rd Quarter 2020 Accuracy	MAD		8822				2387				2090				4726				3590
					MSE		77953863				6915562				4621079				28500401				19865233
					MAPE		16.06%				4.39%				3.83%				8.71%				6.65%

M&M Sales Forecasts (Cases)

2019 Total Sold (Cases) January February March April May June July August September October November December 57342 58236 56843 71343 65875 59351 60050 63259 62518 69002 72102 72952 2020 Total Sold (Cases) 58012 59456 57007 59582 58209 52850 53021 55895 56022 MODEL #1 2020 Forecast 3% Growth Over 2019 Only 59062 59983 58548 73483 67851 61132 61852 65157 64394 71072 74265 75141 MODEL #2 2020 Forecast 3-month Moving Average 71352 67689 63473 58158 58682 58266 56880 54693 53922 54979 #N/A #N/A MODEL #3 2020 Forecast 3-Month Weighted Moving Average 71907 65312 61722 57943 58784 58381 55804 54007 54424 55384 #N/A #N/A MODEL #4 2020 Forecast Exp. Smoothing 64148 66534 68459 65325 63564 61597 60993 60158 57965 56482 #N/A #N/A MODEL #5 2020 Forecast Exp. Smoothing with Trend 65129 68077 70156 65089 62719 60303 59974 59212 56521 55022 #N/A #N/A

Month

Cases Forecast

M&M Sales Trends (Cases Sold)

2019 Total Sold (Cases) January February March April May June July August September October November December 57342 58236 56843 71343 65875 59351 60050 63259 62518 69002 72102 72952 2020 Total Sold (Cases) 58012 59456 57007 59582 58209 52850 53021 55895 56022 2018 Total Sold (Cases) 57487 59513 62834 75115 67409 55863 61869 62850 63224 67351 75195 72488 2017 Total Sold (Cases) 52598 58908 58800 69056 64884 55465 59143 61171 64173 68265 72885 69865

Month

Cases Sold

Calcs

January	57342			Yes	January	Jan-18	Jan-19
February	58236			No	February	Feb-18	Feb-19
March	56843				March	Mar-18	Mar-19
April	71343	57342			April	Apr-18	Apr-19
May	65875	57610.2	107.88		May	May-18	May-19
June	59351	57380.04	-27.336		June	Jun-18	Jun-19
July	60050	61568.928	1659.1536		July	Jul-18	Jul-19
August	63259	62860.7496	1512.2208		August	Aug-18	Aug-19
September	62518	61807.82472	486.162528		September	Sep-18	Sep-19
October	69002	61280.477304	80.7585504		October	Oct-18	Oct-19
November	72102	61874.0341128	285.87785376		November	Nov-18	Nov-19
December	72952	62067.22387896	248.80261872		December	Dec-18	Dec-19
January	58012	64147.656715272	981.4547057568
February	59456	66533.9597006904	1543.3940176214
March	57007	68459.3717904833	1696.20124649
April	59582	65325.1602533383	-235.963866964
May	58209	63564.4121773368	-845.877550579
June	52850	61597.1885241358	-1294.4159916278
July	53021	60992.631966895	-1018.472217873
August	55895	60157.5423768265	-945.1191667512
September	56022	57965.2796637786	-1443.9765852699
October	0	56481.995764645	-1459.6995108154
November	0	56305.8970352515	-946.2591982466
December	0	56220.727924676	-601.8231631782

Forecast Accuracy - MAPE

MODEL #1 2020 YTD 1st Quarter 2nd Quarter 3rd Quarter 0.12126194746809056 1.7998389687442006E-2 0.18522028952045991 0.16056716319636974 MODEL #2 2020 YTD 1st Quarter 2nd Quarter 3rd Quarter 8.3125115689548279E-2 0.16061641275100907 4.4834813348549087E-2 4.3924120969086676E-2 MODEL #3 2020 YTD 1st Quarter 2nd Quarter 3rd Quarter 7.5283722890961358E-2 0.14024051799641754 4.7347062824362997E-2 3.8263587852103556E-2 MODEL #4 2020 YTD 1st Quarter 2nd Quarter 3rd Quarter 0.11565573066088092 0.1419016157546438 0.11796346782790003 8.7102108400098896E-2 MODEL #5 2020 YTD 1st Quarter 2nd Quarter 3rd Quarter 0.11207235075883676 0.16611179341177121 0.10364281013476762 6.6462448729971452E-2

Time Period

MAPE