Answer & Explanation:See attached rubic and data file
20170407144200copy_of_unit_6_data_file.xlsx
20170407143957it153_unit6_assignment.pdf
Unformatted Attachment Preview
Quarter 1 Totals
Employee Rate of Pay Hours Worked Gross Pay
J123
15,75
165,00
K457
11,45
168,00
M235
22,95
175,25
T619
17,50
145,00
Total
Quarter 2 Totals
Employee Rate of Pay Hours Worked Gross Pay
J123
15,75
143,00
K457
11,45
123,00
M235
22,95
178,50
T619
17,50
134,00
Total
Quarter 3 Totals
Employee Rate of Pay Hours Worked Gross Pay
J123
15,75
200,75
K457
11,45
167,00
M235
22,95
182,50
T619
17,50
160,00
Total
Quarter 4 Totals
Employee Rate of Pay Hours Worked Gross Pay
J123
15,75
145,00
K457
11,45
138,75
M235
22,95
189,00
T619
17,50
203,00
Total
Annual Payroll Totals
Employee Hours Worked Gross Pay
J123
K457
M235
T619
Total
Unit 6: Assignment – 65 points
Unit outcomes addressed in this unit:
Create three-dimensional worksheets.
Show how to link separate workbooks to consolidate data.
Use Excel to format worksheets for printing, including headers, footers, and page
breaks.
Insert a SmartArt graphic using the Hierarchy type and Organizational Chart layout.
Course outcome(s) practiced in this unit:
IT153-3: Prepare workbooks to consolidate data.
Scenario:
You work for the payroll department of your company and track the payroll expenses for the
company. Each quarter, you are required to consolidate and total the payroll expenses for the
quarter. Those quarterly worksheets are then linked to an annual worksheet. It is the end of
the year and your boss has asked you to submit the consolidated payroll expenses for the
last year for review.
Instructions:
1. Download the Unit 6 data file from Doc.
2. Download the image “Abacus.jpg” from Doc Sharing. You will use this
image in this Assignment.
3. Name the first four worksheets Quarter 1, Quarter 2, Quarter 3, and Quarter 4.
4. Calculate the Gross Pay using the formula Rate of Pay times the Hours Worked.
5. Use the SUM function to add the totals for the Hours Worked and Gross
Pay in each Worksheet.
6. Each worksheet should appear as below:
7. Name the Sheet5 worksheet Annual Totals.
8. Determine the annual payroll totals on the Annual Totals sheet by using the SUM
function and 3-D references to sum the hours worked on the four quarterly sheets
in cell B11 of the Annual Totals worksheet.
9. Do the same to determine the annual gross pay in cell C11.
10. Copy the range B11:C11 to the range B12:C14.
11. Use the SUM function to add the totals for the Hours Worked and Gross Pay.
12. In cell A1, insert the Abacus type graphic image in the worksheet (found in the
Assignment Data Files in the Doc Sharing area of the class).
13. Move and resize the image so that the upper-left corner of the image is
aligned with the upper-left corner of cell A1 and the lower-right corner of the
image is in cell C8.
14. Format the image to use the double frame, black style in the Picture Styles Gallery.
15. Change the picture border color to light green (or a similar color).
16. Insert a SmartArt graphic using the Hierarchy type and the Organization Chart layout.
17. Using the Add Shape shortcut menu, add an Assistant shape to the first shape.
18. Using the Add Shape shortcut menu, add a shape after the last shape in the third row.
19. Change the text in the first shape to read Jill Van Kirk. Change the text in the
middle row to read Juan Aguilara in the left shape and Elise Hammermill in the
right shape. Change the text in the third row to read, from left to right, Rose
Kennedy, Mark Allen, Karen Franklin, and Lance Marion.
20. Change the color scheme of the hierarchy chart to colored fill – (use any
accent) in the Change Colors gallery.
21. Change the font size of the text in the shapes to 14 points.
22. Use the Shape Effect gallery to change the effects on the SmartArt shapes to
Preset 4 (or similar) in the Preset gallery.
23. Close the text pane, if necessary, and then move the SmartArt graphic so the
upper-left corner of the graphic is in the upper-left corner of cell A16.
Your Annual Totals worksheet should resemble:
24. Select all five worksheets. Add a worksheet header with your name (right
side), course number (left side), and name of workbook (Annual Payroll
Totals) (middle).
25. Add the page number and total number of pages to the footer (right side).
26. Using the Page Layout tab on the Ribbon. Set up all worksheets to center
horizontally on the page and to print without gridlines.
27. Save as Unit_5_Assignment_Your Name and submit the Assignment to the Unit
5 Dropbox.
Unit 6 Assignment grading rubric = 65 points
Assignment Requirements
Name each of the four tabs as specified.
Perform the calculations needed for the Gross
Pay for each person in the four worksheets.
Calculate the totals for Hours
Worked and Gross Pay for the four
worksheets.
Points
Possible
0–3
0–3
0–3
Points
Earned
Rename the Sheet 5 Tab to Annual Totals.
0–2
Determine the annual payroll totals on the
Annual Totals sheet by using the SUM
function.
0–3
3-D references to sum the hours worked on the
four quarterly sheets in cell B11 of the Annual
Totals worksheet.
Do the same to determine the annual gross pay in
cell C11.
Copy the range B11:C11 to the range B12:C14
using the copy button on the Home tab on the
Ribbon and the formulas command button menu
on the Home tab on the Ribbon.
Calculate the totals for Hours Worked and Gross
Pay.
Add the image Abucus.jpg to the Annual Totals
worksheet.
Move the image to the specified area (1).
Format the image to use the double
frame, black style in the Picture Styles
Gallery. (1) Change the Picture border
color to light green (or similar). (1)
Insert a SmartArt graphic using the Hierarchy
type and the Organization Chart layout.
0–3
Using the Add Shape shortcut menu, add an
Assistant shape to the first shape.
Using the Add Shape shortcut menu, add a
shape after the last shape in the third row.
Change the text in the first shape to read Jill
Van Kirk. (1)
Change the text in the middle row to read Juan
Aguilara in the left shape and Elise Hammermill
in the right shape. (1)
Change the text in the third row to read, from
left to right, Rose Kennedy, Mark Allen, Karen
Franklin, and Lance Marion. (1)
Change the color scheme of the hierarchy chart
to colored fill — accent 2 (or similar) in the
Change Colors gallery.
Change the font size of the text in the shapes to
14 points.
0–3
0–3
0–3
0–3
0–3
0–3
0–3
0–3
0–3
0–3
0–3
The effects on the SmartArt shapes are changed
to Preset 4.
Close the text pane, if necessary, and then move
the SmartArt graphic so the upper-left corner of
the graphic is in the upper-left corner of cell A16.
Select all five worksheets. Add a
worksheet header with your name (1),
course number (1), and name of
workbook (Annual Payroll Totals). (1)
Add the page number (1) and total number of
pages to the footer. (2)
Using the Page Layout tab on the Ribbon. (1)
Set up all worksheets to center horizontally on
the page and to print without gridlines. (2)
Total Points
0–3
0–3
0–3
0–3
0–3
0–65
…
Purchase answer to see full
attachment
