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