Discussion Post

Profitability Across Regions & Product Categories in a US Wholesaling Company

Intro

We chose to analyze the profitability of a United States wholesaling company because profitability is a key indicator in determining the efficiencies of an organization and also assists in identifying those areas where improvements can be made (Hofstrand, 2019). Profitability allows businesses to effectively utilize resources, expand, and invest in the future. The reason profitability interests us is because a business must be profitable before it can pay back shareholders. Shark Tank was one of the motivators in picking this topic because the “Sharks” or investors must see a clear pathway to profitability to feel comfortable investing (Yu, 2023). The key indicators of profitability include “effectiveness of management, increasing credit worthiness, attracting investor interest, hiring employees, and increasing the market value of the business (Dalton, 2022).”

The objective of our visualization will be to determine the profitability of this company based on state, region, categories, and sub-categories. Understanding which aspects of the business that are most profitable will provide the C-suite managers with insights needed to make strategic decisions about where to focus resources, how to adjust pricing strategies, and where to expand or scale back operations (Chen, 2019). The questions that will be answered from our visualization will be what regions and products are doing the best within the organization and how to assist the areas that aren’t doing as well (Garbinsky et al, 2020). We will also explore the Return on Sales (ROS %) metric. Datasets

In our comprehensive exploration of the company's sales data, we delve into an extensive dataset that encompasses a robust array of 9,994 transactions, detailed across 22 diverse variables. These variables not only span categorical, numerical, and date information, but also capture the granularity of business operations. We dissect product segments including 'Furniture', 'Office Supplies', and 'Technology', examining their performance metrics by 'City', 'State', and 'Region'. This multifaceted analysis is anchored by unique identifiers such as 'Customer ID' and 'Order ID', which meticulously track each transaction. Core financial metrics such as 'Profit', 'Sales', and 'Quantity' act as beacons, illuminating the company's performance and efficiency in resource utilization (Twin, 2024). Meanwhile, variables like 'Discount', 'Category', and 'Sub-Category' shed light on the intricacies of pricing policies and product nuances.

To elevate the analytical prowess of our investigation, we propose a series of data transformations designed to amplify the depth and breadth of our analysis. By converting dates into a datetime format, we pave the way for sophisticated time series analysis. The introduction of innovative metrics such as 'Sales Margin' and 'Shipping Time' promises a new dimension in assessing profitability and operational efficiency. Venturing further into the data stratification process, we advocate for the segmentation of 'Sales' into distinct categories, thereby unraveling consumer spending patterns and preferences. To enrich our contextual understanding, we consider the integration of market data and customer demographics, as it will infuse our analysis with a perspective on external economic factors and the company's standing in the market ecosystem (Delmagani, 2024).This meticulous augmentation of our dataset serves as a strategic channel to extract actionable insights, aligning with our commitment to reinforce the company's market position.

Visualization Plan - See appendix

1. Return on Sales %: This chart displays the average percentage of the return on sales for different sub-categories of products, which are classified into three main categories: Furniture, Office Supplies, and Technology. The chart can help quickly identify the sub-categories that positively or negatively impact achieving the goal of profitability. The next step would be to investigate why some categories perform better and apply what they have learned to all categories. However, a thorough analysis would need more data and may require statistical or financial modeling.

2. Return on Sales % (Table): This table complements the information provided by the first bar chart by giving a year-by-year breakdown of the return on sales percentages for each sub-category. The primary variable in this table is the "Return on Sales %”, which is calculated as a ratio of profits generated from sales relative to the cost of goods sold. This metric is broken down into sub-categories such as Accessories, Appliances, etc. The units in this table are the subcategories of products sold, whereas the categories are the years 2014 through 2018. It is important to note that this dataset has certain limitations. First, the data is historical and does not include recent years past 2018. Lastly, the specific “Return on Sales %” calculation is not defined, and this calculation can vary depending on what costs are included.

3. Return on Sale by Region: The chart is a stacked bar chart called "Return on Sales % by Sub-Category," which provides additional information about the region. This chart displays the average return on sales percentages for various subcategories and each sub-category bar is further divided by region: Central, East, South, and West. The primary variable is the "Average Return on Sales %". The sub-categories of products sold are on the horizontal axis and the vertical axis shows the average return on sales percentage. Each bar is segmented into color-coded regions. The data is filtered by Ship Date Year, which ranges from 2014 to 2018. This means the returns are not a single year's data but an average over these years. This chart visualizes which sub-categories perform best in which regions, which can be crucial for targeted marketing, inventory distribution, and regional sales strategies. Incorporating this chart into a narrative would be most appropriately used to discuss regional sales performance and planning.

4. Profit Map: The chart analysis is a geographic representation using color coding to indicate the sum of profit made in each state. This is useful for visually identifying which states are the most and least profitable. It has identified two areas where further improvements could be made for better insights. First, the returns are categorized by region which provides more specific insights into market performance. Secondly, the chart displays the return on sales, but it needs to explain the reasons behind the better performance of particular regions and sub-categories. The unit of measurement is the sum of profits, indicated by the color intensity on the map. The legend exhibits the range from a loss (-25,729) to a profit (76,381).

5. Sum of Profit by Region: The chart represents the total profit earned by different sub-categories of products such as Bookcases, Chairs, Furnishings, Tables, etc., further broken down by region. The unit of measurement used is profit in currency The segments of each bar represent different regions, and the colors assigned to each area, such as Central (blue), East (orange), South (red), and West (green) are used to differentiate the portion of the total profit attributed to each region within a sub-category. The chart is ordered by broader categories as indicated by the headers 'Furniture,' 'Office Supplies,' and 'Technology.' Each bar is labeled with the name of the sub-category. This chart would be best used in a narrative section that discusses regional sales strategies, profitability analysis by product line, or decisions related to resource allocation. It could support discussions on where to invest more in marketing, where to cut costs, or which product lines to expand or phase out based on regional performance.

6. Average Profit by Year Region Line: This line graph is a valuable tool for comparing the profitability performance of different regions over time. It indicates significant variations by year and region, such as the sharp increase in profit for the South in 2018 or the decline for the Central region over the same period. The graph shows the average profit trend for each region over five years. The horizontal axis represents the year the products were shipped, while the vertical axis represents the average profit in a currency unit (not specified, but likely USD). The chart uses different colors to represent the four regions. For a more comprehensive analysis, consider this data alongside other variables, such as regional expenses, sales volume, and economic indicators to understand the causes of these profit trends fully.

Conclusion

Our direction to the managers of this company would be to allocate resources to the technology category as this is the most profitable category (Mailchimp, n.d.). The limitations of the data set are years before 2014 and after 2018. Because this data set only contained four years of data, we recommend diving into previous years to have a more accurate historical spread. It could also be essential to consider external factors such as population, average income, presence of competitors, sales volume, and customer demographics to better understand market performance.

References

1. Chen, Z. J. Harford, A. Kamara. Operating Leverage, Profitability, and Capital Structure. (2019). Journal of Financial and Quantitative Analysis, volume 54, issue 1, p. 369 - 392.

2. Dalton, Jack. What is Profitability and Why is More Important Than Profit (2022). Financial Modeling Prep. What is profitability and why is it more important than ... | FMP (financialmodelingprep.com) .

3. Garbinsky, Diana & Lynette M. Hudiburgh. Data Visualization: Bringing Data to Life in an Introductory Statistics Course. (2020). Journal of Statistics Eduation, Vol 28, Issue 3, p. 262 - 279.

4. Hofstrand, D., & Johanns, A. (2019, August). Understanding Profitability | AG Decision maker. https://www.extension.iastate.edu/agdm/wholefarm/html/c3-24.html#:~:text=Profitability%20is%20the%20primary%20goal,measured%20with%20income%20and%20expenses.

5. Yu, J. (2023, June 9). How is a business valued on "Shark Tank"? Investopedia. https://www.investopedia.com/articles/company-insights/092116/how-business-valued-shark-tank.asp#:~:text=The%20Sharks%20ultimately%20want%20to,for%20%24100%2C000%20might%20be%20attractive.

6. Resource allocation: Optimize for business growth | Mailchimp. (n.d.). Mailchimp. https://mailchimp.com/resources/resource-allocation/

Appendix