Case Study: Sales Performance Analysis

Background:

You are a data analyst at a retail company. The company wants to analyze its sales performance over the past year to make informed decisions for the next quarter. You have been given a dataset that includes the following columns:

Date: The date of the sale.

Product: The product sold.

Category: The category of the product.

Sales Amount: The amount of the sale.

Units Sold: The number of units sold.

Region: The region where the sale took place.

Task:

Analyze the sales performance using Excel to provide insights and recommendations.

Questions:

Descriptive Analysis:

What is the total sales amount and total units sold for each product and category?

Calculate the average sales amount per transaction and the total number of transactions.

Trend Analysis:

Create a line chart showing the trend of total sales amount over the months. Identify any significant trends or seasonal patterns.

Comparative Analysis:

Compare the sales performance of different regions. Which region had the highest total sales amount and which had the highest average sales amount per transaction?

Predictive Analysis:

Using historical data, create a simple linear regression model to forecast sales for the next quarter based on the trend observed in the data.

Prescriptive Analysis:

Use the Solver tool to determine the optimal product mix to maximize total sales while staying within a budget constraint for marketing expenses.

Scenario Analysis:

Create a scenario table to analyze how changes in the price of products would impact the total sales amount and units sold.

Exploratory Data Analysis $($EDA$)$:

Use pivot tables to explore the relationship between product categories and sales amounts. Create a bar chart to visualize the top $5$ products by total sales amount.

Inferential Analysis:

Perform a t$-$test to determine if there is a significant difference in average sales amounts between two regions. What are the results?

Instructions:

Use Excel functions such as SUM, AVERAGE, COUNT, and pivot tables to answer the descriptive and comparative questions.

Utilize charts like line graphs and bar charts for trend and comparative analysis.

Apply regression analysis tools for forecasting.

Use the Solver add$-$in for optimization problems.

Create scenario tables to explore different pricing strategies.

Conduct hypothesis testing for inferential analysis.