Microsoft Excel Assignment: Sales Analysis
Connect with me: Youtube | LinkedIn | WhatsApp Channel | Web | Facebook | Twitter
This assignment will help you practice using basic Excel functions and formatting to analyze sales data.
Scenario: You are a sales manager for a company that sells electronics. You are provided with a spreadsheet containing sales data for the past quarter. Your task is to analyze this data and answer some questions to gain insights into sales performance.
Sample Sheet:
| Column A |
Column B |
Column C |
Column D |
| Product |
Quantity Sold |
Unit Price |
Total Sales |
| Laptop A |
25 |
$800 |
=B2*C2 |
| Laptop B |
18 |
$1000 |
=B3*C3 |
| Phone X |
30 |
$500 |
=B4*C4 |
| Phone Y |
12 |
$700 |
=B5*C5 |
| Monitor Z |
15 |
$200 |
=B6*C6 |
Instructions:
- Complete the “Total Sales” column: Use the appropriate formula to calculate the total sales for each product by multiplying the quantity sold (column B) by the unit price (column C).
- Calculate Total Sales: Use the SUM function to calculate the total sales for all products.
- Find the Top-Selling Product: Use conditional formatting to highlight the row with the highest total sales.
- Calculate Average Unit Price: Use the AVERAGE function to calculate the average unit price across all products.
- Analyze Sales by Product Category: Assume the products can be categorized (Laptops, Phones, Monitors). Create a new table summarizing total sales for each category. Use the SUMIF function to achieve this.
- Create a Chart: Create a bar chart to visualize total sales for each product.
Optional:
- Use data validation to ensure only valid data is entered in the “Quantity Sold” and “Unit Price” columns.
- Create a pie chart to show the percentage contribution of each product category to total sales.
Deliverables:
- Submit your completed Excel spreadsheet with all formulas and formatting applied.
Tips:
- Use the Excel Help function (F1 key) for assistance with specific formulas and functions.
- Explore formatting options to make your spreadsheet visually appealing and easy to understand.
This assignment allows you to practice:
- Entering and formatting data in Excel
- Using basic formulas (SUM, AVERAGE, SUMIF)
- Conditional formatting
- Creating charts and graphs
By completing this assignment, you will gain valuable skills in data analysis and presentation using Microsoft Excel.