Excel Core: Capstone Assignment

1

Assignment Description: This assignment will require you to review the terms, concepts and

fundamentals of all the lessons. You will be completing one file for this assignment on your computer

and then submitting it online through Blackboard.

Obtaining the files needed to complete the assignment:

1. Open the file link in the Capstone Assignment folder entitled Capstone Assignment – Student Data

Files

2. Right click on the file Capstone Assignment_Document to open the context menu.

3. Depending on the browser you are using, select the option Save link as or Save target as to open the save dialog box.

4. When the save dialog box opens, save the file to a location on your computer where you will be able to locate it again.

All the instructions required to complete this assignment are listed below in a step‐by‐step

format. Save your work frequently.

Excel Core: Capstone Assignment

2

Capstone Assignment Instructions:

1. Open the Capstone Assignment_Document file in Excel. In the File tab, save the file with the name Firstname_Lastname_Capstone_Assignment, where Firstname and Lastname are replaced with your first and last names.

Go to the Logo worksheet to complete steps 2 through 18.

2. Change the tab colour of the worksheet tab to Blue, Accent 5, Darker 25%.

3. Move the picture so that the upper left corner is in cell E3.

4. Adjust the size of the picture so that the height is 5 inches (12.7 cm). Keep the proportions of the picture the same so that it does not become distorted.

5. Apply the Grayscale colour to the picture.

6. Apply the Soft Edges – 50 Point picture effect to the picture.

7. Apply the correction Brightness: +20% Contrast: 0% (Normal) to the picture.

8. Insert a text box with the text The Dairy Emporium. Make sure that the text box has its fill colour set to No Fill so that it does not cover over the picture and the outline is set to No Outline so there is outline around it. The location of the text box is not critical at this point as you will be moving it in a later step.

9. Change the font of the text box text to Playbill and a size of 88. Adjust the height and width of the text box, if necessary, so the text is contained on one line. When done, the text box should be just a little wider and higher than the text it contains.

10. Apply the Transform text effect named Deflate Bottom to the text box.

11. Change the font colour in the text box to Blue, Accent 5, Darker 25%.

12. Position the text box so that it is approximately centred at the top of the picture. Do not adjust the size of the text box – you should expect that it will be wider than the picture.

13. Insert a WordArt with the style Fill: Blue, Accent color 5; Outline: White, Background color 1; Hard Shadow: Blue, Accent color 5.

14. Change the WordArt text to World’s Best Ice Cream followed by the trademark symbol TM.

15. Change the font size of the WordArt to 32.

16. Change the font colour in the WordArt to Blue, Accent 5, Darker 25%.

17. Position the WordArt so that it is approximately centred at the bottom of the picture.

18. Turn off display of the gridlines.

Go to the Ice Cream Sales worksheet to complete steps 19 through 34.

19. Create a table with the table style White, Table Style Medium 4, for the cell range A3 through I18. You should assume the table has headers.

20. Name the table Sales.

21. Create a multi-level sort to sort the table alphabetically by Category and then alphabetically by Flavour.

22. Create a Column sparkline in cell G4 for the cell range C4 through F4.

23. Turn on only the High Point and Low Point markers for the sparkline.

Excel Core: Capstone Assignment

3

24. Change the High Point marker colour to Green and the Low Point marker to Red.

25. Copy the sparkline to cells G5 through G18.

26. Create a formula in cell H4 that sums cells C4 through F4. Your formula must use an appropriate function. Make sure to use structured references when referencing cells in the table.

27. Create a formula in cell I4 that displays the word Yes if the value in cell H4 is less than the discontinue threshold in cell B21, otherwise the word No is displayed. Your formula must use an appropriate reference to cell B21 so that it will copy correctly. Make sure to use structured references when referencing cells in the table.

28. Apply a Highlight Cells Rules – Text That Contains conditional formatting to the cell range I4 through I18 such that the cell displays the fill colour Light Red Fill with Dark Red Text when it contains the text Yes.

29. Turn off the table Filter Buttons.

30. Select the cell ranges A3:A18 and C3:F18 and create a 3-D Stacked Column chart. You should have two options for a Stacked Column chart. Select the option that has the ice cream flavours on the horizontal axis, rather than the quarters (Q1, Q2, etc.) on the horizontal axis. Position the chart so that the upper left corner is in cell K2.

31. Change the chart style to Style 5.

32. Change the chart title to Sales by Flavour.

33. Adjust the vertical axis so that it has display units of Thousands.

34. Add a vertical axis title with the text Sales ($). Do not add a horizontal axis title.

Go to the Loan Payment Calculator worksheet to complete steps 35 through 45.

35. Change the font of cell A1 to Algerian.

36. Change the font style of the cell range A1 through B8 to Bold.

37. Merge and centre cells A1 and B1.

38. Apply the border All Borders to the cell range A1 through B8.

39. Apply the border Thick Outside Borders to the cell range A6 through B6.

40. Change the font colour of the cell range A6 through B6 to Blue.

41. Create a formula in cell B6 which uses the PMT function to calculate the payment amount. Assume that the payments are made at the end of the payment period. Ensure that the formula returns a positive value. Your formula must use references to cells B2, B3, B4, and B5 for full marks.

42. Create a formula in cell B7 that determines the total amount paid over the duration of the loan. Do not use any functions in your formula. Your formula must use references to cells B4, B5, and B6 for full marks.

43. Create a formula in cell B8 that determines the total amount of interest paid over the duration of the loan. Do not use any functions in your formula. Your formula must use references to cells B2 and B7 for full marks.

44. For cell B2 and the cell range B6 through B8, set the number format to Comma Style with 2 decimal places.

45. For cell B3, set the number format to Percentage with 1 decimal place.

Save the workbook.

Excel Core: Capstone Assignment

4

Rubric: Capstone Assignment

Rubric Marks

Workbook Management 2

Worksheet Formatting 3

Insert and Format Pictures

9

Insert and Format Text Boxes

9

Insert and Format WordArt

10

Tables 8

Sparklines 9

Functions 3

Advanced Functions 11

Conditional Formatting 4

Charts 12

Cell Formatting 16

Formulas 4

Total 100

File to Submit: Firstname_Lastname_Capstone_Assignment

Total marks available for this assignment = 100 marks worth 24% of your final grade.

Excel Core: Capstone Assignment

5

How to Submit Files

Once you have completed the file for this assignment on your computer and saved the file with the

corresponding filename:

1. In Blackboard, go to the assignment area and select Capstone Assignment.

2. Click on Capstone Assignment File Upload link. Scroll down the page and click the Browse My Computer button.

3. A window will open asking you to select your file. Locate your assignment file Firstname_Lastname_Capstone_Assignment on your computer and then click the OPEN button.

4. You should now see the file for submission in the area File Name. 5. Click the Submit button. The Save Draft button does NOT submit assignments to the facilitator.

If you encounter any problems with the assignment or have questions, email your facilitator – include

your name, the course title – Excel Core, and the assignment number – Capstone Assignment in the

email subject line.