Project Title: Unraveling Supply Chain using Excel

Overview

This project delves into the convergence of supply chain operations and digital consumer behavior analysis. By integrating two distinct datasets – a supply chain dataset offering insights into sales, logistics, and delivery performance, alongside an access logs dataset detailing online consumer interactions and preferences – the aim is to discern the relationship between customer engagement, online views, and sales. Employing Excel, this project builds a comprehensive dashboard to identify top-selling product categories and most-viewed products, thereby enabling strategic decision-making in customer relationship management and product marketing.

Two Excel Dataset:

1. DataCoSupplyChainDataset.csv: This dataset likely contains information related to supply chain metrics such as sales data, logistics performance, delivery metrics, inventory levels, etc. Analyzing this dataset can provide insights into the efficiency and effectiveness of the supply chain processes.
2. TokenizedAccessLogs.csv: This dataset probably comprises digital access logs, which track consumer online interactions and preferences. It may include data on website visits, click-through rates, time spent on pages, product views, purchases, etc. Analyzing this dataset can help understand consumer behavior patterns and preferences in the digital space.

Original dataset and .csv file download:

1. DataCoSupplyChainDataset.csv: Download Link
2. TokenizedAccessLogs.csv: Download Link

Project Structure

1. Data Preprocessing: Cleaning and merging the two datasets to create a unified dataset for analysis.

2. Dashboard Design: Leveraging Excel's functionalities, designing a user-friendly dashboard featuring diverse visualizations like bar charts, line graphs, and pivot tables to present key insights effectively.

3. KPI Calculation: Calculating Key Performance Indicators (KPIs) including Total Orders, Total Cost, Total Sales, Total Customers, Total Profits, and Profit Margin using appropriate Excel formulas and functions.

4. Slicer Creation: Implementing slicers to enable dynamic filtering of data based on order date, market continent, delivery status, and type of amount, facilitating interactive analysis.

5. Correlation Analysis: Comparing sales data with online views to identify correlations and patterns, employing techniques such as scatter plots and correlation coefficients.

6. Insights Generation: Conducting in-depth analysis to identify top-selling product categories and the most-viewed products, furnishing actionable insights for strategic decision-making.

Results

The results underscore the significance of embracing the dashboard for strategic decision-making in customer relationship management and product marketing. By capitalizing on insights gleaned from the dashboard, management can judiciously allocate resources and prioritize the marketing of products garnering higher online views and positive reviews.

How to Use

1. Data Preprocessing: Ensure both datasets are formatted correctly and contain necessary fields for merging.

2. Dashboard Design: Open the Excel file containing the dashboard, navigate through various sheets showcasing different visualizations, and interact with slicers for dynamic filtering.

3. KPI Calculation: Review the formulas and functions used in Excel to calculate KPIs, ensuring data integrity and accuracy.

4. Correlation Analysis: Explore scatter plots and correlation coefficients to discern relationships between sales data and online views.

5. Insights Generation: Delve into the insights generated from the analysis, identifying top-selling product categories and most-viewed products for strategic decision-making.

Conclusion

The project underscores the pivotal role of data-driven insights in navigating the complexities of supply chain operations and digital consumer behavior. By harnessing the power of Excel and integrating disparate datasets, this project illuminates actionable insights that can drive strategic decision-making and enhance organizational performance.

Links to my two Excel workbooks:

For cleaned and modified data of Resort datasheet (no Pivot and no visualization): Resort Unraveling Supply Chain RAW Sheet

For data analysis, pivot tables and spreadsheet visualization: Resort Data Analysis and Visualization Unraveling Supply Chain