Lesson Instructions
Background: You will be completing a Summary Inventory worksheet for the Titan Off-Campus Shops based on focused product inventories of individual stores. You will also be highlighting inventory values that fall below acceptable amounts. The workbook you will use is the L5_Titan_Inventory_Summary file. This workbook contains inventory worksheets for individual stores (which are complete and you will leave alone), and a Focused Products Inventory sheet for one of the stores (which you will use). Open the file and complete the following instructions. (Note: when no specific cell reference is given, you are to use your best judgment based upon how the worksheet is set up for you to decide what cells are involved or where a result should be placed.)
1. Re-save the file to either your desktop or other storage device using the name "firstName_lastName_L5_Titan_Inventory_Summary". (Note firstName and LastName are your own first and last names).
2. Create three additional "Focused Products" worksheets, one for the Placentia Store , one for the Harbor Store, and one to be used as a summary, by making copies of the "Focused Products - Chapman" worksheet.
3. Correct the new worksheets subtitles by replacing the words "Chapman Store", to "Placentia Store", "Harbor Store", and "Summary" respectively. Also, re-name the worksheet tabs to "Focused Products
- Placentia", and "Focused Products - Harbor", and "Focused Products-Summary".
4. Re-order the four "Focused Products" worksheets so that the Summary is first, followed by Chapman, then Harbor, and finally Placentia.
5. Replace the inventory quantities on the Focused Products - Harbor, and Focused Products - Placentia sheets to the values below.
|
|
Harbor
|
Placentia
|
|
Sweatshirts - Standard
|
38
|
16
|
|
Sweatshirts - Premium
|
3
|
2
|
|
Baseball Hats
|
14
|
19
|
|
T-Shirts Standard
|
20
|
2
|
|
T-shirts Premium
|
31
|
29
|
|
Sweatpants
|
21
|
23
|
|
Shorts
|
2
|
3
|
|
Mugs
|
18
|
13
|
6. On the Focused Products - Summary sheet, replace the quantity for Sweatshirts-Standard (cell C4), with a 3-D reference function which adds up the quantities of the three "Focused Products" sheets. On the Summary sheet, copy this formula down for the column (through C11). (Be sure not to include the three additional worksheets which are showing all the inventory that are also included in this workbook)
7. Apply conditional formatting to the Quantity column on the Focused Products - Summary sheet so that if the inventory levels drop below 10, they will appear with a red background and a red font color.
Attachment:- l5_inventory_summary.xlsx