ICT761 Business Analytics and Business Intelligence T224
Student ID:
Student Name:
Table of Contents
Pivot Table of the Gross Sales & Product Type 4
Pivot Table of the Product Type & Returns 4
Pivot Table of the Returns & Customer Segment 5
Table of figures
Figure 1 gross sales and product type 4
Figure 2 product type & returns 5
Figure 3 Returns & Customer segemnt 5
Figure 4 Correlation Analysis 7
Figure 5 Regression Analysis 8
Introduction
Business Analytics and Business Intelligence are critical topics that are very essential to consider to improve the business of any sector. The crucial tools of Business Analytics (BA) and Business Intelligence (BI) help a company’s business to make decisions backed by data analysis of both historical and current information to identify promising trends for the future and streamline operations. Both disciplines of Business Analytics (BA) and Business Intelligence (BI) can provide important insight that aids firms in understanding opportunities, refining processes, and keeping ahead of their competitors (Panci?, ?u?i? and Serdaruši?, 2023). This Assessment can easily make the most of Excel, a popular software for Business Analytics and Business Intelligence, due to its availability, flexibility, and data analysis capability. This assessment of business analytics and business intelligence considers and analyzes how BA (business analysis) and BI (business intelligence) methodologies can be implemented in Excel of sales dealings. For this analysis using Excel software the dataset is taken from a company dataset which is used to analyze the Excel dataset visualize, and implement the model in the dataset (Muntean, et al., 2021). This dataset has information on products and their various features like regions, channels, etc. In this dataset, these features include:
Product ID, Product Name & Product Type: These features are used to identify the product showing the product name and its type.
Net Quantity: These features are used to show the quantity of sold products.
Gross Sales, Discounts, Returns & Total Net Sales: These features are used to show the product sales, its discounts, returns of the product, and total sales of the product.
Sales Date, Region, Sales Channel: These features are used to show the date of the sales, the region of the sales, and through which the sales channel that through which mode sales are done.
Customer ID, Customer Segment, Salesperson ID: These features are used to show the information of customers, their purchasing info, and salesperson info.
Discount Type, Return Reason, Order ID: These features are used to show the type of discount on the product, the reason for the return of the product, and information about the ordered product
Payment Method: This feature is used to show the methods of payment for ordering the product such as online payment, credit card, or cash.
Pivot-Table Analysis
The Table Pivot functionality in spreadsheet software is created to arrange, summarize, and analyze data from larger tables. It supports the organization of data for comparison, research of trends, and insights (Skender and Manevska, 2022). In this assessment, the pivot table helps to analyze the details of the sales with the different features of the dataset. Here is the pivot table which explains the analysis of the data.
Pivot Table of the Gross Sales & Product Type
Figure 1 gross sales and product type
This pivot table provides a summary of Gross Sales based on the variation in Product Types and Sales Channels, including “In-store” and “Online”. It describes the whole sales revenue for each category of goods (Art & Sculpture, Basket, Christmas, Home Decor) along two sales channels. From this table, information is gained that points out the important insights that explain that total In-store sales reached 15,088,944.85, with the biggest sales from "Home Decor" being 3,833,258.53 while Online sales reached 14,916,396.51 in their entirety, with "Home Decor" leading the way at 3,776,646.91. The gross sales from diverse product categories and sales channels add up to 30,005,341.36. Among all the product types, "Home Decor" has the highest sales at 7,609,905.44. This table sales balance is quite even between in-store and online channels, with a small advantage to in-store sales. This chart is designed to enable a comparison of multiple sales data for products and marketing methods to aid businesses in refining their inventory management and their marketing strategies.
Pivot Table of the Product Type & Returns
Figure 2 product type & returns
This pivot table shows the total "Sum of Returns" across different regions (East, North, South, and West) for various categories of items which include categories of “Art & Sculpture”, “Basket”, “Christmas”, and “Home Décor”. The values returned in each classification are segmented by regions, and the totals for each region and item are determined.
Art & Sculpture: In this category, the highest returns come from the North at 166,456.11, making a total of 633,638.79 for all regions.
Basket: In this category, the returns are highest in the North (155,220.76), reaching a total of 613,566.26.
Christmas: In this category, the returns here are also led by North, reaching 160,503.61, which together yield 622,157.53.
Home Decor: In this category, the total return is 638,786.22, with the South having the highest return at 157,819.28.
The overall total figure amounts are 2,508,144.78 for all designations and areas in which the northern region receives the most returns of 642,454.06.
Pivot Table of the Returns & Customer Segment
Figure 3 Returns & Customer segemnt
This pivot table presents the total Sum of Returns based on different reasons for return: Redo the sentence by representing it using easier language. These reasons are broken down across three types of customers which include Frequent, High-Value customer, and New customer. This table shows the returns which are classified into returns and the customer classification.
Frequent Customer: In this category of customer, the returns for defective items are highest (218,758.58) and amount to 862,296.65.
High-value Customer: In this category of customer, a total of 203,457.97 is indicated as the greatest input towards returns, resulting in a total value of returns at 818,298.17.
New Customer: In this category of the customer, both categories “Not Defined” and “Not as Described”, report totals above 200,000 in returns, with the total exceeding 827,549.96.
The grand totals for each reason across all product types which include the Change of Mind add up to 612,307.18, the count for Defective items is 632,219.19, Not as Described returns represent 634,903.18, and the total amount Not Defined stands at 628,715.23. Across all types and causes the total comes to 2,508,144.78 which is the total return calculated from the different types of returns.
Correlation Analysis
The correlation analysis is used to show the relations between the features of the dataset which is shown in this assessment using Excel a table of correlation is created in which a few features are selected which is used to show the correlation between themselves (Islam, 2020).
This table shows a correlation matrix between different sales metrics in which the selected features are “Net Quantity”, “Gross Sales”, “Discounts”, “Returns”, and “Total Net Sales”. Through this analysis, it is seen that there is a strong positive correlation (0.984) between Total Net Sales and Gross Sales, which indicates that a climb in gross sales has a heavy duty in causing a complementary rise in net sales. The correlation of Discounts shows a detrimental (-0.121) effect on Total Net Sales, indicating that when discounts grow, net sales tend to lessen somewhat. In this correlational analysis it is seen that Returns have a moderately negative correlation (-0.115) with these Returns are associated with lower net sales. Virtually all links seen between Net Quantity and various factors are insignificant, except for Gross Sales (0.006), which suggests a negligible effect on overall sales metrics.
Regression Analysis
The Regression Analysis is a basic prediction method through which the data is predicted. In Excel, performing regression analysis is a statistical process that looks to discover the link between two or more variables (Baždari?, et al., 2020). This software can create models that judge a dependent variable based on the values of one or more independent variables.
This regression analysis is used to investigate how Sales correlate with the variable "Sales Date." The model exhibits an R-squared value of 0.000301141, which means that only a percentage of the variability in sales is described by the Sales Date. The F value of 0.0827 indicates that there is an insufficient signal of statistical importance in the model at the 0.05 level. The coefficients demonstrate that for each additional unit in the Sales Date, sales are reduced by approximately 2.23, although this discovery is not statistically noteworthy (P-value = 0.0826). Overall, this predictive model is constrained to a certain extent.
Visualization Value
The visualization of the dataset is used to get the insights of this dataset. In this visualization, it is used to represent the insights of the dataset.
Figure 6 Line plot of product type Vs gross sales
This visualization represents that In-stores the most sales in home décor while in the online, the most sales is in the Art and sculpture category.
Figure 7 Bar plot of the customer segment & Returns
This visualization represents that in the frequent customer and New customer categories the returns are the highest which is more than 220000 and 210000 respectively with the reason of Not as Described in both categories while in the high-value customer, the highest return is more than 205000 with the reason of Defective.
Conclusion
Using Excel for Business Analytics and Business Intelligence allows businesses to evaluate data, pick up on patterns, and strengthen their strategies. This shows that Excel software can also be very effectively used for business analytics and intelligence. In the regression analysis, it is seen that Excel can be effectively used to implement the model on a dataset through which the business intelligence can be implemented.
References
Panci?, M., ?u?i?, D. and Serdaruši?, H. (2023) ‘Business intelligence (BI) in firm performance: role of big data analytics and blockchain technology’, Economies, 11(3), pp.1-19, https://www.mdpi.com/2227-7099/11/3/99
Muntean, M., D?n?ia??, D., Hurbean, L. and Jude, C., (2021) ‘A business intelligence & analytics framework for clean and affordable energy data analysis’, Sustainability, 13(2), p.638. https://www.mdpi.com/2071-1050/13/2/638
Skender, F. and Manevska, V., (2022) ‘Data Visualization Tools-Preview and Comparison’, Journal of Emerging Computer Technologies, 2(1), pp.30-35. https://dergipark.org.tr/en/download/article-file/2503669
Islam, M., (2020). ‘Data analysis: types, process, methods, techniques and tools’, International Journal on Data Science and Technology, 6(1), pp.10-15. https://pdfs.semanticscholar.org/4297/626dad995612a5bec4cbd9c41d2a2f6f0146.pdf
Baždari?, K., Šverko, D., Salari?, I., Martinovi?, A. and Lucijani?, M., (2021). ‘The ABC of linear regression analysis: What every author and editor should know’, European science editing, 47, https://www.unirepository.svkri.uniri.hr/islandora/object/medri:7355/datastream/FILE0/download
