Computer Science and Computer Engineering 120
Computerized Information Systems
Fall 2006



Lab 9

Excel Review

The goal for this lab is to help you begin your review of Excel.

CSCE, Inc has hired you to complete a spreadsheet that contains information about vacation pay. The company has a liberal vacation policy that allows employees to have up to 6 weeks of paid vacation annually. Moreover, the company will buy back up any unused vacation time up to 1/2 of the total the employee is allowed.

For example, suppose John is allowed 4 weeks of vacation but has taken only three weeks. Because his unused time is less than 1/2 of what he allowed, he will be paid for 1 week of unused vacation time. On the other hand, suppose Sue who also get 4 weeks of vacation, has only taken 1 week of paid vacation. She was 3 weeks of unused vacation. But she will only be paid for 2 of those weeks. She will loss 1 week of unused vacation. (Note: Actual calculations are done by in days instead of weeks.)

The company president is concerned about how much money the company needs to reserve for vacation pay buy outs and has asked for a spreadsheet report with the total amount that they will need to pay out if employees take no further vacation this year.

Your boss' notes:

• Download the file Lab09.xls from the class' Excel download folder, http://www.cs.plu.edu/courses/csce120/fall06/download/excel/. Complete the spreadsheet.
• Assume that a year has 365.25 days (to account for leap years).
• Assume employees work 8 hour days, 5 days a week for 40 hour weeks.
• In the Liability for Vacation Pay column, calculate the amount that the employee would be paid for unused vacation time. For example, recall Sue who would be paid for 2 weeks or 10 days of unused paid vacation. If she was paid $20 an hour, she would get
      10 days * 8 hours/day * $20/hour = $1600.
  as her vacation buy-out.
• The partially completed spreadsheet contains a table giving the number of weeks of vacation depending on years of service. A second table gives the pay rate for various job positions in the firm.
• You can use additional columns of formulas starting in column M for "scratch work" if it would be helpful. For example, if desired, you use a scratch column to calculate the number of days of vacation to which an employee is entitled. You could use a column to calculate the daily pay for an employee, if that would be helpful. The idea is that by using scratch columns, you can simplify formulas though there will be more of them. You can assume the columns will be hidden eventually. However, please do not hide scratch columns, to make it easier to grade your spreadsheet.
  (Hint: Make sure that you can do the calculations by hand before writing the Excel formulas. Practice doing so a few times trying to set a regular pattern for the calculations. Then tell Excel to carry out the same pattern. Use the scratch columns to calculate intermediate results that you found useful in your hand calculations.)
• Calculates totals for those columns for which a total is appropriate.
• Format the sheet in a professional manner. All the cells with supplied data are subject to change. They must have white background. Color other cells differently. The intention is to make it easy to know what cells can be updated by the user and which cells will automatically update in response changing the input cells.

Possible extra credit:

• Have Excel round the number of "Years of Service" down to the nearest integer. Hint: Checkout the ROUNDDOWN function. (2 points)
• Prepare an appropriate chart on its own chart page that shows the "Liability for Vacation Time" for each of the employees. (2 points)

Additional Requirements:

• Remember to use formulas whenever appropriate. Assume the paper grader will update the data before grading the sheet. Your spreadsheet should correctly solve the problem with the new data.
• Add your name to the end of the page using "Implemented by xxxx".

Grading:

Your spreadsheets will be graded on meeting the requirements, correctness, general looks, and the impression they leave the reader.

Submitting your workbook:

1. Use the URL http://www.cs.plu.edu/submit/login.php
2. Select your class (csce120-brink-secX, Fall 2006 where X is 1 or 3) from the drop down box.
3. Enter your submit system username and password.
4. Click "Login".
5. In the add Assignment box, browse to find the workbook for Lab 9.
6. Select "Lab 9" for the assignment and then click "Upload".
7. Double check to make sure the file has been uploaded.
8. Click "Sign Off" near the top right hand corner of the window.

Due date:

Wednesday, Nov. 15. There will be a 24 hour grace period. After the grace period late submittals will not be accepted except with written permission from the instructor and will be subject to a 20% per day. (Late assignments turned in over 24 hours after the due date are 2 days late and are subject to a 40% penalty.)

Revised: Nov. 9, 2006