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

• During upcoming two weeks you will work with Excel.

• Excel is covered in Chapters 13 and 14 of the textbook. Read through the chapters and pay special attention to the spreadsheet examples provided by the author. Consider entering and testing some of these examples on your own computer or at the computer lab on campus.

• The best way to learn is to try it!

• In addition to the book chapter, read through the material available through the links below:

• Complete the Create Your First Work Book and the Enter Formulas Tutorials Excel 2003, or Create Your First Work Book and the Enter Formuals Tutorials for Excel 2007/2010. These are audio tutorials so if you have speakers, turn them up. If you don't want the audio, the words that are being spoken are also displayed in text. Each unit contains a number of different Excel related topics with some hands-on exercises on each topic. Completing these exercises will help you become more familiar with Excel and and some of its basic features.

• Information regarding Excel conditional formatting: Excel 2007/2010 Conditional Formatting

• Excel 2007/2010 Conditional Formatting - on You Tube!

• Conditional Formatting with 2003 - there's a link to a 4 minute tutorial (watch and listen) at the bottom of this page.

• Conditional Formatting: formula value This article walks you through using conditional formatting using a cell address as well as using a formula's value. There are also other good examples of using Excel available from this link.

• You may find useful MS Excel 2003 Quick Reference to learn about the difference between absolute and relative cell references. This topic is critical to understand. When entering a formula into a spreadsheet cell that you know you will later copy to other areas of the spreadsheet, having a solid understanding of absolute and relative references will be a major advantage.

• The Functions web link provides an alphabetical listing of all Excel functions. You may need to use some functions (such as PMT function in a loan calculation) in your homework. You can find more information on any Excel function from this site. You can also use Excel Help feature to learn more about a particular function.

   

Important Topics

• The topics you'll need to know in order to complete the upcoming Excel homeworks include:

  • FORMATTING

    • Numbers

    • Cells: background (pattern), font color, borders and alignment

    • Conditional Formatting

    • Dates

    • Merging and Centering Cells

  • FUNCTIONS

    • AVERAGE

    • SUM

    • MAX

    • MIN

    • PMT

    • ROUND

    • TODAY

    • AVERAGE

  • Printing

  • Absolute and Relative Cell References (Pay particular attention to this one; it's where many students lose points)

  • Sorting - on two columns

  • Working with multiple worksheets: referencing data from another sheet.

• Most of these topics are included in the required readings; some are not. Here's another site that might be helpful in finding information on a topic not included in the required readings. You're not required to read through each lesson, but you might find some of the individual lessons useful: Excel Tutorial

• If you liked the first two Audio Tutorials, here's a link to a Microsoft site that has many more audio tutorials covering various Excel topics: Excel 2003 Training from Microsoft Office OnLine.

   

Additional Resources

• There are lots of great on-line resources for working with Excel.

• Some of these resources are available through the links I've made available in the Excel Section of the Reference Links and Tutorials document. Use these to supplement the required readings and to follow your interests. If you find other websites that you find useful, please share them with the class by posting them on the discussion board.

   

Payroll Spreadsheet Assignment

• In this assignment you must create a payroll worksheet.

• Please refer to the instructions below for the assignment requirements.

   

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:

   • Column F: Regular Pay is calculated by multiplying the regular hours by the hourly rate.

   • Column G: Overtime Pay is calculated by multiplying the hourly rate by the overtime rate and then multiplying that by the overtime hours.

   • Column H: Gross Pay is calculated by adding the regular pay and the overtime pay

   • Column I: FICA is calculated by multiplying the FICA rate by the Gross Pay. This calculation should be rounded to two decimal positions (you'll need to use the =Round function in this formula).

   • Column J: State Tax is calculated by multiplying the State Tax Rate by the Gross Pay. This calculation should be rounded to two decimal positions (you'll need to use the =Round function in this formula).

   • Column K: Federal Tax is calculated by multiplying the Federal Tax Rate by the Gross Pay. This calculation should be rounded to two decimal positions (you'll need to use the =Round function in this formula).

   • Column L: Net Pay is calculated by subtracting all deductions (FICA, State and Federal Tax) from Gross Pay

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):

     

     

How to Submit

• Submit your spreadsheet using CIT-121 online website.