Alvarez Enterprises employs three categories of workers: apprentice, machinist I, and machinist II. Olivia McGuirt Alvarez's human resources man­ager, is currently reviewing last year's payroll information and wonders if it's time to review standard labor rates. She has provided you the information and asked you IO conduct an analysis and provide a recommendation.

The Excel data file for answering this problem can be fund in WileyPLUS.

Required

a. Using the data for regular hours worked and gross pay, calculate each employee's actual hourly wage ate.

b. Calculate total hours worked and total pay for each employee.

c. Calculate total regular hours worked, total regular gross pay, total overtime hours worked, total overtime pay, total combined hours worked, and total combined pay for each job category. (You may find the SUMIF function helpful.)

d. Calculate the direct labor rate variance for each job category assuming the standard labor rate is set at the minimum hourly rate for the job category.

e. Calculate the direct labor rate variance for each job category assuming the standard labor rate is set at the maximum hourly rate for the job category.

f. Calculate the direct labor rate variance far each job category assuming the standard labor rate is set at the average hourly rate for the job category.

g. Which of the three potential standard labor rates - average, minimum, or maximum - do you recommend Olivia use for the coming year? Why?

h. If Olivia wants to reduce the direct labor rate variance in the future by hiring additional workers to absorb the overtime hours, how many new workers of each type should she hire? Assume that the number of hours required to meet production demands will not change in the future.