PLEASE MAKE AN EXCEL FLE WTH GVEN NSTRUCTONS I SENT THE EXAMPLE EXCEL PAPER MY STUDENT ID IS 11916024. PLEASE BUT PLEASE UPLOAD AN EXCEL FLE

Consider the Excel file named Homework 1". This file contains information about the payroll of employees in the PHM Reliable catering firm. The question is in the first worksheet titled "Question". This excel file is a protected excel file, so you cannot make any changes. First copy contents of this excel worksheet exactly to your own excel worksheet. Values in your copied excel sheet should be at the exact same place as in the original worksheet. You will get zero points if you don't do this step regardless of what you have done in the following steps. In the second worksheet titled "Example Answer", there is an example answer. You can get hints from that excel worksheet. Part 1 (80 Points): Calculations and Sparkline Chart 1) (10 points) Write a formula for the Gross Pay column for each employee using the "IF" function: a. 40*(Rate per Hour)+(Hours Worked-40)*1.5*Rate per Hour if Hours Worked is larger than 40; b. Rate per Hour*Hours Worked if Hours Worked less than or equal to 40. 2) (10 points) Enter the value of the Federal Tax Rate at B17 as the first two digits of your University ID in percentage. For instance, if your ID is 1234567 than enter 12%. 3) (10 points) Enter the value of State Tax Rate at B18 as the last digit of your University ID in percentage. For instance, if your ID is 1234567 than enter 7%. If the last digit is O, then enter %10. 4) (10 points) Write a formula for the Federal Tax column of each employee as Federal Tax Rate*Gross Pay. 5) (10 points) Write a formula for the State Tax column of each employee as State Tax Rate *Gross Pay. 6) (10 points) Write a formula for the Net Pay column of each employee as Gross Pay-State Tax- Federal Tax. 7) (10 points) Calculate the total, average, highest and lowest values of Withholding Allowances, Gross Pay, Federal Tax, State Tax and Net Pay in labeled rows. 8) (10 points) Create a Sparkline chart at 115 by using the data in the Net Pay column (excluding the total, average, highest and lowest values) Part 2 (20 Points): Formatting 1) (10 points) Use Conditional Formatting for Net Pay with the following rule: Fill the cells as light red if they are below the average of Net Pay. 2) (5 points) Format your worksheet similar to the "Example Answer". 3) (5 points) Change the sheet name with your university ID. You should do this by your own. All steps of this homework should be only completed by you. You should use your own computer while doing this homework. All types of collaborations are prohibited. If I detect any cheating attempt, I reserve the right to do an oral examination instead of this homework. Please submit (upload) only your excel file. Any other file format or screenshots will not be accepted. Moreover, you need to change the file name as "studentID.xlsx". For instance, my file will look like "1234567.xlsx". Don't forget to do this or you will not get any points from this assignment. We wish you success. B C D E F G H 1 1 PHM Reliable Catering 2 Weekly Payroll Report 3 Hire Date Withholding Allowances Rate per Hour Hours Worked Gross Pay Federal Tax State Tax Net Pay 4 3 Ocak 2015 Cumartesi 1,0 18,5 45.0 878,8 184,5 131.8 562.4 5 6 ubat 2015 Cuma 2,0 14.3 28,0 399,0 83,8 59,9 255,4 6 5 Kasm 2015 Perembe 0,0 16.0 32.5 520,0 109,2 78,0 332,8 7 12 Kasm 2016 Cumartesi 2.0 18,5 40.0 740.0 155,4 111.0 473.6 8 9 Austos 2016 Sali 2.0 14.3 30,0 427.5 89,8 64,1 273,6 9 15 Nisan 2017 Cumartesi 3,0 19,0 52,0 1102.0 231,4 165,3 705,3 10 2 Haziran 2017 Cuma 1,0 13,0 27,3 354.3 74.4 53,1 226,7 11 11,0 113,5 254,8 4421.5 928,5 663,2 2829,8 12 1.6 16.2 36.4 631,6 132.6 94,7 404,3 13 3,0 19.0 52.0 1102.0 231.4 165.3 705.3 14 0,0 13,0 27.3 354.3 74.4 53.1 226,7 15 16 17 21% 18 15% 19 20 21 24 25 26 27 28 29 30 31 32 33 34 35 36 38 39 40 41 Quortion Gamle Om