CASE STUDY AND QUESTIONS.

EXCEL COURSEWORK

CASE STUDY AND QUESTIONS.

Case Study

The dataset (SalesData.xlsx) includes the following key columns:

OrderNo: Order Number

OrderDate: Date of the sale

ProductLine: Name of the product sold.

QuantityOrdered: Number of units sold.

Sales: Revenue generated from the sale

Country: Geographic country of the sale

You are working as a data analyst for a retail company. The company has provided you with a data set containing sales information for a 3-year period. The excel file that has been provided to you contains the sales dataset of a group of companies that sell cars, planes, ships etc. The dataset has clearly stated the details of all the sales invoices that the companies have had over the years. Your task is to analyze the dataset and provide a brief analytical report on certain key points. The report requires the analyst to use all the necessary data points given within the table to arrive at a desirable conclusion. To help you with your analysis, the following are the cases that you need to concentrate on:

Tasks:

Section A: Cleaning, Formatting and Sorting Data

Open the data set in Excel and inspect the data.

Identify and address any missing or inconsistent values.

Change the format of the order date into one uniform format of your choice.

Sort the data according to ascending order of the YEAR_ID and then by PRODUCTLINE.

Format all the tables to depict a professional look after all the calculations.

(5x5=25 marks available)

Section B: Basic Analysis of the Dataset.

Calculate the total sales for each row of product, given that the price and the quantity sold are already available. Use Excel function and have a dedicated column to show the calculations.

Calculate the Total sales for three product categories of your choice for the year 2003.Use Excel function and have a dedicated row to show the calculations.

Create a pivot table to analyze ProductLine for the 3-year period.

Make a comment on your observations for B3.

Use conditional formatting to highlight the top 10% of revenue generating ProductLine.

(5x5=25 marks available)

Section C: In-depth analysis of the Dataset.

Which order/orders was the highest sale made by the company for the year 2004?

Which order/orders was the lowest sale made by the company for the year 2004?

Conditionally format to know the quantity of orders that are below 25 and above 25.

Use the VLOOKUP function to find the Contact last name of the person/company that spent the most money on a purchase.

Utilize the VLOOKUP function to retrieve additional information about the top selling ProductLine. (5x5=25 marks available)

Select the data for 3 products for the year 2003 and paste it on another worksheet. Name it New Table for Data Visualization exercise.

Section D: Data Visualization of the following situations.

Total sales of each product.

Generate a bar chart illustrating the distribution of units sold across different productsBestselling product.

Price fluctuations of the three categories of product.

Design a pie chart to represent the contribution of each region to the total revenue. (5x5=25 marks available)

Note: By the end of the coursework, you should be having 3 worksheets.

Save your excel file as your_student_id.xlsx. (e.g. 123456.xlsx)

Submit via VLE assignment hand-in link.

Check if youve received the submission confirmation email from VLE. (if not, check your submission, and try again until you receive the email)