ONLY BID IF YOU CAN DO IN 5 HOURS MAX
YO22_Excel_Ch06_Assessment_Video_Game_Sale
ONLY BID IF YOU CAN DO IN 5 HOURS MAX
YO22_Excel_Ch06_Assessment_Video_Game_Sales
Project Description:
Samantha Vee, the owner of a local video game store, is interested in how video games sales vary by platform and region. She has acquired a sample data set and needs your help to explore the data.
Steps to Perform:
Step
Instructions
Points Possible
1
Start Excel. Download and open the file named Excel_Ch06_Assessment_VideoGameSales.xlsx. Grader has automatically added your last name to the beginning of the filename.
0
2
The data on the VideoGameSales worksheet includes sales (in millions) for several video games on various platforms and from various genres. On the VideoGameSales worksheet, insert a table with headers using the data.
0.8
3
Apply the Light Blue, Table Style Medium 27 to the table and with the table selected, including the headers, create a SalesDatabase named range to be used in database functions later.
1
4
Add a Total Row to the table to have the sales values in the NorthAmerica, Europe, Japan, Other, and GlobalSales columns summed.
1
5
Slicers are an easy way to explore data in a table.
Insert slicers for Platform, Year, and Genre.
1
6
Move the Platform slicer so that its top-left corner is just inside the border of cell K1. Modify its Layout property so that it has 3 columns and resize the slicer so that all options are visible and extra white space is minimal. Apply the White, Slicer Style Other 2 style to the slicer.
0.4
7
Move the Genre slicer so that its top-left corner is just inside cell N1. Modify its Layout property so that it has 2 columns and resize the slicer so that all options are visible and extra white space is minimal. Apply the White, Slicer Style Other 2 style to the slicer.
0.4
8
Move the Year slicer so that its top-left corner is inside cell K13. Modify its Layout property so that it has 3 columns and resize the slicer so that all options are visible and extra white space is minimal. Modify the setting so that the caption reads Release Year. Apply the White, Slicer Style Other 2 style to the slicer.
0.4
9
Using the slicers, filter the Table to only show records where the Platform is PS3 and DS and the Genre is either Adventure or Action. Select the filtered data, including column headings and the Total row, and paste the data on the SelectData worksheet, starting in cell A1. Adjust the column widths as necessary so that all data are visible.
1.2
10
Clear the filters applied to the VideoGameSales data. Copy the headings and paste them onto the DatabaseTotals worksheet, starting in cell A1 to begin to setup and create database functions to further analyze the data. If necessary, adjust the column widths so that all data are visible.
1
11
Complete the DatabaseTotals worksheet using the appropriate database functions using the SalesDatabase named range, use GlobalSales as the field in your calculations, and only calculate games that meet the following criteria:
In row 2, specify the criteria for games on the Wii platform with a release year of newer than 2006
In row 3, specify the criteria for games on the DS platform in the Adventure genre.
4
12
Using the data on the VideoGameSales worksheet, create a PivotTable on a new worksheet and rename the worksheet, PivotAnalysis. Do not add the data to the Data Model. Be sure the Report Layout is in Compact Form.
1
13
Structure the PivotTable on the PivotAnalysis worksheet according to the following:
Filters: Publisher
Rows: Platform and Year
Columns: Genre
Values: Sum of GlobalSales
1.2
14
Configure the PivotTable on the PivotAnalysis worksheet according to the following:
Create a custom name for Sum of GlobalSales to be Total GlobalSales (in millions)
Format Total GlobalSales as Accounting with 2 decimal places
Change Row Labels in cell A4 to Platform and Year
Change Column Labels in cell B3 to Genre
Apply Light Blue, Pivot Style Light 9
Adjust the column widths, if necessary.
1.2
15
Using the data on the VideoGameSales worksheet, insert a PivotChart on a new worksheet. Rename the worksheet with the PivotChart and PivotTable, NorthAmericanSalesByPlatform.
1
16
Structure the PivotChart on the NorthAmericanSalesByPlatform worksheet according to the following:
Filters: Genre
Axis (Categories): Platform
Values: Sum of NorthAmerica
1.2
17
Configure the PivotChart on the NorthAmericanSalesByPlatform worksheet according to the following:
Change the Chart Type to Clustered Bar.
Apply a Style 4 Chart Style
Apply a Layout 1 Quick Layout
Edit the chart title to North American Sales by Platform
Delete the Legend
Move the PivotChart to a new worksheet with the name SalesByPlatformPivotChart
1.2
18
Using the PivotTable on the NorthAmericanSalesByPlatform worksheet, create a drill-down of the DS sales and rename the worksheet created NorthAmericanSalesDS.
1
19
Using the PivotChart on the SalesByPlatformPivotChart worksheet, modify the Genre filter to only show data for Action, Adventure, and Fighting genres.
If using a Mac, use the PivotTable filter on the NorthAmericanSalesByPlatform worksheet (the source of the PivotChart) to apply the filters.
1
20
Save and close Excel_Ch06_Assessment_VideoGameSales.xlsx. Exit Excel. Submit the file as directed.
0
Total Points
20