Sales Analytics: Unveiling Insights for Strategic Sales Optimization Using Excel

Introduction

In this comprehensive analysis, we delve into the intricate fabric of sales data to reveal valuable insights that will revolutionize Almert’s approach to strategic sales optimization. By meticulously dissecting the annual sales dataset, we aim to uncover hidden trends, patterns, and actionable insights that will empower Almert to make informed decisions, drive growth, and stay ahead of the competition.

Statement of Business Task

To identify trends, patterns and insights that can be used to optimize the company’s sales and marketing strategies.

Data Source and Tool used

The dataset was gotten from kaggle, here is a link to the dataset: https://www.kaggle.com/datasets/renaldi27/annual-sales-almertshop?rvi=1 and it contains 18 columns and 113,037 rows. A snap shot is shown below.

I used Excel to carry out every stage in the data analysis process i.e data cleaning, data transformation and visualization.

Data Cleaning and Transformation

Data cleaning and transformation are an important aspect in data analysis process as it ensures accuracy and credibility of the insights generated from the data. The dataset is loaded into Excel for the data cleaning and transformation process.

For this stage, the following steps were undertaken:

• I ensured that all the columns are encoded in the correct format, the “unit cost”, unit price, “price“, “cost” and “revenue” columns were set to accounting column type and the dollar monetary sign was added to them.
• Then, I corrected a grammatical error in the country column. I replaced “Rusia” with “Russia”.

After these steps, I was satisfied with the quality of my dataset then I proceeded to the next stage.

Pivot Tables

Pivot tables are one of Excel's most powerful features. We use them in summarizing the data stored in a table. They organize and rearrange statistics (or "pivot") to draw attention to the valuable facts. You can take an extremely large data set and see the relevant information you need in a clean, concise, manageable way.

To create my pivot tables, I performed the following steps:

• I created the first pivot table to group each countries according the total profit made each year. Then I created a second pivot table that shows the cost of unit production in every country in each year.

- I also made pivot tables that grouped total revenue and total product quantity ordered in each month. There is another pivot table which shows the overall revenue in each country.

- Finally, I created pivot tables that grouped the product categories by their number of customers and I also grouped the customers by their gender.

Visualizations & Dashboarding

Dashboarding is a fundamental means of displaying key business information to measure progress against goals, for any aspect of any organization or business, all in a highly visual manner. It allows the user to skip the process of digging through raw data or even curated reports for the answers they need, and instead gain that insight quickly and clearly so they can focus on taking action in time to make a difference.

From each pivot table, I created bar charts, KPIs, Slicers and pie chart to show patterns, trends and insights that can be gotten from the dataset.

Here is an image of the final dashboard:

Recommendations

Based on the analysis of Almert’s annual sales data. Here are my recommendations on how the company can optimize its operation and increase profitability:

• Almert Shop should capitalize on the increased revenue during May and June by analyzing the factors that contribute to this growth and replicating those strategies throughout the year. This could include targeted marketing campaigns, seasonal promotions, or exclusive product offerings.
• As Russia is the most revenue-generating region, Almert Shop should allocate more resources and marketing efforts to further penetrate and expand in this market. This could involve localizing marketing materials, partnering with local influencers, or tailoring product offerings to cater to Russian consumer preferences.
• Almert Shop should review the cost structure in Thailand, where the unit cost of producing products is the highest. Exploring options such as optimizing the supply chain, sourcing materials from alternative vendors, or negotiating better terms with existing suppliers could help reduce production costs and improve profitability.
• To maximize revenue from the customer group between the ages of 35 and 64, Almert Shop should develop targeted marketing campaigns and personalized offerings that cater to their preferences. This could include loyalty programs, exclusive discounts, or tailored product recommendations based on their browsing and purchase history.
• To balance the customer gender distribution, Almert Shop should analyze customer preferences, conduct market research, and tailor marketing campaigns to attract and engage more female customers. This could include showcasing products that appeal to female customers, collaborating with female influencers or creating targeted advertising campaigns.