Microsoft Excel Assignment: Sales Analysis
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.
Explore More Excel Topics
Microsoft Excel Fundamentals
Microsoft Excel More ...