Data Analytics for Managers: Insights from Sales Data Analysis

Running head: Data Analytics for Managers Data Analytics for Managers Name of the student: Name of the university: Author note: 1 Data Analytics for Managers Table of Contents Introduction ................................................................................................................................ 3 Data Cleaning and Preparation .................................................................................................. 3 Overview of Business Performance ........................................................................................... 5 Key Performance Indicators .................................................................................................. 5 Returns ................................................................................................................................... 5 Target Achieved ..................................................................................................................... 5 Sales ....................................................................................................................................... 5 Profit ...................................................................................................................................... 6 Category wise sales performance and sales representative ................................................... 7 Target achieved by sales representatives ............................................................................... 7 Profit by category and region ................................................................................................ 8 Average sales by region and household type ......................................................................... 8 Dashboard ............................................................................................................................ 10 Insights gained through visualizations ..................................................................................... 10 Sales by category ................................................................................................................. 10 Sales by State ....................................................................................................................... 11 Sales by category and state .................................................................................................. 11 Recommendations ................................................................................................................ 12 Overall Summary ..................................................................................................................... 12 2 Data Analytics for Managers 3 Data Analytics for Managers Introduction The main purpose of the present work was to analyze the sales data of New Peth Shoes Ltd. and derive meaningful insights that could help in improving business performance. For any business organizations to stay ahead in the highly competitive market it is very essential to make effective decisions and make efficient business plans. For this purpose, it must properly analyze its past business performance. This needs to be done by gathering data from the past and its thorough analysis. This allows the company to understand its area of good performance and bad performance. A secondary data was obtained which contained information about the historic sales data of the company. The data was in an excel sheet which informed about the sales, sales target, shipping, returns and sales representatives. The data was mainly quantitative which is why quantitative approaches were incorporated in the analysis of the data. The quantitative analyses mainly involved exploratory analysis of the data through descriptive and visual methods. Descriptive methods helped in understanding the numerical variables. Visualizations were applied to summarize the findings from the data through graphical tools. Data Cleaning and Preparation The secondary data obtained from New-Peth Shoes Ltd. was initially not well organized as different data –sales target, shipping, returns, sales representatives were present in different sheet. Prior to analysing the data, the data from different sheets were all integrated into one sheet. This allows the analyst to view the complete data into one single integrated format which in turn makes the process of analysis very smooth. The process of preparing and cleaning the data shall be discussed below in the present section. At first in all the sheets the numerical variables like sales, quantity and others were checked if they were assigned number format in the excel. It was found that the numerical 4 Data Analytics for Managers variables were already assigned number format. The order date and ship date were checked to see if they were assigned auniform date format. They were already assigned auniform date format. Other than that, all other variables represented through texts were assigned the General format. The variables ship date and ship mode from the sheet “Shipping ”were integrated into the “Sales ”sheet. This was performed by matching each order with their unique order ID with the help of in built VLOOKUP() function in excel. Similarly, the variable returned from the sheet “Returns ”was integrated into the “Sales ”sheet with respect to the unique order IDs of each order. The variable sales representatives from the sheet “Sales representatives ” were integrated into the “Sales ”sheet to by matching them with the variable region. While doing so regions North-East and North-West were merged as North and South-East and South-West were merged as South. The variable sales target (£)from the sheet “Sales Target ”was added to the “Sales ”sheet by uniquely matching with variables – order date, household type and category. While doing so it was found that some of the 981 entries in sales target sheet did not match with the unique combination of order date, household type and category of the “Sales ”sheet. So, these 981 rows from the “Sales ”sheet were removed. There were 652 rows remaining which was sufficient enough sample size for the analysis. A new variable called “Target Met ” was created which was conditionally represented “Yes ”if the Sales value for a particular order date was greater than the Sales target and “No ”otherwise. The final prepared data was checked for any duplicate rows to which it was found that there were no duplicate rows. With this final step the data cleaning and data preparation process was completed. All analysis were performed using the prepared data available in the “Sales ”sheet. 5 Data Analytics for Managers Overview of Business Performance Key Performance Indicators The business performance of the New Peth Shoes Ltd. Shall be assessed with the help of the four main key performance indicators (KPIs) –returns, target achieved, sales and profit. A summary of the KPIs shall be presented below. Returns Return was chosen as a performance indicator for the business performance as it indicates the quality of delivery of the items to the customers. Data revealed that only 34 of the items were returned and 618 items observed in the sample were not returned. In terms of percentage, itcan be said that only 5.21 % of the items were returned and rest 94.79 % of the items were not returned. Target Achieved This variable indicated if on a particular date the sales target was achieved by the company. For every order IDs on each particular day there was asales target set which was to be achieved by the sales representative. Out of 652 observations it was found that 451 times the target was achieved which was 69 % in terms of percentage. 201 time the target was not achieved which was 31 % of the total observations. Sales Table 1:Summary statistics for Sales Mean 360.38 Standard Error 22.42 Median 151.33 Mode 799.83 Standard Deviation 572.48 Sample Variance 327736.80 Kurtosis 20.18 Skewness 3.82 Range 5266.65 Minimum 7.05 6 Data Analytics for Managers Maximum 5273.70 Sum 234968.69 Count 652 Sales is a continuous numerical variable so it was examined through a descriptive analysis. The sales in the sample ranged between aminimum of £ 7.05 and amaximum of £5273.7. The observed average sales by the company was £360.38. Standard deviation is a measure of how much the observations in adata adispersed from its mean. The higher the value of the standard deviation the higher is the dispersion about the mean. The standard deviation of the sales values was £572.48. The median observation was £151.33. Median is the observation occurring in the middle of adata set when all observations of the data are arranged in an ascending or a descending order. 50 % of the recorded sales was above £151.33 and rest 50 % of the sales were below £151.33. Profit Profit was again a numerical continuous variable that indicated how much profit a sales representative made over aparticular order. A negative profit indicates aloss and all negative profit values were marked uniquely with red colored texts. Table 2:Summary statistics for Profit Mean 75.97 Standard Error 7.53 Median 23.66 Standard Deviation 192.18 Sample Variance 36931.25 Kurtosis 39.18 Skewness 4.96 Range 2676.51 Minimum -686.97 Maximum 1989.54 7 Data Analytics for Managers Sum 49533.77 Count 652 The observed average profit by the company was £75.97. The standard deviation of the profit values was £192.18. The median observation was £23.66. 50 % of the recorded profit was above £23.66 and rest 50 % of the profit were below £23.66. The maximum recorded profit in the sample was £1989.54. Category wise sales performance and sales representative The highest average sales was observed in the category of Kids Shoe with an average sale of £643.93. This was followed by the category of Men ’sShoe with an average sale of £ 517.68. The least average sales was observed in the case of Women ’sshoe which was equivalent to £ 179.78. In the category of kid ’sshoe sales representative Wendy Juliana showed sold highest amount on average which was equivalent to £976.47. In the category of men ’s shoe sales representative Steve Smith made highest average sales which was equivalent to £ 762.61. In women ’sshoe category the overall sales performance was quite low where Deborah Reid made highest average sales of £215.25. Target achieved by sales representatives Table 3 Target achieved Sales Representatives NO YES Brian Johnson 36% 64% Deborah Reid 37% 63% Emily Waterston 25% 75% Nick Hardy 28% 72% Steve Smith 27% 73% Wendy Juliana 24% 76% Grand Total 31% 69% 8 Data Analytics for Managers The Percentage Of The Sales Target Achieved By Each Sales Representative Were Compared. In Terms Of Target Achievement Performance Of Wendy Juliana Was The Best. 76 % of the times Wendy had achieved the target. The lowest performance was shown by Brian Johnson who had achieved the target on 64 % of his orders. Profit by category and region Table 4 Regions Categories Central East Midlands North South West Grand Total Kids Shoe 98.83 130.60 135.14 151.36 118.80 379.38 141.89 Men's Shoe 134.95 100.39 96.47 55.94 70.68 -56.59 83.51 Women's Shoe 21.78 42.92 38.01 47.63 50.72 24.42 40.32 Grand Total 59.57 89.09 71.13 84.07 69.46 107.31 75.97 The highest average profit by the company was made in the region of West under the category of Kids shoe. The average profit made in the category of Kid ’sshoe in West region was £ 379.38. The lowest profit was observed in West under the category of Men ’sshoe. There was indeed aloss of £56.59. The average profit in the category of Women ’sshoes was lowest in general except for the West region. The highest average profit can be observed in the region of West with all categories combined. The average profit in the West was £107.31. The average profit in the North was £84.07. The average profit in the South was £69.46. The average profit in the Midlands was £ 71.13. The average profit in the Central region was £59.57. The average profit in the East was £89.09. Average sales by region and household type Average of Sales (£) Household types Region Couples Family Singles Grand Total Central 251.43 400.85 347.38 336.05 East 486.43 338.55 632.89 428.52 9 Data Analytics for Managers Midlands 322.94 276.46 350.20 314.03 North 455.36 344.99 430.24 406.55 South 326.68 420.00 226.59 338.90 West 298.79 405.88 341.98 340.96 Grand Total 360.55 354.61 369.39 360.38 It was observed that the highest average sales was made in the region of West. In the Central region the highest purchase was made by families with average sales equivalent to £400.85. In the East region the highest purchase was made by Singles with an average sales amounting to £ 632.89. In the Midlands region the highest purchase was made by singles with an average sales amounting to £ 350.20. In the North region the highest purchase was made by couples with an average sales amounting to £455.36. In the South region the highest purchase was made by family with an average sales amounting to £420. In the West region the highest purchase was made by families with an average sales amounting to £ 405.88. Overall comparing the average sales by household type the highest average purchase was made by singles combined for all six regions. On an average, singles from all six regions purchased shoes worth £ 369.69. The lowest purchase for all six regions was made by families with an average purchase equivalent to £354.61. 10 Data Analytics for Managers Figure 1:Dashboard Dashboard Insights gained through visualizations Sales by category A category wise performance of sales revealed that women ’sshoes was lowest in terms of average sales. The average sales made through women ’s shoes was £ 179.78. 11 Data Analytics for Managers Highest average sales was made through Kid ’sshoes. The average sales made through Kid ’s shoes was £643.93. Sales by State By comparing the average sales by states, it was discovered that highest sales was made in the state of Scotland. The lowest average sales was observed in the state of Wales. In Wales the average sales was £340.96 and in Scotland the average sales was very high which was £487.63. Sales by category and state 12 Data Analytics for Managers By observing the average profit made by the company in different states and comparing them by categories it was observed that men ’sshoes made ahuge loss in Wales. However, in the state of Wales there was a very high profit generate through the kid ’s segment. In the state of Scotland all three categories made very similar profits with little difference on an average. In the state of England, the highest profit was made through the kid ’sshoes again. Recommendations  Overall based on the key insights discussed in the previous section it must be recommended to the company that the company needs to improve its sales for women ’sshoes.  In the state of Wales, the overall sales on an average is quite low which can be improved.  In the state of Wales, the men ’s segment made a loss. The company needs to formulate business plans that helps them improve their profit in the men ’scategory in Wales. By increasing the average profit in men ’scategory of shoes the company can make progress in Wales in terms of average profit.  In terms of target achieved by sales representatives, some of the representatives needs to work hard and smart to improve their target achieved data. Brian Johnson and Deborah Reid need to bring down the target not achieved percentage. Overall Summary This section shall discuss in short, an overall summary of the findings through the analysis of the data. The orders returned data of the company was quite good as only 5% of the orders were returned by the customers. Overall, in all regions combined singles made highest amount of purchase especially in the region of East. The percentage of target not 13 Data Analytics for Managers achieved by the sales representatives was quite high (31%) which needs to be reduced in order to maximize sales and profit. Products under women ’scategory made very less profit on an average which needs to be improved. In the state of Wales the performance of men ’s shoes needs to be improved.