Capstone Assignment_Document.xlsx

Grade
Available

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. Logo worksheet

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 the text box has its fill colour set to No Fill so that it doesn’tcover over the picture and the outline is set to No Outline so there is no outline around it. The location of the text box isnot 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, ifnecessary, so the text is contained on one line. When done, the text box should be just a little wider and higher thanthe 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 textbox – 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. Make sure to insert the trademarksymbol rather than just entering the text 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.

Ice Cream Sales worksheet

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.

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 3-D Stacked Column chart – select the one 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. Make sure to show the display units label on the chart.

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

Loan Payment Calculator worksheet

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 aremade at the end of the payment period. Ensure that the formula returns a positive value. Your formula must use referencesto 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 functionsin 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 useany 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.
0.0
100
0.0%

The Dairy Emporium
Sales – Ice Cream Division
Flavour
Category
Q1
Q2
Q3
Q4
Sparklines
Total
Discontinue
French Vanilla
Classic
503
2,911
1,176
958
OMG
New
733
1,779
1,207
834
Pralines & Cream
Classic
987
2,793
2,440
748
Vodka Martini
Age of Majority
751
2,607
2,794
720
Dill Pickle
Limited Time
5
15
37
12
Strawberry
Classic
915
3,261
2,339
953
Birthday Cake
New
555
2,607
2,185
595
Chocolate Fudge
Classic
943
4,659
1,656
527
Margarita
Age of Majority
681
3,152
2,028
645
Rootbeer Float
New
626
3,702
2,670
894
Sea Salt & Pepper
Limited Time
13
30
45
10
Rum n’ Coke
Age of Majority
809
2,610
2,837
711
Neopolitan
Classic
880
3,258
2,947
617
Mint Julip
New
651
3,533
2,735
606
Butterscotch
Classic
570
3,687
2,580
736
Discontinue Threshold:
$ 5,000

Ice Cream Truck Loan Payment Calculator
Total Cost of Ice Cream Truck
65000
Annual Interest Rate
0.05
Loan Duration (Years)
5
Number of Payments per Year
12
Payment Amount
Total Paid
Total Loan Interest