Excel-sales-analysis

An end-to-end sales analysis project in Excel, turning raw 2014 sales data into an interactive dashboard and strategic business recommendations.

Kekilogus and Co. - 2014 Annual Sales Data

This repository contains a strategic analysis of the 2014 sales data for a wholesale distributor. The project transforms raw transactional data into an interactive dashboard and provides actionable recommendations to guide executive-level business decisions for the upcoming year.

---

Table of Contents

1. Project Objective
2. Dataset Details
3. Tools Used
4. Analysis Workflow
5. Key Insights
6. Visualizations
7. Recommendations
8. Limitation and Next Steps
9. Author

---

Project Objective

The primary objective was to analyze the complete 2014 sales data to identify key business drivers and provide data-driven strategic recommendations for the 2015 fiscal year. The goal was to provide clarity on top-performing products, customers, salespeople, and regions.

Dataset Details

The dataset is the internally logged company sales data for Kekilogus and Co. (2014). This private dataset represents a direct record of business operations and contains all sales transactions for the specified year.

• Structure: The data is a flat table where each row represents a single line item in a sales order.
• Key Features: OrderDate, CompanyName, Region, Salesperson, ProductCategory, and Revenue were essential for the analysis.
• Industry: Wholesale/Distribution of consumer packaged goods (B2B model).

Tools Used

• Microsoft Excel: Used for all stages of the project, from data cleaning to final visualization.
  • PivotTables & PivotCharts: The core engine for data aggregation, analysis, and visualization.
  • Slicers: Implemented to create a dynamic and interactive dashboard experience.
  • Excel Functions: Utilized SUBSTITUTE, VALUE, and TEXT functions for data cleaning and transformation.

Analysis Workflow

The project followed a structured analytical approach:

1. Hypothesis Formulation: Began by establishing guiding questions and initial hypotheses about potential sales trends, seasonality, and the application of the Pareto Principle (80/20 rule) to customers and products.
2. Data Cleaning & Preprocessing: The raw dataset was cleaned to ensure data integrity. This involved converting text-formatted numbers to a numeric format, standardizing date columns, and addressing incomplete rows.
3. Data Aggregation & Analysis: Used PivotTables to systematically test the hypotheses by summarizing data across multiple dimensions, including time, geography, product, customer, and salesperson.
4. Insight Synthesis: The quantitative findings from the analysis were translated into high-level business insights.

Key Insights

The analysis yielded three high-impact insights into the company's operational structure:

1. Extreme Business Concentration

The business is critically dependent on a few pillars. 86% of total revenue comes from just 10 customers. The "Beverages" category and the "North" region are the dominant product and market segments, respectively.

2. The "Superstar" Sales Effect

The sales team's performance is not evenly distributed. The top two salespeople are responsible for 45% of all company revenue, indicating a "superstar" effect where a few individuals drive a disproportionate amount of success.

3. Predictable Seasonality

The business cycle is not random. It follows a predictable seasonal pattern with a strong sales peak in Q4 (October-December), allowing for proactive inventory and marketing planning.

Visualizations

All findings were consolidated into a single, interactive dashboard to provide stakeholders with a comprehensive and easily digestible overview of the business performance.

The dashboard features:

• A Line Chart visualizing the monthly sales trend.
• Bar Charts comparing revenue by top customers, salespeople, and product categories.
• A Pie Chart showing the proportional revenue contribution from each sales region.
• Interactive Slicers to dynamically filter the entire dashboard by region, salesperson, and more.

Recommendations

Based on the findings, a dual-pronged strategy was recommended to ensure sustainable growth.

Strategy A: Guard the Core

Actively protect the assets that generate the most value.

1. Implement a Key Account Management Program: Provide elite service to the top 10 customers to ensure high retention.
2. Establish a Sales Excellence Program: Use top performers to train the entire sales team, cloning their successful strategies.

Strategy B: Expand & Diversify

Systematically reduce risk and build new revenue pillars.

1. Increase Average Order Value (AOV): Introduce product bundles and tiered discounts to encourage larger purchases.
2. Penetrate New Markets: Develop a targeted growth plan for the "West" region, which represents the largest untapped market opportunity.

Limitation and Next Steps

• Limitation: The analysis is based on a single year (2014), which prevents any year-over-year growth analysis. The data also lacks cost information, so the analysis is based on revenue, not profitability.
• Next Steps: Future work could include a profitability analysis by incorporating cost data, and a multi-year trend analysis to measure long-term growth and customer churn.

Author

• Obayemi Oluwafemi Emmanuel
• LinkedIn: https://www.linkedin.com/in/thefemology
• Medium: https://medium.com/@femology
• Email: femimi1234@gmail.com