Home

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:

  1. 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).
  2. Calculate Total Sales: Use the SUM function to calculate the total sales for all products.
  3. Find the Top-Selling Product: Use conditional formatting to highlight the row with the highest total sales.
  4. Calculate Average Unit Price: Use the AVERAGE function to calculate the average unit price across all products.
  5. 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.
  6. 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.