Assignment: Excel Pivot Table
Read the brief case below. Using the Excel spreadsheet provided, complete the questions.
EA Inc. is a growing company for software in the United States. As part of their business expansion initiatives, they are venturing into the Video Game space in 1985. Since electronic video games took off a couple years ago, the management team wants to review the global sales figures to understand if this is a profitable buisness. They have the sales data for the yeas 1984 – 2018 for some of the leading Video Game manufacturers.
Instructions: In the excle spreadheet provided, complete the tasks below. Upon completion you will submit a single, completed, Excel workbook.
17 points each
1.Use the data in the sheet titled “Main” to calculate the total sales for each Publisher (e.g. Nintendo, Electronic Arts, etc.). What were the numbers recorded by Lucas Arts?
a.Create a new worksheet titled – “Q1 – Total Sales per Publisher” and insert a new pivot table for the data in the sheet – “Main”
b.List the total sales for each of the Publisher
c.The names of the Publishers feature in the 1st column
d.Format the numbers to reflect the “$” symbol before the figures. Label this column as – Global Sales (in Million Dollars)
e.Sort the data by Global Sales in Descending order.
f.Highlight the row containing Lucas Arts.
2.The Management team would like to know sales between NA, EU, Japan and Other regions for the games that fall under the genre of Action, Adventure and Racing. They want to emphasize the games that start with the letter E as that is the prominent letter in the company’s logo.
a.Create a new worksheet titled – “Q2 – Specific Sales” and insert a new pivot table for the data in the sheet – “Main”
b.In the first column, list the Publishers first and then the Sales Outlets for NA, EU, JP and Other.
c.Add a new filter to just display the sales figures of the games under the genres – Action, Adventure and Racing
d.Using a Filter on the names of the Publisher, show just those that begin with the letter “E”.
3.There has been an evergreen war between PlayStation and Xbox. The Management team wants to know how they perform against each other on the different platforms. They need a split of the Sales figures between each version of the 2 platforms. Also highlight any sales that were below $20 million dollars.
a.Create a new worksheet titled – “Q3 – PS vs XBox” and insert a new pivot table for the data in the sheet – “Main”
b.Add the Genre in the first column
c.Split the Global Sales across the rest of the columns for PlayStation controllers. Each name begins with “PS” (“platform” should be in the column area)
d.Use Conditional Formatting to highlight the cells in Light Red where the Sales is less than $20 million dollars. Label the columns to reflect the numbers
e.Change the format of each figure to reflect the currency “$”
f.Repeat the same under the above table for Xbox. Each platform name begins with the letter X.
4.The Management team would like to know – what are the aggregated sales figures for all the Genres between the years 2000-2010. They also want to know sales of Sports games played on Wii.
a.Create a new worksheet titled – “Q4 – Sales per Genre 2000s” and insert a new pivot table for the data in the sheet – “Main”
b.The Genre and Platform should be listed in the 1st column.
c.Insert the “Global Sales” to aggregate the figures for each of the platform under each Genre.
d.Insert a Slicer on Year and select the values 2000 through 2010
5.Management would like to know – What are sales between North America and Japan in terms of the sales across the genres. Create a column chart to represent these numbers.
a.Create a new worksheet titled – “Q5 – NA versus Japan” and insert a new pivot table for the data in the sheet – “Main”
b.List all the Genres in the 1st column.
c.Split the Sales across the NA and JP region
d.Select the Pivot Table and Click on Insert> Pivot Chart
e.Insert a Column Chart. Add Data Label to the numbers and skew them vertically. 15 points
6.As the Sr. Data Analyst, you want to shorten the process of creating a Pivot table to compare the Sales figures between NA and Japan. Create a Macro to perform this operation for you with the “click of a button”. Insert a button to run this macro. NOTE: The Macro is just for the table (b,c), do not add the chart from question 5. To run the Macro, you must delete the table first (highlight and hit the delete button).
a.Create a new worksheet titled – “Q6Macro”
b.Record the macro from: View>Macros>Record Macro.
c.Name the Macro “Q6Macro”
d.Insert a new pivot table for the data in the sheet – “Main” and complete steps “b” and “c” from question 5.
e.Stop recording of the Macro
f.Add a New Button titled “Q6Macro” and link it to the Macro created. Place this button on the top right of the sheet titled – “Q6Macro”[Hint: The Option to add a button is available in the “Developer” tab. If the Developer tab is not visible, goto File>Options>Customize Ribbon. Make sure that the ‘Developer’ option is selected as shown