YO_Excel16_Ch02_PS2_v3 Project Description: Roger Harding, the manager of Thunder City Bookstore, wants to improve the appearance of the workbook created to review the store inventory. Roger has a workbook started with the inventory, backorder, and documentation sheets already created. This project will require you to use your Excel skills to improve Roger's workbook. Some of the actions needed include copying the promotions worksheet from the marketing director's workbook and pasting it into Roger's workbook, enhancing the appearance of the worksheets by applying a theme, using built-in cell styles, rotating labels, changing a worksheet theme, formatting a section of a worksheet as a table, and using and printing functions and formulas. Steps to Perform: Step Instructions Points Possible 1 0 Open the Excel file, e01ch02_grader_a1_TCBInventory.xlsx. Save your file as e01ch02_grader_a1_TCBInventory_LastFirst, using your last and first name. 2 Change the workbook theme to Mesh. 4 3 4 Open e01ch02_grader_a1_TCBPromos, and copy the Promotions worksheet to the e01ch02_grader_a1_TCBInventory_LastFirst workbook. Place the Promotions worksheet before Documentation worksheet. Close the e01ch02 grader a1 TCBPromos file. 4 8 On the Inventory worksheet: In cell D4, enter the label Inventory Cost In cell E4, enter the label Subtotal Apply Align Right to cell range C4:04. 5 8 In cell D6, enter a formula to calculate the total cost of inventory for gym shorts depending on price per unit and quantity in stock. Format the resulting figure as Currency with two decimal places. Copy the formula entered in D6 to the range 07:D39. Delete the contents of cells D10, D15, D20, D25, D26, D33, and D34. 6 4 In cell E10, enter a function to calculate the total cost of all gym shorts in inventory. In cell E15, enter the function to calculate the total cost of all sweatpants in inventory. In cell E20, enter the function to calculate the total cost of all t-shirts in inventory. In cell E25, enter the function to calculate the total cost of all sweatshirts in inventory. In cell E33, enter the function to calculate the total cost of all school supplies in inventory. In cell E40, enter the function to calculate the total cost of all miscellaneous items in inventory. Use Fill Color Tan, Accent 3, Lighter 40% for the cell ranges: A10:E10, A15:E15, A20:E20, A25:E25, A33:E33, and A40:E40. 7 4 8 4 Select cell range A1:E1. Apply Merge & Center to the selected range. Apply the Title cell style to the selected range, and then change the Fill Color to Tan, Accent 3, Lighter 40%. 9 4 10 Select cell range A2:E2. Apply Merge & Center to the selected range. Apply the Heading 2 cell style to the selected range. In cell B45, enter a function to display the number of products listed in inventory (reference the appropriate range in column B). In cell B46, enter a function to display the average price of an item in inventory. In cell B47, enter a function to display the highest price of an item in inventory. In cell B48, enter a function to display the lowest price of an item in inventory 16 Grader - Instructions Excel 2016 Project Step Instructions Points Possible 11 6 Format cell range A4:E40 as Table Style Medium 4, with headers and add a Total Row. Format cell E41 as Currency with two decimal places. Note, the style name may be Tan, Table Style Medium 4, depending on the version of Office used. 12 4 Select D6:D9. Apply conditional formatting on the Inventory Costs in column D to format any inventory cost that is greater than $500.00 to appear Bold Italic, and in Orange, Accent 5, Darker 50% Using the Format Painter, apply the conditional format to D11:D14, D21:D24, D27:D32, and D35:D39. 13 4 On the Inventory worksheet, in the center position of the header, enter the phrase Open Daily from 10 AM to 8 PM (no period). 14 Hide the Gridlines of the Inventory worksheet. 4 15 4 On the Promotions worksheet, change the format for cell B5 to Long Date format. Apply the format in cell B5 to cell ranges B6:B8 and C5:C8. Apply AutoFit Column width to columns B:D. 16 8 Select cell range A4:D4, change the cell background color to Tan, Accent 3, Lighter 40%, and then Center, Bold, and Italic. Change the orientation of the text in this range to Angle Counterclockwise. 17 4 On the Documentation worksheet: In cells A8 and A22, type today's date In cells B8 and C22, type your name in the Firstname Lastname format. In cell C8, type Added the Promotions worksheet. In cell B22, type Promotions 18 On the Inventory, Promotions, and Documentation worksheets, change the width to 1 page. 6 19 4 Add the file name code in the left footer on all worksheets. Spell check all worksheets. 20 Save the workbook, exit Excel, and then submit your files as directed by your instructor. 0 Total Points 100 A1 X V fx Excellent Wealth Management A B D E F G H . I J K L M N P 1 Excellent Wealth Management 2 Company 3 Ticker 4 Price 5 Shares Owned 6 Annual Dividends / Share 7 Yield 8 Total Dividends Recieved 9 10 Prime Heartland Diamond 2x4 Lumber HITEND Steel Healthcare Five LLP Co. Security PRIME PULSE FIVE WOOD TEN 41 60 5 24 10 243.90 166.67 2000.00 416.67 1000.00 0 0 0 0 0 Average Annual Income Per Stock 0 0 0 0 0 11 FEB MAR MAY SEP NOV DEC Total JAN 0.37 APR 0.37 12 0 0 0 0 OCT 0.37 0 0 0 0.37 13 0 0.37 0 0 Dividends / Share by Month JUN JUL AUG 0.00 0.37 0 0.37 0 0 0 0 0.04 0.1 0.09 0.1 0 0 0.12 0.37 0 PRIME PULSE FIVE WOOD TEN 14 0 0 0 0.04 0 0 0.04 0.09 0.12 0 15 0.1 0.1 0.07 0.09 0.095 0 0.1 0.04 0.1 0.12 0.095 0 16 0 0 0 0.12 0 17 18 19 FEB MAR NOV DEC Total JAN 90.243 0 Total Dividends by Month JUN JUL AUG 0 90.243 0 61.6679 0 0 APR 90.243 0 0 0 0 61.6679 0 0 STOCK PRIME PULSE FIVE WOOD TEN 0 MAY 0 0 80 37.5003 120 SEP OCT 0 90.243 61.6679 0 0 0 39.58365 39.58365 0 0 0 61.6679 0 41.667 0 80 20 21 22 23 24 25 26 27 0 0 0 0 41.667 0 37.5003 120 29.1669 0 41.667 0 37.5003 0 80 41.667 120 80 41.667 120 41.667 0 28 29 B33 fx A B C D E 1 Excellent Wealth Management 2 3 4 5 Create Date By Whom 6 12/15/18 Michael Malley 7 Mod. Date By Whom 8 Description Stock Portfolio Monthly Dividend Analysis Mod. Description Workbook Name Washington_e01ch02_grader_h1_Portfolio.xlsx Last Version Backup Name 9 10 11 12 13 14 15 16 17 18 19 Create Date Sheet Name 12/15/18 Dividend Portfolio Creator Michael Malley Purpose Monthly Dividend Income from Stock Portfolio 20 21 22 23 24 YO_Excel16_Ch02_PS2_v3 Project Description: Roger Harding, the manager of Thunder City Bookstore, wants to improve the appearance of the workbook created to review the store inventory. Roger has a workbook started with the inventory, backorder, and documentation sheets already created. This project will require you to use your Excel skills to improve Roger's workbook. Some of the actions needed include copying the promotions worksheet from the marketing director's workbook and pasting it into Roger's workbook, enhancing the appearance of the worksheets by applying a theme, using built-in cell styles, rotating labels, changing a worksheet theme, formatting a section of a worksheet as a table, and using and printing functions and formulas. Steps to Perform: Step Instructions Points Possible 1 0 Open the Excel file, e01ch02_grader_a1_TCBInventory.xlsx. Save your file as e01ch02_grader_a1_TCBInventory_LastFirst, using your last and first name. 2 Change the workbook theme to Mesh. 4 3 4 Open e01ch02_grader_a1_TCBPromos, and copy the Promotions worksheet to the e01ch02_grader_a1_TCBInventory_LastFirst workbook. Place the Promotions worksheet before Documentation worksheet. Close the e01ch02 grader a1 TCBPromos file. 4 8 On the Inventory worksheet: In cell D4, enter the label Inventory Cost In cell E4, enter the label Subtotal Apply Align Right to cell range C4:04. 5 8 In cell D6, enter a formula to calculate the total cost of inventory for gym shorts depending on price per unit and quantity in stock. Format the resulting figure as Currency with two decimal places. Copy the formula entered in D6 to the range 07:D39. Delete the contents of cells D10, D15, D20, D25, D26, D33, and D34. 6 4 In cell E10, enter a function to calculate the total cost of all gym shorts in inventory. In cell E15, enter the function to calculate the total cost of all sweatpants in inventory. In cell E20, enter the function to calculate the total cost of all t-shirts in inventory. In cell E25, enter the function to calculate the total cost of all sweatshirts in inventory. In cell E33, enter the function to calculate the total cost of all school supplies in inventory. In cell E40, enter the function to calculate the total cost of all miscellaneous items in inventory. Use Fill Color Tan, Accent 3, Lighter 40% for the cell ranges: A10:E10, A15:E15, A20:E20, A25:E25, A33:E33, and A40:E40. 7 4 8 4 Select cell range A1:E1. Apply Merge & Center to the selected range. Apply the Title cell style to the selected range, and then change the Fill Color to Tan, Accent 3, Lighter 40%. 9 4 10 Select cell range A2:E2. Apply Merge & Center to the selected range. Apply the Heading 2 cell style to the selected range. In cell B45, enter a function to display the number of products listed in inventory (reference the appropriate range in column B). In cell B46, enter a function to display the average price of an item in inventory. In cell B47, enter a function to display the highest price of an item in inventory. In cell B48, enter a function to display the lowest price of an item in inventory 16 Grader - Instructions Excel 2016 Project Step Instructions Points Possible 11 6 Format cell range A4:E40 as Table Style Medium 4, with headers and add a Total Row. Format cell E41 as Currency with two decimal places. Note, the style name may be Tan, Table Style Medium 4, depending on the version of Office used. 12 4 Select D6:D9. Apply conditional formatting on the Inventory Costs in column D to format any inventory cost that is greater than $500.00 to appear Bold Italic, and in Orange, Accent 5, Darker 50% Using the Format Painter, apply the conditional format to D11:D14, D21:D24, D27:D32, and D35:D39. 13 4 On the Inventory worksheet, in the center position of the header, enter the phrase Open Daily from 10 AM to 8 PM (no period). 14 Hide the Gridlines of the Inventory worksheet. 4 15 4 On the Promotions worksheet, change the format for cell B5 to Long Date format. Apply the format in cell B5 to cell ranges B6:B8 and C5:C8. Apply AutoFit Column width to columns B:D. 16 8 Select cell range A4:D4, change the cell background color to Tan, Accent 3, Lighter 40%, and then Center, Bold, and Italic. Change the orientation of the text in this range to Angle Counterclockwise. 17 4 On the Documentation worksheet: In cells A8 and A22, type today's date In cells B8 and C22, type your name in the Firstname Lastname format. In cell C8, type Added the Promotions worksheet. In cell B22, type Promotions 18 On the Inventory, Promotions, and Documentation worksheets, change the width to 1 page. 6 19 4 Add the file name code in the left footer on all worksheets. Spell check all worksheets. 20 Save the workbook, exit Excel, and then submit your files as directed by your instructor. 0 Total Points 100 A1 X V fx Excellent Wealth Management A B D E F G H . I J K L M N P 1 Excellent Wealth Management 2 Company 3 Ticker 4 Price 5 Shares Owned 6 Annual Dividends / Share 7 Yield 8 Total Dividends Recieved 9 10 Prime Heartland Diamond 2x4 Lumber HITEND Steel Healthcare Five LLP Co. Security PRIME PULSE FIVE WOOD TEN 41 60 5 24 10 243.90 166.67 2000.00 416.67 1000.00 0 0 0 0 0 Average Annual Income Per Stock 0 0 0 0 0 11 FEB MAR MAY SEP NOV DEC Total JAN 0.37 APR 0.37 12 0 0 0 0 OCT 0.37 0 0 0 0.37 13 0 0.37 0 0 Dividends / Share by Month JUN JUL AUG 0.00 0.37 0 0.37 0 0 0 0 0.04 0.1 0.09 0.1 0 0 0.12 0.37 0 PRIME PULSE FIVE WOOD TEN 14 0 0 0 0.04 0 0 0.04 0.09 0.12 0 15 0.1 0.1 0.07 0.09 0.095 0 0.1 0.04 0.1 0.12 0.095 0 16 0 0 0 0.12 0 17 18 19 FEB MAR NOV DEC Total JAN 90.243 0 Total Dividends by Month JUN JUL AUG 0 90.243 0 61.6679 0 0 APR 90.243 0 0 0 0 61.6679 0 0 STOCK PRIME PULSE FIVE WOOD TEN 0 MAY 0 0 80 37.5003 120 SEP OCT 0 90.243 61.6679 0 0 0 39.58365 39.58365 0 0 0 61.6679 0 41.667 0 80 20 21 22 23 24 25 26 27 0 0 0 0 41.667 0 37.5003 120 29.1669 0 41.667 0 37.5003 0 80 41.667 120 80 41.667 120 41.667 0 28 29 B33 fx A B C D E 1 Excellent Wealth Management 2 3 4 5 Create Date By Whom 6 12/15/18 Michael Malley 7 Mod. Date By Whom 8 Description Stock Portfolio Monthly Dividend Analysis Mod. Description Workbook Name Washington_e01ch02_grader_h1_Portfolio.xlsx Last Version Backup Name 9 10 11 12 13 14 15 16 17 18 19 Create Date Sheet Name 12/15/18 Dividend Portfolio Creator Michael Malley Purpose Monthly Dividend Income from Stock Portfolio 20 21 22 23 24