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. Note all the skills required to complete this assessment can be found in the course materials.
This Excel_Final_Assessment_Template.xlsx
Follow the instructions below or download this Excel_Final_Assessment Instruction sheet.
Part A
1. Open Excel_Final_Assessment_Template.xlsx & save in your documents folder with the name Games_Inventory_List_(Your last name-Your Student Number).xlsx
2. On the Games List sheet autofit the contents as needed
3. Apply Cell style Heading 1 to A1
4. Apply Cell Style Heading Style 3 to the range A3:J3
5. Sort the inventory list first by Game Title in descending order, and then by Developer in Ascending order Hint:(Use Custom sorting, under the Sort & Filter Tool)
6. Apply % format to the range G4:G149- decrease the decimal place to zero.
7. Apply freeze panes so rows 1:3 remain static when scrolling up and down.
Hint: One down and One Over
8. 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. Hint: First determine what the mark up rate is by multiplying the Wholesale cost and the mark up rate then add it to the Wholesale cost. Your formula should not have any brackets.
9. In K3 Type Inventory Value (use the format painter to apply the same formatting as cells A3:J3)
10. In the range K4:K149 insert a formula that calculates the total retail price of each game by the stock total.
11. In Cells L3 Type Total Wholesale Cost, apply the same formatting as the rest of the column headings.
12. In cells L4:L149 create a formula to calculate the total Wholesale cost of the games in stock.
13. Apply Accounting format to all columns containing monetary values.
14. 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
15. Change the sort to one level by Game ID in Descending order. Hint: Remember use Custom Sort
Remember to save your work
16. Using the data on Game List worksheet create a table with Green, Table Style Medium 21 Hint: Check off “My table has Headers”
17. 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.
18. 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. Hint: Total profit is the inventory – the wholesale cost.
19. Create a conditional formatting rule to highlight all games that contain the word Shooter in their genre. (Use the fill color of your choice).
20. Add conditional formatting Star Ratings to the North America Release date column. (Note there should be no stars in the TBA or Unreleased games)
21. Create a 3D-Pie chart using only the Game Titles that begin with the letter R, and their Retail price. Hint: (Use the correct text filter options to select the appropriate Game Titles.- you should end up with 6 games.)
22. Add “Best fit” Data Labels and change the Rec Room Game Fill Color Section to Standard Red.
23. Format Rogue Company Data series by exploding it 35% with a 25-degree angle. Hint: Use the Format Data Point Menu
24. 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.
Part B
25. On the Customer Survey Sheet – Use a custom 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.
26. 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.
27. In Cells L4, L5 & L6 use enter a COUNTIF formula to provide the answer of how many customers are interested in that specific Genre, be sure to use the data range A2:E301. Hint: COUNTIF is demonstrated on the Practice Functions page of the course materials.
28. Create Thick weighted Outside borders around each answer in cells L4, L5 & L6, each with their own color applied
29. In Cells L14, L15 & L16 use a VLOOKUP function to determine the annual funds spent by each customer ID listed in the data range A2:E301 (Be sure to return an exact match for each) Hint: VLOOKUP is demonstrated on the Practice Functions page of the course materials.
30. Apply currency format to cells L14:L16
31. Answer the questions in Cell L20:L22 using AVERAGEIFS function, change the answers to number format with zero decimal places. Hint: AVERAGEIFS is demonstrated on the Practice Functions page of the course materials.
32. 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. Hint: Insert Pivot table and select the PGBG worksheet option and cell A1.
33. Insert a Slicer to display ages between 20 and 45 only – format the slicer style to Light Orange Slicer style, Dark 2
34. Name your Pivot Table Popular Genre by Age Group.
35. 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
36. Change the chart style to Style 11 and add Center Data Labels.
37. Name your Chart Visual PGBG, change the data series for Survival to Standard Yellow Fill, and Outline
38. Add primary Horizontal Axis title of “Money Spent”
39. Filter the Gender category to reflect Males only in the Pivot Chart – Save your work.
Part C
40. On the Games List sheet, Clear the filters in the Games Title column.
41. 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 with text that contains that value.(Use a color of your choice in the rule).
42. Find the Developer for that highest price listed and filter the table to display only games from that developer.
43. 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.
44. 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.
45. 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. Hint: Goal Seek is demonstrated on the Learn More about Data Analysis Page of the course content. (Remember there must be a formula in cells K4:K7 for Goal seek to work)
46. In Cell E10, create a formula (Or 3D cell reference) that refers to the Total Wholesale 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.
47. 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. Hint: The PMT Function is demonstrated on the PMT functions in Excel Content Page
48. 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)
49. Group all sheets together and create a Footer, (accept the freeze panes warning), 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)
50. Ungroup the worksheets.
51. 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.
52. Navigate to the file advanced properties and add “Excel Final Assessment” as the Title, First Attempt as the Subject, Excel Skills as the Keywords and your Name under the Tags section.
53. Inspect the workbook for any accessibility issues, correcting any that arise. (Reinspect if necessary)
54. 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)
55. Save your workbook. (Check that you have saved it according to Step 1 in Part A)
56. Answer all the questions on the “End of Assignment Quiz” using answers from the rest of your workbook. (Be sure to read any hints provided and answer all questions!) Read carefully before answering. Do not include = in front of any of your formulas.
57. 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.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
