Question

EX 6-13e In the Lab Design and implement a solution using creative thinking and problem-solving skills. Create and Query an Inventory Problem: A local company would like to be able to search their shipping supplies inventory in an easy manner. They have given you sample data shown in Table 6-7. Table 6–7 Inventory Data Description Inventory Number Unit List Price Quantity in Stock CX1D1 mailing labels carton $12.86 ED7XL bubble wrap roll $27.59 T562W packing tubes single $ 1.75 VP45L corrugated boxes bundle $26.80 DC30W packing tape case $65.00 LX550 poly mailers carton $ 8.99 SR123 packing peanuts bag $21.00 Perform the following tasks: Part 1: Create the criteria range, the table, and the extract range with formatted headings and data. Save the workbook. Perform the following extractions. a) all inventory items with more than 10 in stock b) all inventory items in cartons c) all inventory items under $25 d) all inventory items with 10 or less in stock and a list price of less than $10

Answer 1

Inventory Number Description Unit Quantity in Stock List Price CX1D1 ED7XL T562W VP45L DC30W LX550 SR123 mailing lables carton bubble wrap roll packing tubes single corrugated box bundle packing tape case poly mailers carton packing peanut bag $ $ $ $ $ $ $ 12.86 27.59 1.75 26.80 65.00 8.99 21.00 All inventory With items more than 10 in stock Inventory Number Description Unit Quantity in Stock List Price 25 14 CX1D1 T562W VP45L DC30W mailing lables carton packing tubes single corrugated box bundle packing tape case $ $ $ $ 12.86 1.75 26.80 65.00 17 11 Just put filter and apply filter on Quantity in stock at More than 10 b) All inventory items in cartons Inventory Number Description Unit Quantity in Stock List Price CX1D1 LX550 mailing lables carton poly mailers carton $ $ 12.86 8.99 Just put filter and apply filter on Unit and select carton

All inventory items under $25 Quantity in Stock Inventory Number CX1D1 T562W LX550 SR123 Description Unit mailing lables carton packing tubes single poly mailers carton packing peanut bag List Price $ 12.86 $ 1.75 $ 8.99 $ 21.00 5 Put Filter and apply filter on list price less than 25 d) All inventory With items 10 or less in stock and a list price of less than $10 Quantity in Stock 25 Inventory Number - Description Unit CX1D1 mailing lables carton ED7XL bubble wrap roll T562w packing tubes single VP45L corrugated box bundle DC30W packing tape case LX550 poly mailers carton SR123 packing peanut bag 4 14 17 List Price $ 12.86 No $ 27.59 Yes 1.75 Yes $ 26.80 No $ 65.00 No $ 8.99 Yes $ 21.00 Yes 11 9 We can use if function to shortlist the items whose invetory is 10 or less OR list price is less than $10 IF(E47<=10,"Yes",IF(F47<10,"Yes","No")) After applying the logical test using IF function, select items having Yes as output Quantity in Stock Inventory Number ED7XL T562w LX550 SR123 Description Unit bubble wrap roll packing tubes single poly mailers carton packing peanut bag List Price $ 27.59 Yes $ 1.75 Yes $ 8.99 Yes $ 21.00 Yes