Toyota

DATA HANDLING AND
BUSINESS INTELLIGENCE

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
PART 1............................................................................................................................................1
By using data set of superstores analyse profit and sales over years and analyse it by using
Excel for pre- processing of data, also analyse and visualize the data........................................1
Demonstration of ways in which data can be practically analysed using Excel functions such
as Lookup, Pivot table, graphs and charts...................................................................................6
PART 2............................................................................................................................................9
By using audidealership.csv file show conjunction with Weka with the example of clustering.9
Explanation of commonly used data mining methods that can be used in business. Explain
them with real time example......................................................................................................12
Advantages and disadvantages of Weka....................................................................................13
CONCLUSION..............................................................................................................................13
REREFENCES..............................................................................................................................14

INTRODUCTION
Data handing can be defined as a process through which data can be handled, stores,
disposed off in a secure and safe manner. There are various ways through which data can be
handled. Data handling lies within Business Intelligence as it helps an organization to manage,
store and analyse their data in order to reach to a conclusion. Business Intelligence can be
defined as a technology driven process which is used for data analysis and presentation of
information or data which can further be used by organizations to take important business
decisions (Tirpude, Karandikar and Welekar, 2020). BI helps in providing historical and current
data to be analysed so that predictive views for the business can be generated. Business
intelligence with data mining helps organizations to take effective and appropriate decisions and
develop future business strategies which not only helps them to achieve their defined goals and
objectives but also helps them to enhance their overall revenue and profitability. However, it can
also be said that data mining is a part of Business intelligence (Bayer and et. al., 2017). Both of
them are fruitful for business in different ways like, data mining helps in analysing patterns
within the data. Data mining helps in resolving any kind of issue where as BI helps in decision
making. The only difference between BI and data mining is that data mining is used for small
data set whereas BI is used for huge data sets. This assignment will lay emphasis on analysis of
data of Audi leadership and Superstore data in order to analyse overall sales and profit of the
organization. This assignment will also help in explaining different kinds of data mining methods
that can be used by organizations and advantages and disadvantages of Weka over Excel.
PART 1
By using data set of superstores analyse profit and sales over years and analyse it by using Excel
for pre- processing of data, also analyse and visualize the data
There are various kinds of tools and technologies that can be used for analysis of a data
set and can be used for extracting and analysing useful and important data in an appropriate
manner (Sasikala, Kalaiselvi and Scholar, 2016). These tools can be used by organizations for
analysing their yearly or quarterly sales or profit so that they can take important decision or
develop strategic plans in order to increase sales and profit. Excel is one of the most commonly
used data mining tools which can be used by organizations for analysis of their historical and
current data so that they can take appropriate decisions. It is one of the most commonly preferred
tools because it has various kinds of inbuilt functions that helps in doing calculations, calculate
1

sales, profit, extract and analyse important and required data, search doe a data or relevant value.
Most importantly it is used for organizing data or information in a useful manner so that data can
be understood and analysed appropriately (Hamed and et. al., 2017). Not only this organization
of data also helps in using formulas wherever and whenever required. Excel provides an option
to the user to implement required formula and generate graph or charts for better understanding.
One of the functions in Excel that can be used by organizations for analysis of their overall or
historical sales and profit is Pivot table and graph.
Here, data of Superstore data will be analysed in order to analyse decline in sales and
profit over years.
 Sum of sales and sum of profit from 2009 to 2012
Row Labels Sum of Sales
Sum of
Profit
Furniture 5178590.542 117433.03
2009 1472671.724 61804.53
2010 1252518.416 9397.4
2011 1268656.078 50422.45
2012 1184744.324 -4191.35
Office Supplies 3752762.1 518021.43
2009 1035399.64 177646.27
2010 910359.95 118143.24
2011 796383.79 86960.01
2012 1010618.72 135271.91
Technology 5984248.182 886313.52
2009 1701825.482 194645.22
2010 1397208.679 237376.69
2011 1364905.113 242928.04
2012 1520308.909 211363.57
Grand Total 14915600.82 1521767.98
Figure 1 Sum of sales and sum of profit from 2009 to 2012
2

Figure 2 Sum of sales
Figure 3 Sum of profit
Data Interpretation: All the three graphs above clearly explain decline in sales and profit over
years. First graphs explain both sum of sales over years and sum of profit over years in one graph
so that comparison between both of them can be done. Other two graphs explain sum of sales
and profit individually. From all the threw graphs it has been interpreted that highest sales was
seen in 2009 in Technology department and highest profit was seen in year 2011 in Technology
department.
 Average sales and profit of Technology over years
Row Labels
Average of
Sales
Average of
Profit
Technology 2897.941008 429.2075157
2009 3145.703293 359.7878373
2010 2631.278114 447.037081
2011 2916.463917 519.0770085
2012 2895.826493 402.5972762
Grand Total 2897.941008 429.2075157
3

Figure 4 Average sales and profit of Technology
Data Interpretation: From the above graph it can be clearly interpreted that average sales of
Technology was lowest in 2010 and average profit of technology was lowest in 2009.
 Average sales and profit of Furniture over years
Row Labels
Average of
Sales
Average of
Profit
Furniture 3003.82282 68.11660673
2009 3287.21367 137.9565402
2010 2846.632764 21.35772727
2011 3035.062388 120.6278708
2012 2834.316565 -10.02715311
Grand Total 3003.82282 68.11660673
4

Figure 5 Average sales and profit of Furniture
Data Interpretation: From the above graph it can be interpreted that Average sales of furniture
was highest in 2009 and lowest in 2010 whereas it cannot be determined properly for Average
profit but from the table it can be interpreted that average profit of furniture was highest in 2009
and lowest in 2012.
 Average sales and profit of office Supplies over years
Row Labels
Average of
Sales
Average of
Profit
Office Supplies 814.0481779 112.3690738
2009 885.7139778 151.9643028
2010 778.0854274 100.9771282
2011 716.172473 78.20144784
2012 871.9747368 116.7143313
Grand Total 814.0481779 112.3690738
Figure 6 Average sales and profit of office Supplies
5

Data Interpretation: From the above graph it can be interpreted that average sales of Office
supplies were highest in 2012 and lowest in 2012 whereas average profit of Office supplies was
highest in 2009 and lowest in 2011.
Demonstration of ways in which data can be practically analysed using Excel functions such as
Lookup, Pivot table, graphs and charts
There are various kinds of in-built function present in Excel. Out of all of those lookup
and Pivot table are most commonly used in-built functions of excel.
Lookup Function: This function has been categorised in Excel under Lookup and
Reference functions. This function helps in performing rough match lookup either in one row or
in one column (Massaro and et. al., 2019). It is basically used for selecting values from a defined
range. It is mostly used in financial calculations in which comparison of two rows or column is
required to be done, for this, lookup function can be used. It is basically designed to handle
simplest case of horizontal and vertical look up as well. This formula is basically applied to the
data which is assembled in a particular order. It is basically used for a large amount of data and
in order to find out value of an appropriate data from a huge set of data. The basic syntax used
for lookup function for searching a data is
=LOOKUP (value, loopup_range, [result_range])
For example:
If user wants to find average sales valie in 2011 from the below data then lookup finction can be
used
Row Labels
Average of
Sales
Average of
Profit
Furniture 3003.82282 68.11660673
2009 3287.21367 137.9565402
2010 2846.632764 21.35772727
2011 3035.062388 120.6278708
2012 2834.316565 -10.02715311
=LOOKUP(A5,A3:A6,B3:B6)
3035.062388
6

Pivot Table: Out of all the in-built functions pivot table and pivot graph are one of the most
powerful and most commonly used in built functions of Excel (Trivedi and et. al., 2017). It is
used to extract required and important data in order to be analysed in a proper manner.
 Sum of profit and sum of sales region wise
Row Labels
Sum of
Profit Sum of Sales
Atlantic 238960.66 2014248.204
North Carolina 2841.11 116376.4835
Northwest
Territories 8307.05 83817.746
Ontario 439214.57 3780242.063
Prarie 321160.12 2837304.602
Quebec 140426.65 1510195.08
West 297008.61 3597549.276
Yukon 73849.21 975867.371
Grand Total 1521767.98 14915600.82
Figure 7 Sum of profit and sum of sales region wise
 Average sales and Average profit region wise
Row Labels
Average of
Sales
Average of
Profit
Atlantic 1865.044633 221.2598704
North Carolina 1473.120044 35.96341772
Northwest Territories 1420.639763 140.7974576
Ontario 1749.302204 203.2459833
Prarie 1663.132826 188.2532943
Quebec 1933.668476 179.8036492
West 1806.905713 149.1755952
7

Yukon 1800.493304 136.253155
Grand Total 1775.878179 181.1844243
Figure 8 Average sales and Average profit region wise
 Average sales and Average profit of Ontario and Prarie region
Row Labels
Average of
Sales
Average of
Profit
Ontario 1749.302204 203.2459833
Georgina 1961.88645 209.2537984
Hanover 1550.93624 193.4693109
Ontario 1778.693177 168.9889851
Orangeville 1580.510948 222.3565569
Waterloo 2140.386816 275.659791
Prarie 1663.132826 188.2532943
Manitoba 1731.209057 172.0392938
Saskachewan 1604.004183 202.3362103
Grand Total 1711.286957 196.6316757
8

Figure 9 Average sales and Average profit of Ontario and Prarie region
PART 2
By using audidealership.csv file show conjunction with Weka with the example of clustering
Figure 10 audidealership data relationship
9

Figure 11 Hierarchical clustering
10

Figure 12 Cluster data using the k means algorithm
11

Explanation of commonly used data mining methods that can be used in business. Explain them
with real time example
Data mining can be defined as a process which is used by organizations for analysis of
raw data for extraction of useful and important information. It is used for analysing patterns
within the data and also for resolving any kind of issue faced by the organizations. it can also be
used by organizations for gaining more knowledge about their customers, their buying pattern or
behaviour so that they can develop effective and appropriate strategies in order to increase their
overall sales, revenue and profit (Zhou and et. al., 2017). Some of the most commonly used data
mining methods are as follows:
Association: This data mining method is used by organizations in order to find correlation
between data. It is mostly used by business organizations for identification of hidden patterns
within the data so that relationship between selected data can be identified. It is mostly used for
decision making and for strategic future planning (Saleem and et. al., 2019). For example: this
data mining method can be used by retail sector organizations for identification of their
customers buying behaviour or pattern. Such as: if a supermarket wants to check what
combination of items are mostly sold by them, like, more than 70 percent of customers who buy
milk also purchase bread and approximately 18 percent of customers are those who purchase
both the items.
Classification: This method is used by organizations so that they can distinguish between data or
information on the basis of their features, behaviour and many more. This method can also be
used by banks, retail sector organizations. With the help of this method organizations can
categorise their data on the basis of some set criteria’s. For example, if a bank wants to
categorize their loans on the basis of income of customers who took loan and on the basis of risk
associated with each loan this method can be used.
12

Clustering Analysis: this method is similar to classification method but with little bit of
difference (Kavakiotis and et. al., 2017). This method is used segregate the data in small
segments on the basis of similarities and dissimilarities of the data or information. For example:
if an automotive organization wants to segregate their customers who have paid for the vehicle in
single time and those who have taken loan to purchase vehicle.
Prediction: it is a kind of data mining method which is used by organizations so that they can
predict future by analysing current and past data. It is one of the most important and valuable
method of data mining that can be used by organizations for predicting their future sales, revenue
and for many other factors (Mendes and Vilela, 2017). For this many other data mining methods
are clubbed together so that future can be predicted such as: trend analysis, pattern matching,
relation analysis and classification. It is mostly used by retail sector organizations for prediction
of next year sales and profit so that on the basis of this data they can plan their strategies
accordingly.
Advantages and disadvantages of Weka
Weka is the toll that mainly used for pre-processing, classification, regression and
association rules. It is data mining software that is very helps as this uses collection of machine
learning of algorithms. It is the algorithms that can be applied directly to the data and data and
java code. However, this is well suited for developing new machine of learning scheme.
Therefore, advantages and disadvantage of Weka are termed out in following context as are-:
Advantages of Weka-:
Weka data mining supports an enterprise that attains its fullest perspective. This is termed
out as approach that helps to evaluate how business becoming impacted with particular qualities,
supports company entrepreneur and leads to improve their earnings. In addition to this,
individual have already been utilising weka data mining for the years in different formats. Its
supports firm entrepreneurs advance the earnings and steer clear generating entity mistakes down
the line. This is one of the attractive computer software that supports an enterprise to evaluate
and analyse the whole information in more effective mode. In addition to this, it can be stated
that Weka mainly contains tools for data pre-processing, classification, regression, clustering,
association rules, and visualisation and this very helpful for business to collect crucial elements.
Disadvantages of Weka
13

Despite of all the above mentioned advantage, there are some of the disadvantage such as
excessive work intensity that needs investment in high performance team and staff training. The
of the disadvantage of Weka is that this experience problem with processing if the amount of
data becomes too much. This kind of the situation occurred because data mining tools tries to
load all of it into the memory. In order to avoid it, Weka offers the simple command line that
makes the things easier to handle large amount of data. There is also difficulty in collecting the
data.
CONCLUSION
Hereby, this can be summarized that data handling and business intelligence are defined
as a research area that links to intersection of the computer science, mathematics, artificial
intelligence and statistics. However, business intelligence and analytics are much more than the
technical advancement as this mainly used to collect and analyse data. This assignment has
covered the emphasis on analysis of data of Audi leadership. Furthermore, report has explained
different kinds of data mining methods that can be used by organizations. Lastly, advantages and
disadvantages of Weka over Excel has been defined.
14

REREFENCES
Books and Journals
Bayer, H., and et. al., 2017. Big data mining and business intelligence trends. Journal of Asian
Business Strategy. 7(1). p.23.
Hamed, M., and et. al., 2017. Using Data Mining and Business Intelligence to Develop Decision
Support Systems in Arabic Higher Education Institutions. In Modernizing Academic
Teaching and Research in Business and Economics (pp. 71-84). Springer, Cham.
Kavakiotis, I., and et. al., 2017. Machine learning and data mining methods in diabetes
research. Computational and structural biotechnology journal. 15. pp.104-116.
Massaro, A., and et. al., 2019. A business intelligence platform Implemented in a big data system
embedding data mining: a case of study. International Journal of Data Mining &
Knowledge Management Process (IJDKP). 9(1). pp.1-20.
Mendes, R. and Vilela, J.P., 2017. Privacy-preserving data mining: methods, metrics, and
applications. IEEE Access. 5. pp.10562-10582.
Saleem, H., and et. al., 2019. Novel Intelligent Electronic Booking Framework for E-Business
with Distributed Computing and Data Mining. IJCSNS. 19(4). p.270.
Sasikala, D., Kalaiselvi, S. and Scholar, M.P., 2016. Data Mining for Business Intelligence in
CRM System. International Journal of Multidisciplinary Research and
Development. 3(3). pp.198-200.
Tirpude, S., Karandikar, A. and Welekar, R., 2020. An Approach for Environment Vitiation
Analysis and Prediction Using Data Mining and Business Intelligence. In Smart Trends
in Computing and Communications (pp. 327-338). Springer, Singapore.
Trivedi, S.K., and et. al., 2017. Handbook of research on advanced data mining techniques and
applications for business intelligence. IGI Global.
Zhou, Q., and et. al., 2017, April. An Advanced Inventory Data Mining System for Business
Intelligence. In 2017 IEEE Third International Conference on Big Data Computing
Service and Applications (BigDataService) (pp. 210-217). IEEE.
15