Final Assessment Instructions & Rubric:
You have been tasked to manipulate team data to create visual charts and/or calculations, that your team can use in their business proposal of open a gaming store based on PlayStation 4 games that have been released in North America. The report will demonstrate both graphical and numerical formats, the cost of the games in wholesale form, and the proposed profit to be made in their sales after applying their markup values. You will create charts to display results based on age and gender and their likelihood of purchasing these games along with filtered results. Include all the formulas, functions and formats listed below along with any additional formats you see suitable to create your report and make it unique to you.
Download the following Templates to begin your final assessment.
Open Excel_Final_Assessment_Template.xlsx & save in your documents folder with the name Games_Inventory_List_(Your last name-Your Student Number).xlsx
On the Games List sheet autofit the contents as needed
Apply Cell style Heading 1 to A1
Apply Cell Style Heading Style 3 to the range A3:J3
Sort the inventory list first by Game Title in descending order, and then by Developer in Ascending order (Use Custom sorting)
Apply % format to the range G4:G149
Apply freeze panes so rows 1:3 remain static when scrolling up and down.
Create a formula in the range H4:H149 that calculates the retail selling price of the games once the mark up price has been added to the Wholesale cost.
In K3 Type Inventory Value (use the format painter to apply the same formatting as cells A3:J3)
In the range K4:K149 insert a formula that calculates the total retail price of each game by the quantity in stock.
In Cells L3 Type Total Wholesale Cost, apply the same formatting as the rest of the column headings.
In cells L4:L149 create a formula to calculate the total Wholesale cost of the games on hand.
Apply Accounting format to all columns containing monetary values.
In I4 use Flash Fill to create unique Game List Codes by using first the Game ID and then the Genre for each listed in inventory. Hint: Be sure to place a – between the ID and Genre Name
Change the sort to one level by Game ID in Descending order.
Remember to save your work
Using the data on Game List worksheet create a table with Green, Table Style Medium 21
Using an Excel Feature, add a total row to the table, that calculates the Average Wholesale Cost, The average Mark Up Rate, the total Inventory Value on hand and the Total Wholesale cost on hand.
Add a column to the table on the Game List worksheet with the title Profit. Create a formula in the range M3:M149 that calculates how much profit can be made on the games currently in stock, be sure to sum it in the total row and highlight the total profit that can be made.
Create a conditional formatting rule to highlight all games that contain the word Shooter in their genre. (Use the fill color of your choice).
Add conditional formatting Star Ratings to the North America Release date column. (Note there should be no stars in the TBA or Unreleased games)
Create a 3D-Pie chart using only the Game Titles that begin with the letter R, and their Retail price. (Use the correct filter and sort options to select the appropriate Game Titles.)
Add “Best fit” Data Labels and change the Rec Room Game Fill Color Section to Standard Red.
Format Rogue Company Data series by exploding it 35% with a 25-degree angle.
Bold the legend and labels and move the chart to the worksheet named Retail Prices for R-Games. Change the Chart title to Retail Prices for R-Games.
On the Customer Survey Sheet – Use multilevel sort to sort the data, first by Participant ID Largest to smallest, and then by Age Smallest to largest, and then by gender A-Z.
Freeze the top row and apply the Title Style changing the font color to a color of your choice, be sure to apply center alignment, to the text.
In Cells L4, L5 & L6 use enter a COUNTIF formula to provide the answer of how many customers are interested in that specific Genre.
Create Thick weighted Outside borders around each answer in cells L4, L5 & L6, each with their own color applied
In Cells L14, L15 & L16 use a VLOOKUP function to determine the annual funds spent by each customer ID listed. (Be sure to return an exact match for each)
Apply currency format to cells L14:L16
Answer the questions in Cell L20:L22 using AVERAGEIFS, change the answers to number format with zero decimal places.
Create a Pivot Table of the Customer Survey data on the PGBG worksheet. Your pivot table should include all 3 Genders in the Rows, Genre of Interest at the columns (filter to display only Party, Strategy, Survival and Battle Royale genres) and Values from the total of Annual Funds spent on Gaming.
Insert a Slicer to display ages between 20 and 45 only – format the slicer style to Light Orange Slicer style, Dark 2
Name your Pivot Table Popular Genre by Age Group.
Add participant ID as a sub row to Gender and create a Pivot Chart based on your slicer filter results using the Clustered Bar option. Move the top left corner of the chart to cell A29 and drag the bottom right-hand corner to cell E49
Change the chart style to Style 11 and add Center Data Labels.
Name your Chart Visual PGBG, change the data series for Survival to Standard Yellow Fill, and Outline
Add primary Horizontal Axis title of “Money Spent”
Filter the Gender category to reflect Males only in the Pivot Chart – Save your work.
On the Games List sheet, Clear the filters in the Games Title column.
In the total row change the calculation for the Retail price to show the Maximum price listed. Create a conditional formatting rule to highlight that price in the Retail price list. (Use a color of your choice in the rule).
Find the Developer for that highest price listed and filter the table to display only games from that developer.
Copy the data shown in your filtered table to the worksheet named Max Revenue, paste the contents starting at cell A3.(be sure to include any formulas and number formatting)
Hint: there should be only four games listed.
Convert the data to a table style of your choice, add a total row and total the retail price list and the total inventory list.
In Cells K4:K7 use goal seek to change the retail price to 5 dollars more than the current price by changing the Mark up rate for each of the games, accepting the changes. Ensure to set the new markup rate to 2 decimal places for each of the games if required.
In Cell E10, create a formula that refers to the Total Wholesale Inventory Cost on the Game List sheet without any filters applied. As a new business, your company does not have this much funding upfront and would like to create a payment plan for the inventory purchases over a 3-year period. The wholesale company has agreed to this payment plan with a 2.4% fixed interest rate.
In Cell E11 type 2.4% and in E12 type 36, in cell G10 enter a payment function to calculate how much the monthly payment on the inventory will be for the wholesale total. (Be sure to return a positive value) Copy this number to Cell G11 without formulas or formatting, adjusting to show only 2 decimal places and bold.
In Cell F11 type Total Wholesale Monthly Payments, In Cell F10 type Adjusted Monthly Payments. (Note your adjusted payment will reflect the wholesale total for your filtered data only when changes are made to your table)
Group all sheets together and create a Footer that contains your Last Name in the Left Section, your Student Number in the Center and File Name code in the right.
(Note: refreeze panes that are undone because of the footer creation, after you return to normal view)
Ungroup the worksheets.
View your Retail Prices for R Games Pie Chart –make the necessary filter changes to the Game List worksheet to reflect the original Pie Chart “R-Games” -Review the Pie chart to ensure it has returned to its original state.
Navigate to the file advanced properties and add “Excel Final Assessment” as the Title, First Attempt as the Subject, Excel Skills as the Keywords
Inspect the workbook for any accessibility issues, correcting any that arise. (Reinspect if necessary)
Select the Game List worksheet (this should be number one in your workbook) and insert an icon or logo of your choice in Cell A2- format the logo/Icon to be exactly 2.5 inches high and 2.5 inches wide, adjust the row height to accommodate the logo. (Be sure to add Alt text and recheck accessibility issues)
Answer the questions on the “End of Assignment Quiz” using answers from the rest of your workbook. (Be sure to read any hints provided)
Note your correct and Incorrect Answers. If you have achieved a Minimum of 70% then upload to the Excel Final Assessment folder 1 in Blackboard.
If you have not achieved a Minimum of 70%, review your final assignment steps, correct the items you may have done incorrectly and re-answer your questions.