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

This is an example project where I have used example data (not real life) to analyse the imaginery company’s sales information and performance.

The information was in Excel-file where I did my analysis. The data was product purchase information with order ID’s, order dates, shipping mode, order priority, customer information, regional and country information, sales, profit, quantity, discount and shipping cost information.

How many rows: 51 290

Orders from: 2011 – 2015

My intentions were:

  • get a clear overall view of the company and its sales’ situation
  • get insight on customer behaviour. To get recommendations, among other things, for marketing, product offerings, sales and customer behaviours
  • get insight on sales performance. To know where are the areas for improvement and know what to suggest to increase sales results
  • get other insights for the company’s benefit

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 is profitable. It made 2,67 Million euros in sales in 2014 and 504 165,97€ in profit. Company sold 48 136 pieces of products in 2014. The profit margin was 18,88%.


Sales came in 50.44% from consumers, contributing 51.11% to profits.

29.89% from corporate customers, contributing 27,81% to profits

– and 19.66% from home office, contributing 21,08% to profits.

The company sells:

FURNITURE – 30.20% of sales, which include

  • bookcases
  • chairs
  • furnishing
  • tables

Its profit margin is: 11.08%

TECHNOLOGY – 34,36% of sales, which include

  • accessories
  • copiers
  • machines
  • phones

Its profit margin is: 25.60%

OFFICE SUPPLIES – 35.44% of sales, which include

  • appliances
  • art
  • binders
  • envelopes
  • fasteners
  • labels
  • paper
  • storage
  • supplies

Its profit margin is: 19.01%

TRENDS

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

201220132014
SALES22,01 %25,31 %26,65 %
PROFIT23,49 %32,89 %23,41 %
QUANTITY21,21 %26,30 %25,94 %

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.

2011201220132014
Quantity / Sales – Ratio2,28 %2,27 %2,28 %2,27 %
Discount / Sales – Ratio0,10 %0,09 %0,09 %0,09 %
Shipping costs / Sales – Ratio17,71 %16,85 %17,29 %17,25 %
Other expenses / Sales – Ratio64,14 %64,79 %63,24 %61,51 %

MARKET AND REGIONS

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

How much different markets contribute to sales, 2014

This is how the sales distribute in the markets:

CONSUMERCORPORATEHOME OFFICE
Africa9,88 %8,69 %8,51 %
APAC21,70 %20,78 %22,87 %
Canada0,64 %0,73 %0,95 %
EMEA10,27 %10,19 %10,70 %
EU20,12 %21,35 %18,23 %
LATAM18,66 %19,42 %19,12 %
US18,72 %18,84 %19,62 %
TOTAL100,00 % *100,00 %100,00 %
* of all sales

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.

MARKETCONSUMERSCORPORATEHOME OFFICE
Africa54,37 %27,89 %17,73 %
APAC51,08 %28,53 %20,39 %
Canada44,88 %29,92 %25,20 %
EMEA50,69 %29,32 %19,99 %
EU50,96 %31,55 %17,49 %
LATAM50,12 %30,43 %19,45 %
US50,41 %29,59 %20,01 %

PRODUCTS

48 136 pieces of products were sold in 2014.

The top selling products, by profit and by category, in 2014 are:
CONSUMERS
Product
Canon imageCLASS 2200 Advanced Copier
Cisco Smart Phone, Full Size
Sauder Classic Bookcase, Metal
Brother Fax Machine, Laser
Bush Classic Bookcase, Pine
GBC DocuBind P400 Electric Binding System
Sharp Wireless Fax, Color
SAFCO Executive Leather Armchair, Black
Apple Smart Phone, Cordless
Dania Classic Bookcase, Traditional
CORPORATE
Product
Hoover Stove, Red
Sauder Classic Bookcase, Traditional
Canon Wireless Fax, Laser
Hewlett Packard LaserJet 3310 Copier
Hewlett Wireless Fax, High-Speed
Cisco Smart Phone, Full Size
Apple Smart Phone, Cordless
Motorola Smart Phone, Full Size
Fellowes PB500 Electric Punch Plastic Comb Binding Machine with Manual Bind
Cisco Smart Phone, with Caller ID
HOME OFFICE
Canon imageCLASS 2200 Advanced Copier
Motorola Smart Phone, Full Size
Breville Refrigerator, White
Hewlett Copy Machine, Color
Cuisinart Stove, Red
Hewlett-Packard Desktjet 6988DT Refurbished Printer
Rogers Lockers, Single Width
Motorola Smart Phone, with Caller ID
Canon Image Class D660 Copier
Okidata Inkjet, Wireless
The lowest selling products, by profit and by category, in 2014 are:
CONSUMERS
Product
Cubify CubeX 3D Printer Double Head Print
Bevis Round Table, Adjustable Height
Lesro Training Table, Rectangular
Chromcraft Conference Table, with Bottom Storage
Lesro Round Table, Adjustable Height
Bush Library with Doors, Pine
GBC DocuBind P400 Electric Binding System
Hon Conference Table, Rectangular
Hoover Stove, Silver
Chromcraft Bull-Nose Wood Oval Conference Tables & Bases
CORPORATE
Product
Motorola Smart Phone, Cordless
Cubify CubeX 3D Printer Triple Head Print
Ibico EPK-21 Electric Binding System
Barricks Conference Table, Rectangular
Hoover Stove, White
Chromcraft Coffee Table, Fully Assembled
Nokia Smart Phone, Full Size
Apple Smart Phone, with Caller ID
Lesro Wood Table, Adjustable Height
Hon Executive Leather Armchair, Black
HOME OFFICE
Product
Lexmark MX611dhe Monochrome Laser Printer
Apple Smart Phone, Full Size
Bevis Conference Table, Fully Assembled
Barricks Conference Table, Adjustable Height
Cisco TelePresence System EX90 Videoconferencing Unit
Breville Microwave, Silver
Sharp Fax Machine, Digital
Office Star Swivel Stool, Adjustable
Sauder Classic Bookcase, Metal
Barricks Computer Table, Fully Assembled


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

2011201220132014
Profit Margin18,05 %18,27 %19,38 %18,88 %

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.

WHAT PRODUCTS THEY BUY
Furniture30,20 %
Office Supplies35,44 %
Technology34,36 %
WHAT CUSTOMER SEGMENT THEY ARE
Consumer51,70 %
Corporate30,08 %
Home Office18,22 %

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

Average amount of different products in one order2,0
Average amount of products in total in one order7,0
Average amount of profit in one order57,19 €
Average amount of sales in one order309,87 €

Corporate

Average amount of different products in one order4,0
Average amount of products in total in one order14,0
Average amount of profit in one order115,40 €
Average amount of sales in one order617,86 €

Home Office

Average amount of different products in one order4,0
Average amount of products in total in one order13,9
Average amount of profit in one order118,18 €
Average amount of sales in one order600,96 €

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%

Furniture:29.16% of consumer sales22.15% of segment profit14.53% profit margin
Office Supplies:36.12% of consumer sales33.23% of segment profit17.60% profit margin
Technology:34.71% of consumer sales44.63% of segment profit24.59% profit margin

Corporate

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

Furniture:31.82% of corporate sales10.76% of segment profit5.94% profit margin
Office Supplies:34.91% of corporate sales42.90% of segment profit21.59% profit margin
Technology:33.27% of corporate sales46.35% of segment profit24.47% profit margin

Home office

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

Furniture:30.37% of home office sales16.14% of segment profit10.76% profit margin
Office Supplies:34.50% of home office sales32.15% of segment profit18.86% profit margin
Technology:35.13% of home office sales51.71% of segment profit29.80% profit margin

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.

CATEGORYSUBCATEGORY2011201220132014TOTAL
FurnitureBookcases27 518,86 €28 137,27 €43 049,24 €63 219,05 €161 924,42 €
Chairs29 943,16 €28 755,35 €42 027,02 €41 248,27 €141 973,80 €
Furnishings7 310,21 €9 661,92 €14 604,65 €15 390,65 €46 967,43 €
Tables-11 075,29 €-8 421,70 €-14 040,49 €-30 545,91 €-64 083,39 €

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.

MONTH% ORDERS
jan5,07 %
feb4,23 %
march5,92 %
april5,96 %
may7,31 %
june10,39 %
july6,17 %
aug9,90 %
sep11,65 %
oct8,75 %
nov12,36 %
dec12,29 %
TOTAL100,00 %

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.

DAYCRITICALHIGHMEDIUMLOW
Mon18,95 %17,15 %18,24 %16,71 %
Tue17,12 %19,28 %17,78 %17,08 %
Wed19,18 %17,64 %17,48 %15,84 %
Thur17,09 %16,97 %17,37 %18,81 %
Fri17,52 %18,26 %18,21 %19,31 %
Sat8,62 %8,88 %8,93 %9,69 %
Sun1,53 %1,81 %1,99 %2,56 %
TOTAL100,00 %100,00 %100,00 %100,00 %
Order priority
DAYORDERS COMING IN %
MON17,89 %
TUE18,15 %
WED17,58 %
THUR17,30 %
FRI18,23 %
SAT8,93 %
SUN1,93 %
TOTAL100,00 %

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