EXP ECH02 H2 - Metropolitan Zoo Gift Shop Weekly Payroll 2.1
As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas and avoid circular references.
For the purpose of grading the project you are required to perform the following tasks:
Start Excel. Download and open the file namedexploring_e02_grader_h2.xlsx. Read the business rules in the range A27:G34.
Enter an IF function in cell E5 to calculate the regular pay based on a regular 40-hour work week for the first employee. If the employee worked 40 hours or less, calculate the pay based on the hourly rate and number of actual hours worked. If an employee worked more than 40 hours, calculate the pay based on the hourly rate for only 40 hours. Use relative and absolute cell references appropriately. Do not enter the value 40 in the function. Refer to cell B21 that contains the value instead.
Enter an IF function in cell F5 to calculate the overtime pay for the first employee. If an employee worked less than 40 hours, the overtime pay is 0. If an employee worked 40 or more hours, calculate the overtime pay on only the overtime hours based on 1.5 times the hourly pay rate. Use relative and absolute cell references appropriately. Do not enter the values 40 and 1.5 in the function. Refer to cells B21 and B22 that contain those values.
Create a formula in cell G5 to calculate the gross pay based on the regular and overtime pay for the first employee.
Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent (stored in cell B24), and then subtract that from the gross pay. Use relative and absolute cell references appropriately.
Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. The function looks up the employee’s taxable pay, compares it to the table stored in the range D21:E25, and returns the appropriate tax rate from the second column of the lookup table. Then multiply the function results by the taxable pay. Use relative and absolute cell references appropriately.
Calculate FICA in cell J5 based on gross pay and the FICA rate (stored in cell B23). Use relative and absolute cell references appropriately.