Assignment -20 | Customer Analysis

Excepted timing : 10 minutes

| Customer ID | Customer Name | City        | Total Orders | Total Spend |

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

| 1           | John Doe      | New York    | 10           | 5000        |

| 2           | Jane Smith    | Los Angeles | 15           | 7000        |

| 3           | David Brown   | Chicago     | 8            | 4000        |



Task :
  1. Create a PivotTable to summarize total spend by City.
  2. Use slicers in the PivotTable to filter data by Customer Name and City.
  3. Create a PivotChart to visualize total orders by Customer Name.
  4. Add a timeline slicer to the PivotTable to analyze data for specific date ranges.
  5. Insert a PivotTable to summarize the average total spend per order for each customer.

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

Steps to Perform:

  1. Create a PivotTable to Summarize Total Sales by Category
    • Select the data range that includes Product Name, Category, Price, Quantity, and Sales Date.
    • Go to the "Insert" tab in Excel and click on "PivotTable" under the "Tables" group.
    • In the Create PivotTable dialog box, ensure the correct data range is selected and choose where to place the PivotTable.
    • Drag "Category" to Rows and "Price" (or "Quantity") to Values to summarize total sales by category.
  2. Add a Calculated Field for Profit Margin
    • In the PivotTable Fields pane, right-click on any field and choose "Fields, Items & Sets" -> "Calculated Field."
    • Name the calculated field (e.g., Profit Margin) and enter the formula:= 'Sales' - 'Total Cost'
    • Adjust 'Sales' and 'Total Cost' to match your actual field names (e.g., Price * Quantity for Sales, and possibly a manually entered Cost for Total Cost).
  3. Insert a PivotChart Based on the PivotTable
    • Click inside the PivotTable to select it.
    • Go to the "Insert" tab and click on "PivotChart" in the "Charts" group.
    • Choose the type of chart (e.g., Clustered Column) that best represents your data from the options provided.
    • Excel will generate a PivotChart based on the summarized data in the PivotTable.
  4. Filter the PivotTable to Show Only Electronics Category Products
    • Click on the drop-down arrow next to "Category" in the PivotTable.
    • Uncheck "Select All," then select only the "Electronics" category.
    • Excel will filter the PivotTable to show data for Electronics category products only.
  5. Group the Sales Date Field by Month and Year
    • Right-click on any date under "Sales Date" in the PivotTable.
    • Choose "Group" from the context menu.
    • In the Grouping dialog box, select "Months" and "Years," then click "OK."
    • Excel will group the Sales Date field by month and year in the PivotTable.