--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%)
Focusing on a specific product (e.g., product 2002), the manufacturer intends to invest all of its revenue (now in January 2024) in a bank for a certain number of years. Three banking options with varying Annual Percentage Rates (APRs) are being considered: Option 1 at
2.4%, Option 2 at 2.6%, Option 3 at 2.8%, Option 4 at 3%, Option 5 at 3.2%. Design a tool on a new sheet titled "Finance" that calculates and displays the expected savings after 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, or 24 years for each banking option. This tool should provide a simultaneous comparison of all outcomes and include a clear analysis. This tool should
also work for any product.
Use sheet "Task 4" to complete this task
Marking Criteria:
1. Product Revenue Calculation - 5%
a. Enter the Product ID in an input cell.
b. Compute the total revenue generated by the selected product.
2. Future Value Calculation - 5%
a. For a specified Annual Percentage Rate (APR) and a number of years, calculate the future value.
3. APR vs. Years Analysis - 5%
a. Develop an analysis showing all possible outcomes for different APR options compared against various timeframes (years).
4. Design, Layout, and Creativity - 5%