Excel Question

The data on the CandySalesData worksheet contains the candy sales data from the conditional aggregate functions assignment. Use this data to complete the following tasks.
1.1Create a PivotTable to summarize sales for each sales person.
Insert the PivotTable on the SalesPerson worksheet.
Place SalesPerson as the row labels.
Use an appropriate presentation of the Dollars field as the PivotTable values to display the total, average, and number of sales.
9
1.2Create a PivotTable to summarize product sales.
Insert the PivotTable on the Products worksheet.
Place Product as the row labels.
Use an appropriate presentation of the Dollars field as the PivotTable values to display the total, average, and number of sales.
9
1.3Create a PivotTable to show the total sales by sales person and product.
Insert the PivotTable on the TotalSales worksheet.
Place SalesPerson as the row labels.
Place Product as the column labels.
Use an appropriate presentation of the Dollars field as the values to display the total sales.
Turn off the grand totals for rows and columns on the PivotTabl

9
1.4Create a PivotTable to show the total sales by sales person for the years 2016 and 2017.
Insert the PivotTable on the Revenue worksheet.
Place SalesPerson as the row labels.
Place Date as the column labels.
Show only the year totals.
Use an appropriate presentation of the Dollars field as the PivotTable values to display the total sales.
Use the Filter element to filter the PivotTable column labels to display the years 2016 and 2017.
Turn off the grand totals for rows and columns on the PivotTable.
9
1.5Create a PivotTable to show the aggregate sales by month.
Insert the PivotTable on the Month worksheet.
Place Date as the row labels.
Show only the months (Date) so that we can see if there are any seasonal patterns (note this will group all of the sales into the month they happened regardless of the year).
Use an appropriate presentation of the Dollars field as the PivotTable values to display the total sales by month.
Create a PivotChart to show the data.
9
1.6Create a PivotTable to show the aggregate sales by month and year.
Insert the PivotTable on the MonthAndYear worksheet.
Place Date as the row labels.
Show only months (Date) as the row labels
Place Year as the column labels.
Use an appropriate presentation of the Dollars field as the PivotTable values to display the total sales by month and year.
Create a PivotChart to show the data.
9
1.7Create a PivotTable to show the price per unit for each transaction.
Insert the PivotTable on the Transaction worksheet.
Place Trans Number as the row labels.
Use an appropriate presentation of the Dollars and Units fields as the PivotTable values to display the total dollars and units per transaction.
Add a calculated field named “PricePerUnit” (without the quotes) to show the price per unit for each transaction.
9
1.8Create a PivotTable to show the price per unit for each product.
Insert the PivotTable on the Product worksheet.
Place Product as the row labels.
Use an appropriate presentation of the Dollars and Units fields as the PivotTable values to display the total dollars and units for each product.
Add the PricePerUnit field you created in the previous task as a values field to show the price per unit for each product.
9
1.9Using the data on the CountryPopulations worksheet create a PivotTable with a calculated item to show the population of North America.
Insert the PivotTable on the NorthAmerica worksheet.
Place Country as the row labels.
Use an appropriate presentation of the Population as the PivotTable values to display the total population for each country.
Add the a calculated item named “NorthAmerica” (without the quotes) to the Country field to calculate the population of North America.
Note that North America includes the countries of Canada, Mexico, and the United States.
9
1.10Using the data on the DrugUseByAge worksheet create a PivotTable to show the alcohol use by age.
Insert the PivotTable on the Alcohol worksheet.
Place age as the row labels.
Use an appropriate presentation of the alcohol-use field as the PivotTable values to display the total alcohol use by age.
Create a PivotChart on the Alcohol worksheet based on the PivotTable.
9
1.11Using the data on the DrugUseByAge worksheet create a PivotTable to show the “legal” drug use by age.
Insert the PivotTable on the LegalDrugs worksheet.
Place age as the row labels.
Use an appropriate presentation of the alcohol-use, sedative-use, stimulant-use, tranquilizer-use, pain-reliever-use, oxycontin-use fields as the PivotTable values to display the total legal drug use by age.
Create a PivotChart on the LegalDrugs worksheet based on the PivotTable.
10

 

Last Completed Projects