Assignment 1
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Assignment 1
Introduction
Once a small local shop in the heart of the city, Mountain Wheels Superstore has now evolved into a bustling hub for all things biking. From high-performance bicycles to trendy cycling apparel, and from durable components to innovative accessories, Mountain Wheels offers a comprehensive range of products to cater to every cycling enthusiast’s needs.
With their recent expansion, both in physical storefronts and online presence, the management at Mountain Wheels faces new challenges. The diverse range of products, growing customer base, and expanding geographical reach have made it increasingly complex to track sales performance, understand customer preferences, and strategize marketing efforts effectively.
To steer Mountain Wheels towards continued success, a detailed sales and marketing dashboard/report is proposed in Power BI. This tool would be leveraged to harness the power of data, transforming it into actionable insights and guiding the team in their strategic decisions. Through this dashboard, Mountain Wheels Superstore aims to pedal ahead, not only in terms of sales but also in customer satisfaction and market presence.
Datasets Provided
• Sales Data: Includes transactional details like sales date, customer ID, product ID, and order quantity
• Calendar Data: Contains date values from 2015, 2016 and 2017
• Customer Details: Covers customer demographics like age, gender, location, occupation, education and e-mail
• Product Categories: Lists the primary categories of the products sold
• Product Sub-Categories: Offers amore detailed breakdown of the product categories
• Products: Detailed data on each product, including product ID, name, category ID, sub-category ID and price
• Returns: Information about returned products, including customer ID, product ID and date of return
• Territories: Geographic data on sales territories, including territory ID, name, and associated region
Descriptive Analytics
Create 2 files - a Power BI report and a Word document with the solutions to the following questions.
Within the Power BI Report, create 3 pages and put solution (i.e. visuals) to one question per page. [6 marks total; 2 marks per ques]
Within the Word file, for each question:
(i) Briefly mention the steps to your solution (i.e. Datasets used, Power Query steps,
Relationship Mapping, Calculation steps and final Visualization). [2 marks per ques.] Note: Screenshots are optional.
(ii) Briefly explain the reason behind choosing the visual (i.e. bar graph, line chart etc.)
[1 mark per ques.]
(iii) Briefly explain how can the solution(s) be leveraged by business stakeholders to make better data-driven decisions [1.5 marks per ques.]
Questions:
1. What is the total sales revenue generated by each product category in 2017, and how does it compare to the previous years i.e. 2016 and 2015?
FAQ Note:
• Returns are NOT to be accounted in the revenue calculation
2. Which territories, product categories and product subcategories have the highest return rates?
FAQ Note:
• The business stakeholders would like the solution to be granular and have the ability to drill- down by Territory → Product Category → Product Sub-Category
3. What is the customer demographics breakdown by gender, age and education level in 2017 and how does it compare to the previous years i.e. 2016 and 2015?
FAQ Note:
• The business stakeholders would like the solution to be granular and have the ability to drill- down by Gender → Age → Education Level
2024-01-25