Project activity 2: Excel for Data Analytics

Project activity 2: Excel for Data Analytics

Analyzing Monthly Sales Data and Creating Charts in Excel

Objective:

The objective of this project activity is to analyze monthly sales data for a retail company and create various calculations and charts using Microsoft Excel. Students will perform data analysis, calculate key metrics, and visualize the data through charts to gain insights and present their findings effectively.

Data:

Here’s the monthly sales data for the retail company:

Tasks:

1. Calculate Total Sales for each month:

   – Create a new column labeled “Total Sales” and calculate the sum of sales for each month by adding the sales figures for Product A, Product B, and Product C.

2. Calculate Average Sales per Month:

   – Create a new column labeled “Average Sales” and calculate the average sales for each month by dividing the total sales by 3 (since there are three products).

3. Identify the Month with the Highest and Lowest Total Sales:

   – Use Excel’s MAX and MIN functions to determine the month with the highest and lowest total sales. Display the results in a clear format.

4. Create a Column Chart:

   – Select the month names and the total sales column to create a column chart.

   – Customize the chart title, axes labels, and data labels to provide clear and informative visual representation of the monthly sales data.

5. Create a Line Chart:

   – Select the month names and the average sales column to create a line chart.

   – Customize the chart title, axes labels, and data labels to visualize the trend in average sales over the months.

6. Create a Pie Chart:

   – Select a specific month’s sales data (e.g., January) for all products to create a pie chart.

   – Customize the chart title, data labels, and colors to represent the distribution of sales for different products in that month.

7. Interpret the Charts and Summarize Findings:

   – Analyze the charts created and identify any notable trends, patterns, or insights.

   – Summarize the findings in a separate worksheet, providing a clear explanation of the sales performance and any observations derived from the data analysis and visualizations.