Excel 2016

Problem: You are working for an organic farming collective. You have been asked to create two PivotTables and corresponding PivotCharts from the annual sales worksheet for the collective. One PivotTable and PivotChart summarize the sales by farm. The other PivotTable and PivotChart summarize the dairy sales by month for the top dairy producer.

Instructions: Perform the following tasks:

1. Start Excel. Open the Excel Chapter 8 – Lab Test A – Data file from the Data Files provided in the location specified by your instructor. Save the wokbook using the file name, Lab Test A – Excel Chapter 8.

2. Create the PivotTable and associated PivotChart shown in Figure E8A – 1 in a separate sheet in the workbook. Name the worksheet Sales by Farm.

a. Change cell A4 to Farms and cell B3 to Stores. Apply the Pivot Style Light 18 style to the PivotTable. Format the values as Currency values with a dollar sign and no decimal places. Apply the Style 6 to the PivotChart.

3. Create a second PivotTable and associated PivotChart as shown in Figure E8A–2 in a separate sheet in the workbook. Name the worksheet Dairy Sales by Month.

4. Change cell A4 to Month and cell B3 to Farm. Apply the Pivot Style Light 18 style to the PivotTable. Format the values as Currency values with a dollar sign and no decimal places. Turn off the legend for the chart. Apply the Style 5 to the PivotChart.

a. Filter the product type by Dairy. Filter the farm to Red Earth.

b. Change the chart type to Line and then add a linear trendline that forecasts the trend for two more months.

c. Change the document properties as specified by your instructor. Change the Dairy Sales by Month worksheet header to include your name, course number, and other information as specified by your instructor. Print the worksheet.

5. Change the Sales by Farm worksheet header to include your name, course number, and other information as specified by your instructor. Print the worksheet, and then save the workbook.

6. Submit the assignment as requested by your instructor.

Figure E8A – 1

Figure E8A – 2

Page 2