BMG880 | Data Analytics for International Business

Ulster University Birmingham Campus

Course Work 1

Report of Business Intelligence Support for Sales Analysis of Staples E-Commerce

Contents

1. Introduction

1.1 The Problem

1.2 The Role of Business Intelligence

2.Theoretical Frame Work

2.1Profit Margin Predicament in Staple Online Store, Causes and Impact

2.2Applying Business Intelligence to Overcome the Weakness

2.3Business Analytics in E-Commerce Industry (Performance Management)

3.Data Analysis

3.1The process of analyzing Staples Data

3.2Dashboard

4.Dashboard Justification

4.1Dashboard Overview

4.2Slicers

4.3KPIs

4.4Graphs and Tables

5.Recommendation and Conclusion

References

1.Introduction

The emerging of e-ecommerce has fundamentally transformed the business to customer interaction by offering a convenience in terms of time, choice, and experience in a virtual arena (Goldmanis et. al, 2010). Although these virtual shelves on the online stores aims to increase customer satisfaction and create a valuable experience, the business goal of generating profit remains unchanged. Online businesses aim to convert website sessions into revenue through converted checkouts by enhancing customer virtual journey, competitive pricing, and marketing (Har et. al, 2022). In this dynamic process, profitability is the vital vessel for e-commerce venture growth and sustainability.

Access to real-time data is what differentiates e-commerce from any other commerce business (Har et. al, 2022). Through applying on-site tracking and off-site preference tracking known as cookies, e-commerce has access to dynamic data that provides unique insights into consumer behavior, preferences, and emerging trends (Norton, 2023). On the other hand, decision for business intelligence, real-time data empowers the business with flexible and more efficient decision-making process with the aim to utilize business strengths and uncover any underlying weaknesses to resolve (Sharda, et, al. 2014).

1.1 The Problem

Staples is a major e-commerce business in the United States specializing in furniture, technology, and office supplies (Staples.com, 2023). In the recent years, Staples has been facing a profitability challenge. Despite the broad and various selection of items and diverse customer base across the nation, Staples struggled with low profit margin in 2022 at just 12%, which is far below the flourishing industry average of 41% (NYU, 2023). This issue reflects a broader trend of shrinking margins that has caused many e-commerce ventures to vanish. Shortage in cash flows driven from low margin rates contributed to 52 U.S e-commerce bankrupts in the past three years (Forbes, 2023).These initial insights highlight the crucial importance for a data-driven decision making to boost profitability.

1.2 The Role of Business Intelligence

Detailed analysis of Staples 2022 online sales data is required to explore the set of factors resulting in low profit margin. Investigating sales pattern, pricing, geographical distribution, and customer behavior will set the compass to spot the main factor for Staples margin squeeze.

This report will apply business intelligence to investigate main factors correlated to Staples low margin rate. It will analyze sales patterns, inefficiencies, growth obstacles, and pricing strategy. This report will discuss how Business Intelligence can improve Stable inventory management, product selection, and pricing. The report will analyze approximately 3,000 transactions that took place on the online platform of Staples using Excel formats and visualizations. The insights gathered along with essential KPIs will be visualized in an interactive Excel dashboard to offer a dynamic decision-making regarding pricing, products selection, inventory and logistical operation.

2.Theoretical Frame Work

2.1Profit Margin Predicament in Staple Online Store, Causes and Impact

Staples online store faces a concerning financial issue with profit margins below the promising U.S e-commerce industry average. Relevant financial vulnerabilities threaten the viability of any business and lowers chances of sustainability (Evans, 2022). A bundle of factors can be contributing to this profitability weakness such as pricing strategies, intensive competition, and operational inefficiencies. On the pricing part, a margin-eroding promotional pricing is a common mistake done by many online retailers that sets a pricing strategy that meets customer perceived value but contrast the actual business long-term goals and risk its competitive advantage. On the operational inefficiency perspective, an inefficient supply chain and inventory management is one of the main causes of an overhead inflation and undermined margins (Financial Times, 2023).

The impact of low profit margin can reach all components of the business, it can even jeopardize business viability and presence in the market (Evans, 2022). Internally, low margin leads to unstable cash flows, limits growth investments, and frame innovation. On the external landscape, it will affect the business competitiveness as competitors with superior margins enjoy greater pricing flexibility in the e-commerce market that is driven by rarity and price sensitivity (McKinsey & Company, 2021). In other words, without feasible profits Staples will gradually lose the capability to adapt to market changes and eventually risk its market share.

2.2 Applying Business Intelligence to Overcome the Weakness

Effective e-commerce operations are directly related to smooth and efficient supply chain that ensures products reach their destination in the right time and place (Vitasek, 2023). In addition to supply chain, suitable pricing methodologies guarantee financial feasibility for the company to sustain and expand (Ghose & Sundararajan, 2006). But when profit margins are low, businesses like Staples face a critical problem that threatens its existence as a key player.

To navigate this issue, and with the privilege of real-time data available to online businesses, Staples can rely on data analytics to overcome this challenge. This includes the implication of business intelligence to interpret large amounts of data with the aim to make smarter business decisions as it enhances business processes, decision-making, and organizational performance, offering a competitive edge (Shanks & Bekmamedova, 2012). By applying business intelligence, Staples will be able to analyze sales data, customer preferences and competitors insights to gain better understanding of the ecosystem that leads to better pricing strategies and more efficient operations driven from data understanding (Wisniewski, 2010). These insights can help Staples optimize inventory, create proper promotional prices, and make informed decisions that increase profit margins (Davenport & Harris, 2007). Applying Business Intelligence will provide Staples management with a clear and up-to-date optimal view of their business performance and guide the business toward a more profitable path (Cosic et. al, 2015).

According to Baar and Kemper (2018), The Business Intelligence theoretical framework shows the conceptual structure to monetize, process, and analyze knowledge in an understandable form to generate decisions. In other words, BI framework consists of data layer, logic layer, and access layer as shown in figure 1 below. (Baar & Kemper, 2018)

Data Layer

Data can be obtained from numerous sources and systems related to business value chain like ERP, CRM and SCM. This data must be collected and structured in one place before stored in the data warehouse. Data from all sources must be extracted and converted in symmetrical format before it transfers to the logic layer for analysis. (Baar & Kemper, 2018)

Logic Layer (Analysis Layer)

Logic layer applies descriptive and predictive techniques to analyze data transformed from the warehouse either by multidimensional or data mining analysis approaches. Which equip the data scientist with exploration of large datasets to uncover meaningful patterns and rules and view data through combining dimensions like sales, parcel size, discount, etc. Overall, the logic layer enables examination of aggregated data to find actionable knowledge through multidimensional techniques. (Baar & Kemper, 2018)

Access Layer (Reporting and Visualization)

Access Layer is a critical step in the BI framework where it communicates the data with decision makers. Dashboards are visual and dynamic representation of data outcomes in form of graphs, KPIS, indicators, and tables that interpret the outcome of the raw data sorted in the data warehouse and analyzed in the logic layer. Some businesses such as e-commerce require real-time integration of data into the framework for a dynamic decision making. (Baar & Kemper, 2018)

2.3 Business Analytics in E-Commerce Industry (Performance Management)

Business Analytics plays a vital role in the e-commerce industry by harnessing data-driven insights to decision makers looking to enhance their business performance. Business analytics empowers businesses to enhance operations, optimize decision making, and gain a competitive advantage in the digital landscape. With the ongoing feed of numerous amounts of data online, Business analytics implication can be the key to predict ahead and act in advance in the competitive landscape in the e-commerce industry (Goldmanis et. al, 2008). The relationship between business analytics and e-commerce performance management is shown in many success cases such as Boss implication to business intelligence that led to creating brand extensions for different age groups classified as GenZ and Millennials (Vogue Business, 2023).

3.Data Analysis

3.1The process of analyzing Staples Data

Data Analytics in business context is the identification of meaningful patterns within immersive data through the use of technology to gain insights that improve decision making according to Chandler and Munday (2016).

Into this context, Staples has a problem with their e-commerce division profit margins that has effect on their long-term financial situation. Addressing this challenge involves a systematic data analytics approach. The initial step is to collect and assemble comprehensive data from the online store, the source could be from the website server or cloud-based data warehouse. However, the data assembled were acquired from Kaggleincluding more than 3,000 transaction details through the year of 2022. Afterward, data processing and analysis takes place to reveal pattern and meaningful insights through applying techniques such as pivot tables and cross tabulations. These analytic techniques can help uncover causal factors. The data analysis will be focusing on main metrics shown in figure 2 below. Finally, the outcomes are represented in an interactive Excel dashboard that represent results in a meaningful and accessible manner. This dashboard will serve as the decisions making tool, aiding management in formulation of strategic decisions in order to retrieve the business.

4.Dashboard Justification

4.1Dashboard Overview

Overall, the dashboard shown in figure 3 will give a comprehensive visual analysis for the whole sales data of the year 2022. It will also be implemented to track the daily and monthly sales of stables through the year 2023 to give instant feedback of the online store performance at the right time, which will give the relevant departments an opportunity to do the needed amendments to improve performance. The performance metrics will be displayed in bar charts, pie charts, trend lines, and a treemap. Applying the proper visualization in performance dashboards helps creating an instant recognition of the situation for the reader (Eckerson, 2006).

4.2 Slicers

Slicers enable dashboard users to engage with the data reports and create a user experience through filtration on several connected charts and graphs in the dashboard, which makes information extraction easier (Eckerson, 2006). As shown in figure 4 below, dashboard slicers are categorized in fur sections (month, segment, category, and region). By applying these slicers filters on the dashboard, staples managers can easily understand the current situation according to their need. They can surf either by monthly performance for example or by investigating every segment separately to check their performance and KPIs.

4.3 KPIs

Key Performance indicator (KPI) is a measurement of performance illustrated in quantified results which provides time related insights that helps entities make better decisions. (Cadle et. al, 2014). As for Staples dashboard, it has 4 primary KPIs based on the metrics related to the issue of low profit margins, which are (Average profit margin, Average discount rate, Average quantity per order known as parcel size, and Average profit per transaction). It also has one secondary KPI which is the variance between staples average profit margin and the industry average, this secondary KPI is highlighted to show the performance in regards to the market overall. These KPIs shown in figure 5 below are connected to slicers shown in figure 4 to give a dynamic experience for users as well. Some KPIs like average profit margin and profit per transaction are conditionally formatted to reflect the performance in color reference (Red, Yellow, Green).

4.4 Graphs and Tables

Graphs and tables are the core of dashboards as they help with data interpretation to communicate with the user, they show statistical information into a simple illustration to make it easier to understand (Eckerson, 2006). In this part of the report, we will review each visualization separately.

• Profit Margin Per Region

Staples divide their sales to regional segmentation (Central, East, West, South). Figure 6 shows a bar chart showing profit margin per each region. The bar chart shows that the central region had a negative profit margin which means it has been causing financial losses to the company. On the other hand, the western region had the higher profit margin rates of 22%, which is still 29-digit points below the industry average profit margin of 41%.

• Sales Per Category

Figure 7 above is a donut chart, a custom design of pie chart, that shows sales of Staples in regards to category. Staples has three head categories for its inventory (Furniture, Office Supplies, Technology) with hundreds of items under these main categories. In 2022 the technology category made up to 37$ of staples online stores, followed by Office supplies with 34% and furniture with 29%. The sales ratios show that there is no dominant category with high variance.

• Profit Margin Per Segment

Staples has three main customer segments of home office, corporate and individuals referred to as consumers. This segmentation is directly related to the items offered online by Staples. However, the pie chart in figure 8 shows that the home office segment has generated 36% of Staples profit margin for the year 2022 followed by Corporate and Consumer segments with 32?ch.

• Profit Margin per Category

As staples has three main categories, it is important to put their profitability under analytical testing. The bar chart in figure 9 below shows that the technological sector is the most profitable sector for staples with 16% profit margin rate, followed by office supplies with 16% and only 4% profit margin from the furniture category. This profitability margins order is the same as the categorys sales order shown in figure 7. However, they all still far below the industry average and the furniture category has a very low profit margin which requires further investigation.

• Profit Per Region and Category

Figure 10 shows a bar chart for a cross tabulation table that includes sales per region based on the product category. As mentioned before, the central region shows a negative profit (Loss) specially in the furniture category with break even on the office supplies category. On the other hand, the west region has a relevantly high profits with approximately $20,000 in office supplies and technology. Followed by the east region. Figure 10 confirms the issue with the central region and the furniture category overall as it had losses in two regions.

• Profit Per Shipping Mode

Staples has 4 delivery options (Standard, First Class, Second day, and Same Day). These shipment modes shown in figure 11 are related to the supply chain staples provides to give exceptional experience for their customers. The most profitable mode was the standard class with a little below $50,000, The least profitable mode was the same day with a little above $3,000, this low profit in the same day mode is justified with the higher cost related to it.

• Most Profitable Items

The treemap illustrated in figure 12 shows the most profitable items sold on 2022 through Staples online store along with their profit margin rates. This treemap is important to show the management what are their cash cow items in order to reflect this on their marketing efforts with the aim to increase their profitability. In addition to the marketing department, this treemap can be used by the inventory management departments to make sure they have them available on spot.

• Sales Trend in 2022

Figure 13 is a trend line that shows the sales performance through the year of 2022. As shown below, Staples started 2022 with low sales before they started making a growth in august before they hit their maximum sales on November and December which are the holidays season such as Christmas and Black Friday. Nonetheless, February was the worst month were stables barely made $20,000 of sales.

• Profit Margin Trend Through 2022

The profit margin has been fluctuating through the month, with variation from month to another. The trend line shown in figure 14 shows that April was the lowest month in terms of profit margin with only 5%. The best performing month regarding profit margin was August with only 16% profit margin. Although the last fourth quarter had the most sales as shown in figure 13, the profit margin rates were lower than Staples average on October and November and only one percent point higher on December. At last, the third quarter had the higher profit margin rates with 15%, 16%, and 15% on July, August, and September respectfully.

5.Recommendation and Conclusion

Business Intelligence has provided Staples with valuable understanding of their sales case by looking at their historic data of 2022. It has also helped spotting serious issues with their sales strategy, such as the losses accounted in the central region and the very low profits generated from the furniture sections. The use of business intelligence has also brought the high discount rates issue to the management attention. Therefore, the low margin rates of Staples can be related to two three main factors which are Pricing (Discounts), segmentation, and product selection which can be resolved by reallocating their activities to the more profitable segments and categories like technology in the west and east areas.

In conclusion, Staples management needs to take into consideration applying data intelligence in their daily operations to be able to take decisions at the right time and avoid unnecessary losses. Along from the analysis of the historic data, they must acquire business intelligence software that can provide instant analysis along with predictive models that can empower management with futuristic vision to act upon. Online Commerce is full of data that can be used to empower and strengthen. Without data, you are blind, deaf, and in the middle of a freeway (Geoffrey Moore, 1999)

References

Baars, H. Kemper, H. (2018) Management Support with Structured and Unstructured Data - An Integrated Business Intelligence Framework. Information Systems Management 25 (2):132-148

Cadle, J., Paul, D. Turner, P. (2014), Business analysis techniques: 99 essential tools for success, British Computer Society (BCS), London.

Chandler, D. Munday, R. (2016) Data analytics.InA Dictionary of Social Media; Oxford University Press, United Kingdom.

Colback, L. (2023) The rise of the platform economy, Financial Times. Available at:

https://www.ft.com/content/e5f5e5b9-3aec-439a-b917-7267a08d320f[Accessed: 16 August 2023]

Cosic, R., Jovanovic, D., & Rankovic, A. (2015). A Review of Business Intelligence Systems. In Proceedings of the European Conference on Information Systems (ECIS)

Danziger, N. (2023) More retail bankruptcies are brewing after Bed Bath & Beyond and davids bridal, Forbes. Available at:https://www.forbes.com/sites/pamdanziger/2023/05/03/more-retail-bankruptcies-are-brewing-after-bed-bath--beyond-and-davids-bridal/?sh=4271a5034656[Accessed: 17 August 2023]

Davenport, T. H., & Harris, J. G. (2007). Competing on Analytics: The New Science of Winning. Harvard Business Review Press.

Eckerson, W. (2006), Performance dashboards: measuring, monitoring, and managing your business, John Wiley & Sons, New Jersey

Evans, M. (2022) These five digital trends are changing commerce this year, Euromonitor. Available at:https://www.euromonitor.com/article/these-five-digital-trends-are-changing-commerce-this-year[Accessed: 16 August 2023]

Ghose, A. and Sundararajan, A., 2006. Evaluating pricing strategy using e-commerce data: Evidence and estimation challenges

Goldmanis, M. Hortasu, A. Syverson, C. and Emre, . (2010). E?commerce and the Market Structure of Retail Industries.The Economic Journal,120 (545), pp.651-682

Goldmanis, M. Hortacsu, A. Syverson, C. Emre, O. (2008) E-COMMERCE AND THE MARKET STRUCTURE OF RETAIL INDUSTRIES. NATIONAL BUREAU OF ECONOMIC RESEARCH, Massachusetts

Har, L., Rashid, U. Te Chuan, L. Sen, C. and Xia,Y. (2022). Revolution of retail industry: from perspective of retail 1.0 to 4.0.Procedia Computer Science,200, pp.1615-1625.

High growth, low profit: The e-commerce dilemma for CPG companies (2021) McKinsey & Company. Available at:https://www.mckinsey.com/industries/consumer-packaged-goods/our-insights/high-growth-low-profit-the-e-commerce-dilemma-for-cpg-companies#/[Accessed: 16 August 2023]

Moore, G. (1999) The Gorilla Game: Picking Winners in High Technology, Harper Collins. Chicago.

Shanks, G., & Bekmamedova, N. (2012). The Role of Business Intelligence in Decision Making. Journal of Decision Systems, 21(3), 249-258

Sharda, R., Turban, E., Delan, D. (2014) Business intelligence and analytics; systems for decision Support, Boston: Pearson.

Sharda, R., Turban, E., Delan, D. (2014) Business intelligence and analytics; systems for decision Support, Boston: Pearson.

Smith, S. (2023) Retailers have fixed a major profit-crushing problem: Excess inventory Retailers are getting a better grip on inventory and it's a big win for investors; Yahoo Finance, Available at:

https://uk.finance.yahoo.com/news/retailers-have-fixed-a-major-profit-crushing-problem-excess-inventory-120056608.html[Accessed: 16 Aug, 2023]

Vitasek, K. (2023) What e-commerce supply chain management really means (its more than you think...), Forbes. Available at:https://www.forbes.com/sites/katevitasek/2023/01/25/what-e-commerce-supply-chain-management-really-means-its-more-than-you-think/?sh=246978d6123f[Accessed: 16 August 2023]

What are tracking cookies and how do they work? - norton (2022) Norton Softwares. Available at:https://us.norton.com/blog/privacy/what-are-tracking-cookies[Accessed: 16 August 2023]

Wisniewski, M. (2010). Quantitative methods for decision makers with mathxl. Pearson Education.