Payroll Assignment

The purpose of this assignment is to:

Reinforce previous learning
Calculate regular wages, overtime wages and gross earnings
Introduce conditional formatting
Change page orientation
Create a new sheet
Add a header to the workbook

Preparation: (Review these terms)

Formatting cell content
Adjusting column width
Creating simple formulas
Centering column labels
Using Auto Fill to drag formulas
Using currency feature to add dollar signs and decimals
Wrap Text
Merge and Center
Using the launcher button
Use absolute cell references

New Terms: (Terms are bolded in the steps below.)

(THIS ASSIGNMENT IS DIVIDED INTO TWO PARTS; THE DOTTED LINE BELOW DIVIDES THE TWO SECTIONS.)

Inserting blank column (Step #3, pages 1-2)
Calculate Regular Pay (Step #5, page 2)
Arithmetic Operators (Table mentioned In Step #5. Can be found on page 4)
Calculate OT rate and OT pay. (Step #6, page 2)
Calculating Gross Pay (Step #18, page 3)

—————————————————————-

Creating a new sheet (Steps #20, page 3)
Inserting a header (Step #28, page 4))

Narrative:

Spreadsheets are often used to determine the company payroll. This assignment breaks down regular pay, overtime pay and gross earnings. Additionally, conditional formatting is introduced to enhance the years of service for each employee by using color. (THIS ASSIGNMENT IS WORTH 25 POINTS. THE RUBRIC IS ON PAGE 5.)

Download and open the Payroll Form and save it to your storage device as Payroll. ‘
Adjust the columns if necessary to see all the data. (NOTE for clarification: in cell C4 the term Reg. means Regular; in cell D4, OT means Overtime.)
Insert new columns. Move your mouse cursor above the Years of Service to the letter “E” which signifies what column you are in. As you move your mouse upwards to that letter “E” it becomes a black downward facing arrow. Once you see the black arrow, right click your mouse to get the short cut menu. From the menu, choose INSERT. A new column appears. Staying in that same position, INSERT to add more columns for a total of three new columns between OT Hours and Yrs of Service.
Type the following: Reg. Pay in Cell E4; OT Pay in F4 and Gross Pay in G4.
Calculating Regular (Reg) Pay. Create a formula to multiply Reg (Regular) pay times the number of hours that person worked. (Arithmetic operators such as the multiply symbol can be found at the end of this assignment and in the Extra Help Content folder called TABLES.) Formulas always start with the equal sign, use the cell reference such as B5 times C5. Make sure you start your formula where you want the answer to be. In this case start your formula in E5. (Hint: John Adams’ result will be 420.)
Calculate Overtime (OT) pay. Create a formula to calculate overtime pay and have the result show in cell F5. To determine the overtime rate, multiply the regular rate times 1.5 (means overtime pay is 1 ½ times the regular rate.) To calculate the overtime pay, multiply the rate times the overtime hours. Two multiplication operators can be in the same formula. It is similar to using a calculator, but amounts are cell references. (Hint: John Adams’ Overtime pay should be 78.75.)
Calculate Gross Pay. Gross pay is found by adding the regular pay plus the overtime pay. Start the formula in the gross pay column G5 of the first employee.
Drag the formulas in E5, F5 and G5 down each column for each employee.
In Cell A16 type the word TOTAL in all capital letter.
Using the AutoSum key, calculate the totals of Reg. Pay (E16), OT Pay (F16) and Gross Pay (G16).
Format these total as currency with two decimal places.
Apply Conditional Formatting. Conditioning formatting is used to highlight information by using color combinations.
- Select cells H5-H14.
- From HOME tab, find the Styles group. On the left side of that group, find Conditional Formatting.
- Click on the Color Scales Option and then click on “More Rules”.
- In the lower box labeled “Edit Rule Description Box,” select Format Style and change to the 3- color option. Choose red for the lowest, yellow for the mid color, and green for the highest. Then click okay. ]
Adjust all columns to make sure all the headings and entries are displayed.
Center all entries and labels in columns B through H.
Type your name in cell A18. Use Text Wrap to keep it all in one cell.
Select the title “Payroll Report.” Change the font to AHARONI, 14 pt.
Merge and Center the title between columns A-H.
Change Orientation. Pages can be viewed with the long side on the left (Portrait) or the long side can be across the top (Landscape). From the HOME tab, From the PAGE LAYOUT tab, find the group called Page Layout. In that group find Orientation. For this assignment, change the orientation of the worksheet to LANDSCAPE.
Save this worksheet as Payroll Report. Continue on to the second part of this assignment in the next steps. ———————————————————————————————–
Creating a new Sheet. At the bottom of the worksheet you have been working on, click the plus sign next to Sheet named Payroll. A blank sheet appears
- Double-click on the new “Sheet 2.” Type What If.
- Select Columns A1 to H18. From the HOME tab, Clipboard group, click COPY icon. You should see a dotted line around the section you selected.
- Place your cursor in cell A1 and press PASTE. The previous columns A1 to H18 appear on the What If sheet.
- Adjust any columns so that they are wide enough for all columns.
On the “What if” worksheet, type the words “What if” in cell I4. Widen the column if necessary
Type 0.05 in cell J3
In cell I5, use a formula that includes dollar signs in the formula (Absolute Referencing) to calculate the 5% pay raise in J3 times the Pay Rate in B5. YOU MUST USE ABSOLUTE CELL REFERENCING. [Hint: Use the decimal given Step-p 22]. Do you remember the formula used in Assign 3, Public Expenses by the Quarter Instructions to increase another value? IIf not, see Extra Help folder
Your correct answer should be 53 cents more than the regular pay rate.
Once you have the correct formula, use the short-cut menu to pull this formula down column from I5 to I14.
In Cell K4 type the label: Difference.
Insert a formula in K5 to determine the difference between the original Pay Rate and the “What If” increased pay rate. Once the formula is correct, drag the formula down K5 to K14. Make all amounts only two decimal places.
Insert a Header.
- A Header will appear at the top of every page, if there were more than one page in the worksheet.
- A Header only appears in the Print Out or in Print Preview. Otherwise, you won’t see it.
- Click the PAGE LAYOUT tab and find Page Setup Group.
- Click the launcher for Page Setup.
- Find the tab called Header/Footer and click on it.
- In the new dialog box that appears, click on Custom Header.)
In the Center section of the Header , type XYZ Corporation and then click OK.
Save this worksheet as Payroll and submit it through Blackboard. When you save this workbook, both the Payroll and the What If sheets will save together.
If you are not taking this class on line, ask the instructor for information on how to print both the Payroll and the What If sheets that display the formulas.

TABLE: Arithmetic Operators used in Spreadsheets

TABLE: Order of Precedence in formulas

THE ORDER OF PRECEDENCE IS VERY IMPORTANT WHEN YOU WORK WITH FORMULAS THAT CONTAIN MORE THAN ONE OPERATOR. THIS IS BECAUSE IT AFFECTS THE FINAL VALUE (THE FINAL ANSWER).

Week 3