Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

COMP2200 – Information Systems

Quiz 3 – Version D

Fall 2022

All solutions must be contained within the QUIZ3 workbook, located in your COMP2200 One Drive folder and all answers must be contained in the worksheet(s) identified in the question.  Ensure that your solution follows best practices outlined in class including the use of Names, external referencing, and best practices of graphing.  All workbooks should have a documentation page.

Question 1

In the Q1_Salary worksheet add the following calculations:  All calculations should be rounded to 0 decimals.

Conditional Formatting

Format the salary (Column C) with a red fill for any salary greater than the average salary and green fill for any salary less than or equal to the average salary.

Summary Functions

Complete the following summary calculations in column K (higher marks gained if criteria are defined in a cell and referenced within your formula):

A.  What is the total salary for all active players from the defence team who make more than $1,000,000?

B. What is the number of offence team players drafted before 2019?

C. What is the players name with the highest salary (utilize an Index and Match)?

Question 2 – Compare the total salary for each position and status?  

Utilizing the data contained in worksheet, Q1_Salary, construct a pivot table that compares the total salary for each position for each player status.  

Construct a pivot chart from the pivot table that compares the total salary for each position for each player status.  

Add a timeline to the pivot table to filter by date drafted.

Question 3 – What is the composition of total salary by position for each team?

Construct a pivot table that calculates the composition of total salary by salary within each team in the worksheet Q3.

 Construct a pivot chart to visualize the composition of total salary by position for each team.

Ensure that your pivot table and chart utilize best practices and incorporates techniques to draw the reader’s attention.

Question 4 – Nested IF Statement

ACME Company has a bulk buy purchase that they offer customers.  They have found that the discounts are mot effective if applied differently for times of the year.

Between the months of January to the end of June, customers are offered a discount of 10% if the quantity was greater than or equal to 20 and a 5% discount if the quantity is over 10.  For the remainder of the months  the discounts have been reduced to 8% and 3% respectively.  Construct an IF statement in column E that will calculate the discount %?  The Total Cost is calculated (Price x Quantity x (1 – Discount)

** if you cannot get the discount by month working, just use the quantity discount to gain partial marks on the question.

Higher marks are gained when the variables are defined in cells and referenced.