Assignment -39 | Inventory Management and Analysis

Excepted timing : 10 minutes

Sample Data

| Product ID | Product Name | Category   | Quantity | Reorder Level | Last Ordered |

|------------|--------------|------------|----------|---------------|--------------|

| 1          | Laptop       | Electronics| 10       | 5             | 2023-01-01   |

| 2          | T-shirt      | Clothing   | 50       | 20            | 2023-02-05   |

| 3          | Smartphone   | Electronics| 15       | 8             | 2023-03-08   |

 


Task :
  1. Calculate the reorder quantity (Reorder Level - Quantity) for each product.
  2. Use TODAY function to calculate the number of days since the last order.
  3. Create conditional formatting rules to highlight products below the reorder level.
  4. Use COUNTIFS function to count the number of products in each category.
  5. Create a PivotTable to summarize inventory details and insert a PivotChart to visualize it.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

 

Steps to Perform:

  1. Calculate Reorder Quantity for Each Product:
    • Subtract Quantity from Reorder Level to calculate the reorder quantity. For example:
      • For Laptop: 5 - 10 = -5
      • Adjust formula for other products accordingly.
  2. Use TODAY Function to Calculate Days Since Last Order:
    • Assuming Last Ordered date is in column F, use:
      • =TODAY() - F2 This formula calculates the number of days since the last order was placed.
  3. Create Conditional Formatting Rules to Highlight Products Below Reorder Level:
    • Select the Quantity column.
    • Go to Home > Conditional Formatting > New Rule.
    • Use a formula to determine which cells to format: =C2 < E2 (assuming Quantity is in column C and Reorder Level is in column E).
    • Choose the formatting style (e.g., fill color).
  4. Use COUNTIFS Function to Count Products in Each Category:
    • Assuming Category is in column C, use:
      • =COUNTIFS(C2:C4, "Electronics") for Electronics category
      • =COUNTIFS(C2:C4, "Clothing") for Clothing category Adjust criteria and range as per your actual data.
  5. Create a PivotTable to Summarize Inventory Details and Insert a PivotChart:
    • Select the data (Product ID, Product Name, Category, Quantity, Reorder Level, Last Ordered).
    • Go to Insert > PivotTable and place it in a new or existing worksheet.
    • Drag fields to Rows, Columns, and Values area to summarize the data as desired.
    • After creating the PivotTable, go to Insert > PivotChart to create a chart based on the PivotTable data.