Scribd
Upload
32 views1 page

DVE Assignment II

The document outlines an assignment focused on analyzing and visualizing sales data using Excel. It includes steps for creating a sample dataset, performing key calculations such as total sales and monthly summaries, and generating visualizations like line, bar, and pie charts. The objective is to uncover trends, identify top-performing products, and assess regional performance.

Uploaded by

firecrator39
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
32 views1 page

DVE Assignment II

The document outlines an assignment focused on analyzing and visualizing sales data using Excel. It includes steps for creating a sample dataset, performing key calculations such as total sales and monthly summaries, and generating visualizations like line, bar, and pie charts. The objective is to uncover trends, identify top-performing products, and assess regional performance.

Uploaded by

firecrator39
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Assignment: - II

“Sales Data Analysis & Visualization Using Excel”


Objective:
Analyze monthly sales data to uncover trends, identify top-performing products, and assess
regional performance.

Step 1: Sample Data

Here's a basic sample dataset. You can copy this into Excel:

Date Region Sales Rep Product Units Sold Unit Price Total Sales
01/01/2024 North Aryan Laptop 10 800 =E2*F2
01/01/2024 South Banita Smartphone 20 500 =E3*F3
01/01/2024 East Chandna Tablet 15 300 =E4*F4
01/01/2024 North Ayus Laptop 8 800 =E5*F5
01/01/2024 West Deepa Smartphone 25 500 =E6*F6
01/02/2024 South Banita Tablet 18 300 =E7*F7
01/02/2024 East Chandana Laptop 12 800 =E8*F8
➡️ Add 20–30 more rows for richer analysis.

Step 2: Key Calculations

1. Total Sales:
Use formula: =Units Sold * Unit Price
(e.g., =E2*F2)
2. Monthly Sales Summary:
Create a new column or pivot table to summarize total sales by month.
3. Top Sales Rep:
o Use Pivot Table: Rows → Sales Rep, Values → Sum of Total Sales.
o Sort to find the top performer.
4. Sales by Region:
o Create another pivot table: Rows → Region, Values → Sum of Total Sales.
o Use a pie chart to visualize.

Step 3: Visualizations
Line Chart for Monthly Sales:
o Insert → Chart → Line.
o X-axis: Month
o Y-axis: Total Sales
2. Bar Chart for Sales by Product:
o Pivot Table with Product as rows, Total Sales as values.
o Insert → Chart → Bar/Column
3. Pie Chart for Regional Sales:
o Use region-wise sales pivot table.
o Insert → Chart → Pie

You might also like