Scribd
Upload
2 views2 pages

Lab 6 Assignment

The assignment requires the creation and analysis of a monthly sales dataset for 10 products using Excel. Tasks include calculating total and average sales, identifying highest and lowest sales, visualizing trends, classifying products, and applying COUNTIF and conditional formatting. The final submission must include the dataset, calculations, formulas, and visualizations, evaluated on completeness and accuracy.

Uploaded by

kelvinhunter692
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
2 views2 pages

Lab 6 Assignment

The assignment requires the creation and analysis of a monthly sales dataset for 10 products using Excel. Tasks include calculating total and average sales, identifying highest and lowest sales, visualizing trends, classifying products, and applying COUNTIF and conditional formatting. The final submission must include the dataset, calculations, formulas, and visualizations, evaluated on completeness and accuracy.

Uploaded by

kelvinhunter692
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

MUKUBA UNIVERSITY

COMPUTER SCIENCE DEPARTMENT

LAB 6 ASSIGNMENT

QUESTION
Analyzing Monthly Sales Data
SCENARIO:
You are the manager of a store that sells 10 different products. Over the past year, you have
been tracking monthly sales for each product. Your task is to create and analyze this dataset
using Excel, applying mathematical functions and tools to extract insights.

INSTRUCTIONS:
1. Dataset Creation
 Create a dataset in Excel with the following details:

o Product Name: Choose 10 unique products (e.g., "Product A," "Product


B").
o Month: Include all months from January to December.
o Sales: Generate random sales values between 100 and 1,000 for each
product and month.

2. Analysis Tasks
Using the dataset you created, perform the following calculations and analyses in Excel:
A) Total Sales (monthly & yearly)
 Calculate the total monthly sales for your products
 Determine the total yearly sales for each of your products
B) Average Sales (Monthly & Yearly)
 Calculate the average monthly sales for your products
 Determine the average yearly sales for each of your products.
C) Highest and Lowest Sales
 Identify the highest and lowest sales across all products the whole year
D) Sales Trend
 Create a line chart to visualize the sales trend for your products over the year.
E) Product Classification
 Use the IF function to classify each product as a "High Performer" (if total sales >
K600) or a "Low Performer." Add this classification as a new column and ensure
you values above and below 600 in your dataset
3. COUNTIF
 Use the COUNTIF function to count how many months each product had sales
above K500.
 Apply Conditional Formatting to highlight sales below K300 in red.
4. Submission Requirements
Submit the Excel file containing:
 The dataset.
 All calculations, formulas, and visualizations

EVALUATION CRITERIA:
 Completeness and accuracy of the dataset.
 Correct application of Excel functions and formulas.
 Quality and clarity of visualizations.

ELIAS M. CHIMBA (Mr)


Lab Technician- Computer Science

You might also like