Data Handling and Business Intelligence

This article discusses the use of Excel for data pre-processing, analysis and visualization. It evaluates the most common data mining methods like classification, clustering, regression, prediction and association rules. It also provides insights on decision making and future predictions.

DATA HANDLING AND
BUSINESS INTELLIGENCE
1

TABLE OF CONTENTS
TABLE OF CONTENTS................................................................................................................2
PART A...........................................................................................................................................3
Evaluate the use of Excel for pre-processing the data, analysing the data and visualising the
data...............................................................................................................................................3
PART 2............................................................................................................................................7
2.1................................................................................................................................................7
2.2 Describing the most common data mining methods............................................................13
2.3..............................................................................................................................................14
Advantages and disadvantages of SPSS over Excel..................................................................14
REFERENCES..............................................................................................................................17
2

PART A
Evaluate the use of Excel for pre-processing the data, analysing the data and visualising the data
Excel is utilized for various purposes in turn ability to accomplish the objectives can
become successful. Data processing is the procedure which is concerned with converting the raw
information is understandable manner. The reason behind it is to get the ability to have analyse
data in order to formulate appropriate decision. There are several features available in excel that
provides assistance in exerting data mining, analysing and decision making procedure efficient.
Use of excel in data processing become possible due to presence of different kind of
features such as is number, find, mid, if error, if, trim, count blank, etc. These widely contribute
in achieving ability identify the relevant pattern, lacking areas so that strategic decision can be
formulated (Irafahmi and Williams, 2021). Data analysis is concerned with evaluating each
segment of provided data to have sufficient & reliable kind of information for gaining accuracy.
Spreadsheet is great tool for fitting solutions as it emphasizes on extracting actionable insights.
This allows to get the various advantages to establish relationship so that relevant pattern can be
identified. It organizes the raw data into readable format that permits the user to customize field
and functions for gaining proper visualization. Full scale data product can be built by applying
any of the suitable function into cells which allows to obtain proper format that can be visualized
by using different displaying tools like charts, etc. in order to match the changing requirement
of customers excel pay attention on upgrading its features which is highly beneficial for users.
Data analysis can be done by applying pivot table tool available in excel which provides
assistance in making large statistical into desirable manner. Establishing relationship can be done
by users through taking data analytical tool pivot table, graph, harts, etc. These mentioned
course of action allows to convert the data in organized and analyzable structure so that relevant
interpretation can be done. In excel there is availability of features like sorting, conditioning
formatting, filter, what if analysis, solver, etc. that permits the company to convert data
according to requirement. Advance excel play crucial role in all functional areas of organization
which allows to get significant amount of efficiency and accuracy. Report formulation by
utilizing the specified manner of tactics available in excel which provides different kinds of
aspects that are essential in obtaining higher level of accuracy & efficiency in turn strategic
3

decision can be taken. It is essential for the companies to draw conclusion regarding data via
utilizing advance analytical tool like excel for having ability to make proper strategic decision.
Excel is powerful and flexible tool used for analytical activity to get broad data
evaluation and visualization capabilities. For the purpose of presenting data graph, charts play
crucial role in reflecting complex and critical information in understandable manner. Making
comparison via setting relationship among two or more variables through graphical presenting
chart and bars can be exerted in effectual manner (Dunbar, 2020). From the evaluation it can be
articulated that company of any scale can use excel in order to make proper evaluation and
analysis of the projected data. In addition to this, it can be evaluated that companies in order to
make proper evaluation via conducting data analysis via taking the excel tool into practices.
These can give several different kinds of pros to organization which includes best way to store,
transform, clean, visualize, etc. which largely contribute in making appropriate decision. Data
analysis can be done by firm for making evaluation of market trends, competitors performance,
time consuming, proper integration, implication, streamlining, etc.
In excel there are several functions which can be used by companies to make proper
evaluation in turn higher productivity and profitability via considering the significant
components through identifying with help of available options. There are certain options in
excel that ensures accuracy and reliability which motivates the different kinds of users to utilize
this tool. These benefits can help in having capabilities of achieving the desire level of
information that can contribute in maximizing profitability & sales.
Pivot table can be sued by organization to have proper evaluation and analysis sales and
profit. It is widely sued by business analyst and other users to have proper ability to make
evaluation of vast static data to summarize, analyze and interpret in significant manner. It is one
of the systematic procedure that advanced features which enables user to get efficiency in
arranging data according to requirement (Harvey, 2018.). It includes few steps which can be
used to get guidance for making comparing of prevailing trends. The steps are as follows:
 Click on the cells of table range and then go to insert option available in the ribbon of
excel in next to home tab.
 In the next step pivot table option will be displayed which need to be selected in order to
obtain summarize data. For creating pivot table two questions will appear in form of
4

providing command in which selecting data and place to paste (Existing and new
worksheet) table will be asked.
 After this pivot field pane will appear which help in setting the relationship between two
or more variables. In this step the proper in rows, columns and um of values can be used
to have this kind of pivot table (Raubenheimer, 2017). By following this particular steps
below pivot table has been formulated For superstore to have depth knowledge for
analyzing and visualizing data in systematic manner. It is important to have information
regarding the prevailing trend to identify cause and improvement actions for achieving
success.
Column Labels
Row Labels <1/1/2009 Qtr1 Qtr2 Qtr3 Qtr4 Grand
Total
<1/1/2009
Sum of Sales
Sum of Profit
2009
Sum of Sales 384000.468 583155.794
5
369589.
014
417315.
919
175406
1.196
Sum of Profit 18509.72 47701.7 63265.3
6
22776.2
1
152252.
99
2010
Sum of Sales 338003.962
5
260170.541 337843.
7215
382849.
19
131886
7.415
Sum of Profit 28030.26 17129.61 40106.6
4
46888.3
9
132154.
9
2011
Sum of Sales 366284.752 434479.037 291268.
3345
381322.
467
147335
4.591
Sum of Profit 36982.13 61150.01 26314.7
7
36967.2
2
161414.
13
5

2012
Sum of Sales 472812.385
5
356108.784
5
363187.
272
409443.
6835
160155
2.126
Sum of Profit 42316.3 36221.4 36743.1
2
15686.1
5
130966.
97
Total Sum of Sales 1561101.57 1633914.15
7
136188
8.342
159093
1.26
614783
5.327
Total Sum of Profit 125838.41 162202.72 166429.
89
122317.
97
576788.
99
From the above table it can be interpreted that from profitability in four years are
fluctuating. In the above illustrated table, the figures of sales and profitability are shown in
quarters of each year. The sum of profitability & sales is for each quarter helps in understanding
details regarding the same. From the comparison it can be identified that in sum of each quarter
helps in getting the deeper insights about the performance of company. The overall
performance of profits are in declining pattern as from quarter 1 to 4 there is drastic change in
earning capacity of firm. Sales figure of four years' quarter indicates that it has previously
inclined and then declined which is presenting upward directional growth in quarter four. There
is requirement of improving results as it may be getting influenced from several different factors
which is ultimately requires higher attention.
This can be justified from the opinion of Martino (2019) who articulated that there are
various factors which influences the progress of organization. For leading in industry it becomes
essential to pay attention on several different aspects that results in organizational growth. The
most important aspect that required to be focused by companies includes internal processing
which can be controlled and managed by responsible authorities. It comprises organizational
strategy, structure, availability of resources, etc. in order to have inclined level of profitability for
the purpose of gaining sustainability. On the other side Batch and Elmqvist (2017) said that the
major element organization includes aspect external factors like political, social, economic and
legal that can affect the business. It highly influences the procedure of business in both the
positive and negative manner. The negative impact can result in declining figures of sales which
can affect the progress.
6

On the basis of evaluation, it can be interpreted that sales & profitability are fluctuation
g and can affect the company negatively. In addition to this, it can be interpreted that that
organization can get affected due to increase in cost. The reason behind this can be said that
lower profitability will not allow the firm to cover its operational and declined revenue can
influence in overall growth of company. The cause of such factor is that it can be understood
by giving deeper emphasis on components that are increasing profitability. It can be impacted by
discount policy of firm which is provided by company to its customers for boosting growth and
development via covering larger range of market share. For having higher level of sustainability
firm can focus on inclining the attention of customers towards organizational products &
services. Implementing discount policy, marketing & promotional practices can help in having
the ability to attract customers for gaining competitiveness and profitability to lead in industry in
effective pattern.
PART 2
2.1
K Means
7

8

9

Initial Cluster Centers
Cluster
1 2
Gender 1 2
Age 13 26
Rice 1 0
10

Iteration Change in Cluster
Centers
1 2
1 4.450 2.659
2 .241 .420
3 .000 .000
a. Convergence achieved due to no
or small change in cluster centers.
The maximum absolute coordinate
change for any center is .000. The
current iteration is 3. The
minimum distance between initial
centers is 13.077.
Final Cluster Centers
Cluster
1 2
Gender 1 2
Age 18 24
Rice 1 1
From the above table it can be interpreted that age & gender are greater in category 2 of final
cluster centers. Rice utilization is same in both the parts of mentioned table.
Number of Cases in each
Cluster
Cluster 1 54.000
2 41.000
11

Valid 95.000
Missing .000
12

Descriptive
Gender Age Rice
N Valid 95 95 95
Missing 0 0 0
Mean 1.53 20.33 .59
Median 2.00 19.00 1.00
Mode 2 19a 1
Sum 145 1931 56
a. Multiple modes exist. The smallest value is shown
The information regarding the mean, mode, median and sum has been illustrated above.
The valid number in all the category are 95 and sum of rice utilization is less as compared to
other gender and age.
2.2 Describing the most common data mining methods
There are several approaches that can be used by businesses for having for mining data in
effectual manner. Each method provides cretin level of benefits and drawbacks which required to
be understood by making proper evaluation according to firm need. It includes classification,
clustering, regression, outer, sequential patterns, prediction, association rules, etc.
 Classification is one of the common method that can be sued by companies for exerting
data mining actions (Mendes and Vilela, 2017). It can be done effectively according to
the prevailing requirements such as type of data sources, database, kind of knowledge,
etc. For instance- it is used for object oriented, transnational, relational, etc.
13

 Cluster is division of information into groups by describing similar characteristics so that
better steps for gaining knowledge can be done. It can have used for having point of view
which is largely taken into consideration in fields of medical diagnostics, web analysis,
computational biology, etc. These is largely helps the organizations to be prompt in
decision making procedure.
 Association rules helps in discovering the links between the large data set. For example-
it is majorly sued in sales department of companies to get information regarding
correlational prevailing among factors (Gibert and et.al., 2018). This is usually based on
the two rules like single and multi-dimensional association rules.
 Prediction is used to analyze future trends on the basis of past and present action. This
provides assistance in having the ability of to have continuous valued function that
indicates missing numeric data values. Regression analysis is the best method that can be
used by firm for conducting future predictions.
 Decision tree is structure that is made by having each internal node, branches, etc which
allows to create presentable chart that highly focuses on having clarity regarding specific
subject matter.
 Sequential pattern focuses on identifying the frequently occurring pattern over the certain
time. This is utilized by firm to make proper analysis of market forces that has same
nature in certain duration such as in Christmas increasing trend of purchasing can be
seen.
2.3
Advantages and disadvantages of SPSS over Excel
In comparison to Excel SPSS has the following advantages,
Easy statistical accessibility:
SPSS is a dedicated software for statistics due to which it has building in functions which
help in the data analysis. Functions of SPSS is much easier and faster to work on than excel.
Easier Statistical tests:
Running statistical tests is very easy as the complete set are already preloaded in the
software which in comparison to excel is much easy and effective.
Pivot table:
14

Known as one of the main tools of SPSS Pivot tables. SPSS brings a very strong built in
function and add in tools which can be used for data analysis which with the help of strong Pivot
tables make SPSS better than Excel (Rice and et.al., 2017).
Quality of Tables and Graphs:
In SPSS the quality of graphs and charts help in statistical analysis of data. Also, the
method of creation of tables and report is very easy which provides much better quality that
Excel.
Disadvantages of SPSS over Excel:
Expensive:
The software of SPSS in comparison to Excel is very expensive due to which the users
tend to chose Excel for the data analysis. SPSS is expensive as compared to excel which may not
be affordable for students. In excel there are several functions which can allow users to be
prompt and effective in respect to accomplish the objectives that are offered freely (SPSS
Advantages And Disadvantages, 2018).
Requires training:
In comparison to Excel there are a very few people which can operate SPSS due to which
it becomes difficult for them to make full use of the SPSS software. This is why for the
utilization of SPSS a training is necessary so that the features can be exploited.
Graphical features:
The graph features on the MS Excel is much simpler to be made in comparison to SPSS.
This puts SPSS on a disadvantage as for the data analysis graphical representation is very
essential.
Complex
It is little complex procedure to understand which requires qualified training to proper
use (Barrera-Dia and et.al., 2018). On the others is one the easiest tool which can provide
larger emphasis on making simple to use.
Not suitable for large data set
SPSS major disadvantage is that it fails to analyse a very large set of data which can be
complex in Excel but is possible.SPSS is not suitable for large data set as compared to excel
15

which is one of the biggest disadvantage which required to be considered while using it. In
addition to this, excel is user friend which is lacking in SPSS.
These features can be obtained by users while utilizing the SPSS as compared to excel. In
order to achieve the advantages firm should pay larger attention on using SPSS.
16

REFERENCES
Books and journals
Barrera-Diaz, C. A and et.al., 2018. Discrete event simulation output data-handling system in
an automotive manufacturing plant. Procedia Manufacturing. 25. pp.23-30.
Batch, A. and Elmqvist, N., 2017. The interactive visualization gap in initial exploratory data
analysis. IEEE transactions on visualization and computer graphics. 24(1). pp.278-287.
Dunbar, L., 2020. The Other Part of the Job: Rapid Data Analysis with Excel and
Sheets. General Music Today. 33(2). pp.83-86.
Gibert, K and et.al., 2018. Which method to use? An assessment of data mining methods in
Environmental Data Science. Environmental modelling & software, 110, pp.3-27.IGI
Global.
Harvey, G., 2018. Excel 2019 for dummies. John Wiley & Sons.
Irafahmi, D. T. and Williams, P. J., 2021. ‘This Isn’t My Expectation’: Excel in Auditing. Asian
Journal of Business and Accounting. 14(1). pp.87-112.
Martino, J.C.R., 2019. Hands-On Machine Learning with Microsoft Excel 2019: Build complete
data analysis flows, from data collection to visualization. Packt Publishing Ltd.
Mendes, R. and Vilela, J.P., 2017. Privacy-preserving data mining: methods, metrics, and
applications. IEEE Access, 5, pp.10562-10582.literacy for open and big data (pp. 153-
193).
Raubenheimer, J., 2017. Excel-lence in Data Visualization?: The Use of Microsoft Excel for
Data Visualization and the Analysis of Big Data. In Data visualization and statistical
Rice, S., and et.al., 2017. Advantages and disadvantages of using internet-based survey methods
in aviation-related research. Journal of Aviation Technology and Engineering. 7(1). p.5.
Online
SPSS Advantages And Disadvantages. 2018. [Online]. Available through:
<https://www.ipl.org/essay/Spss-Advantages-And-Disadvantages-PCP7NHZZ26>
17