BUSINESS ANALYSIS REPORT
Key Insights and Recommendations
BSc Data Science and Business Analytics
(Standard Entry)
Business analytics, applied modelling and prediction
(ST2187)
1. Executive Summary
The business report outlines the insights from the visualizations created using Tableau and provides recommendations and strategies to acton those findings. There are primarily 7 insights:
i. Tables are the least profitable product among all other products sold.
ii. Tables are significantly more profitable in some countries compared to others.
iii. Majority of customers who purchased Tables are associated with low volume of sales and high losses.
iv. Tables have a substantially greater percentage of discount compared to other products.
v. Significantly greater percentage of discount on Tables in unprofitable countries over profitable countries.
vi. Forecast of sales and profits of Tables indicate that Tables remain unprofitable in the future. vii. Tables experience volatile seasonality effects in its sales.
The findings outlined above help set the direction of the report. The report focuses on Tables as it is the only unprofitable product among all other products sold by the firm. The difference in profitability of Tables in different countries raises the question on what makes Tables profitable in some countries and unprofitable in others.
An analysis on the average discount on all products sold by the firm reveals that Tables have a significantly greater percentage of discount compared to the discount on other products. This finding exposes the underlying reason for the unprofitability of Tables which is due to excessive discounts that are reducing profits margins.
The firm could address the excessive discount on Tables by balancing the discount in profitable and unprofitable countries. It could raise discounts in profitable countries to increase profits generated and reduce discounts in unprofitable countries to decrease losses generated which would improve the overall profitability of Tables.
The company could take advantage of seasonality in sales of Tables by designing Tables to be locally responsive to different cultures in different timings. New marketing strategies and promotions could be tested during off-peak seasons for Tables and implemented in peak seasons to boost sales and profitability of Tables. Additional strategies such as personalized discounts for customers and loyalty programs could be implemented to attract and retain customers.
Implementation of above-mentioned strategies would improve the profitability of Tables and help the firm gain a competitive edge over its competitors in the long run.
2. Introduction
The business report is conducted based on data collected by the company from December 2016 to December 2020. The data includes information on the sales and profits of multiple products sold by the company along with geographical information such as country and region, shipping information such as shipping date and shipping cost, customer information such as customer names and customer segmentation, the category and sub-category of products, the discount on products sold, and etc.
The aim of this report is to identify unprofitable products sold by the company and the underlying causes for the unprofitability of the products, so as to formulate strategies and provide recommendations to minimize losses and maximize profits generated by the company.
3. Insights from Story
3.1 Overview of Profits and Sales by Product
Figure 1: Ranking of Products by Overall Profitability
Tables are ranked 17th in overall profits amongst 16 other products sold. They are the least profitable product among all products sold from 2017 to 2020, resulting in a loss of $64,000.
Figure 2: Ranking of Products by Overall Sales
Tables are ranked 8th in overall sales amongst 16 other products sold. They have comparable sales with respect to sales volume of other products sold from 2017 to 2020, resulting in sales of $757,000. Thus, the losses experienced from the sale of Tables is not associated with the lack of sales, but rather with other underlying issues which will be exposed in this report.
3.2 Geographical Overview of Profitability of Tables
Figure 3: Top 10 Most and Least Profitable Countries for Tables
From figure 1, we have identified Tables to be the least profitable product. Figure 3 exposes that Tables are not unprofitable throughout all the countries. In some countries like India and United Kingdom, the sale of Tables is much more profitable compared to its sale in countries like United States and Indonesia.
Given the discoveries made, it is crucial to understand why the sale of Tables is profitable in some countries while unprofitable in others. The next few sections will shed more light on the overall unprofitability of Tables and itsunprofitability in certain countries.
3.3 Overview of Overall Distribution of Customers
Figure 4: Distribution of Customers by Profits and Sales of Tables
Figure 4 displays the distribution of customers by the total sales of Tables made to each individual customer from 2017 to 2020, and the corresponding profits generated from those sale of Tables. Observe that there is a greater proportion of unprofitable customers over profitable customers in the sale of Tables which aligns with the overall unprofitability of Tables.
Furthermore, observe that significant proportion of customers associated with low sales generate higher losses compared to customers associated with high sales. This indicates that the sale of Tables is extremely unprofitable under certain conditions as the company experiences large losses from individual customers despite having low sales. Thus, it is imperative that the company discovers the conditions it should avoid to ensure that Tables become profitable.
Figure 5: Distribution of Customers in Top 10 Most and Least Profitable Countries for Tables
From figure 5, it is notable that significant proportion of individual customers who purchase Tables tend to be associated with low profits in profitable countries and low losses in unprofitable countries. This reinforces the reason for Tables to be unprofitable in some countries while being profitable in others which is attributed to its distribution of customers in profits and sales. Given the discoveries made earlier, the next section will expose the underlying reason for Tables to be the only unprofitable product, and the reason for some countries to be profitable in the sale of Tables while others remain unprofitable.
3.4 Overview of Discount on Products
Figure 6: Average Discount of Products
Figure 6 shows that the average discount on Tables from 2017 to 2020 is 29% which is significantly greater than the 15% average discount on all products. The significantly greater percentage of discount provided on Tables greatly reduces the profits generated from the sale of Tables. This explains why Tables are the only unprofitable product in figure 1 despite its comparable sales with respect to other products with similar sales volume such as Machines and Accessories.
Figure 7: Average Discount in Top 10 Most and Least Profitable Countries for Tables
Furthermore, the average discount on Tables differs significantly from the top 10 most and least profitable countries for Tables. Figure 7 shows that the average discount on Tables is 52% in the top 10 least profitable countries for Tables while the average discount on Tables is only 7% in the top 10 most profitable countries for Tables. This explains why tables are profitable in some countries while unprofitable in others in figure 3, which is attributed to the difference in average discount on Tables from 2017 to 2020.
3.5 Overview of Seasonality and Forecast of Tables
Figure 8: Forecast of Profits and Sales for Tables
Based on the four years of data from 2017 to 2020, the forecast of the following two years in figure 8 indicate similar sales volume for the sale of Tables in 2021 and 2022. Furthermore, the forecast of profits in 2021 and 2022 remains to be negative as it predominantly was from 2017 to 2020.
Figure 9: Seasonality of Profits and Sales for Tables
Observe that there is a seasonal effect on the sale of Tables during the months of June, November, and December which experience a significantly greater volume of sales compared to the other months. Furthermore, July in particular tends to experience significantly lower volume of sales compared to the other months.
It is notable that on months that experience significantly greater sales of Tables such as June, November,and December, the company generates significantly greater losses compared to the other months. Observe that the greater the volume of sales of Tables, the greater the losses suffered by the company. As previously discovered, this is directly attributed to the significantly greater discount on Tables which reduces profits greatly as the volume of sales increases.
4. Conclusion
The report has exposed Tables to be the least profitable product among all other products sold despite having comparable sales. Furthermore, Tables are unprofitable in some countries while profitable in others. The distribution of customers who purchased Tables by sales and profits indicates significant proportion of individual customers associated with low volume of sales but high losses which supports the overall unprofitability of Tables.
The unprofitability of Tables is mainly due to the significantly greater percentage of discount on Tables compared to the discount on other products. This finding is supported by the significantly greater percentage of discount on Tables in unprofitable countries compared to the discount on Tables in profitable countries.
Given the insights derived, strategies and recommendations have been provided in the next section to minimize the losses generated from Tables and transform. Tables into a profitable product.
5. Recommendations
5.1 Reducing Average Discount for Tables
Figures 6 and 7 for reference
Given that the average discount on Tables is significantly greater than the average discount on other products as shown in figure 6, the intuitive approach would be to reduce the discount on Tables. However, such an approach might reduce the revenue generated due to a fall in sale of Tables. Thus, given that the average discount for Tables in the top 10 most profitable countries are significantly lower than the average discount in the top 10 least profitable countries as shown in figure 7, the company could balance the discount on Tables in different countries.
Discounts on Tables could be adjusted to be greater in profitable countries and be reduced in unprofitable countries. This would reduce the losses generated due to reduced discount in unprofitable countries while increasing revenue generated from the increase in sales due to greater discount on Tables in profitable countries. Overall, this would increase the profitability of Tables.
5.2 Taking Advantage of Seasonality for Tables
The company can take advantage of seasonality by designing Tables to suit the needs of customers during the various seasons. For example, a proportion of Tables manufactured could be designed to be horror themed during the autumn season when Halloween is celebrated. Thus, the Tables manufactured would be locally responsive to the different cultures in countries.
Portion of Figure 9 for reference
During periods of low sales of Tables such as in July, the company can test out the effectiveness of various promotions through A/B testing. It involves offering the promotion to half the customers over multiple time periods and weighing the differences in profits generated between the two groups. If a type of promotion proves to be profitable, it could be implemented during peak seasons to further boost the sales of Tables.
Discounts could be increased to enhance sales further during peak season such as in November and December, and reduced during off-peak season such as in July to balance and optimize overall discount on Tables throughout the year.
The company could take advantage of seasonality of other products by bundling products together to be sold with Tables. For example, if Chairs experience a period of high sales, customers could be incentivized to buy a Table along with Chairs by offering discounts when both products are purchased together. Such an approach preserves the sales volume of chairs while increasing the sale of Tables as well.
5.3 Additional Strategies
Discounts could be personalized to customer purchasing patterns. Some customers may require a 10% discount incentive to purchase products while others may require a greater amount of discount. Furthermore, the firm could take note of repeat customers and offer them a loyalty program that provides them with special discounts that other customers would not have access to. This creates loyal customers for the firm who would make repeated purchases and increase sales for the firm over time.
It is crucial for the firm to develop additional strategies on top of the recommendations provided above to develop a competitive advantage over its competitors.