--BUSN3640 Project : Sales Data Analysis--

A leading manufacturer in the UK, supplying six retailers (Retailers A, B, C, D, E, and

F) as well as selling through its own online store, offers five main products (Product IDs:

2001, 2002, 2003, 2004, and 2005). The company aims to analyze its sales data from 2021 to 2023, focusing on its top 15 customers (Customer IDs: 1001 to

1015). For this purpose, all relevant transactions within this period have been compiled into the provided Excel file (Dataset 3640.xIsx). This file includes details such as transaction

time, product number, customer ID, quantity purchased, unit price, payment method, retailer, and customer information (including email address, phone number, and region). The

manufacturer seeks to gain insights into its product and customer profiles. You are required to prepare these using the specific instructions in Tasks 1, 2, 3 and 4 in an MS Excel file.

Ensure the instructions below are adhered to.

General Instructions:

1. Dynamic Formulae: You will need to build a spreadsheet model in Excel to carry out your analysis. The model should be fully functional and interactive in that the inputs can be

changed by a user (i.e., your seminar leaders) and the model's results automatically calculated anddisplayed. Please adhere to good spreadsheet practice.

2. Originality and Plagiarism Caution: You are required to use your own unique ideas for this project. It is imperative that you do not share your work with others. Since no templates are   provided, submissions with identical designs, layouts or content will be considered

plagiarism. Such cases will receive zero marks and be referred to the academic misconduct committee for further action.

3. Modification Transparency: If you make any modifications (other than those advised in this

brief) to the original data sheet or add additional columns, these changes must be

prominently highlighted and clearly documented. This ensures transparency and ease of evaluation.

4. Enhancing User Experience: You are encouraged to utilize various functions and tools to make your Excel model both user-friendly and aesthetically appealing.

Consider incorporating elements like buttons, color schemes, and borders to enhance usability and visual appeal.

5. Exploration and Innovation: While you are encouraged to explore and employ

Excel's built-in functions that may not have been covered in lectures and seminars, the use of external coding is strictly prohibited.

Task 1 (10%):

TIP: Watch last week's lecture recording (week 11) and follow along with the steps.

As one of the 15 customers, you will use the "Project_Randomiser" file to access your

unique information. To begin, simply input your university email address into cell B2 and click "Refresh". Follow these steps:

1. Open the Workbook: Launch the "Project Randomiser" workbook and enter your Kent email address (e.g., [email protected]) without any spaces into cell B2. Then, click on   "Refresh".

2. View Your Details: After refreshing, your details will appear:

a. Your first name in cell B8.

b. Your customer ID in cell B9.

c. Your unit price in cell B10.

d. Your region/town in cell B11.

e. Access Your Dataset: Open your "Dataset_3640.xIsx" file.

3. Filter and Edit Data:

a. In "Dataset 3640.xisx", use column C to filter data to your customer ID (from cell B9 of "Project Randomiser").

b. Replace the entire customer ID in the filtered data with your first name (from cell B8 of "Project Randomiser").

c. With the filter still active, change the Unit Price in column E to the random number shown in cell B10 of "Project Randomiser".

d. With the filter still active, update the customer email in column H to your Kent email address (from cell B7 of "Project Randomiser").

e. With the filter still active, in column J, replace the region with the one in cell B11 of "Project Randomiser".

4. Save Your Dataset: Save the "Dataset_3640.xIsx" file with the new name provided in cell B12 of "Project Randomiser".

5. Continue Working on the New File: Clear the filter applied in your dataset and proceed to Task 2. Ensure all subsequent work is done in the workbook saved under the new name, as this is the file you need to submit.

Task 2: Data Set Overview and Insights (30%):

Provide a comprehensive description of the dataset with managerial insights. Employ

appropriate graphical representations and analysis methods (e.g., pivot tables) to aid in

understanding. Present this information and a summary (up to 300 words) on the sheet titled "Task 2".

Marking criteria:

1. Revenue Analysis - 5%

a. Calculation: Compute the revenue in the "DataSet" sheet (Column K).

b. Presentation in 'Task 2' Sheet: Present the total revenue, average revenue, and maximum revenue.

c. Identification: Determine the specific transaction that generated the maximum revenue and identify the corresponding customer.

d. Additional Statistics: You may include other descriptive statistics.

2. Pivot Tables - 10%

a. Creation in 'Task 2' Sheet: Generate meaningful Pivot Tables, showcasing data like Total Quantity, categorized by Product ID and Region.

b. Produce at least three distinct tables.

3. Charts and Summary - 10%

a. Develop meaningful charts in the "Task 2" sheet.

b. Insights Summary: Utilize the information from steps 1, 2, and 3 to summarize your findings and insights.

4. Sheet Organization: Arrange your sheet to be aesthetically pleasing, tidy, and easy to interpret.

5. Design and Creativity - 5%

Task 3: Product and Customer Analyzer Tools: (40%)

On a new sheet named "Analyzer," develop a product analyzer tool. This tool should reveal   key characteristics of a selected product (e.g. "NOT LIMITED TO* top customer, region, etc.) when the user inputs/selects a product ID. Similarly, create a customer analyzer tool on the   same sheet that displays vital information about a selected customer. Utilize conditional

functions and "search and retrieve/lookup and reference" functions for these tools. Use sheet "Task 3" to complete this task

Hint: you may use countifs, averageifs, maxifs, vlookup, match, index, frequency formulas, etc.

Marking criteria:

1. Product Analyzer - 15%

a. For a product selected in an input cell, extract and display relevant business information. Provide at least five distinct pieces of information related to the selected product.

Example - Top Region:

Calculate the number of transactions for the selected product across

each region.

product.

Identify the region with the highest number of transactions for this

ill.

Label this region as the "top region" for the product.

2. Customer Analyzer - 15%

a. For a customer selected in an input cell, extract and display relevant information.   b. Provide at least five distinct pieces of information related to the selected customer.

Example: This may include the total number of transactions by the customer and their contact details.

3. Design, Layout, and Creativity - 10%

Task 4: Financial Analvsis for Product Revenue Investment: (20%)