ITSS 3300 Assignment Excel Fundamentals

Assignment: Excel Pivot Table (100 points)

Read the brief case below. Using the Excel spreadsheet provided, complete the questions.

EA Inc.

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.

20 points each

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?
1. Create a new worksheet titled – “Q1 – Total Sales per Publisher” and insert a new pivot table for the data in the sheet – “Main”
2. List the total sales for each of the Publisher
3. The names of the Publishers feature in the 1^st column
4. Format the numbers to reflect the “$” symbol before the figures. Label this column as – Global Sales (in Million Dollars)
5. Sort the data by Global Sales in Descending order.
6. Highlight the row containing Lucas Arts.

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.
1. Create a new worksheet titled – “Q2 – Specific Sales” and insert a new pivot table for the data in the sheet – “Main”
2. In the first column, list the Publishers first and then the Sales Outlets for NA, EU, JP and Other.
3. Add a new filter to just display the sales figures of the games under the genres – Action, Adventure and Racing
4. Filter out the names of the Publisher, which begin with the letter “E”.
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.
1. Create a new worksheet titled – “Q3 – PS vs XBox” and insert a new pivot table for the data in the sheet – “Main”
2. Add the Genre in the first column
3. Split the Global Sales across the rest of the columns for PlayStation controllers. Each name begins with “PS”
4. 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
5. Change the format of each figure to reflect the currency “$”
6. Repeat the same under the above table for Xbox. Each platform name begins with the letter X.

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.
1. Create a new worksheet titled – “Q4 – Sales per Genre 2000s” and insert a new pivot table for the data in the sheet – “Main”
2. The Genre and Platform should be listed in the 1^st
3. Insert the “Global Sales” to aggregate the figures for each of the platform under each Genre.
4. Insert a Slicer on Year and select the values 2000 through 2010

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.
1. Create a new worksheet titled – “Q5 – NA versus Japan” and insert a new pivot table for the data in the sheet – “Main”
2. List all the Genres in the 1st column.
3. Split the Sales across the NA and JP region
4. Select the Pivot Table and Click on Options> Pivot Chart
5. Insert a Column Chart. Add Data Label to the numbers and skew them vertically.

EA Inc.

Instructions: In the excle spreadheet provided, complete the tasks below. Upon completion you will submit a single, completed, Excel workbook.

20 points each

Setup the spreadsheet with proper formatting and formulas so it can be presented to management.
1. Create a new worksheet titled – “Q1 – Regional Sales” and copy the data from the “Main” worksheet onto the sheet.
2. Add a column to calculate the total for each product. Use the SUM function.
3. Add rows to calculate the Total, Average and Median sales for each quarter. Use the SUM, AVG, and MEDIAN functions.
4. Format the title at the top so it stands out. Add distinct formatting to the column and row headings. Format the data in the cells to show currency.

Management would like to decide whether they should venture into a certain genre. Any game which has sales less than $ 200,000 in at least 2 regions should not be considered. Are there any video games/genres that they should not consider? List the game(s) and your recommendation in the worksheet and highlight them using conditional formatting.
1. Copy the data from the worksheet you created in step 1 and preserve the formatting. Name the worksheet – Q2 - Not to be considered Games [Hint: Copy the worksheet.]
2. Change the title at the top to be descriptive of the problem.
3. Use conditional formatting to highlight the cells that are below $500.

Management would like to know – for each of the video games, what was the highest and lowest sales for that product – across all 4 regions. Which was the most profitable video game?
1. Create a new worksheet titled “Q3 - Popular Games”.
2. Add a meaningful title to the top of the worksheet.
3. Copy the Product names.
4. Using the data in worksheet titled “Q1 - Regional Sales” determine the Min and Max total sales for each region. Add 1 column titled Highest Sales 1984. Use a function to determine the highest sales each product made across the regions. [Hint: Use MAX and refer to the Q1 sheet to calculate your numbers.] Do the same thing to calculate the minimum – add a column titled Lowest Sales 1984.
5. Add the ability to filter and sort each column. Then, sort the products based on highest sales, descending order. [Hint: Use the sort and filter feature.]
6. Add a column titled – Gain/Loss – to determine the range between the highest and the lowest sales. Highlight the top three cells. [Hint: Use the conditional formatting feature to highlight the top 3 cells post calculation .]

Management would like to know – for each of the products, what was the contribution of each video game in terms of sales per region? Which video game had the most sales globally and in each of the regions?
1. Create a new worksheet titled “Q4 - % Sales for each VideoGame”.
2. Add a meaningful title.
3. Copy the names of the Video Games.
4. Using the data in worksheet titled “Q1 - Regional Sales”, represent all the numbers as percentages (percent of total revenue for that quarter). Round to the nearest whole number if needed.
5. Create a pie chart to illustrate the revenue of each video game in terms of global sales. The pie chart should have a title, data labels, and a legend. Format the chart to display the data clearly.
6. Write the name of the product that had the most sales globally.

Management would like to know – for each of the genres, what was the total sales?
1. Create a new worksheet titled “Q5 – Total Sales per Genre”.
2. Copy the list of Genres from Column B and paste it below the table
3. Using Data >> Remove Duplicates, clear the list of any duplicate values
4. Using the Global Sales in Column H, sum up the total sales for each Genre [Hint: Use Sumif to add up the values of Column H belonging to the same Genre]

Not the Exact Question you were looking for? Post your question for assignment help and get instant help on your homework and assignment questions from our experts.