Question

Table 1: Time Clock Data from Security Detail (Supervisor is on salary)

Employee	Rate	Monday	Tuesday	Wednesday	Thursday	Friday
Supervisor	$25.00	8.0	8.0	8.0	8.0	8.0
Officer 1A	$20.00	8.1	7.6	8.2	7.5	7.8
Officer 1B	$20.00	7.4	7.3	7.8	7.1	8.1
Officer 2A	$17.50	7.0	8.3	8.4	8.1	8.4
Officer 2B	$17.50	8.2	7.3	7.7	7.7	8.4
Officer 2C	$17.50	7.1	7.5	8.5	8.3	7.7
Officer 3A	$15.00	7.2	7.8	8.3	7.8	7.6
Officer 3B	$15.00	7.5	7.0	7.8	7.3	7.5
Officer 3C	$15.00	7.1	7.0	7.4	7.0	7.5
Officer 3D	$15.00	8.2	7.1	8.3	8.5	8.0

1. Using Table 1 above, create a second table that contains the daily payroll cost for each employee (rate times hours worked). Make this easy. Enter a single formula in the cell that is on the supervisor row and Monday column. Use absolute cell reference (dollar sign) to freeze the rate column in your formulas. Then copy this formula to the rest of the cells. Hint: to set the $ signs, put your cursor on a cell reference in the formula and press the F4 key (Doing this on a Mac is a bit different!)
2. Create a total column at the right of Friday. Use the SUM() function to sum the weekly cost of each employee. Then, use the SUM() function to compute the total payroll cost for all employees.
3. Now let’s compute some summary statistics.
   1. Create a new column in another part of the spreadsheet. The first column of this new table will be Position. List all four positions below the heading (Supervisor, Officer 1, Officer 2, and Officer 3).
   2. Create a new column in this table called Position Cost. Then, use the SUM() function to compute the total cost by position (The function should reference the total cost calculations from part 2).
   3. Create a new column in this table called Average Cost. Then, use the AVERAGE() function to compute the average cost by position. (The function should reference the total cost calculations from part 2).
   4. Finally, create a new column called Proportion. Then, compute the proportion of the total payroll cost consumed by each position. To do this, divide position cost (calculated in part b above) by the total payroll cost. Enter the formula in the first row, reference the position cost of the supervisor, divide by an absolute cell reference to the total cost cell, and copy this formula to the other positions. State this proportion as a percentage. My formula for supervisor looks like this: =J15/$P$12, where J15 refers to supervisor position cost and P12 refers to the total cost cell. Your cell references will probably be different, but the format should be the same.

Answer 1

Paste this data to an excel sheet to obtain desired results. Ensure that the top left corner 'Employee' Cell is at 1A position.

Employee	Rate	Monday	Tuesday	Wednesday	Thursday	Friday
SuperVisor	25	8	8	8	8	8
Officer 1A	20	8.1	7.6	8.2	7.5	7.8
Officer 1B	20	7.4	7.3	7.8	7.1	8.1
Officer 2A	17.5	7	8.3	8.4	8.1	8.4
Officer 2B	17.5	8.2	7.3	7.7	7.7	8.4
Officer 2C	17.5	7.1	7.5	8.5	8.3	7.7
Officer 3A	15	7.2	7.8	8.3	7.8	7.6
Officer 3B	15	7.5	7	7.8	7.3	7.5
Officer 3C	15	7.1	7	7.4	7	7.5
Officer 3D	15	8.2	7.1	8.3	8.5	8
Employee	Rate	Monday	Tuesday	Wednesday	Thursday	Friday	Total
SuperVisor	25	=$B16*C2	=$B16*D2	=$B16*E2	=$B16*F2	=$B16*G2	=SUM(C16:G16)
Officer 1A	20	=$B17*C3	=$B17*D3	=$B17*E3	=$B17*F3	=$B17*G3	=SUM(C17:G17)
Officer 1B	20	=$B18*C4	=$B18*D4	=$B18*E4	=$B18*F4	=$B18*G4	=SUM(C18:G18)
Officer 2A	17.5	=$B19*C5	=$B19*D5	=$B19*E5	=$B19*F5	=$B19*G5	=SUM(C19:G19)
Officer 2B	17.5	=$B20*C6	=$B20*D6	=$B20*E6	=$B20*F6	=$B20*G6	=SUM(C20:G20)
Officer 2C	17.5	=$B21*C7	=$B21*D7	=$B21*E7	=$B21*F7	=$B21*G7	=SUM(C21:G21)
Officer 3A	15	=$B22*C8	=$B22*D8	=$B22*E8	=$B22*F8	=$B22*G8	=SUM(C22:G22)
Officer 3B	15	=$B23*C9	=$B23*D9	=$B23*E9	=$B23*F9	=$B23*G9	=SUM(C23:G23)
Officer 3C	15	=$B24*C10	=$B24*D10	=$B24*E10	=$B24*F10	=$B24*G10	=SUM(C24:G24)
Officer 3D	15	=$B25*C11	=$B25*D11	=$B25*E11	=$B25*F11	=$B25*G11	=SUM(C25:G25)
						Total Payroll for all	=SUM(H16:H25)
Position	Position Cost	Average Cost	Proportion
Supervisor	=SUM(H16)	=AVERAGE(H16)	=B30/H26*100
Officer 1	=SUM(H17:H18)	=AVERAGE(H17:H18)	=B31/H26*100
Officer 2	=SUM(H19:H21)	=AVERAGE(H19:H21)	=B32/H26*100
Officer 3	=SUM(H22:H25)	=AVERAGE(H22:H25)	=B33/H26*100