RELIABLE PAPERS ONLY PLEASE

Unit 6    [IT153: Spreadsheet Applications] 

 

Assignment 6 and Midterm Details and Rubrics

There are two Assignments due in Unit 6.

1. Assignment 6 2. Midterm Assignment

Note: you will be turning in two assignments for this unit; the regular Assignment 6, plus the Midterm Assignment.

Assignment 6 (1 of 2)

Outcomes addressed in this activity: Unit Outcomes:

 Create three-dimensional worksheets  Use Excel to format worksheets for printing, including headers, footers, and page breaks  Demonstrate the ability to link separate workbooks to consolidate data

Course outcome: IT153-3: Integrate workbooks to consolidate data.

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 Sharing and save the workbook using the file name: Unit 6 Assignment Your Name.

2. Download the image “Abacus.jpg” from Doc Sharing. You will use this image in this assignment.

3. Name the first 4 worksheets Quarter 1, Quarter 2, Quarter 3 and Quarter 4.

4. Calculate the Gross Pay using the formula the Rate of Pay times the Hours Worked.

Unit 6    [IT153: Spreadsheet Applications] 

 

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.

Unit 6    [IT153: Spreadsheet Applications] 

 

15. Change the Picture Border Color to Light Green.

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 – Accent 2 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 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.

Unit 6    [IT153: Spreadsheet Applications] 

 

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 the workbook and rename as Assignment 6 – Your Name.

28. Submit the final version of the workbook to the Unit 6 Dropbox by the due date.

Unit 6    [IT153: Spreadsheet Applications] 

 

All deliverables should be professionally formatted and should be free of spelling errors. Points deducted from the grade for each error are at your instructor’s discretion.

Assignment 6 grading rubric = 30 points

Unit 6 Assignment Requirements Points possible

Points earned

Name each of the four tabs as specified. 0 - 1

Perform the calculations needed for the Gross Pay for each person in the 4 Worksheets.

0 - 1

Calculate the totals for Hours Worked and Gross Pay for the 4 worksheets.

0 - 1

Rename the Sheet 5 Tab to Annual Totals.

0 - 3 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.

Do the same to determine the annual gross pay in cell C11. 0 - 3

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.

0 - 1

Calculate the totals for Hours Worked and Gross Pay. 0 - 1

Add the Image Abucus.jpg to the Annual Totals Worksheet. 0 - 1

Move the Image to the specified area. Format the image to use the Double Frame, Black Style in the Picture Styles Gallery. Change the Picture Border Color to Light Green.

0 - 1

Insert a SmartArt graphic using the Hierarchy type and the Organization Chart layout.

0 - 1

Using the Add Shape shortcut menu, add an Assistant shape to the first 0 - 1

Unit 6    [IT153: Spreadsheet Applications] 

 

shape.

Using the Add Shape shortcut menu, add a shape after the last shape in the third row.

0 - 1

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.

0 - 1

Change the color scheme of the hierarchy chart to Colored Fill – Accent 2 in the Change Colors gallery.

0 - 1

Change the font size of the text in the shapes to 14 points. 0 - 1

The effects on the SmartArt shapes are changed to Preset 4. 0 - 1

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.

0 - 1

Select all five worksheets. Add a worksheet header with your name, course number and name of workbook (Annual Payroll Totals).

0 - 1

Add the page number and total number of pages to the footer. 0 - 1

Using the Page Layout tab on the Ribbon. Set up all worksheets to center horizontally on the page and to print without gridlines.

0 - 1

Save the workbook and rename as Assignment 6 – Your Name. Submit the final version of the workbook to the Unit 6 Dropbox by the due date.

0 - 1

Assignment reflects the concepts in the readings and Step-by-steps for the Unit.

0 - 5

Submitted late (point deductions as per policy as stated in the Syllabus.

TOTAL POINTS: 0 - 30

Points deducted for spelling or formatting

Adjusted total points

Unit 6    [IT153: Spreadsheet Applications] 

 

Midterm Assignment (2 of 2)

1. Download the unit Midterm data file from Doc Sharing and save the workbook using the file name: Midterm Assignment Your Name.

2. Analyzing Annual College Expenses and Resources Academic College expenses are skyrocketing and your resources are limited. To plan for the upcoming year, you have decided to organize your anticipated expenses and resources in a workbook. The data required to prepare the workbook is shown in Table below.

3. Open the Midterm Assignment Data File and use the instructions below to complete the Assignment Worksheet(s).

4. Using the 1st Semester Worksheet. Format the worksheet as follows:

a. Apply the Theme of Equity. b. Merge & Center the Title in Cell A1 to B1, Apply the Title Style, and Wrap the text:

i. Set the Width of Column A to 20 , B to 15 and the Row 1 Height to 110. ii. Center both Vertically and Horizontally in the cell.

c. A2 apply the Heading 1 Style: i. Merge and Center Cell A2 to B2. ii. Set the Row 2 Height to 25. iii. Center Vertically and Horizontally in the cell.

d. Range A4 to B13 and A16 to B23 Format as table, then Convert the Range: i. Center both Vertically and Horizontally the data in Cells A4,B4, A18 and B16. ii. Add the Total to cells B12 and B22.

Unit 6    [IT153: Spreadsheet Applications] 

 

iii. Add the Average to cells B13 and B23. iv. Set both areas to the Currency style. v. Set the Ranges A12 to B13 and A22 to B23 to the Total Style. vi. Name the Range A5 to B11 FirstExpense. vii. Name the Range A17 to B21 FirstResource.

5. Copy the worksheet 2 times, name the worksheets 2nd Semester and Summer and change

the data for the 2 worksheets using the Table above.

Using the information and instructions below in the Consolidated Total and Average Worksheet. Use a 3-D cell references to consolidate the data from the 3 semesters worksheet as a Total in Column B (ranges B5 to B11 and B17 to B21) and Average in Column C (ranges C5 to C11 and C17 to C21). Use the concepts and techniques described in this chapter to finish the workbook as shown below. Add the following Data and functions to the cells below:

a. In Cells B12 and B22 sum the cells above. b. In cells B13 and B23 Average the cells above (data only not totals).

Unit 6    [IT153: Spreadsheet Applications] 

 

6. Save the File as Midterm Assignment-Your Name.

Unit 6    [IT153: Spreadsheet Applications] 

 

7. Using the Academic Year Worksheet apply the concepts you have learned thus far to complete the worksheet using the instructions below.

a. For all the 3 semester’s worth of data use the VLOOKUP function to obtain the data to populate the ranges of B5 to D11 and B17 to D21. (Make sure that you highlight the ranges in each of the specified worksheet ranges for the table, also do not forget to use the Exact Match Option).

b. In Cell E5 add the data for the Row: i. Copy the formula to all of the rows in the ranges E6 to E11 and E17 to E21) Use

the Fill without Formatting Option). c. In Cell F5 compute the average of the data only for the row:

i. Copy the formula to all of the rows in the ranges F6 to F11 and F17 to F21) Use the Fill without Formatting Option).

d. In Cell B12 and B22 compute the SUM for the data in cells B5 to B11 and B17 to B21): i. Copy the formula to all of the cells in the ranges C12 to E12 and C22 to E22)

Use the Fill without Formatting Option). e. In Cell B13 and B23 compute the Average for the data in cells B5 to B11 and B17 to

B21): i. Copy the formula to all of the cells in the ranges C13 to E13 and C23 to E23)

Use the Fill without Formatting Option). f. In Cell G5 compute the percent of each Expense to the Total (E12) (Do not forget to

use an absolute reference as needed): i. Copy the formula to all of the cells in the ranges G5 to G11 Use the Fill without

Formatting Option). g. In Cell G17 compute the percent of each Expense to the Total (E22) (Do not forget to

use an absolute reference as needed): i. Copy the formula to all of the cells in the ranges G18 to G21 (Use the Fill without

Formatting Option). h. Using Conditional Formatting Format the range G5 to G11 applying the Rules as

follows: i. Values Greater Than 50% Green Fill. ii. Values between 25% and 50% Yellow Fill. iii. Values Less Than 25% Red Fill.

i. Completed Worksheet shown below:

Unit 6    [IT153: Spreadsheet Applications] 

 

Using the Outlook Worksheet Make the following Modifications to the worksheet (as instructed below).

j. Link the Cells in the Range B5 to B11 to the cells in the Academic Year Worksheet cells E5 to E11 (Use the Fill without Formatting Option).

k. Link the Cells in the Range B17 to B21 to the cells in the Academic Year Worksheet cells E17 to E21 (Use the Fill without Formatting Option).

l. In Cell C5 use create a formula to calculate the Increase of 8.5% to each expense Item: i. Formula should be =Expense Item +(Expense Item * % of increase) Do not

forget to use Absolute Reference as needed. ii. Copy this formula to the range C6 to C11 (Use the Fill without Formatting

Option). m. B12, C12 and B22 compute the Total for each column of data. n. Cell B25 = Difference between Total Resources (B22) – Total Outlook(C12):

i. Cell C25 = Using the IF Function return (“More Resources Required” if the results of Cell B25 is less than zero or “OK” if the results of Cell B25 is greater than or equal to zero. (see completed view below).

Unit 6    [IT153: Spreadsheet Applications] 

 

8. Create a Clustered Column Chart plotting all Expenses from the Academic Year Worksheet (Range A4 through D11) Apply a Chart style of your choice:

a. MOVE THE Chart to a Chart Sheet named College Expenses. b. Put a Title on the chart:

i. Apply a Shape Style to your Title that would match your Chart Style. c. Move the legend to the bottom of the Chart sheet. d. Move the Chart sheet to the Right of the Outlook Worksheet. e. Chart should appear as shown below.

Unit 6    [IT153: Spreadsheet Applications] 

 

Create a 3-D Pie chart for all resources for the Year from the Academic Year Worksheet (Range A16 through D21) Apply a Chart Style of your choice.

f. Move the chart to a new Chart Sheet named College Resources. g. Include the Title for the Chart Academic Resources and Change the Shape style to

match your Chart Style. h. Remove the Legend. i. Add Data labels that will include the Category and Percent only. j. Change the Shape Style of the Data Labels to a style of your choice. k. Move the Chart Sheet to the Right of the College Expenses Chart Sheet (This should

now be the very last sheet).

 $‐

 $2,000.00

 $4,000.00

 $6,000.00

 $8,000.00

 $10,000.00

 $12,000.00

Academic Expenses

1st Semester

2nd Semester

Summer

Unit 6    [IT153: Spreadsheet Applications] 

 

9. Save and submit the assignment to the Unit 6 Midterm Assignment Dropbox for final grading.

Midterm Assignment grading rubric = 70 points

Assignment Requirements Points

Possible Points Earned

1. Using the 1st Semester Worksheet. Format the worksheet as follows: a. Apply the Theme of Equity. b. Merge & Center the Title in Cell A1 to B1, Apply the Title Style, and Wrap the text. i. Set the Width of Column A to 20 , B to 15 and the Row 1 Height to 110. ii. Center both Vertically and Horizontally in the cell c. A2 apply the Heading 1 Style.

0 - 10

Savings 17%

Parents 26%

Part‐time job 7%

Student Loan 38%

Scholarship 12%

Academic Resources

Unit 6    [IT153: Spreadsheet Applications] 

 

i. Merge and Center Cell A2 to B2. ii. Set the Row 2 Height to 25. iii. Center Vertically and Horizontally in the cell. d. Range A4 to B13 and A16 to B23 Format as table, then Convert the Range. i. Center both Vertically and Horizontally the data in Cells A4,B4, A18 and B16. ii. Add the Total to cells B12 and B22. iii. Add the Average to cells B13 and B23. iv. Set both areas to the Currency style. v. Set the Ranges A12 to B13 and A22 to B23 to the Total Style. vi. Name the Range A5 to B11 FirstExpense. vii. Name the Range A17 to B21 FirstResource.

2. Copy the worksheet 2 times, name the worksheets 2nd Semester and Summer and change the data for the 2 worksheets using the Table above.

3. Create a consolidated data worksheet using the information and instructions below named Consolidated Total and Average. Use a 3-D cell references to consolidate the data from the 3 semesters worksheet as a Total in Column B (ranges B5 to B11 and B17 to B21) and Average in Column C (ranges C5 to C12 and C17 to C22). Use the concepts and techniques described in this chapter to format the workbook as shown below. Add the following Data and functions to the cells below:

0 - 10

4. Using the information and instructions below in the Consolidated Total and Average Worksheet. Use a 3-D cell references to consolidate the data from the 3 semesters worksheet as a Total in Column B (ranges B5 to B11 and B17 to B21) and Average in Column C (ranges C5 to C11 and C17 to C21). Use the concepts and techniques described in this chapter to finish the workbook as shown below. Add the following Data and functions to the cells below: a. In Cells B12 and B22 sum the cells above. b. In cells B13 and B23 Average the cells above (data only not totals).

0 - 10

5. Using the Academic Year Worksheet apply the concepts you have learned thus far to complete the worksheet using the instructions below. a. For all the 3 semester’s worth of data use the VLOOKUP function to obtain the data to populate the ranges of B5 to D11 and B17 to D21. (Make sure that you highlight the ranges in each of the specified worksheet ranges for the table, also do not forget to use the Exact Match Option).

0 - 10

Unit 6    [IT153: Spreadsheet Applications] 

 

b. In Cell E5 add the data for the Row. i. Copy the formula to all of the rows in the ranges E6 to E11 and E17 to E21) Use the Fill without Formatting Option). c. In Cell F5 compute the average of the data only for the row. i. Copy the formula to all of the rows in the ranges F6 to F11 and F17 to F21) Use the Fill without Formatting Option). d. In Cell B12 and B22 compute the SUM for the data in cells B5 to B11 and B17 to B21). i. Copy the formula to all of the cells in the ranges C12 to E12 and C22 to E22) Use the Fill without Formatting Option). e. In Cell B13 and B23 compute the Average for the data in cells B5 to B11 and B17 to B21). i. Copy the formula to all of the cells in the ranges C13 to E13 and C23 to E23) Use the Fill without Formatting Option). f. In Cell G5 compute the percent of each Expense to the Total (E12) (Do not forget to use an absolute reference as needed) i. Copy the formula to all of the cells in the ranges G5 to G11 Use the Fill without Formatting Option). g. In Cell G17 compute the percent of each Expense to the Total (E22) (Do not forget to use an absolute reference as needed). i. Copy the formula to all of the cells in the ranges G18 to G21. (Use the Fill without Formatting Option). h. Using Conditional Formatting Format the range G5 to G11 applying the Rules as follows: i. Values Greater Than 50% Green Fill. ii. Values between 25% and 50% Yellow Fill. iii. Values Less Than 25% Red Fill.

Unit 6    [IT153: Spreadsheet Applications] 

 

j. Using the Outlook Worksheet Make the following Modifications to the worksheet (as instructed below). k. Link the Cells in the Range B5 to B11 to the cells in the Academic Year Worksheet cells E5 to E11 (Use the Fill without Formatting Option). l. Link the Cells in the Range B17 to B21 to the cells in the Academic Year Worksheet cells E17 to E21 (Use the Fill without Formatting Option). m. In Cell C5 use create a formula to calculate the Increase of 8.5% to each expense Item. i. Formula should be =Expense Item +(Expense Item * % of increase) Do not forget to use Absolute Reference as needed ii. Copy this formula to the range C6 to C11 (Use the Fill without Formatting Option). n. B12, C12 and B22 compute the Total for each column of data o. Cell B25 = Difference between Total Resources (B22) – Total Outlook(C12). i. Cell C25 = Using the IF Function return (“More Resources Required” if the results of Cell B25 is less than zero or “OK” if the results of Cell B25 is greater than or equal to zero.

0 - 10

6. Create a Clustered Column Chart plotting all Expenses from the Academic Year Worksheet (Range A4 through D11) Apply a Chart style of your choice. a. MOVE THE Chart to a Chart Sheet named College Expenses b. Put a Title on the chart. i. Apply a Shape Style to your Title that would match your Chart Style. c. Move the legend to the bottom of the Chart sheet. d. Move the Chart sheet to the Right of the Outlook Worksheet.

0 - 7

7. Create a 3 – D Pie chart for all resources for the Year from the Academic Year Worksheet (Range A16 through D21) Apply a Chart Style of your choice. a. Move the chart to a new Chart Sheet named College Resources. b. Include the Title for the Chart Academic Resources and Change the Shape style to match your Chart Style. c. Remove the Legend. d. Add Data labels that will include the Category and Percent only. e. Change the Shape Style of the Data Labels to a style of your choice. f. Move the Chart Sheet to the Right of the College Expenses Chart Sheet (This should now be the very last sheet.

0 - 7

8. Save the completed workbook as Save the File as Midterm Assignment-Your Name. (Example: Midterm Assignment Robert Aguiar).

0 - 1

Unit 6    [IT153: Spreadsheet Applications] 

 

9. Ensure your assignment shows a clear understanding of the concepts covered in the Units as applied in this assignment from the Required Readings and Step-by-Step Examples in the text. Ensure that the work presented reflects appropriate use of Microsoft Excel's features, Functions and Professional Formatting.

0 - 5

TOTAL POSSIBLE POINTS: 0 - 70

Points deducted for spelling or formatting

Adjusted total points