Project Description:

One method that people use to provide income during their retirement years is investing in dividend-paying stocks. The dividends allow investors to make withdrawals from their retirement account without having to reduce their invested principal. Michael Malley, president and chief investment officer of your new employer, Excellent Wealth Management, has developed a worksheet to show how a portfolio of stocks can create an additional income stream for his clients. You are asked to determine how much income the current portfolio is generating in order to assist with future investments decisions in the form of adding to current investments or diversifying and adding other dividend-paying investments.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step	Instructions	Points Possible
1	Start Excel. Open the downloaded file named e01ch02_grader_h1_Portfolio.xlsx. Save it as e01ch02_grader_h1_Portfolio_LastFirst using your last and first name.	0
2	On the DividendPortfolio worksheet, center and bold cell range B2:F3.	4
3	Merge & Center cell range A1:N1. In cell A1, apply the Title cell Style and bold the title.	8
4	Select cell range N12:N16 and N20:N24, and then calculate row totals using the SUM function.	7
5	In cell G8, calculate the average of Total Dividends Received.	6
6	Apply cell style Heading 3 to cells G7, B10, and B18.	6
7	Apply the Currency format to cell ranges B4:F4, B6:F6, B8:G8, B12:N16, and B20:N25.	6
8	In B7, calculate the yield of Prime Steel - the annual dividends per share divided by the price per share. Copy the formulas to cell range C7:F7. Format the yield figures as Percent Style with two decimal places.	8
9	Hide rows 10:17.	5
10	Format cell range A19:N24 as a table with headers. Apply Table Style Light 11. Sort by STOCK from A-Z, and then turn off Filters. Note, depending upon the version of Office being used, the table style name may be White, Table Style Light 11.	12
11	Add a Total Row to the table, and calculate the sum of columns B:N.	6
12	Apply a top border to cell range A25:N25.	4
13	Apply cell style 40% - Accent3 to cell ranges A20:A25, B25:N25, and N20:N24.	4
14	Apply Conditional Formatting to cell range B20:M24. Display the Top 10 items as Green Fill with Dark Green Text.	6
15	Turn off gridlines in the DividendPortfolio worksheet.	4
16	In the Documentation worksheet, enter 12/15/2018 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 and then make cell A1 the active cell.	4
17	Spell check the entire workbook and correct the one misspelling. Insert the file name on the left page footer on all worksheets.	4
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.	6
19	Save the workbook, exit Excel, and then submit your file as directed by your instructor.	0
	Total Points	100

alani _e01ch02 grader h1 Portfolio - Protected View - Excel Sign in File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be carefu files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing A1 , x fi | Excellent Wealth Management 1 Excellent Wealth Managemen Prime eartland Diamond 2x4 Lumber HiTENd Steel PRIME PULSE 2 Company 3 Ticker 4 Price 5 Shares Owned 6 Annual Dividends / Share 7 Yield 8 Total Dividends Recieved Healthcare Five LLP Co Securi FIVE WOOD TEN 60 24 10 243.90 166.67 2000.00 416.67 1000.00 Average Annual Income Per Stock Dividends Share by Month JAN 0.37 APR 0.37 JUN 0.00 0.37 JUL 0.37 OCT 0.37 FEB MAR MAY AUG SEP NOV PRIME PULSE FIVE WOOD TEN 0.37 0.37 0.04 0.09 0.12 0.04 0.09 0.12 0.04 0.1 0.12 0.04 0.1 0.12 0.1 0.07 0.09 0.0950.095 17 Total Dividends by Month MAR AUG STOCK DividendPortfolio Documentation JAN FEB APR MAY JUN JUL SEP OCT NOV alani _e01ch02 grader h1 Portfolio - Protected View - Excel Sign in File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do PROTECTED VIEW Be carefu files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing A1 , x fi | Excellent Wealth Management 1 Excellent Wealth Managemen Prime eartland Diamond 2x4 Lumber HiTENd Steel PRIME PULSE 2 Company 3 Ticker 4 Price 5 Shares Owned 6 Annual Dividends / Share 7 Yield 8 Total Dividends Recieved Healthcare Five LLP Co Securi FIVE WOOD TEN 60 24 10 243.90 166.67 2000.00 416.67 1000.00 Average Annual Income Per Stock Dividends Share by Month JAN 0.37 APR 0.37 JUN 0.00 0.37 JUL 0.37 OCT 0.37 FEB MAR MAY AUG SEP NOV PRIME PULSE FIVE WOOD TEN 0.37 0.37 0.04 0.09 0.12 0.04 0.09 0.12 0.04 0.1 0.12 0.04 0.1 0.12 0.1 0.07 0.09 0.0950.095 17 Total Dividends by Month MAR AUG STOCK DividendPortfolio Documentation JAN FEB APR MAY JUN JUL SEP OCT NOV