Grader - Instructions Excel 2019 Project YO19_Excel_Ch02_PS1_Books_Inventory 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 Start Excel. Download and open the file named Excel_Ch02_PS1_Booksinventory.xisx. 0 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. 2 To quickly change the appearance of all worksheets in the workbook, apply the Mesh 4 workbook theme. If the theme is not available, browse for the downloaded theme file Mesh.thmx. 3 On the Inventory worksheet: 4 In cell D4, enter the label Inventory Cost In cell E4, enter the label Total 4 You want to determine the inventory cost of each inventory item. 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 cell D6 to cell range D7:D39. Delete the contents of cells D10, D15, D20, D25, D26, D33, and D34. 5 Next, you want to calculate the various inventory items total costs. For example, you want to 6 know the total inventory cost of gym shorts. In cell E10, enter a function to calculate the total cost of all gym shorts in inventory found in cell range D6:D9. In cell E15, enter a function to calculate the total cost of all sweatpants in inventory. In cell E20, enter a function to calculate the total cost of all t-shirts in inventory. In cell E25, enter a function to calculate the total cost of all sweatshirts in inventory. In cell E33, enter a function to calculate the total cost of all school supplies in inventory. In cell E40, enter a function to calculate the total cost of all miscellaneous items in inventory. 6 To add division between the inventory items, you will add color formatting. 4 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. To create a title that is centered over the worksheet data, merge and center cell range A1:E1. 5 Apply the Title cell style to the selected range, and then change the Fill Color to Tan, Accent 3, Lighter 40%. 8 Merge and Center cell range A2:E2. Apply the Heading 2 cell style to the selected range. 4 9 You want to know the average price of inventory items, and you also want to identify the 9
