Strategic Economic Analysis of Agri-Food Markets，Excel

profilehans111
FRE501Assignment1Dataset2022.xlsx
Home>Business & Finance homework help>Economics homework help>Strategic Economic Analysis of Agri-Food Markets，Excel

Qn 1

Data Sources
Wheat Prices: World Bank Commodities Price Data (The Pink Sheet) https://www.worldbank.org/en/research/commodity-markets
Canadian Inflation Rate: Bank of Canada https://www.bankofcanada.ca/rates/price-indexes/cpi/
Period Wheat, US HRW ($/mt) Canadian Inflation Rates Example
2015-Jan 248 1.0%
2015-Feb 237 1.0%
2015-Mar 231 1.2%
2015-Apr 223 0.8%
2015-May 215 0.9%
2015-Jun 210 1.0%
2015-Jul 197 1.3%
2015-Aug 180 1.3%
2015-Sep 173 1.0%
2015-Oct 173 1.0%
2015-Nov 177 1.4%
2015-Dec 189 1.6%
2016-Jan 193 2.0%
2016-Feb 187 1.4%
2016-Mar 191 1.3%
2016-Apr 187 1.7%
2016-May 172 1.5% Answer
2016-Jun 173 1.5%
2016-Jul 152 1.3%
2016-Aug 149 1.1%
2016-Sep 151 1.3%
2016-Oct 152 1.5%
2016-Nov 151 1.2%
2016-Dec 142 1.5%
2017-Jan 153 2.1%
2017-Feb 155 2.0%
2017-Mar 154 1.6%
2017-Apr 166 1.6%
2017-May 180 1.3%
2017-Jun 190 1.0%
2017-Jul 202 1.2%
2017-Aug 171 1.4%
2017-Sep 179 1.6%
2017-Oct 176 1.4%
2017-Nov 180 2.1%
2017-Dec 184 1.9%
2018-Jan 192 1.7%
2018-Feb 192 2.2%
2018-Mar 192 2.3%
2018-Apr 214 2.2%
2018-May 214 2.2%
2018-Jun 219 2.5%
2018-Jul 218 3.0%
2018-Aug 237 2.8%
2018-Sep 212 2.2%
2018-Oct 213 2.4%
2018-Nov 204 1.7%
2018-Dec 211 2.0%
2019-Jan 210 1.4%
2019-Feb 219 1.5%
2019-Mar 206 1.9%
2019-Apr 200 2.0%
2019-May 200 2.4%
2019-Jun 206 2.0%
2019-Jul 196 2.0%
2019-Aug 181 1.9%
2019-Sep 190 1.9%
2019-Oct 200 1.9%
2019-Nov 203 2.2%
2019-Dec 211 2.2%
2020-Jan 225 2.4%
2020-Feb 215 2.2%
2020-Mar 209 0.9%
2020-Apr 219 -0.2%
2020-May 206 -0.4%
2020-Jun 198 0.7%
2020-Jul 222 0.1%
2020-Aug 223 0.1%
2020-Sep 248 0.5%
2020-Oct 272 0.7%
2020-Nov 273 1.0%
2020-Dec 269 0.7%
2021-Jan 289 1.0%
2021-Feb 289 1.1%
2021-Mar 273 2.2%
2021-Apr 281 3.4%
2021-May 297 3.6%
2021-Jun 286 3.1%
2021-Jul 294 3.7%
2021-Aug 325 4.1%
2021-Sep 338 4.4%
2021-Oct 355 4.7%
2021-Nov 379 4.7%
2021-Dec 377 4.8%
2022-Jan 374 5.1%
2022-Feb 391 5.7%
2022-Mar 486 6.7%
2022-Apr 495 6.8%
2022-May 522 7.7%
2022-Jun 460 8.1%
2022-Jul 383 7.6%

Qn 2

Data Sources
Statistics Canada. Table 32-10-0098-01  Farm product price index (FPPI), monthly https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=3210009801
Statistics Canada. Table 18-10-0004-01  Consumer Price Index, monthly, not seasonally adjusted https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=1810000401
Date Farm Product Price Index Food CPI
Jan-20 132.4 153.5 Question 2a)
Feb-20 132.4 152.9
Mar-20 132.7 152.8
Apr-20 130.5 154
May-20 134.5 154.3
Jun-20 133 154.8
Jul-20 132.5 155
Aug-20 132.9 153.8
Sep-20 134.3 152.6
Oct-20 137.3 153.3
Nov-20 138.2 153.9
Dec-20 139.5 153.6
Jan-21 143 155
Feb-21 147.9 155.6
Mar-21 153.1 155.5
Apr-21 158.5 155.4 Question 2bi)
May-21 158.6 156.6
Jun-21 164.4 156.8
Jul-21 167.5 157.6
Aug-21 163.8 158
Sep-21 167 158.5
Oct-21 167.4 159.2
Nov-21 173.6 160.7
Dec-21 175.7 161.6
Jan-22 185.7 163.9
Feb-22 195.3 166
Mar-22 201.4 167.5

Qn 3a

Data Sources
Statistics Canada. Table 32-10-0098-01  Farm product price index (FPPI), monthly https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=3210009801
Statistics Canada. Table 18-10-0004-01  Consumer Price Index, monthly, not seasonally adjusted https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=1810000401
Date Farm Product Price Index Food CPI Quarterly Food Product Price Index Quarterly Food CPI Period
1/1/00 85.4 92.1
2/1/00 86.7 91.9
3/1/00 88.3 92.3 86.80 92.10 Quarter
4/1/00 90 92.5
5/1/00 89.9 93.2
6/1/00 88.4 93.8 89.43 93.17 Quarter
7/1/00 87 94.3
8/1/00 87.6 94
9/1/00 86.8 93.3
10/1/00 87.1 93.1
11/1/00 87.7 93.9
12/1/00 89.1 95.1
1/1/01 88.4 95.3
2/1/01 91.4 96.1
3/1/01 94.4 96.9
4/1/01 94 97.2
5/1/01 94.2 98
6/1/01 94.4 98.2
7/1/01 94.4 98.6
8/1/01 96.1 97.9
9/1/01 97.3 97.2
10/1/01 95.7 97
11/1/01 95.4 97.5
12/1/01 95.2 98.6
1/1/02 97.5 99.7
2/1/02 99.7 100.3
3/1/02 99.1 100.1
4/1/02 96.8 100.5
5/1/02 96.9 100.1
6/1/02 96.5 100.5
7/1/02 99.1 100.5
8/1/02 98.7 99.9
9/1/02 96.7 99
10/1/02 98.8 98.5
11/1/02 98.6 100.1
12/1/02 98.8 100.8
1/1/03 101.3 101.6
2/1/03 102.8 101.5
3/1/03 101.1 101.7
4/1/03 100.4 101.8
5/1/03 100.4 102.1
6/1/03 94.3 102.4
7/1/03 87.8 102.4
8/1/03 86.6 101.5
9/1/03 89 100.8
10/1/03 89.7 100.7
11/1/03 90 101.7
12/1/03 89.5 102.6
1/1/04 89.6 102.8
2/1/04 90.1 102.6
3/1/04 93.3 102.7
4/1/04 94 102.5
5/1/04 95.8 103.4
6/1/04 95.9 104.4
7/1/04 94 104.4
8/1/04 94.6 104.1
9/1/04 91.9 103.7
10/1/04 89.2 104.2
11/1/04 86.7 105.2
12/1/04 88.5 105.7
1/1/05 89.2 105.4
2/1/05 91.1 105.2
3/1/05 88.8 105.6
4/1/05 87.8 106.6
5/1/05 88.2 106.8
6/1/05 87.7 107.2
7/1/05 87.9 106.9
8/1/05 92.1 106.8
9/1/05 89.6 105.8
10/1/05 88.9 106.2
11/1/05 87.2 106.7
12/1/05 88.1 107.5
1/1/06 87.6 108.4
2/1/06 87.5 108.2
3/1/06 87.7 108.2
4/1/06 87.3 108.1
5/1/06 88.6 108.7
6/1/06 89.6 109.2
7/1/06 89.4 109.2
8/1/06 92.8 109.2
9/1/06 92.1 108.8
10/1/06 90.6 109.2
11/1/06 91.5 110.1
12/1/06 91.8 109.9
1/1/07 93.1 110.9
2/1/07 96 112.6
3/1/07 97.9 112.2
4/1/07 98 112.2
5/1/07 98 112.5
6/1/07 97.6 112.6
7/1/07 96.9 112.3
8/1/07 103.7 111.8
9/1/07 106.2 110.9
10/1/07 104.2 110.7
11/1/07 105 111.3
12/1/07 104.1 111.7
1/1/08 104.8 112.4
2/1/08 109.2 112.8
3/1/08 110.3 112.6
4/1/08 112.4 113.5
5/1/08 115.2 114.6
6/1/08 118.8 115.8
7/1/08 120.5 116.5
8/1/08 116.3 116.8
9/1/08 113.4 117.1
10/1/08 113.5 117.4
11/1/08 112.2 119.5
12/1/08 110.1 119.8
1/1/09 108.6 120.6
2/1/09 109.5 121.2
3/1/09 110.8 121.5
4/1/09 110.9 121.6
5/1/09 110.7 121.9
6/1/09 108.9 122.2
7/1/09 107.5 122.3
8/1/09 104.1 121.5
9/1/09 99 120.4
10/1/09 97.4 120.1
11/1/09 96.6 121.5
12/1/09 100.2 121.8
1/1/10 100 122.3
2/1/10 100.7 122.7
3/1/10 100.6 123.1
4/1/10 101.4 122.8
5/1/10 104 122.9
6/1/10 101.9 123
7/1/10 102.6 123.7
8/1/10 109 123.5
9/1/10 109.6 122.9
10/1/10 108.4 122.7
11/1/10 107.9 123.3
12/1/10 110 123.9
1/1/11 113.7 124.9
2/1/11 117.3 125.3
3/1/11 120.2 127.2
4/1/11 121.7 126.9
5/1/11 119.2 127.7
6/1/11 120.1 128.3
7/1/11 121.4 129
8/1/11 124 128.9
9/1/11 123.8 128.2
10/1/11 124.2 128
11/1/11 124.7 129.2
12/1/11 123.5 129.3
1/1/12 124.6 130.2
2/1/12 125.8 130.4
3/1/12 126 130
4/1/12 124.3 130.1
5/1/12 124.2 130.9
6/1/12 126.1 130.9
7/1/12 127.6 131.7
8/1/12 127.4 131.7
9/1/12 126.2 130.3
10/1/12 127.7 130.5
11/1/12 129.3 131.4
12/1/12 129.7 131.2
1/1/13 130.6 131.6
2/1/13 132.6 132.9
3/1/13 132.5 132.4
4/1/13 132.6 132.1
5/1/13 134.7 132.6
6/1/13 136.5 132.5
7/1/13 133.1 132.7
8/1/13 129.5 133
9/1/13 125.5 131.9
10/1/13 124.6 131.7
11/1/13 121.5 132.8
12/1/13 121.7 132.5
1/1/14 121.5 133
2/1/14 122.3 134.3
3/1/14 128.8 134.4
4/1/14 132 134.6
5/1/14 131.9 135.7
6/1/14 131.3 136.4
7/1/14 131.3 136.5
8/1/14 135.3 135.9
9/1/14 130.7 135.5
10/1/14 132.5 135.4
11/1/14 131.9 136.9
12/1/14 133.4 137.4
1/1/15 134.2 139.1
2/1/15 136 139.5
3/1/15 136.4 139.5
4/1/15 137.6 139.4
5/1/15 138 140.8
6/1/15 137.6 141
7/1/15 138.3 140.9
8/1/15 139.5 140.8
9/1/15 138.8 140.3
10/1/15 135 140.9
11/1/15 133 141.6
12/1/15 131.6 142.5
1/1/16 134.3 144.6
2/1/16 134.9 145
3/1/16 135.6 144.5
4/1/16 133.7 143.8
5/1/16 136.4 143.3
6/1/16 137.1 142.8
7/1/16 133 143.2
8/1/16 127.8 142.3
9/1/16 124.7 140.4
10/1/16 124.5 139.9
11/1/16 125.1 140.6
12/1/16 128.8 140.6
1/1/17 129.5 141.5
2/1/17 130.8 141.7
3/1/17 132.6 141.8
4/1/17 132.1 142.2
5/1/17 135.2 143.1
6/1/17 136.2 143.6
7/1/17 136.2 144.1
8/1/17 132.8 143.6
9/1/17 128.6 142.4
10/1/17 128.5 141.7
11/1/17 130.2 142.9
12/1/17 129.1 143.4
1/1/18 129.7 144.7
2/1/18 129.9 144.7
3/1/18 130.6 144.2
4/1/18 129.5 144.8
5/1/18 131.2 144.6
6/1/18 132.8 145.6
7/1/18 133.6 146.1
8/1/18 130.1 145.9
9/1/18 128.1 144.9
10/1/18 129.4 144.6
11/1/18 128.4 146.1
12/1/18 127.8 147.5
1/1/19 130 148.7
2/1/19 129.9 149.3
3/1/19 132 149.4
4/1/19 135.2 149
5/1/19 135.2 149.7
6/1/19 133.5 150.7
7/1/19 131.6 151.6
8/1/19 132.8 151.1
9/1/19 127.6 150.2
10/1/19 128.8 149.9
11/1/19 129.2 151.1
12/1/19 130.4 151.9
1/1/20 132.4 153.5
2/1/20 132.4 152.9
3/1/20 132.7 152.8
4/1/20 130.5 154
5/1/20 134.5 154.3
6/1/20 133 154.8
7/1/20 132.5 155
8/1/20 132.9 153.8
9/1/20 134.3 152.6
10/1/20 137.3 153.3
11/1/20 138.2 153.9
12/1/20 139.5 153.6
1/1/21 143 155
2/1/21 147.9 155.6
3/1/21 153.1 155.5
4/1/21 158.5 155.4
5/1/21 158.6 156.6
6/1/21 164.4 156.8
7/1/21 167.5 157.6
8/1/21 163.8 158
9/1/21 167 158.5
10/1/21 167.4 159.2
11/1/21 173.6 160.7
12/1/21 175.7 161.6
1/1/22 185.7 163.9
2/1/22 195.3 166
3/1/22 201.4 167.5

Notes: The quarterly series will consist of 3-month averages. • Q1 (The first quarter) will be the average value of Jan, Feb, Mar • Q2, the average of Apr, May, Jun • Q3, the average of Jul, Aug, Sep • Q4, the average of Oct, Nov, Dec Tips (one of several methods): 1)Use a formula in column G to identify the end of a quarter 2) Filter by Period, "Quarter" 3) Copy quarterly data and paste as values into the Qn 3b & c sheet

Qn 3b & c

Plot these over the period Q2-2000 to Q1-2022 Example
Period Quarterly Food Product Price Index Quarterly Food CPI Quarterly Percentage Change in Food Product Price Index Quarterly Percentage Change in Food CPI
Q1-2000 86.80 92.10
Q2-2000 89.43 93.17 3.03% 1.16%
Q3-2000
Q4-2000
Q1-2001
Q2-2001
Q3-2001
Q4-2001
Q1-2002
Q2-2002 Question 3c)
Q3-2002
Q4-2002
Q1-2003
Q2-2003
Q3-2003
Q4-2003
Q1-2004
Q2-2004
Q3-2004
Q4-2004
Q1-2005
Q2-2005
Q3-2005
Q4-2005
Q1-2006
Q2-2006
Q3-2006
Q4-2006
Q1-2007
Q2-2007
Q3-2007
Q4-2007
Q1-2008
Q2-2008
Q3-2008
Q4-2008
Q1-2009
Q2-2009
Q3-2009
Q4-2009
Q1-2010
Q2-2010
Q3-2010
Q4-2010
Q1-2011
Q2-2011
Q3-2011
Q4-2011
Q1-2012
Q2-2012
Q3-2012
Q4-2012
Q1-2013
Q2-2013
Q3-2013
Q4-2013
Q1-2014
Q2-2014
Q3-2014
Q4-2014
Q1-2015
Q2-2015
Q3-2015
Q4-2015
Q1-2016
Q2-2016
Q3-2016
Q4-2016
Q1-2017
Q2-2017
Q3-2017
Q4-2017
Q1-2018
Q2-2018
Q3-2018
Q4-2018
Q1-2019
Q2-2019
Q3-2019
Q4-2019
Q1-2020
Q2-2020
Q3-2020
Q4-2020
Q1-2021
Q2-2021
Q3-2021
Q4-2021
Q1-2022

image1.png

image2.png