YO_Excel16_Ch02_PS2_v2 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. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Open the Excel file, e01ch02_grader_a1_TCBInventory.xlsx. Save your file as e01ch02_grader_a1_TCBInventory_LastFirst, using your last and first name. 0 2 Change the workbook theme to Mesh. 4 3 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 4 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:D4. 8 5 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 D7:D39. Delete the contents of cells D10, D15, D20, D25, D26, D33, and D34. 8 6 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. 4 7 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. 4 8 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%. 4 9 Select cell range A2:E2. Apply Merge & Center to the selected range. Apply the Heading 2 cell style to the selected range. 4 10 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 11 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. 6 12 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. 4 13 On the Inventory worksheet, in the center position of the header, enter the phrase Open Daily from 10 AM to 8 PM (no period). 4 14 Hide the Gridlines of the Inventory worksheet. 4 15 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. 4 16 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. 8 17 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 4 18 On the Inventory, Promotions, and Documentation worksheets, change the width to 1 page. 6 19 Add the file name code in the left footer on all worksheets. Spell check all worksheets. 4 20 Save the workbook, exit Excel, and then submit your files as directed by your instructor. 0 Total Points 100
