Marcello Peterbu coordinates the farm share program for his family's farm. Besides the traditlonal vegetable and fruit shares, the Peterbu farm share has also been expanded to include meat and dairy options for its members. Marcello is reviewing the farm share sales data from the last few years to see how best to expand the program. He first wants to create a line chart of the total sales from the farm shares over a four year period Go to the 2018-2021 Share Totais worksheet. Create a Line with Markers chart based on the range A3:E4. Move and resize the chart so that the upper left corner is within cell A7 and the lower right corner is within in cell H24 Format the line with markers chart as described below a. Use 2018-2021 Farm Share Sales as the title of the chart. b. Add a Primary Vertical Axis title to the chart. Use Dollars (U.S.) as the Primary Vertical Axis title Add a Primary Horizontal Axis title to the chart. Use Sales Year as the Primary Horizontal Axis title c. d. Select the 2020 Sales data point on the chart, then change the size of the marker to 10 and the fill color of the marker to Green, Accent 6 (10h column, 1 row in the Theme Colors palette) Marcello now wishes to review how the sales of the non-produce shares might grow over the next two years by adding a trendline to his sales data chart. Go to the Non-Produce Chart - Trends worksheet. Add a linear trendline to the chart with the following options Set the trendline to forecast forward 2 periods. Display the R-squared value on the chart. a. b. Peterbu farm shares come in three different sizes: small, large, and regular (for those shares without different size options). Marcello wants to visually display how the sales of different share sizes have changed over time Go to the 2018-2021 Share Size Chart worksheet. Remove the Average Share Cost series from the PivotChart. Format the PlvotChart as described below a. Change the format of all four data serles to Cylinder b. Change the fill color of the Back Wall of the chart to Blue, Accent 1, Lighter 80% (5th column, 2nd row in the Theme Colors palette) and fill color of the side wall of the chart to Orange, Accent 2, Lighter 80% (6th column, 2nd row in the Theme Colors palette) Go to the 2018-2021 Share Sales worksheet. Select cell A4 and create a PivotChart & PivotTable on a new worksheet as described below: a Use Share Size Pivot as the name of the worksheet. b. If necessary, move the worksheet directly after the 2018-2021 Share Size Chart worksheet. c. Add the Share Size field to the Axis (Category) area of the PivotChart field d. Add the Share Type field to the Values area of the PivotChart field task e. Move the PivotChart so that the upper left corner is located within cell A9 f. Use Share Options by Size as the title of the PivotChart. task pane pane. (Hint: The field name wl be updated to Count of Share Type.) and the lower right corner is located within cell G23 g Add a Primary Vertical Axis title to the PivotChart. Use Number of Share Options as the Primary Vertical Axis title h. Add a Primary Horizontal Axis title to the PivotChart. Use Share Size as the Primary Horizontal Axis title Customers can pick up their weekly farm shares at designated locations in town. if Marcello decides to expand his program, he needs to know how each location will be impacted by farm share pick up Go to the 2021 North Region Particlpants worksheet and select cell A4. Create a blank PivotTable report in a new worksheet. (Hint: The PivotTable report should be based on the table NorthRegion2021.) a. Use North Pickup Location Report as the name of the worksheet. b. Add the Pickup Location and Share Type in that order) to the Rows area of the PivotTable Fields task pane. Peterbu Farms Farm Share Prices Share Type 2018 Sales 2019 Sales 2020 Sales 2021 Sales 4 All 5 Non-Produce 10,370.0013,515.00 17,490.00 25,610.00 $45,675.00 68,495.00 $ 80,445.00 $ 96,855.00 Non-Produce Sales 2018- 2021 30,000 $25,000 sis,000 10,000 $5,000 2038 5aes 20205 aes 2021 Sales Peterbu Farms Total Sales by Farm Share Size 2018-2021 $50,000 $45,000 A0,000 35,000 $30,000 Share Cost Total 2018 Sales Total 2019 Sales Total 2020 Sales 525,000 20,000 Total 2021 Sales $15,000 $10,000 55,000 SHARE SRE Peterbu Farms Farm Share Prices Share ID Share Type Share Size Share Cost 2018 Sale 2019 Sale 2020 Sale 2021 Sale Milk 100.00 600.00 $1,000.00 1,300.00 1,800.00 150.00 300.00 600.00 450.00 450.00 120.00 S 120.00 240.00 240.00 $ 360.00 300.00 300.00600.00 1,200.00$ 2,100.00 425.00 1,275.00 2,550.00 $ 3,400.00 4,250.00 300.00 600.00$ 1,500.001,800.00$ 1,800.00 250.00 1,250.00 $ 1,250.00 1,750.00 $ 3,500.00 250.00 1,000.00 1,000.00 1,250.003,000.00 350.00 1,750.00 1,400.00 1,750.00 3,850.00 200.00 400.00 600.00 1,000.00 1,000.00 325.00 975.00 $ 975.00 650.00 650.00 150.00 1,800.00 1,800.00 2,700.00 2,850.00 150.00 S 5,700.00 8,850.00 10,500.00 13,800.00 240.00 7,680.00 16,080.00 18,240.00 $ 19,680.00 200.00 $ 7,200.00 8,600.00 $ 11,400.00 10,200.00 320.00 12,800.0017,600.00 $19,840.00 21,440.00 175.00 $ 1,925.00 3,850.00 $ 2,975.00 6,125.00 45,675.00 68,495.00 80,445.00 96,855.00 4 D101 5 D102 6 D103 Beef Pork 9 M103 1104 11 M105 Chicken Chicken Fish Assorted Meat 13 M106 14 M107 16 P101 17 P102 18 P103 19 P104 20 P105 21 Total Vegetable Vegetable Fruit & Vegetable S Fruit & Vegetable Fruit Marcello Peterbu coordinates the farm share program for his family's farm. Besides the traditlonal vegetable and fruit shares, the Peterbu farm share has also been expanded to include meat and dairy options for its members. Marcello is reviewing the farm share sales data from the last few years to see how best to expand the program. He first wants to create a line chart of the total sales from the farm shares over a four year period Go to the 2018-2021 Share Totais worksheet. Create a Line with Markers chart based on the range A3:E4. Move and resize the chart so that the upper left corner is within cell A7 and the lower right corner is within in cell H24 Format the line with markers chart as described below a. Use 2018-2021 Farm Share Sales as the title of the chart. b. Add a Primary Vertical Axis title to the chart. Use Dollars (U.S.) as the Primary Vertical Axis title Add a Primary Horizontal Axis title to the chart. Use Sales Year as the Primary Horizontal Axis title c. d. Select the 2020 Sales data point on the chart, then change the size of the marker to 10 and the fill color of the marker to Green, Accent 6 (10h column, 1 row in the Theme Colors palette) Marcello now wishes to review how the sales of the non-produce shares might grow over the next two years by adding a trendline to his sales data chart. Go to the Non-Produce Chart - Trends worksheet. Add a linear trendline to the chart with the following options Set the trendline to forecast forward 2 periods. Display the R-squared value on the chart. a. b. Peterbu farm shares come in three different sizes: small, large, and regular (for those shares without different size options). Marcello wants to visually display how the sales of different share sizes have changed over time Go to the 2018-2021 Share Size Chart worksheet. Remove the Average Share Cost series from the PivotChart. Format the PlvotChart as described below a. Change the format of all four data serles to Cylinder b. Change the fill color of the Back Wall of the chart to Blue, Accent 1, Lighter 80% (5th column, 2nd row in the Theme Colors palette) and fill color of the side wall of the chart to Orange, Accent 2, Lighter 80% (6th column, 2nd row in the Theme Colors palette) Go to the 2018-2021 Share Sales worksheet. Select cell A4 and create a PivotChart & PivotTable on a new worksheet as described below: a Use Share Size Pivot as the name of the worksheet. b. If necessary, move the worksheet directly after the 2018-2021 Share Size Chart worksheet. c. Add the Share Size field to the Axis (Category) area of the PivotChart field d. Add the Share Type field to the Values area of the PivotChart field task e. Move the PivotChart so that the upper left corner is located within cell A9 f. Use Share Options by Size as the title of the PivotChart. task pane pane. (Hint: The field name wl be updated to Count of Share Type.) and the lower right corner is located within cell G23 g Add a Primary Vertical Axis title to the PivotChart. Use Number of Share Options as the Primary Vertical Axis title h. Add a Primary Horizontal Axis title to the PivotChart. Use Share Size as the Primary Horizontal Axis title Customers can pick up their weekly farm shares at designated locations in town. if Marcello decides to expand his program, he needs to know how each location will be impacted by farm share pick up Go to the 2021 North Region Particlpants worksheet and select cell A4. Create a blank PivotTable report in a new worksheet. (Hint: The PivotTable report should be based on the table NorthRegion2021.) a. Use North Pickup Location Report as the name of the worksheet. b. Add the Pickup Location and Share Type in that order) to the Rows area of the PivotTable Fields task pane. Peterbu Farms Farm Share Prices Share Type 2018 Sales 2019 Sales 2020 Sales 2021 Sales 4 All 5 Non-Produce 10,370.0013,515.00 17,490.00 25,610.00 $45,675.00 68,495.00 $ 80,445.00 $ 96,855.00 Non-Produce Sales 2018- 2021 30,000 $25,000 sis,000 10,000 $5,000 2038 5aes 20205 aes 2021 Sales Peterbu Farms Total Sales by Farm Share Size 2018-2021 $50,000 $45,000 A0,000 35,000 $30,000 Share Cost Total 2018 Sales Total 2019 Sales Total 2020 Sales 525,000 20,000 Total 2021 Sales $15,000 $10,000 55,000 SHARE SRE Peterbu Farms Farm Share Prices Share ID Share Type Share Size Share Cost 2018 Sale 2019 Sale 2020 Sale 2021 Sale Milk 100.00 600.00 $1,000.00 1,300.00 1,800.00 150.00 300.00 600.00 450.00 450.00 120.00 S 120.00 240.00 240.00 $ 360.00 300.00 300.00600.00 1,200.00$ 2,100.00 425.00 1,275.00 2,550.00 $ 3,400.00 4,250.00 300.00 600.00$ 1,500.001,800.00$ 1,800.00 250.00 1,250.00 $ 1,250.00 1,750.00 $ 3,500.00 250.00 1,000.00 1,000.00 1,250.003,000.00 350.00 1,750.00 1,400.00 1,750.00 3,850.00 200.00 400.00 600.00 1,000.00 1,000.00 325.00 975.00 $ 975.00 650.00 650.00 150.00 1,800.00 1,800.00 2,700.00 2,850.00 150.00 S 5,700.00 8,850.00 10,500.00 13,800.00 240.00 7,680.00 16,080.00 18,240.00 $ 19,680.00 200.00 $ 7,200.00 8,600.00 $ 11,400.00 10,200.00 320.00 12,800.0017,600.00 $19,840.00 21,440.00 175.00 $ 1,925.00 3,850.00 $ 2,975.00 6,125.00 45,675.00 68,495.00 80,445.00 96,855.00 4 D101 5 D102 6 D103 Beef Pork 9 M103 1104 11 M105 Chicken Chicken Fish Assorted Meat 13 M106 14 M107 16 P101 17 P102 18 P103 19 P104 20 P105 21 Total Vegetable Vegetable Fruit & Vegetable S Fruit & Vegetable Fruit