- Sales_Data_.xlsx (129.279 KB)
You’ve got gotten been employed as a marketing analyst by a with out notice rising mid-sized retail company that has accumulated a critical amount of gross sales knowledge. No matter their development, the corporate is struggling to extract actionable insights due to the fragmented and disorganized reporting processes. The management team is desirous to title the principle drivers of profit, sight high-performing products and customers, and optimize gross sales options in some unspecified time in the future of varied states and market segments. Nonetheless, their capacity to manufacture knowledgeable, knowledge-driven choices is currently hindered by the absence of a comprehensive, user-pleasant dashboard.Your assignment is to make a comprehensive gross sales dashboard that consolidates all the corporate’s gross sales knowledge true into a single, interactive platform. This dashboard ought to aloof empower the corporate’s management and gross sales groups to mercurial assess key performance metrics, title traits, and fabricate knowledgeable choices that can power extra efficient and strategic trade actions.Design: The dashboard ought to aloof visually say the gross sales knowledge supplied, modeled after the instance dashboard confirmed within the supplied video. The key metrics and visualizations ought to aloof encompass:
- Total Sales
- Total Earnings
- Earnings Proportion
- Units Sold
- Top Selling Product
- Sales and Earnings by Month
- Sales Generated by Customer
- Sales by States
- Sales Breakup by Section
You prefer to train Excel to maintain the dashboard.Make sure your submitted recordsdata encompass a sheet demonstrating the thoroughness of your evaluation.Any dashboard generated the usage of AI will seemingly be marked zero.Files supplied contains the following columns:
- Section: The market section where the product used to be supplied (e.g., Franchise, Retail, Wholesale, On-line).
- Customer_ID: Odd identifier for purchasers.
- Product_ID: Odd identifier for products.
- Cut value Band: The crop value stage utilized (e.g., Low, Medium).
- Units Sold: The desire of devices supplied.
- Manufacturing Label: The cost to manufacture the product.
- Sale Label: The selling value of the product.
- Reductions: Total reductions given.
- Date: The date of the sale.
- Customer_Name: The title of the client.
- Product_Name: The title of the product.
- Issue: The protest where the sale happened.
- Imperfect Sales: Total gross sales sooner than deductions.
- Sales: Total gross sales after deductions.
- COGS: Label of Goods Sold.
- Earnings: Earnings generated from the gross sales.
- Earnings ('000): Earnings in thousands.
- Month: The month of the sale.
- Quarter: The quarter of the sale.
- Yr: The 300 and sixty five days of the sale.
Steps:
- Files Cleaning and Preparation:
- Make certain knowledge consistency and accuracy.
- Sort out lacking values and accurate any knowledge inconsistencies.
- Gain additional columns if critical (e.g., Month-to-month Sales, Yearly Earnings, and so forth.).
- Visualizations:
- Total Sales & Earnings: Display total gross sales and total profit.
- Earnings Proportion: Calculate and show the profit share.
- Units Sold: Display total devices supplied.
- Top Selling Product: Name and show the tip-promoting product.
- Sales & Earnings by Month: Construct a bar chart exhibiting gross sales and profit for every month.
- Sales Generated by Customers: List customers and their corresponding gross sales.
- Sales by States: Visualize gross sales distribution in some unspecified time in the future of varied states.
- Sales Breakup by Section: Pie chart exhibiting the percentage of gross sales by varied market segments.
- Dashboard Impact:
- Impact a dashboard that is evident, visually appealing, and simple to clarify.
- Use appropriate chart kinds, colors, and labels to enhance readability.
- Evaluation and Insights:
- Analyze the records and provide insights per the visualizations.
- Name traits, patterns, and any principal observations from the records.
- Presentation:
- Put together a short document or presentation summarizing the findings.
- Spotlight key metrics and insights from the dashboard.
- Encompass a 20 – 25 minute presentation video where you fade thru the workings of the dashboard and a short summary of the evaluation. The presentation ought to aloof duvet:
- Overview of the Dashboard: Introduce the critical metrics and visualizations integrated.
- Files Sources and Preparation: Display the records cleaning and preparation steps and highlight key aspects of your evaluation.
- Detailed Walkthrough: Warfare thru every visualization, explaining what it presentations and why it is required.
- Key Insights and Observations: Summarize the principle findings and insights from the records.
- Technical Parts: Temporarily focus on any technical challenges or attention-grabbing options veteran in developing the dashboard.
Submit the Excel file containing the final dashboard, alongside with a sheet demonstrating the thoroughness of your evaluation. Encompass a document or presentation with your evaluation and insights. Additionally, post the recorded presentation video file.
-
Sales_Data_.xlsx
Sheet1
Section | Customer_ID | Product_ID | Cut value Band | Units Sold | Manufacturing Label | Sale Label | Reductions | Date | Customer_Name | Product_Name | Issue | Imperfect Sales | Sales | COGS | Earnings | Earnings ('000) | Month | Quarter | Yr |
Retail | CUST_US_002 | PROD_US_002 | Medium | 1575 | 40.14 | 168.3 | 339.21 | 2021-06-01 00:00:00 | Grace | Doodad | Original York | 265072.5 | 264733.29 | 63220.5 | 201512.79 | 201.513 | Jun | Q2 | 2021 |
Retail | CUST_US_003 | PROD_US_003 | Low | 835 | 40.17 | 136.17 | 87.35 | 2023-04-01 00:00:00 | Eve | Widget | Original York | 113701.95 | 113614.6 | 33541.95 | 80072.65 | 80.073 | Apr | Q2 | 2023 |
Wholesale | CUST_US_004 | PROD_US_003 | Low | 1989 | 18.68 | 190.42 | 724 | 2021-09-01 00:00:00 | Ivy | Widget | Texas | 378745.38 | 378021.38 | 37154.52 | 340866.86 | 340.867 | Sep | Q3 | 2021 |
On-line | CUST_US_005 | PROD_US_004 | Medium | 1654 | 22.7 | 181.16 | 351.21 | 2024-07-01 00:00:00 | Frank | Gizmo | Illinois | 299638.64 | 299287.43 | 37545.8 | 261741.63 | 261.742 | Jul | Q3 | 2024 |
Wholesale | CUST_US_006 | PROD_US_001 | Low | 211 | 30.84 | 198.93 | 73.38 | 2021-02-01 00:00:00 | Charlie | Thingamajig | Florida | 41974.23 | 41900.85 | 6507.24 | 35393.61 | 35.394 | Feb | Q1 | 2021 |
Franchise | CUST_US_007 | PROD_US_001 | Low | 1007 | 26.09 | 166.05 | 257.98 | 2024-05-01 00:00:00 | David | Thingamajig | Illinois | 167212.35 | 166954.37 | 26272.63 | 140681.74 | 140.682 | Could perchance additionally simply | Q2 | 2024 |
Franchise | CUST_US_008 | PROD_US_003 | Low | 1932 | 19.82 | 170.42 | 754.51 | 2023-10-01 00:00:00 | Alice | Widget | Original York | 329251.44 | 328496.93 | 38292.24 | 290204.69 | 290.205 | Oct | Q4 | 2023 |
Wholesale | CUST_US_009 | PROD_US_002 | None | 1348 | 47.6 | 152.36 | 221.fifty three | 2021-06-01 00:00:00 | Grace | Doodad | California | 205381.28 | 205159.75 | 64164.8 | 140994.95 | 140.995 | Jun | Q2 | 2021 |
Franchise | CUST_US_010 | PROD_US_004 | Medium | 527 | 33.26 | 40.36 | 781.59 | 2024-06-01 00:00:00 | Frank | Gizmo | Original York | 21269.72 | 20488.13 | 17528.02 | 2960.11 | 2.96 | Jun | Q2 | 2024 |
Train Sales | CUST_US_011 | PROD_US_001 | Medium | 1088 | 9.7 | 194.09 | 308.34 | 2024-08-01 00:00:00 | Charlie | Thingamajig | Original York | 211169.92 | 210861.58 | 10553.6 | 200307.98 | 200.308 | Aug | Q3 | 2024 |
Franchise | CUST_US_012 | PROD_US_002 | Medium | 160 | 36.5 | 84.58 | 638.83 | 2023-02-01 00:00:00 | Hank | Doodad | Illinois | 13532.8 | 12893.97 | 5840 | 7053.97 | 7.054 | Feb | Q1 | 2023 |
On-line | CUST_US_013 | PROD_US_001 | Low | 1419 | 40.12 | 168.42 | 106.07 | 2024-03-01 00:00:00 | Bob | Thingamajig | California | 238987.98 | 238881.91 | 56930.28 | 181951.63 | 181.952 | Mar | Q1 | 2024 |
Train Sales | CUST_US_014 | PROD_US_004 | High | 199 | 30.4 | 59.48 | 340.27 | 2021-07-01 00:00:00 | Ivy | Gizmo | Original York | 11836.52 | 11496.25 | 6049.6 | 5446.65 | 5.447 | Jul | Q3 | 2021 |
Retail | CUST_US_015 | PROD_US_003 | High | 324 | 32.09 | 64.14 | 240.14 | 2023-01-01 00:00:00 | Bob | Widget | Illinois | 20781.36 | 20541.22 | 10397.16 | 10144.06 | 10.144 | Jan | Q1 | 2023 |
Wholesale | CUST_US_016 | PROD_US_002 | High | 1995 | 7.97 | 48.85 | 206.68 | 2024-06-01 00:00:00 | Grace | Doodad | Florida | 97455.75 | 97249.07 | 15900.15 | 81348.92 | 81.349 | Jun | Q2 | 2024 |
Wholesale | CUST_US_017 | PROD_US_002 | None | 1366 | 16.46 | 157.52 | 967.24 | 2023-07-01 00:00:00 | Charlie | Doodad | Texas | 215172.32 | 214205.08 | 22484.36 | 191720.72 | 191.721 | Jul | Q3 | 2023 |
Train Sales | CUST_US_018 | PROD_US_004 | High | 891 | 11.66 | 143.22 | 220.03 | 2024-11-01 00:00:00 | Bob | Gizmo | Original York | 127609.02 | 127388.Ninety nine | 10389.06 | 116999.93 | 117 | Nov | Q4 | 2024 |
Retail | CUST_US_019 | PROD_US_003 | Medium | 303 | 23.52 | 87.01 | 276.7 | 2024-10-01 00:00:00 | David | Widget | Texas | 26364.03 | 26087.33 | 7126.56 | 18960.77 | 18.961 | Oct | Q4 | 2024 |
Train Sales | CUST_US_020 | PROD_US_003 | High | 1596 | 20.95 | 79.06 | 78.35 | 2024-06-01 00:00:00 | Grace | Widget | Illinois | 126179.76 | 126101.41 | 33436.2 | 92665.21 | 92.665 | Jun | Q2 | 2024 |
Franchise | CUST_US_021 | PROD_US_004 | None | 1670 | 10.94 | 26.05 | 201.55 | 2021-03-01 00:00:00 | David | Gizmo | Original York | 43503.5 | 43301.95 | 18269.8 | 25032.15 | 25.032 | Mar | Q1 | 2021 |
Wholesale | CUST_US_022 | PROD_US_004 | Medium | 942 | 15.89 | 169.63 | 356.33 | 2023-03-01 00:00:00 | Charlie | Gizmo | Illinois | 159791.46 | 159435.13 | 14968.38 | 144466.75 | 144.467 | Mar | Q1 | 2023 |
Train Sales | CUST_US_023 | PROD_US_003 | Low | 1173 | 10.31 | 89.38 | 479.59 | 2021-08-01 00:00:00 | Eve | Widget | California | 104842.74 | 104363.15 | 12093.63 | 92269.52 | 92.27 | Aug | Q3 | 2021 |
Retail | CUST_US_024 | PROD_US_002 | Low | 1144 | 47.15 | 35.5 | 163.73 | 2021-05-01 00:00:00 | Frank | Doodad | Original York | 40612 | 40448.27 | 53939.6 | -13491.33 | -13.491 | Could perchance additionally simply | Q2 | 2021 |
Franchise | CUST_US_025 | PROD_US_001 | Medium | 1199 | 43.89 | 34.94 | 793.77 | 2021-02-01 00:00:00 | Bob | Thingamajig | Texas | 41893.06 | 41099.29 | 52624.11 | -11524.82 | -11.525 | Feb | Q1 | 2021 |
On-line | CUST_US_026 | PROD_US_001 | High | 1105 | 16.17 | 64.96 | 753.37 | 2023-04-01 00:00:00 | Alice | Thingamajig | California | 71780.8 | 71027.43 | 17867.85 | 53159.58 | fifty three.16 | Apr | Q2 | 2023 |
On-line | CUST_US_027 | PROD_US_002 | Low | 986 | 40.03 | 34.03 | 378.fifty three | 2024-08-01 00:00:00 | Grace | Doodad | Florida | 33553.58 | 33175.05 | 39469.58 | -6294.fifty three | -6.295 | Aug | Q3 | 2024 |
Wholesale | CUST_US_028 | PROD_US_004 | None | 501 | 29.66 | 71.12 | 155.63 | 2021-10-01 00:00:00 | Hank | Gizmo | Florida | 35631.12 | 35475.49 | 14859.66 | 20615.83 | 20.616 | Oct | Q4 | 2021 |
Franchise | CUST_US_029 | PROD_US_003 | Low | 743 | 6.3 | 89.06 | 876.21 | 2021-11-01 00:00:00 | Eve | Widget | Florida | 66171.58 | 65295.37 | 4680.9 | 60614.47 | 60.614 | Nov | Q4 | 2021 |
Franchise | CUST_US_030 | PROD_US_002 | High | 1467 | 32.52 | 151.3 | 937.35 | 2024-05-01 00:00:00 | Ivy | Doodad | Florida | 221957.1 | 221019.75 | 47706.84 | 173312.91 | 173.313 | Could perchance additionally simply | Q2 | 2024 |
Train Sales | CUST_US_031 | PROD_US_001 | Low | 1505 | 29.77 | 120.56 | 226.54 | 2021-01-01 00:00:00 | Charlie | Thingamajig | Original York | 181442.8 | 181216.26 | 44803.85 | 136412.41 | 136.412 | Jan | Q1 | 2021 |
Franchise | CUST_US_032 | PROD_US_001 | Medium | 807 | 35.42 | 112.44 | 498.71 | 2021-03-01 00:00:00 | Alice | Thingamajig | Illinois | 90739.08 | 90240.37 | 28583.94 | 61656.43 | 61.656 | Mar | Q1 | 2021 |
Retail | CUST_US_033 | PROD_US_002 | None | 1632 | 14.69 | 47.81 | 509.66 | 2021-07-01 00:00:00 | Charlie | Doodad | California | 78025.92 | 77516.26 | 23974.08 | 53542.18 | fifty three.542 | Jul | Q3 | 2021 |
Franchise | CUST_US_034 | PROD_US_004 | Low | 872 | 44.67 | 84.02 | 380.94 | 2023-02-01 00:00:00 | Hank | Gizmo | Florida | 73265.44 | 72884.5 | 38952.24 | 33932.26 | 33.932 | Feb | Q1 | 2023 |
Train Sales | CUST_US_035 | PROD_US_004 | None | 843 | 36.72 | 185.38 | 343.64 | 2024-05-01 00:00:00 | Bob | Gizmo | Illinois | 156275.34 | 155931.7 | 30954.96 | 124976.74 | 124.977 | Could perchance additionally simply | Q2 | 2024 |
Train Sales | CUST_US_036 | PROD_US_004 | Medium | 1011 | 30.54 | 28.96 | 232.55 | 2024-02-01 00:00:00 | Ivy | Gizmo | Original York | 29278.56 | 29046.01 | 30875.94 | -1829.93 | -1.83 | Feb | Q1 | 2024 |
Train Sales | CUST_US_037 | PROD_US_004 | High | 781 | 9.75 | 48.89 | 981.75 | 2024-04-01 00:00:00 | Frank | Gizmo |