Use cell references wherever possible. Input data is shaded in gray. You may not use cell references

that are unshaded until after you have been asked to answer the question to solve for that cell.

$1.$ Write a formula in cell Wages!D$3$ to be copied down to calculate the benefit value in dollars per

hour that will be paid for the corresponding Craft$-$Skill.

$2.$ Write an Excel formula for cell Crew!F$3$ to calculate the number of years the corresponding

employee has worked for the company. Round each answer to the nearest year. Remember that the

date of reference for this calculation can be found in the Info worksheet.

$3.$ Write a formula in cell Crew!G$3,$ which can be copied down the column, to determine the total

number of hours this person has worked for the current pay period $($from the Time worksheet$).$

Reminder, each person in a particular crew works the same number of hours, as described in the

problem description.

$4.$ Write a formula in cell Crew!H$3,$ which can be copied down the column, to determine the total dollar

amount of the compensation earned by the first person based on the number of hours worked. Total

compensation includes dollars earned from wages and dollars earned from benefits.

$5.$ Write a formula in cell Crew!I$3$ which can be copied down the column, to determine the percent of

total hours worked for the first employee listed. This formula must also work when copied across to

determine the percent of total compensation earned by the corresponding employee.

$6.$ Write an Excel formula for cell Summary!B$2$ to determine the highest total compensation earned by

your employees. This formula must also work when it is copied down to determine the second, third

and fourth highest total compensation amounts $($respectively$).$

$7.$ Write an Excel formula for cell Summary!B$9$ to be copied down to calculate the average number of

hours employees in the corresponding crew worked. This formula must also work when copied

across to calculate the average total compensation earned by employees in the corresponding crew.

$8.$ Write a formula in cell Crew!K$3$ which can be copied down the column to determine the seniority for

the corresponding employee. Seniority is based on the years of service to the company. The

employee with the most years of service to the company should be assigned the seniority of $1,$ the

employee with the second highest years of service should be assigned the seniority of $2,$ etc.

$9.$ Write an Excel formula for cell Crew!K$15$ to determine if none of your employees has ever had a

work related injury.

$3$

$10.$ Write an Excel formula for cell Crew!K$16$ to determine if Russell Peters is the only employee who has

ever had a work related injury. This formula does not need to automatically determine which

employee is Russell and which are not.

$11.$ Write an Excel formula for cell Crew!K$17$ to determine the seniority of the person with the second

highest total compensation?

$12.$ Your company has built in some incentives for your employees to work efficiently on every project

they complete by offering bonuses for how closely the project stays at or below budget at various

points in the process. Write a formula in call Crew!K$18$ to determine the dollar amount of the bonus

earned for this phase of the job based on the following criteria:

$$ If the total compensation paid out for all of the employees listed for this current pay period

$($assume the compensation for each employee in the Crew worksheet reflects this pay period$)$ is

less than the amount budgeted for this phase of the project $($see the Info worksheet to

determine what percentage of this project this pay period represents$),$ the employees will earn a

bonus of $5\%$ of the total compensation paid for this pay period.

$$ If the employees do not earn a $5\%$ bonus, check to see if the either the average total hours

worked by all employees this pay period was less than $26\mathrm{.}5$ or the highest total compensation

paid out to any one person this pay period was less than $$1300,$ then the employees will earn a

bonus of $3\%$ of the total compensation paid out this pay period.

$$ If neither of these sets of conditions is met, the employees will not earn a bonus