Scribd
Upload
44 views2 pages

Excel Data Analysis for Retail Sales

The Excel assignment focuses on developing proficiency in data organization, analysis, and visualization using Microsoft Excel in a business context. Students are tasked with creating a dataset for a retail company, TechZone, and performing various analyses including calculations of total sales, averages, and classifications of performance. The assignment also requires the creation of PivotTables, charts, and the application of data validation and conditional formatting, with a submission deadline of October 27, 2025.

Uploaded by

Fazeelat Shoukat
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
44 views2 pages

Excel Data Analysis for Retail Sales

The Excel assignment focuses on developing proficiency in data organization, analysis, and visualization using Microsoft Excel in a business context. Students are tasked with creating a dataset for a retail company, TechZone, and performing various analyses including calculations of total sales, averages, and classifications of performance. The assignment also requires the creation of PivotTables, charts, and the application of data validation and conditional formatting, with a submission deadline of October 27, 2025.

Uploaded by

Fazeelat Shoukat
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

Excel Assignment: Business Data Analysis

Objective:

To develop students’ proficiency in using Microsoft Excel for data organization, analysis, and
visualization in a business context.

Scenario:

You are a data analyst in a retail company named TechZone. The company sells electronic products
(Laptops, Phones, Tablets, Accessories) across four regions of Pakistan: North, South, East, and
West. Your manager has provided a dataset of monthly sales and asked you to prepare a detailed
Excel report.

Dataset:

Create a dataset in Excel (or use the one provided by your instructor) with the following columns:

Product Product Category Region Month Units Unit Total Salesperson


ID Name Sold Price Sales

P001 Laptop Laptop North Jan 25 75000 Ali


X1

P002 Phone Z5 Phone South Jan 40 55000 Sara

... ... ... ... ... ... ... ... ...

Add at least 30 rows of data covering all four regions and four product categories.
Tasks:

Part 1: Data Preparation

1. Enter the dataset as described above.

2. Use a formula to calculate Total Sales = Units Sold × Unit Price.

3. Format the sheet neatly (bold headers, borders, and cell shading for headings).

4. Apply data validation for:

o Category (Laptop, Phone, Tablet, Accessory)


o Region (North, South, East, West)

Part 2: Formulas and Functions

1. Calculate:

o Average Units Sold per Category


o Highest and Lowest Sales values

o Total Sales per Region

2. Use COUNTIF and SUMIF functions for summary analysis.

3. Use IF function to classify performance:


o If Total Sales > 1,000,000 → “Excellent”

o If between 500,000 and 1,000,000 → “Good”

o Otherwise → “Needs Improvement”

Part 3: Data Analysis Tools

1. Create a PivotTable showing Total Sales by Category and Region.

2. Add a PivotChart (Column or Pie chart) to visualize sales distribution.

3. Use Conditional Formatting to highlight:

o Top 3 highest Total Sales values in green.


o Sales below 300,000 in red.

Part 4: Visualization

1. Create a bar chart showing monthly total sales.

2. Create a line chart comparing sales trends between two products.

3. Add data labels and titles to make the charts presentation-ready.

Submission Requirements:
 File Name: YourRegistraionNo_ExcelAssignment.xlsx

 Include all sheets (Data, Pivot, Charts, Dashboard).

 Ensure all formulas are visible and functional.

 Submit via WhatsApp group by 27-10-2025.

You might also like