BCC Computer Information Systems http://cisweb.bristolcc.edu/

Assignment a6 -- Microsoft Excel Payroll Spreadsheet

  1. Microsoft Excel
  2. Important Topics
  3. Additional Resources
  4. Payroll Spreadsheet Assignment
  5. Getting Started: Creating The Workbook
  6. How to Submit

Microsoft Excel

Important Topics

Additional Resources

Payroll Spreadsheet Assignment

Getting Started: Creating The Workbook

  1. Create the payroll report displayed below.

  2. Use formatting as displayed in the below report; columns must be widened as needed, cell wrapping must be used for the column headings, dates must be formatted as displayed, numeric values must be formatted as displayed.

  3. Use cell bordering where I've used bordering and use cell patterns (fill color) where I have. You don't have to use the same border or the same color, but you must use this formatting feature in the spreadsheet. Make it appealing to the eye.

  4. Be sure to use the appropriate numeric formatting for all numeric values; i.e., notice that when a cell has a value of zero it displays as a dash; some of the numbers have dollar signs and some don't; all numeric values are displayed with two decimal positions, etc.

  5. Be sure to use copying whenever possible and absolute cell referencing whenever necessary. (Efficiency will be considered in the grading process)

  6. The today function must be used to display the current date in the upper left corner of the spreadsheet

  7. The columns that must contain calculations are:

  8. The lower left corner of the sheet contains rates. When calculating Overtime Pay, FICA, State Tax and Federal tax, use the rates specified in this area. Your formulas must reference the cell, they must not contain the actual rate.

    (For example, when calculating FICA for Crystal Oates you should be multiplying her gross pay (cell I8) by the cell that contains the FICA rate (cell B26) =I8*B26. You must also apply absolute cell references in this formula so that it can easily be copied. Do NOT use the actual number i.e., =I8*7.5%)

  9. Apply conditional formatting to the Hourly Rate Column. The hourly rate should be displayed in Red if it's less than $10 per hour and in Blue if it's $10.00 per hour or more.

  10. Print the worksheet. (Tip: since you don't actually have to hand-in a hard copy, but you do have to send me the file with the appropriate print settings, you can save paper by using Print Preview.) Make sure that

    1. the sheet fits on one page, and

    2. the sheet includes gridlines on the printout.

  11. Sort the worksheet by department name and within department -- by employee's name.

  12. Save the spreadsheet and name it PayrollByDept

  13. Worksheet Example (an unsorted version is shown):

      payroll worksheet


How to Submit