Assignment -21 | Inventory Management analysis

Excepted timing : 10 minutes

| Product ID | Product Name  | Category   | Price | Quantity |

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

| 1          | Laptop        | Electronics| 1000  | 5        |

| 2          | T-shirt       | Clothing   | 20    | 10       |

| 3          | Smartphone    | Electronics| 500   | 8        |


Task :
  1. Calculate the total inventory value for each product (Price * Quantity).
  2. Use SUMIF function to calculate the total inventory value for Electronics category products.
  3. Calculate the average price of all products using AVERAGE function.
  4. Use COUNTIFS function to count the number of products with Price > $500 and Quantity > 5.
  5. Create a bar chart to visualize the inventory value of each product.

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

Steps to Perform:

  1. Calculate Total Inventory Value for Each Product
    • Insert a new column titled "Inventory Value" next to the existing data.
    • Use the formula =Price * Quantity in the first cell under "Inventory Value."
    • Drag down the formula to calculate the total inventory value for each product.
  2. Use SUMIF Function to Calculate Total Inventory Value for Electronics Category
    • In a new cell, use the formula =SUMIF(Category range, "Electronics", Inventory Value range) to calculate the total inventory value for Electronics category products.
    • Replace Category range and Inventory Value range with the actual ranges of Category and Inventory Value columns respectively.
  3. Calculate Average Price of All Products Using AVERAGE Function
    • Use the formula =AVERAGE(Price range) to calculate the average price of all products.
    • Replace Price range with the actual range of Price column.
  4. Use COUNTIFS Function to Count Products with Price > $500 and Quantity > 5
    • Use the formula =COUNTIFS(Price range, ">500", Quantity range, ">5") to count the number of products that have a Price greater than $500 and Quantity greater than 5.
    • Replace Price range and Quantity range with the actual ranges of Price and Quantity columns respectively.
  5. Create a Bar Chart to Visualize Inventory Value of Each Product
    • Select the Product Name column and Inventory Value column (including headers).
    • Go to the "Insert" tab in Excel and click on "Bar Chart" in the "Charts" group.
    • Choose a suitable bar chart style (e.g., clustered bar chart) to visualize the inventory value of each product.