Assignment -11 | Sales Dashboard

Excepted timing : 30 minutes

| Order ID | Customer Name | Product  | Quantity | Unit Price | Order Date |
|----------|---------------|-----------|----------|------------|------------|
| 1001     | John Doe      | Laptop    | 2        | 1000       | 2023-05-10 
| 1002     | Jane Smith    | Smartphone| 5        | 500        | 2023-06-15 
| 1003     | David Brown   | Tablet    | 3        | 700        | 2023-07-20 
| 1004     | John Doe      | Laptop    | 2        | 100        | 2023-05-18 
| 1005     | Jane Smith    | Smartphone| 10       | 500        | 2023-06-17 
| 1005     | David Brown   | Tablet    | 13       | 710        | 2023-07-21

 
Task :
  1. Create a table to store sales data with columns for Order ID, Customer Name, Product, Quantity, Unit Price, and Order Date.
  2. Calculate the total sales revenue for each order (Quantity * Unit Price).
  3. Use INDEX and MATCH functions together to find the Customer Name for Order ID 1003.
  4. Create a PivotChart to visualize total sales by product category.
  5. Use slicers in the PivotChart to filter data by month and product.

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

 

 

Step 1: Create a Table to Store Sales Data

  1. Enter Data: Enter your sales data into Excel with the following columns:
  2. Create Table: Select your data range including headers (A1), then go to the Insert tab > Table.

Step 2: Calculate Total Sales Revenue for Each Order

  1. Insert Column: In cell G1, enter Total Revenue.
  2. Formula: In cell G2, enter the formula =D2 * E2 to calculate total revenue for the first order.
  3. Auto-fill: Double-click the fill handle (small square at the bottom-right corner of the cell) of cell G2 to apply the formula down the column.

Step 3: Use INDEX and MATCH Functions to Find Customer Name for Order ID 1003

  1. Formula: In a cell (e.g., H1), enter the formula:

This formula searches for Order ID 1003 in column A and returns the corresponding Customer Name from column B.

Step 4: Create a PivotChart to Visualize Total Sales by Product Category

  1. Insert PivotTable: Select your table data range (A1) and go to the Insert tab > PivotTable.
  2. PivotTable Fields:
    • Drag Product to Rows area.
    • Drag Total Revenue to Values area (ensure it calculates as Sum).
  3. Insert PivotChart: With your PivotTable selected, go to the Insert tab > PivotChart. Choose the chart type you prefer (e.g., column chart).

Step 5: Use Slicers in the PivotChart to Filter Data by Month and Product

  1. Insert Slicers:
    • With the PivotChart selected, go to the Insert tab > Slicer.
    • Choose Order Date to add a slicer for filtering by month.
    • Choose Product to add another slicer for filtering by product.