Paycheck
So you've been working for your company several days and possibly weeks now and you would like to get paid. That's not going to happen all by itself. Someone, possibly you, will have to figure out how many hours you have contributed to your company's success and how many dollars that translates into. A spreadsheet is a good tool for such a task and luckily you have one at your fingertips, right next to Word which you use so often. It should be a snap!
Instructions
You started this project as an activity and should have an initial version of the file that will eventually be transformed in three different steps into the final product made up of multiple sheets (pages). There are many, many steps in this process and we will break them up into groups and print out intermediate results so that you can make sure you are on track.
Part 1
Format the cells in column C (PayRate) as currency.
Format the cells in column D (Hours) to show 3 decimal places to match the CompanyReport.
Insert seven columns between PayRate and Hours to record your schedule for each day of the week. Select seven columns starting at D and choose to insert.
Enter Monday into cell D1
Select the cell and then expand the selection to the right until just before Hours. The days of the week should get filled in.
Add a row at the top. Select row 1 and insert.
Above Monday type 12/5/10.
Format D1 through J1 as a Date.
Into E1 enter a formula =d1+1. Clicking can save some typing.
Copy the formula through the column for Sunday by selecting and dragging.
Realize that the date for Monday was incorrect. Change it to 12/6/10 and observe that the other cells are updated automatically.
Select the blank times Monday through Sunday for the first employee.
Erase the hours you had typed in previously. Instead of adding them up yourself, have Excel do that for you.
Enter 1 for the first employee's hours for Monday. This will help Word figure out how to sum all the hours.
It looks like this cell, and probably all the other blank ones, are formatted as currency. Select the range from D3 through K6 (D3:K6) and format the cells to display a number to three decimal places.
Select the first employee's hours for Monday through Sunday and the cell for Hours at the end. Then click AutoSum from the Home tab. The hours should be summed automatically. Enter additional hours on each day to test the functionality.
Format the Pay column as currency with two decimal places.
Copy cell K3 to the three cells below it so that the hours are added for the other employees as well.
Highlight the cells where data needs to be entered, D3:J6, by changing the background color to yellow. The fill color button can be used for this.
Rename the tab by right clicking on it and selecting rename. Have it match Monday's date, 12/6/10. You'll find that 12-06 works better.
You made it to the first checkpoint. Be certain that your project matches the sample document both in how it looks and how it acts.
Part 2
Label the C8 cell Total
Make D8 the sum of D3 through D7. Select D3 through D8 and use the AutoSum function.
Change the format of D8 to be a number with three decimal places.
While the cell is still selected, "stretch" the selection through J8. Totals for each separate day should appear as well as the total for the entire week.
Although you have an hourly wage at your desktop publishing company, you earn 1.5 times that if you work over 10 hours during the week and you get paid double on weekends. (In the one case this is to discourage the company from giving you too much work on school days and to encourage you to work on the weekends.) So, rather than a simple sum of hours, you will sum the hours in different categories. Change the Hours label to Weekday.
In cell K3 type "=sum(" and then select that row for the weekday columns. Lastly close the formula with a right parenthesis.
Copy and paste in the formula for the other employees.
After Weekday insert a column called Regular for your hours of regular pay. To do this, select the Pay column, right click, and choose insert.
Under the top label, at cell L3, enter the formula =MIN(10,K3). This takes the lesser of your hours and 10 for regular pay.
Copy the formula to the three cells below it.
Insert another column after it with the label Overtime
In the first overtime cell use the formula =K3-L3. You can click on the cells in order to enter their coordinates.
Copy the formula to the three cells below it.
Insert yet another column after this one with the label Weekend.
Use =I3+J3 in the first cell below the label. This adds hours from Saturday and Sunday.
Copy the formula to the three cells below it.
Extend the row of totals three more to the right by selected cell K8 and stretching it three rows to the right.
Now it's time to get paid. For the first worker in the Pay column you need to add the Regular hours, 1.5 times the overtime hours, and 2 times the weekend hours and multiple by the PayRate. The formula is =(L3+1.5*M3+2*N3)*C3.
Copy the formula to the three cells below it. You should now know how much you earn for the hours you put in.
Lastly, sum the earnings of all the employees by selecting the range O3:O8 and performing the AutoSum. This sum is how much the company has to have on hand (or in the bank) to process the payroll.
This is the second checkpoint. Compare your file to the reference version and how it acts.
Part 3
It is a new week, so you'll need to start a new tab. Although the default Workbook includes three Worksheets, make a new one for practice. Right click on the Sheet2 tab and choose Insert... and then Worksheet.
Name the Worksheet 12-13.
Return to 12-06 and press Ctrl+A once or twice to select the entire sheet. Press Ctrl+C to copy it. Switch to 12-13 and use Ctrl+V to copy. Now you have a space to enter this new week's data.
While the first sheet is still stored in the clipboard, copy it to the last sheet as well.
Rename the last sheet to YearlyTotals.
Rename the one remaining sheet to EndOfYear. This will just be a place holder with no data so that you can insert pages in front of it so that ranges work across sheets. Your sheets should now be named 12-06, 12-13, EndOfYear, and YearlyTotals.
On sheet 12-13 change the date of Monday to 12/13 and add some data that we can observe later in the yearly totals.
Return to the YearlyTotals page and remove the dates because they don't make sense.
The yellow background color doesn't make sense either, so change the background back to No Fill.
Here comes the 3D part. On each workday and for each worker we want to add up the hours for the entire year. Start on Monday for the first worker with =sum(. Click on the 12-06 tab and then select cell D3. Now press and hold the shift key and click on the tab for EndOfYear. The formula should become =SUM('12-06:EndOfYear'!D3 and as soon as you add the last ) and press enter, it will add up all the hours.
The Regular and Overtime calculations aren't meant for weeks rather than years, so their equations won't apply to this page. Instead of using their formulas, just sum up the Weekday, Regular, Overtime, and Weekend hours over the entire year. Select Monday's cell and stretch it all the way to the Pay column to get the sums.
While most of the row is now selected, stretch the selection downward to cover the other three workers. Now you should know how much each worker earned and how much the company paid in all.
In one of the copying steps, the currency formatting of the Pay column was lost. Select the column and reset the format to Currency with $ and two decimal places.
Congratulations. You have just done the books for the year. Double check your results from Parts 1 and 2 and make sure that changing your work hours on any day from the two weeks results in an automatic change in the yearly amounts. Compare your work to how the reference version looks and how it acts. Note that only the two new sheets are shown.
Delivery
First double check the lists above and verify that all listed items are present and functional. Email the file to the instructor or transfer it to the designated computer via a USB drive.
Grading
This project is longer than most, so it will be weighted as being equivalent to two of the normal sized projects. Each item in the instructions (except that last in each part) includes a change to the spreadsheet that is verifiable. Whether one used Ctrl+V to paste a formula, achieved the same result using the mouse, or typed out the formula cannot be ascertained after the fact. However, either the cell formula or format (or both) should match the instructions. Many students were able to add special formatting to the spreadsheets in dimensions outside the bounds of these instructions, such as creative fonts, interesting foreground colors, pay rates beyond compare, etc. For these additions, some extra credit may be granted. The list below summarizes the distribution of points:
Part 1: 20
Part 2: 20
Part 3: 13
Extra credit