Business Analytics III

Step

Instructions

Points Possible

1

Download and open the file named Excel_Ch02_Assessment_Portfolio.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

0

2

To improve the readability of the DividendPortfolio worksheet, center and bold cell range B2:F3.

0.8

3

To format a worksheet title, apply Center Across Selection to cell range A1:N1. Format the title with a Title cell style, and then bold the title.

1.2

4

Continue formatting the worksheet by adding a Thick Bottom Border to the title in cell A1:N1. Increase the height of row 1 to 30

0.8

5

The DividendPortfolio worksheet will help you determine how much income the current portfolio is generating. Therefore, you want to calculate the totals of the stock dividends per share and the total dividends. In cell ranges N12:N16 and N20:N24, use a function calculate the range totals.

1.2

6

You are interested in knowing the average of the total dividends. In cell G8, use a function to calculate the average total dividends.

1.6

7

Apply cell style Heading 3 to cells G7, B10, and B18.

1.2

8

You want to format the numbers to improve the readability of the data. Apply the Currency format to cell ranges B4:F4, B6:F6, B8:G8, N12:N16 and N20:N24. Apply the Comma style to cell ranges B12:M16, and B20:M24.

1.6

9

In cell B7, you want to calculate the yield of Prime Steel. Dividend yield is the income return on an investment and usually expressed as a percentage. To calculate the yield, divide the annual dividends per share by the price per share. Format the yield figures as Percent Style with two decimal places. Copy the formula to cell range C7:F7.

1.8

10

Apply right alignment to cell ranges A11:A16 and A19:A24, and then apply Bold to both cell ranges. Increase the indent of cells A11 and A19.

1.6

11

To quickly format cell ranges, you will use cell styles. Apply cell style 40% - Accent1 to cell ranges A11:A16 and A19:A24.

1.2

12

To display the top 5 dividends by month, apply a Top/Bottom Conditional Formatting rule to cell range B20:M24. Display the Top 5 items as Green Fill with Dark Green Text.

1.2

13

Turn off gridlines in the DividendPortfolio worksheet.

0.8

14

Change the color of the DividendPortfolio worksheet tab to Blue.

0.8

15

Apply the Mesh theme to the workbook.

1

16

Spell check the entire workbook. Ignore the spelling of stock names, if necessary. Insert the File Name on the center header of the DividendPortfolio worksheet.

0.8

17

In the Documentation worksheet, enter today's date into cell A8. In cell B8, type your name in the Firstname Lastname format. In cell C8, type Completed Mr. Malley's monthly dividend income worksheet Wrap the text in cell C8.

1.2

18

For the DividendPortfolio and Documentation worksheets, set the Orientation to Landscape, and then set the Width to 1 page. Change the Print Setting to Print Entire Workbook.

1.2

19

Save and close Excel_Ch02_Assessment_Potfolio.xlsx. Exit Excel. Submit the file as directed.

0

Total Points

20