IMPORTANT NOTE: This site will not let me post the text of the excel sheet. I can only post an image of the excel sheet. Sorry about that.

I need help with this assignment. Please follow all instructions for this assignment. The excel data sheet, the "IF Q1" tab, and the "IF Q2" tab are on the bottom. Thank you.

Instructions

Excel Data Sheet

"IF Q1"

Total	Performance Evaluation			PASS	$40,000
				GOOD	$48,000
				EXCELLENT	$64,000

"IF Q2"

Performance Evaluation

PASS

$8,000

For PART 1 Pivot Table tasks, you need to create new tabs that contain required tables. For PART 3 IFs tasks, you need to keep and show all formulas. If you only show values, you won't get points. You need to write down your answers of PART 2 & 3 questions in a SEPARATE doc/PDF file. You need to submit both your excel file (with tabs/tables and formulas) and your doc/PDF file. PART 1: Pivot Table (10 Points) Task 1: Create a Total column to the dataset in the "Sales" tab, the Total should be the sum of four quarters sales. Name Gender Region Role 01 02 03 04 Total Task 2: Use PivotTable to create a Quarterly Region Sales table, which should at least contain the following columns (you can add any columns you need, or you feel interesting to display): Regions Sum of Q1 Sum of Q2 Sum of Q3 Sum of Q4 Sum of Total Task 3: Use Pivot Table to create a Quarterly Employee Sales table, which should at least contain the following columns (you can add any columns you need, or you feel interesting to display) and Gender as filter: Names Sum of Q1 Sum of Q2 Sum of Q3 Sum of Q4 Sum of Total Task 4: Use Pivot Table to create a Role Sales table, which should at least contain the following columns (you can add any columns you need, or you feel interesting to display): Roles Sum of Total Percentage of Total PART 2: Questions (10 Points) 1. Based on the Quarterly Region Sales Table: a. Which region has the highest sales record in Q1? (1 Point) b. Which region has the lowest sales record in Q3 (1 Point) c. Which region has the overall second highest sales record? (1 Point) d. What is the employee average sales record for the Midwest region in Q4? (2 Points) 2. Based on the Quarterly Employee Sales Table: a. In all male employees, who has the highest sales record in Q2? (1 Point) b. In all female employees, who has the lowest sales record in Q3? (1 Point) c. In all employees, which five employees are the top 5 in total sales record? (1 Point) 3. Based on the Role Sales Table: a. What is the proportion of different roles in total sales? (2 Points) PART 3: IFs (10 Points) 1. In the tab "IF Q1", Copy and paste the values of your Total column from PART 1 - Task 1. If an employee's TOTAL sales exceed $ 64,000, he/she will be considered EXCELLENT. If this employee's total sales are below $ 40,000, we consider his/her performance to be POOR. You will find several other conditions at the top right of the tab. Complete the Performance Evaluation column (3 Points) and answer the following questions: a. How many employees are considered to have EXCELLENT performance? (1 Point) b. How many employees are considered to have POOR performance? (1 Point) 2. Now, the company developed another evaluation system. In the tab IF Q2, If an employee's sales exceed $ 8,000 for ALL quarters, he/she will be considered PASS. Otherwise, we consider his/her performance to be FAILED. Complete the Performance Evaluation column (3 Points) and answer the following questions: a. How many employees are considered to have PASS performance? (1 Point) b. How many employees are considered to have FAILED performance? (1 Point) Q1 B 1 Name Gender 2 Ahyan Jackson Male 3 Alicia Villalobos Female 4 Aneesah Ellwood Female 5 Axl Hancock Male 6 Bartlomiej Hodge 7 Brian Lowe Male 8 Cali Perry Male 9 Carley Holland Female 10 Christos Moody Male 11 Clementine Hebert Female 12 Cobie Millar Male 13 Daisy-May Maxwell Female 14 Darin Holland Male 15 Deen Wood Male 16 Eesa Sawyer Male 17 Fariha Neville Female 18 Griffin Odonnell Male 19 Haniya Randall Female 20 Harlee Dalton Male 21 Harrison Lamb Male 22 Howard Goddard Male 23 Issa Rosales Male 24 Jude Lovell Female 25 Julian Noble Male 26 Katerina Kelly Female C D Region Role South Junior Associate South Senior Associate Northeast Manager South Junior Associate Midwest Manager South Manager South Manager Midwest Junior Associate Midwest Senior Associate Northeast Junior Associate South Senior Associate West Junior Associate West Junior Associate South Junior Associate South Manager South Junior Associate South Junior Associate Midwest Senior Associate West Junior Associate South Manager West Manager Manager Northeast Manager West Junior Associate South Senior Associate West Senior Associate E Q2 10935 8720 19255 7757 17259 19426 6705 15057 18793 6817 13664 16957 17637 6978 9619 17321 9266 14083 13668 14108 14693 18195 18758 12378 16995 F Q3 18086 15791 16704 11745 14725 18178 13900 10176 15394 7050 15018 11406 13735 12877 19858 10597 14139 18877 9910 16767 12402 15182 6958 12133 10438 G Q4 18182 10429 9124 15349 9200 17544 9205 15110 11838 19410 19018 19759 17213 14798 15733 7055 17132 15053 17366 8799 8967 9035 10390 13075 19842 9156 10486 13454 11635 19284 14210 19412 17131 9944 6606 6341 13783 9162 18001 7220 19951 16505 7167 13302 6415 12986 19132 11189 14012 13953 South South Northeast Northeast South West 27 Keiran Hilton Male 28 Kirk Kirkland Male 29 Krzysztof Vance Male 30 Laurel Jarvis Female 31 Linda Avery Female 32 Lisa Wicks Female 33 Luis Hook Male 34 Lylah Joseph Female 35 Macie O'Reilly Female 36 Maurice Alvarado Male 37 Merryn Crossley Male 38 Nadia Frank Female 39 Parris Powers Male 40 Pearl Felix Male 41 Rhonda Hackett Female 42 Roshan Pierce Male 43 Roxy Keeling Female 44 Saffron Amos Female 45 Shivam Barrett Male 46 Suman Keith Female 47 Ty Stone Male 48 Tye Price Male 49 Uzair Moses Male 50 Wade Shaffer Female 51 Waseem Melia Male Senior Associate Senior Associate Junior Associate Junior Associate Junior Associate Senior Associate Manager Senior Associate Junior Associate Senior Associate Senior Associate Junior Associate Junior Associate Junior Associate Junior Associate Junior Associate Junior Associate Junior Associate Junior Associate Senior Associate Senior Associate Manager Junior Associate Junior Associate Senior Associate South West Midwest Northeast South Northeast Northeast South South South Northeast Northeast Northeast Midwest Northeast West South West West 19229 8440 6024 8442 10574 17446 16145 6436 11677 14726 14617 15419 12488 14202 18026 8744 15825 12012 10003 13067 11039 12505 10290 19887 19603 19380 11651 11764 6414 18953 12267 11933 12092 9767 11706 15282 16730 13222 7564 14066 18383 11725 17472 16210 19515 17136 18343 19924 14908 6725 17716 9292 8382 13934 17412 11050 19395 7821 14698 13856 7404 8574 7188 17066 15469 9336 15787 14167 9384 12586 12708 10950 10529 15689 13962 10450 8235 13228 14534 13371 9028 6452 17962 19788 7039 11295 9398 9090 13886 15329 7012 9693 7758 6314 7024 15905 9371 7876 17339 7356