Sort the worksheet by department name and within department by employee's name.
Save the spreadsheet name it PayrollDeptSortYourName.xls
Email all three files created in this assignment.
CIT-11 Home http://www.c-jump.com/CIT11/CIT11syllabus.htm
During weeks 8 and 9 you will work with Excel.
I've learned that some students have different editions of the textbook.
If you have the second edition of the textbook, Excel is covered in Chapter 13.
If you have the third edition of the textbook, Excel is covered in Chapter 14.
If you have the first edition of the textbook, Excel is not included in that textbook, however I am posting its content here.
Read through the excel chapter. The author provides some spreadsheet examples in the chapter. Consider entering and testing some these examples.
The best way to learn is to try it!
In addition to the book chapter, read through the material available through the below links:
Complete the and the Tutorials Excel 2003, or
and the Tutorials for Excel 2007. 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:
- on You Tube!
- there's a link to a 4 minute tutorial (watch and listen) at the bottom of this page.
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.
This document walks you through using the function wizard to use the PMT function for a loan calculation:
Mext document explains 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:
Next link provides an alphabetical listing of all Excel's functions. You will be required to use some functions that may not have been included in the required readings. You can find more information on any Excel function from this site. You can also use Excel's Help feature to learn more about a particular function:
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:
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: from Microsoft Office OnLine.
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.
Lots more resources at the Introduction to Business Computer Applications web site.
Create the payroll report displayed below.
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.
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.
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.
Be sure to use copying whenever possible and absolute cell referencing whenever necessary. (Efficiency will be considered in the grading process)
The today function must be used to display the current date in the upper left corner of the spreadsheet
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
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%)
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.
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 the sheet fits on one page and include gridlines on the printout.
Save the spreadsheet name it PayrollYourName.xls
Sort the worksheet by Employee Name.
Save the spreadsheet name it PayrollNameSortYourName.xls
Sort the worksheet by department name and within department by employee's name.
Save the spreadsheet name it PayrollDeptSortYourName.xls
Email all three files created in this assignment.