Homework 1

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.
a. 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).
b. 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 4).
c. 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 4).
d. 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. first draft by Saturday.

Homework 1