This project demonstrates a business insights dashboard built in Microsoft Excel by leveraging data from GitHub-hosted tables: Orders, Returns, and People. The data was connected to Excel via GitHub and transformed using Power Query Editor to perform meaningful analyses.
The dashboard includes dynamic visualizations, slicers, and a timeline for intuitive interaction and analysis. It addresses key performance indicators (KPIs) and provides actionable insights into sales, profits, customer behavior, and market trends.
- Enhanced Decision-Making: Offers clear and actionable insights to improve business strategies.
- Scalable Approach: Easily adaptable for any business dataset hosted on GitHub.
- Dynamic Visualizations: Interactive dashboard with slicers and a timeline to analyze multiple KPIs.
Objective: To track and visualize the key metrics for the business performance, allowing stakeholders to have an overview of the business health.
Solution:
The dashboard dynamically displays the following KPIs:
- Total Sales (💰): Sum of all sales generated.
- Total Profit (📈): Sum of all profits made across the dataset.
- Total Quantity (📦): Total quantity of items sold.
- Total Orders (🛒): Total count of orders placed.
- Profitability (💹): Sum of profit margins for the orders.
Objective: To analyze how sales and profits are distributed across various dimensions like products, regions, and time.
Solution:
A chart display how total sales and profit are affected by different segments such as region, category, and market. Visual trends help identify periods of peak sales and profitability.
Objective: To evaluate the profit generated by each product category and identify the most profitable categories.
Solution:
A chart visualizes the profit distribution across various product categories. Categories with the highest profitability are highlighted, allowing business managers to focus on high-margin products.
Objective: To determine which customer segments contribute the most to the overall sales.
Solution:
A chart shows the percentage share of sales for each customer segment (Consumer, Corporate, Home Office). This helps identify high-value customer segments and optimize marketing strategies.
Objective: To analyze sales distribution across different countries.
Solution:
A chart shows sales performance in different countries, highlighting top-performing regions. This helps identify potential markets for expansion and regions with the highest demand.
Objective: To identify which sub-categories generate the most sales and profit.
Solution:
A chart identify the top sub-categories based on sales and profit. This allows businesses to optimize their product offerings and focus on high-demand items.
Objective: To pinpoint underperforming sub-categories with low sales or profit.
Solution:
A chart to the "Top Sub-Categories" analysis, but it displays the bottom sub-categories. Identifying these products helps in inventory management and potential discontinuation or promotion strategies.
Objective: To visualize how sales trends have evolved over the years.
Solution:
visualization shows yearly sales trends, helping to identify long-term growth patterns, seasonal dips, or spikes in sales. This is crucial for forecasting and budgeting.
Objective: To examine the return rates and analyze the reasons for returned orders.
Solution:
A chart analyzes the percentage of returned orders by various factors such as product type, region, or customer segment. This helps to understand return trends and address product issues or customer dissatisfaction.
Objective: To identify the top and bottom customers based on total spending.
Solution:
Two charts (Top Customers and Bottom Customers) show the highest and lowest-spending customers. This analysis helps in targeted marketing campaigns and rewarding loyal customers.
Objective: To understand market performance and identify top-performing markets.
Solution:
A column chart visualizes the total sales and profit by market (e.g., US, APAC, EMEA). This enables businesses to allocate resources effectively across regions and focus on profitable markets.
Objective: To calculate and visualize the market share of each region in terms of sales.
Solution:
A pie chart is used to show the market share of different regions (e.g., East, West, North America, EMEA). This helps in understanding regional dominance and growth opportunities in each market.
- Orders Table: View Orders Data
- Returns Table: View Returns Data
- People Table: View People Data
| KPI Name | Description | Symbol |
|---|---|---|
| Total Sales | Sum of sales generated | 💰 |
| Total Profit | Sum of profit earned | 📈 |
| Total Quantity | Sum of quantities sold | 📦 |
| Total Orders | Count of total orders | 🛒 |
| Profitability | Overall profitability | 💹 |
-
Data Collection:
- Host the tables (Orders, Returns, People) on GitHub.
- Connect Excel to GitHub to load the data into Power Query Editor.
-
Data Transformation:
- Cleaned, merged, and transformed data using Power Query Editor to create meaningful relationships.
-
Dynamic Dashboard Creation:
- Added slicers and a timeline for dynamic filtering.
- Designed interactive visualizations using charts to represent KPIs and insights.
-
Insights and Analysis:
- Analyzed trends, profit, and sales to derive actionable insights.
- Identified top and bottom customers and regions to inform business strategies.
This table shows details of returned products, including order ID and market.
| Returned | Order ID | Market |
|---|---|---|
| Yes | MX-2013-168137 | LATAM |
| Yes | US-2011-165316 | LATAM |
| Yes | ES-2013-1525878 | EU |
| Yes | CA-2013-118311 | United States |
| Yes | ES-2011-1276768 | EU |
| Yes | MX-2013-131247 | LATAM |
| Yes | ID-2011-20975 | APAC |
| Yes | IN-2014-58460 | APAC |
| Yes | ES-2011-3028321 | EU |
| Yes | MX-2014-148285 | LATAM |
| Yes | IN-2014-54708 | APAC |
| Yes | ID-2011-20989 | APAC |
| Yes | ES-2013-3323529 | EU |
| Yes | MX-2014-135328 | LATAM |
| Yes | IN-2012-63934 | APAC |
| Yes | IN-2014-43039 | APAC |
| Yes | CA-2012-150875 | United States |
| Yes | ES-2011-3074997 | EU |
| Yes | CA-2011-133690 | United States |
| Yes | IN-2014-84948 | APAC |
| Yes | CA-2013-157280 | United States |
| Yes | ID-2012-44173 | APAC |
| Yes | CA-2012-111948 | United States |
| Yes | CA-2014-167003 | United States |
| Yes | CA-2012-149636 | United States |
| Yes | IN-2014-46007 | APAC |
| Yes | CA-2014-154074 | United States |
| Yes | MX-2014-114601 | LATAM |
| Yes | MX-2014-142643 | LATAM |
This table includes detailed information on customer orders, including product IDs and categories.
| Row ID | Order ID | Returned | Order Date | Ship Date | Ship Mode | Customer ID | Customer Name | Segment | City | State | Country | Postal Code | Market | Region | Product ID | Category | Sub-Category | Product Name | Sales | Quantity | Discount | Profit | Shipping Cost | Order Priority |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 32298 | CA-2012-124891 | No | 7/31/2020 | 7/31/2020 | Same Day | RH-19495 | Rick Hansen | Consumer | New York City | New York | United States | 10024 | US | East | TEC-AC-10003033 | Technology | Accessories | Plantronics CS510 - Over-the-Head Headset | 2309.65 | 7 | 0 | 762.18 | 933.57 | Critical |
| 26341 | IN-2013-77878 | Yes | 2/5/2021 | 2/7/2021 | Second Class | JR-16210 | Justin Ritter | Corporate | Wollongong | New South Wales | Australia | APAC | Oceania | FUR-CH-10003950 | Furniture | Chairs | Novimex Executive Leather Armchair | 3709.39 | 9 | 0.1 | -288.77 | 923.63 | Critical | |
| 47221 | SG-2013-4320 | No | 11/5/2021 | 11/6/2021 | Same Day | RH-9495 | Rick Hansen | Consumer | Dakar | Dakar | Senegal | Africa | Africa | TEC-SHA-10000501 | Technology | Copiers | Sharp Wireless Fax, High-Speed | 2832.96 | 8 | 0 | 311.52 | 903.04 | Critical | |
| 22732 | IN-2013-42360 | No | 6/28/2021 | 7/1/2021 | Second Class | JM-15655 | Jim Mitchum | Corporate | Sydney | New South Wales | Australia | APAC | Oceania | TEC-PH-10000030 | Technology | Phones | Samsung Smart Phone, with Caller ID | 2862.68 | 5 | 0.1 | 763.28 | 897.35 | Critical | |
| 30570 | IN-2011-81826 | No | 11/7/2019 | 11/9/2019 | First Class | TS-21340 | Toby Swindell | Consumer | Porirua | Wellington | New Zealand | APAC | Oceania | FUR-CH-10004050 | Furniture | Chairs | Novimex Executive Leather Armchair | 1822.08 | 4 | 0 | 564.84 | 894.77 | Critical |
This table lists different people involved with the project and their respective regions.
| Person | Region |
|---|---|
| Anna Andreadi | Central |
| Chuck Magee | South |
| Kelly Williams | East |
| Matt Collister | West |
| Deborah Brumfield | Africa |
| Larry Hughes | AMEA |
| Nicole Hansen | Canada |
| Giulietta Dortch | Caribbean |
| Nora Preis | Central Asia |
| Jack Lebron | North |
| Shirley Daniels | North Asia |
| Anthony Jacobs | Oceania |
| Alejandro Ballentine | Southeast Asia |
| Siraj | North |
| Field | Description |
|---|---|
| Returned | Indicates if the product was returned (Yes/No) |
| Order ID | Unique identifier for each order |
| Market | The market in which the product was sold (e.g., LATAM, EU, APAC) |
| Field | Description |
|---|---|
| Row ID | Unique identifier for the row |
| Order ID | Unique identifier for each order |
| Returned | Indicates if the product was returned (Yes/No) |
| Order Date | Date when the order was placed |
| Ship Date | Date when the order was shipped |
| Ship Mode | Mode of shipment (e.g., Same Day, Second Class) |
| Customer ID | Unique identifier for the customer |
| Customer Name | Name of the customer |
| Segment | Customer segment (e.g., Consumer, Corporate) |
| City | City where the customer is located |
| State | State where the customer is located |
| Country | Country where the customer is located |
| Postal Code | Postal code of the customer’s location |
| Market | Market in which the customer belongs |
| Region | Region where the customer is located (e.g., North, APAC) |
| Product ID | Unique identifier for the product |
| Category | Category of the product (e.g., Technology, Furniture) |
| Sub-Category | Sub-category of the product (e.g., Phones, Chairs) |
| Product Name | Name of the product |
| Sales | Sales value for the product |
| Quantity | Number of units sold |
| Discount | Discount applied to the product |
| Profit | Profit made from the sale of the product |
| Shipping Cost | Shipping cost associated with the order |
| Order Priority | Priority level of the order (e.g., Critical) |
| Field | Description |
|---|---|
| Person | Name of the person involved in the project |
| Region | The region the person is associated with (e.g., Central, North, Africa) |
- Excel File: Contains the data and dashboard.
- Dataset: Orders, Returns, and People tables.
- README File: Documentation of the project.
This project involves analyzing sales orders, returned products, and personnel data across different regions and markets. By exploring this data, insights can be drawn about order trends, product performance, and the geographical distribution of sales.