Forecasting

Moving Average Forecasting – Avocados!

Assignment

After viewing the video in Module #3, “Time Series Forecasting: Introduction,” you should be able to complete a spreadsheet that is similar to the example used by Mr. Macarty in the video. As part of this assignment, you will find an Excel file for you to complete. You should be able to complete all of the cells that are not locked (i.e., all of the cells highlighted in yellow in the diagram below.

Assignment Part #

1

– Complete the following:

· Conventional avocado price 5-week moving average forecast for 2/6/2020 through 12/31/2020

· Organic avocado price 5-week moving average forecast for 2/6/2020 through 12/31/2020

· Conventional avocado sales 5-week moving average forecast for 2/6/2020 through 12/31/2020

· Organic avocado sales 5-week moving average forecast for 2/6/2020 through 12/31/2020

· Weekly Forecast Error calculations for all forecasts from 2/6/2020 through 12/24/2020

· Conventional avocado price 5-week moving average forecast in column H

· Organic avocado price 5-week moving average forecast in column I

· Conventional avocados sold 5-week moving average forecast in column N

· Organic price 5-week moving average forecast in column O

· Conventional avocado price 5-week moving average forecast MAPE in cell H4 (MAPE measurements need to be formatted as percentages).

· Organic avocado price 5-week moving average forecast MAPE in cell I4 (MAPE measurements need to be formatted as percentages).

· Conventional avocados sold 5-week moving average forecast MAPE in cell N4 (MAPE measurements need to be formatted as percentages).

· Organic avocados sold 5-week moving average forecast MAPE in cell O4 (MAPE measurements need to be formatted as percentages).

Assignment Part #2 – Complete the following:

Create and insert three-line graphs as indicated below

Graph Weekly Conventional and Organic Avocado Prices (2020)

· Data line for 2020 Conventional Weekly Avocado Price

· Data line for 2020 Organic Weekly Avocado Price

· Chart Title

· x-Axis with week number indicated

· y-Axis with avocado price indicated

· x-Axis Title

· y-Axis Title

· Legend

Graph of 2020 weekly avocado prices and sales for conventional avocados

· Data line for 2020 Conventional Weekly Avocado Sales on the primary axis

· Data line for 2020 Conventional Weekly Avocado Prices on the secondary axis

· Chart Title
· x-Axis with week number indicated

· y-Axis with avocado sales indicated

· x-Axis Title
· y-Axis Title
· Legend

Graph of 2020 weekly avocado prices and sales for organic avocados

· Data line for 2020 Organic Weekly Avocado Sales on the primary axis

· Data line for 2020 Organic Weekly Avocado Prices on the secondary axis

· Chart Title
· x-Axis with week number indicated
· y-Axis with avocado price indicated
· x-Axis Title
· y-Axis Title
· Legend

The graphs should appear as in the examples below. Do not copy these images into your Excel document. Doing so will result in you receiving zero credit for the graphs in your final grade for this assignment.

Assignment Part #3 – Answer the questions on the second tab of the spreadsheet:

Notes
: Submit your Excel document to the proper dropbox.

Grading
: Assignment is worth 80 points as follows:

· Correct data calculated in respective cells 48 points

· Correct graphs and graph elements 24 points

· Questions on Question Tab 8 points

TOTAL 80 POINTS

1

2

>DataSet

20

Avocado Price and Sales Forecasting

s

-Week Moving Avg Forecast

Date

Conventional Organic Conventional Forecast Organic Forecast Conventional MAPE Organic MAPE

,074.

1

.

Nashville 2

1.53

5,094.

5.75

Nashville 3

/20

,9

.74

Nashville 4

/20

9.

.

Nashville 5

/20

,

2.73

Nashville 6

7,7

.70

Nashville 7

0.66

Nashville 8

5,664.23

Nashville 9

0.68

6,2

.59

,920.78

Nashville 10

.

.

Nashville

/20

Nashville 12

,023.12

Nashville 13

.88

Nashville 14

8.76

6.78

Nashville

1.53

.95

Nashville 16

Nashville 17

Nashville 18

1.14

Nashville 19

0.84

8,796.80

Nashville 20

0.87

Nashville 21

0.99

Nashville 22

1.16

Nashville 23

1.14

Nashville 24

.49

Nashville 25

1.16

Nashville 26

Nashville 27

0.96 1.20

Nashville 28

0.99

Nashville 29

1.54

Nashville 30

1.43

Nashville

1.14

Nashville 32

1.13 1.54

Nashville 33

Nashville 34

1.07 1.84

Nashville 35

5.49

Nashville 36

Nashville 37

Nashville 38

Nashville 39

Nashville 40

2.22

Nashville 41

2.17

Nashville 42

Nashville 43

Nashville 44

1.14 1.76

Nashville 45

1.02

Nashville 46

1.07

Nashville 47

1.02 1.61

Nashville 48

1.57

Nashville 49

Nashville 50

0.90 1.59

Nashville 51

0.91

Nashville 52

Nashville 53

																																																																																																																																																																																																																																																20
2020	Date	Weekly Avg. Avocado Price 2020	Weekly Avg. Avocado Price	5	Weekly Avg. Avocado Price 5-Week Forecast Errors	Weekly Avocados Sold 2020	Weekly Avocados Sold 5-Week Moving Avg Forecast	Weekly Avocados Sold 5-Week Forecast Errors
Region	Week #		Conventional		Organic		Conventional Forecast		Organic Forecast		Conventional MAPE		Organic MAPE
																																																				Nashville	1	1/2/20	0.	7	3			1.	53	2	4	9	8	3,7	49	48
1/9/20		0.84		25	17	3,1	6
1/	16	0.89			1.54	2	51	14	2,962.	13
1/	23		0.87	0.86	251,	18	52	6,6	27	34	Create graph of 2020 weekly avocado prices for conventional and organic avocados here
1/	30	0.93	1.	41	2	38	39	3,254.87
2/6/20		0.68			1.16		37	19	4,305.56
2/13/20		0.66	271,393.	50	13,802.00
2/20/20	0.82	0.67		22	13,534.67
2/27/20	0.88		21	26		10
3/5/20	0.60	0.51	379,3	24	44	17,1	35	45
11	3/	12	0.75	0.61	261,830.38	11,558.79
3/19/20		0.96	0.92	2	28	5,986.	47
3/26/20		0.91	0.77	276,7	32	9,755.34
4/2/20	0.95	1.17	281,	43	6,	33
15	4/9/20	1.03	251,089.69	5,3	40
4/16/20	1.09					1.14	230,566.12	8,448.33
4/23/20	1.10		1.43	226,088.30	7,063.97
4/30/20			0.99	286,32	2.22	11,100.24
5/7/20	1.22		36	6,216.76
5/14/20			1.02	225,323.91	16,265.74
5/21/20		1.20	259,15	1.84	9,281.83
5/28/20	0.97	282,671.14	8,676.66
6/4/20			1.07	269,10	2.17	10,578.11
6/11/20		1.13	1.15	218,540.28	13,8	29
6/18/20	1.00	260,893.43	12,361.79	Create graph of 2020 weekly avocado prices and sales for conventional avocados here
6/25/20		0.90	1.27	287,217.67	11,077.27
7/2/20	281,067.39	12,497.92
7/9/20	1.49	267,296.70	6,879.00
7/16/20	1.18	212,210.95	8,490.45
7/23/20	1.21	211,384.21	11,898.47
31	7/30/20	1.50	217,774.89	8,654.39
8/6/20	243,526.27	8,077.28
8/13/20	1.05		1.61	268,655.66	4,739.29
8/20/20	261,757.88	7,240.00
8/27/20	1.28	1.89	184,	46	9,333.46
9/3/20	1.34	2.04	188,701.54	8,629.45
9/10/20	1.	42	2.15	176,082.45	9,103.32
9/17/20	1.48	2.19	167,440.24	8,579.11
9/24/20	1.66	2.24	159,412.07	8,917.50
10/1/20	1.70	154,956.62	8,229.65
10/8/20		1.76	140,968.03	7,071.72
10/15/20		1.57	1.96	161,020.39	7,545.78
10/22/20	1.41	1.78	167,135.85	8,715.84
10/29/20	217,723.59	7,350.15
11/5/20	1.71	229,240.49	6,330.44
11/12/20	1.74	202,631.00	5,833.90	Create graph of 2020 weekly avocado prices and sales for organic avocados here
11/19/20	188,444.00	5,134.74
11/26/20	1.08	159,826.00	5,631.95
12/3/20	1.01		1.59	229,337.00	4,984.55
12/10/20	239,587.94	5,477.32
12/17/20	1.62	197,844.12	6,706.15
12/24/20	1.04	1.56	182,002.85	7,151.34
12/31/20
Note: 2017 Avocado Price & Sales Data Retrieved from Kraggle at https://www.kaggle.com/neuromusic/avocado-prices

5-Week Moving Avg Forecast for Conventional and Organic Avocado Price forecast for 12/31/2017

5-Week Moving Avg Forecast for Conventional and Organic Avocado Sales forecast for 12/31/2017

Questions

2020 Avocado Price and Sales Forecasting
Questions:
#1	Which of the four 12/31/2020 forecasts do you have the most confidence in and why (forecast for organic prices, conventional prices, organic sales, or conventional sales)?
#2	California and Mexico, major producers of avocados will be impacted by the developing 2020-2021 El Nina pattern with drier and warmer than normal conditions resulting in lower avocado yields, reducing the supply of avocados. Based on your analysis of prices and sales, discuss how you believe the El Nina weather pattern will impact prices and sales in the first quarter of 2021. Be specific and include supporting evidence concerning the connection between avocado yields, prices, and sales forecasts.