SALES DATA – INCREASING SALES AND OPERATIONAL PERFORMANCE

GOAL

Get a clear understanding of the company and its sales to improve sales and operational performance.

Create a simple 2014 Sales Overview – with important information of sales performance in 2014, yearly trends and showing key points necessary of understanding where company stands in its sales.

OVERVIEW

FINDINGS AND RESULTS

RECOMMENDATIONS

• Make changes to the products that sell the lowest: see if they have been long underselling, see where the lack of profitability comes from. Drop products where necessary.
• Go over tables (products) under the Furniture -category: they have been non-profitable, negative, for for years straight. See where the lack of profitablity comes from and drop products where necessary.
• Time series forecasting: Q1 is the slowest order quarter – with 15.4% of orders coming in, Q3 and Q4 are the busiest – they are when the most orders come in. Weekends are the slowest for customers to make orders. Then orders distributes evenly between weekdays, with slightly more critical and high-status orders made from Monday to Wednesday. Plan inventory, resources, schedules, amount of employees accordingly.
• Order shipping: From order to shipment, especially among critical and high-priority orders, is slow. See why that is and change the necessary aspects.

SALES DASHBOARD

The company has been growing steadily each year. It has had an average of 24.7% yearly sales growth.

Determining if a growth rate is good or not, depends on different variables. As I don’t have any other company information on hand (growth strategies, industry standards for example), I am assuming a generic 10-25% annual growth rate to be used as variable to see if growth rate is good; which it is.

The Annual Growth % for each are:

Calculations: (period – previous period) / previous period * 100

Have there been differences or new patterns when it comes to what the sales numbers entail? No – in the ratios of quantity, discount or shipping costs. They have stayed close the same, with no large changes. Other expenses have been fluctuating more – with a positive trend of decrease.

MARKET AND REGIONS

The company operates globally in 5 Markets, in 13 regions.

The largest consumer markets are APAC and EU. The largest corporate markets are EU and APAC. The largest home office market is APAC.

The Markets and Regions follow the average of the whole company where approximately 50% of sales comes from consumers, 30% from corporate customers
and 20% from home office.

Expect Canada where consumer segment is slightly smaller and home office segment sligthly larger than average.

PRODUCTS

48 136 pieces of products were sold in 2014.

The top selling products, by profit and by category, in 2014 are:

The lowest selling products, by profit and by category, in 2014 are:

To create better sales performance, sligthly deeper analysis on the unprofitable and low-profitable products is recommended. To see how long the products have been lacking, what causes the low performance and decide if certain products will be dropped.

Yearly trends

The whole company’s profit margin has been growing until 2013 and then it has slightly dipped in 2014 from the 2013 value.

CUSTOMER SEGMENTATION

The company has 795 individual customers who all have been customers of the company for the whole analysis period, from years 2011 to 2014.

To understand what types of customers we have, we would look at the typical patterns and identifying factors they have in common.

In this example data set there was not as much information to analyse on regards the demographics or motivations of the customers, so customer segmentation and analysis was done based on purchase behaviour and on the types of products they bought.

This analysis was deliberately more higher level categorizing. When doing deeper customer grouping and finding what types of customers we have by their purchase behaviour and motivation, we would analyse deeper things such as purchase frequency, the patterns with what products they buy and when; and learning the patterns the customers display.

Even in this, the more noticable differences can be seen between the consumer, corporate and home office customers.

These can be taken into account when planning selling, marketing and operational aspects.

Consumers

Corporate

Home Office

PROFIT MARGIN

Gross profit margin: profit / revenue * 100

Low profit margin risks companies ability to respond (and lead) competition, sustain theirselves, migitate risks, maintain and create growth and investments.

A “good profit margin” depends/varies within industries. Benchmarking number I have used in this analysis for gross profit is 30% – as this company is a global technology, furniture and office supply retailer. The information of the company consists only of these order id’s. They tell us the company is in retail – technology, furniture and office supplies and it operates globally. However, no other information was available to create the most accurate comparison value.

Customers

The profit margin in consumer segment in total is:
19.13%

Corporate

The profit margin in corporate segment in total is:
17.57%

Home office

The profit margin in home office segment in total is:
20.24%

Technology is in all segments highest-margin product category. Its profit margin is over 24% in all the segments. I recommend, if it fits company’s overall strategy and responds to customers’ needs, to find outlets and ways to increase the sales of products in Technology-category.

The Furniture-category raised concerns – especially within Corporate and Home Office segments, as the profit margin was drastically smaller than the average.
I looked deeper, what the issue was and found out that the problem mostly lies in the Tables-subcategory that has been unprofitable, negative, for four years straigth. This needs to be addressed in order to increase profit margins and cash in the whole company.

CUSTOMER LIFETIME VALUE

CLV not possible to determine as spefic metrics would need discussion and company or industry-specific knowledge to be able to be determined.

TIME SERIES FORECASTING – ORDER FREQUENCY

To see how the company can increase its operational performance, I checked how orders manifest in quarters, months and days: to see the patterns that help the company to plan resources.

With the yearly growth rate, sales estimate and order distribution knowledge we can estimate and plan effectively how to distribute our resources so we can meet these demands.

Most of the orders come in Q4 – 33.41%. The Q1 is the least active, with 15.21% of orders coming through. Q2 has contained 23.66% of orders and Q3 27.72% of orders.

The different product segments follow this same frequency.

MONTH

The busiest months are November and December with 12.36% and 12.29% of years orders coming in average then. The least busy months are January and February.

WEEK

To be able to plan better, more efficiently, inventory and employee schedules and such, I wanted to see how usually, on average, orders with
different priorities are placed so the company can plan accordingly – schedule employees when there is the most orders , especially critical and high ones, coming in.

Sundays and Satudays are the days when orders coming in is low.

Overall, the weekend is slower when it comes to orders, and then the orders are evenly divided between the weekdays.

On Mondays and Wednesdays there is slightly more critical orders coming in. And on Tuesdays more high-priority orders.

ORDER PROCESSING TIME

How fast items arrive to customers, play a significant role in customer satisfaction – which in turn can increase customer loyalty and the possibility of them making new purchases.
Thus, looking at how fast orders are sent out, gives us the ability to see and control better that part of our customers experience.

The most common time from the company to send out the order that was made, is 4 days.

I wanted to see, how fast orders were sent out when it came to each different order priority – to pinpoint potential problem areas.

If orders were marked critical, they were
sent out within 0-3 days

If orders were marked high, they were
sent out within 0-5 days

If orders were marked medium, they were
sent out within 0-7 days

If order were marked low, they were
sent out within 6-7 days

42.15% of orders marked critical were sent out 2 days after the order was placed.

I recommend investigating this – see what is causing this as it has a large effect on customer satisfaction and customer retention.
People buy more often from companies who deliver and send out their orders fast.

BEHIND THE SCENES

Going through the process, my thinking and reasoning:

When I have sales data on my hands, there are few things I want to know as an business development focused data-analyst and an entrepreneur.
I want to know my sales; where they come from, who contributes to them, what are they like, how frequently our customers or clients buy, why they buy, are
they one time clients or do they return. I want to know how much they buy and their patterns and trends around sales and what actions
on our businesses part contributes to sales results and profits.
I also always want to know the profit and costs associated to sales and what the most and least profitable products and how products or services all in all
distribute between profit categories, obviously recognising that some products that are not as profitable might work as “invitation products”.

This sales data is example data “Superstore” (link at the end of the page). This is not real-life sensitive information data.
The data is in Excel-file and I also transported it into MySQL to run SQL scripts.

So, what I did:

First I looked at the data to get an overall view and feel of it. What information is there, what values are there.
When working with clients, this part is done in communication with you as it is necessary for me to know and understand how the data is collected,
what exactly the values collected represent and if there are something missing so I can analyse and come up with comprehensive view of the whole process – plus being able to recommend you ways to optimize it.

Then I cleaned the data in the Excel.

I:

• Formatted column headers
• Removed unnecessary spaces using the TRIM-function.
• Eliminated extra spaces between words using Find & Replace.
• Checked for duplicate order numbers using Conditional Formatting and sorted the data to group orders with the same orderID.
• Standardized date formatting and converted it to the correct date cell format using Find & Replace.
• Removed extraneous characters in the values in State-column using the RIGHT-function.
• Clarified unclear data entries, such as fixing Hanoi’s unclear format.
• Ensured there are no empty cells.

After, I transferred the data to MySQL, where I had created a new database for it. And ran SQL scripts. (Link to GitHub showing the SQL at the end of the page).

Then I started with the Sales Dashboard and analysing the data.

Some of the processes I did in Excel are:
Added a column for the time from order to shipment, calculating days with the DATEIF function.
Different Pivot-tables to extract findings.
Extracted the Day of the Week using the TEXT-function.
Created a Pivot-table for Average Order Processing Time.
Added new columns for Months and Quarters for visualizing Order Frequency trends.
Did Customer Segmentation based on buying patterns and product categories.
Time Series Forecasting using historical data for future order volumes.

LINKS

SQL Scripts

GitHub: https://github.com/stellarparadigm/stellarprodata/blob/main/sql/sales-project2

The Data Set

Kaggle: https://www.kaggle.com/datasets/aditisaxena20/superstore-sales-dataset