PLEASE EMAIL YOUR ANSWER AT @cybersafetygyaan

Excel - Employee Payroll Project In this Project, your task is to create an Excel worksheet with formulas where needed to calculate Employee Payroll. IMPORTANT-MARKS WILL BE DEDUCTED IF FORMATTING IS NOT DONE EXACTLY AS BELOW FORMATTING DETAILS All Column headings must be bold, font size 18 pts, color light blue, color for Net Pay is light green, for Gross Pay is dark green and for Total Deductions is yellow All column headings must be Centered within their cells 2 decimal points must be used for all cells with numbers All worksheet cells must have ALL BORDERS Font size for cells other than headings is 14 pts ONLY Cells where formulas have been applied must be in light grey color All other cells should have no shading or color The following column names are required EMPLOYEE PAYROLL (in cell A1) (Main Heading, Center Justified) EMPLOYEE A2 (Enter any 10 names, A3 to A12) MON, TUE, WED, THU, FRI, SAT, SUN B2 to H2 REGULAR-HRS 12 OVERTIME-HRS J2 TOTAL-HRS K2 REGULAR-PAY-RATE L2 OVERTIME-PAY-RATE M2 REGULAR-PAY N2 OVERTIME-PAY 02 INCOME-TAX P2 OTHER-DEDUCTIONS (enter $15.00 for all employees) UNION-FEES R2 (enter 3.75 for all employees) TOTAL-DEDUCTIONS S2 GROSS-PAY T2 NET-PAY U2 Q2 . The following cells will need Formulas: REGULAR-HRS OVERTIME-HRS TOTAL-HRS OVERTIME-PAY-RATE REGULAR-PAY OVERTIME-PAY INCOME TAXES TOTAL-DEDUCTIONS GROSS-PAY NET-PAY Enter the following work hours for all employees as shown below: A2 D2 E2 F2 G2 H2 12 12 K2 L2 B2C2 Name Mon Tue 1 Any 8 Wed Thu Sat Sun Pay Rate 8 8 8 8 8 0 21.00 2 Any 8 8 8 6 0 0 0 12.50 3 Any 8 8 8 8 8 0 0 11.75 4 Any 6 7 6 8 8 0 0 18.00 5 Any 8 8 8 8 10 10 0 6.00 6 Any 7 7 7 7 7 0 0 6.50 7 Any 8 8 8 8 8 8 0 7.00 8 Any 9 9 12 12 10 10 0 9.50 9 Any 8 8 0 0 0 0 0 15.00 10 Any 8 8 8 0 0 0 0 19.00 Income Tax Calculation 1. Calculate 15% of gross pay as income tax for employees with Regular Pay Rate over $12 an hour 2. Calculate 10% of gross pay as income tax for employees with Regular Pay Rate LESS THAN $12 an hour and GREATER THAN $7.00 an hour (i.e. between $7.01 and $11.99) 3. Enter zero (No Tax) for employees earning Regular Pay Rate of $7 or less per hour FORMULA The regular format of the IF formula is IF(x1,1,21) (this means that If x1 is true then do y1, else do zl) However in our case we need Nested IF statement like IF(x1, y1, (IF(x2, y2,22)) The 21 part in the above Nested IF statement is an IF statement also by itself with its own x,y,z parameters written here as x2, y2 and 22 for clear understanding. Any of the x, y, or z can be a cell name and its value like A15, or an expression with formula or function This is how our Nested IF formula works: The first xl part checks if per hour rate is over $12. if true, the y1 part calculates 15% of gross-pay as income tax. If not true then the z1 part starts, which is an IF statement with its own x,y and z parameters. We call that here x2, y2 and 22. In the 21 part which is the second IF statement with x2, y2 and 22, the x2 part says if per hour rate is $7 or less than $7 then y2 part is to insert zero as income tax, otherwise the 22 part will then calculate income tax by calculating 10% of gross-pay for those Employees with regular-pay-rate of between $7.01 and $11.99 per hour. So let's now translate the income tax formula in English using meaningful column names: IF(REGULAR-PAY-RATE IS GREATER THAN 12), INCOME-TAX is GROSS-PAY* 15%, else IF(REGULAR-PAY-RATE IS LESS THAN OR EQUAL TO 7, INCOME-TAX is ZERO, else INCOME-TAX is GROSS-PAY* 10% All you need to do is to write the IF formula based on the explanation and and formula explanation. Other formulas are all simple and straight-forward A snapshot picture of how the completed Payroll worksheet should look like in included B F K V W Y Z AA AE A D E G H 1 M N 0 P R $ T U PAY ROLL EMPLOYEE MON TUE WED THU FAR SAT SUN REGULAR-HRS OVERTIME-HRS TOTAL-HRS REG-PAY-RATE OVERTIME-PAY-RATE REGULAR-PAY OVERTIME-PAY INCOME TAXES OTHER-DEDUCTIONS UNION-FEES TOTAL-DEDUCTIONS GROSS-PAY NET-PAY ANKIT 8.00 8.00 8.00 8.00 8.00 8.00 0.00 40.00 8.00 48.00 21.00 31.50 840.00 252.00 163.80 15.00 3.75 182.55 1092.00 909.45 PRAGNESH 8.00 8.00 8.00 6.00 0.00 0.00 0.00 30.00 0.00 30.00 12.50 0.00 375.00 0.00 56.25 15.00 3.75 75.00 375.00 300.00 GOVIND 8.00 8.000 8.00 8.00 8.00 0.00 0.00 40.00 0.00 40.00 11.75 0.00 470.00 0.00 47.00 15.00 3.75 65.75 470.000 404.25 MASSI 6.00 7.00 6.00 8.00 8.00 0.00 0.00 35.00 0.00 35.00 18.00 0.00 630.00 0.00 94.50 15.00 3.75 113.25 630.00 516.75 ALLEN 800 8.00 800 8.00 10.00 10.00 0.00 40.00 12.00 52.00 6.00 9.00 240.00 108.00 0.00 15.00 3.75 18.75 348.00 329.25 BORDER 7.00 7.00 7.00 7.00 7.00 0.00 0.00 35.00 0.001 35.00 6.50 0.00 227.50 0.00 0.00 15.00 3.751 18.75 STEVE 227.50 208.75) 8.00 8.00 8.00 8.00 8.00 8.00 0.001 40.00 8.00 48.00 7.00 10.50 280.00 84.00) 0.00 15.00 3.75 18.75 364.00 345.25 ARPAN 9.00 9.00 12.00 12.00 12.00 10.00 10.00 0.00 40.00 22.00 62.00 9.50 14.25 380.00 313.50 69.35 15.00 3.75 88.10 693.50 605.40 SUMIT 8.00 8.00 0.00 0.00 0.00 0.00 0.00 16.00 0.00) 16.00 15.00 0.001 240.00 0.00 36.00 15.00 3.75 54.75 240.00 8.000 AJIT 8.00 185.25 8.00 0.00 0.00 0.00 0.00 24.00 0.00 24.00 0.001 456.00 0.00 68.40 15.00 3.75 87.15 456.00 368.85 LI TTTTTTT 19.00 Excel - Employee Payroll Project In this Project, your task is to create an Excel worksheet with formulas where needed to calculate Employee Payroll. IMPORTANT-MARKS WILL BE DEDUCTED IF FORMATTING IS NOT DONE EXACTLY AS BELOW FORMATTING DETAILS All Column headings must be bold, font size 18 pts, color light blue, color for Net Pay is light green, for Gross Pay is dark green and for Total Deductions is yellow All column headings must be Centered within their cells 2 decimal points must be used for all cells with numbers All worksheet cells must have ALL BORDERS Font size for cells other than headings is 14 pts ONLY Cells where formulas have been applied must be in light grey color All other cells should have no shading or color The following column names are required EMPLOYEE PAYROLL (in cell A1) (Main Heading, Center Justified) EMPLOYEE A2 (Enter any 10 names, A3 to A12) MON, TUE, WED, THU, FRI, SAT, SUN B2 to H2 REGULAR-HRS 12 OVERTIME-HRS J2 TOTAL-HRS K2 REGULAR-PAY-RATE L2 OVERTIME-PAY-RATE M2 REGULAR-PAY N2 OVERTIME-PAY 02 INCOME-TAX P2 OTHER-DEDUCTIONS (enter $15.00 for all employees) UNION-FEES R2 (enter 3.75 for all employees) TOTAL-DEDUCTIONS S2 GROSS-PAY T2 NET-PAY U2 Q2 . The following cells will need Formulas: REGULAR-HRS OVERTIME-HRS TOTAL-HRS OVERTIME-PAY-RATE REGULAR-PAY OVERTIME-PAY INCOME TAXES TOTAL-DEDUCTIONS GROSS-PAY NET-PAY Enter the following work hours for all employees as shown below: A2 D2 E2 F2 G2 H2 12 12 K2 L2 B2C2 Name Mon Tue 1 Any 8 Wed Thu Sat Sun Pay Rate 8 8 8 8 8 0 21.00 2 Any 8 8 8 6 0 0 0 12.50 3 Any 8 8 8 8 8 0 0 11.75 4 Any 6 7 6 8 8 0 0 18.00 5 Any 8 8 8 8 10 10 0 6.00 6 Any 7 7 7 7 7 0 0 6.50 7 Any 8 8 8 8 8 8 0 7.00 8 Any 9 9 12 12 10 10 0 9.50 9 Any 8 8 0 0 0 0 0 15.00 10 Any 8 8 8 0 0 0 0 19.00 Income Tax Calculation 1. Calculate 15% of gross pay as income tax for employees with Regular Pay Rate over $12 an hour 2. Calculate 10% of gross pay as income tax for employees with Regular Pay Rate LESS THAN $12 an hour and GREATER THAN $7.00 an hour (i.e. between $7.01 and $11.99) 3. Enter zero (No Tax) for employees earning Regular Pay Rate of $7 or less per hour FORMULA The regular format of the IF formula is IF(x1,1,21) (this means that If x1 is true then do y1, else do zl) However in our case we need Nested IF statement like IF(x1, y1, (IF(x2, y2,22)) The 21 part in the above Nested IF statement is an IF statement also by itself with its own x,y,z parameters written here as x2, y2 and 22 for clear understanding. Any of the x, y, or z can be a cell name and its value like A15, or an expression with formula or function This is how our Nested IF formula works: The first xl part checks if per hour rate is over $12. if true, the y1 part calculates 15% of gross-pay as income tax. If not true then the z1 part starts, which is an IF statement with its own x,y and z parameters. We call that here x2, y2 and 22. In the 21 part which is the second IF statement with x2, y2 and 22, the x2 part says if per hour rate is $7 or less than $7 then y2 part is to insert zero as income tax, otherwise the 22 part will then calculate income tax by calculating 10% of gross-pay for those Employees with regular-pay-rate of between $7.01 and $11.99 per hour. So let's now translate the income tax formula in English using meaningful column names: IF(REGULAR-PAY-RATE IS GREATER THAN 12), INCOME-TAX is GROSS-PAY* 15%, else IF(REGULAR-PAY-RATE IS LESS THAN OR EQUAL TO 7, INCOME-TAX is ZERO, else INCOME-TAX is GROSS-PAY* 10% All you need to do is to write the IF formula based on the explanation and and formula explanation. Other formulas are all simple and straight-forward A snapshot picture of how the completed Payroll worksheet should look like in included B F K V W Y Z AA AE A D E G H 1 M N 0 P R $ T U PAY ROLL EMPLOYEE MON TUE WED THU FAR SAT SUN REGULAR-HRS OVERTIME-HRS TOTAL-HRS REG-PAY-RATE OVERTIME-PAY-RATE REGULAR-PAY OVERTIME-PAY INCOME TAXES OTHER-DEDUCTIONS UNION-FEES TOTAL-DEDUCTIONS GROSS-PAY NET-PAY ANKIT 8.00 8.00 8.00 8.00 8.00 8.00 0.00 40.00 8.00 48.00 21.00 31.50 840.00 252.00 163.80 15.00 3.75 182.55 1092.00 909.45 PRAGNESH 8.00 8.00 8.00 6.00 0.00 0.00 0.00 30.00 0.00 30.00 12.50 0.00 375.00 0.00 56.25 15.00 3.75 75.00 375.00 300.00 GOVIND 8.00 8.000 8.00 8.00 8.00 0.00 0.00 40.00 0.00 40.00 11.75 0.00 470.00 0.00 47.00 15.00 3.75 65.75 470.000 404.25 MASSI 6.00 7.00 6.00 8.00 8.00 0.00 0.00 35.00 0.00 35.00 18.00 0.00 630.00 0.00 94.50 15.00 3.75 113.25 630.00 516.75 ALLEN 800 8.00 800 8.00 10.00 10.00 0.00 40.00 12.00 52.00 6.00 9.00 240.00 108.00 0.00 15.00 3.75 18.75 348.00 329.25 BORDER 7.00 7.00 7.00 7.00 7.00 0.00 0.00 35.00 0.001 35.00 6.50 0.00 227.50 0.00 0.00 15.00 3.751 18.75 STEVE 227.50 208.75) 8.00 8.00 8.00 8.00 8.00 8.00 0.001 40.00 8.00 48.00 7.00 10.50 280.00 84.00) 0.00 15.00 3.75 18.75 364.00 345.25 ARPAN 9.00 9.00 12.00 12.00 12.00 10.00 10.00 0.00 40.00 22.00 62.00 9.50 14.25 380.00 313.50 69.35 15.00 3.75 88.10 693.50 605.40 SUMIT 8.00 8.00 0.00 0.00 0.00 0.00 0.00 16.00 0.00) 16.00 15.00 0.001 240.00 0.00 36.00 15.00 3.75 54.75 240.00 8.000 AJIT 8.00 185.25 8.00 0.00 0.00 0.00 0.00 24.00 0.00 24.00 0.001 456.00 0.00 68.40 15.00 3.75 87.15 456.00 368.85 LI TTTTTTT 19.00