LCBB5000 - Data Handling and BI: Excel, Data Mining, and SPSS Analysis

This report evaluates the use of Excel for data preprocessing, analysis, and visualization, highlighting its features for statistical analysis and reporting. It discusses functions like pivot tables and conditional formatting for efficient data evaluation and decision-making. The report also analyzes a sample dataset using a pivot table, identifying fluctuating growth trends. Furthermore, it describes various data mining methods, including association, classification, and clustering, and compares the advantages and disadvantages of using SPSS over Excel for data analysis. The document is available on Desklib, a platform offering AI-powered study tools and a wide array of past papers and solved assignments for students.

DATA HANDLING AND
BUSINESS INTELLIGENC
LCBB5000 ASSESSMENT 2

TABLE OF CONTENTS
PART 1............................................................................................................................................3
Evaluating use of of excel for pre processing , analysing and visualizing data........................3
PART 2............................................................................................................................................7
2.1.................................................................................................................................................7
2.2 Describing data mining methods.........................................................................................15
2.3 Describing the advantages and disadvantages of SPSS over excel....................................16
REFERENCES................................................................................................................................1

PART 1
Evaluating use of of excel for pre processing , analysing and visualizing data
Excel is one of the important tool that is utilized by various kinds of users to accomplish
certain objectives in effectual manner. It is widely taken into consideration for manipulating
rows and columns for conducting statistical analysis. This is found to be convenient as
provides features like simpler , faster and more intuitive. The particular specified tool
empowers users to get understandability by providing command for suing certain relatable
formulas which can accomplish objectives of users (Zhou, 2020.). It allows the users to get
features of providing ranks, trends, outlier, majority ,etc. This provides assistance in having
proper knowledge regarding particulars subject matter through storing, summarizing and
analysing selected range of data.
There are variety of functions available in excel that helps in proving various different abilities
to meet requirements in easy and manageable manner. In addition to this, it includes LEN,
count, net workdays, sum, average, if, error, v & h lookup, find, left and right, ranking, min,
max, sum product, etc. each function provides certain level of advantages to users that helps in
having capability of using advanced features. Concatenate aids in combing numbers, data and
test which is is helpful in obtaining excellent functions to attain the purpose in effective
manner. It becomes possible to have capability to store, analyse and summarize data in better
manner so that including complex data set of business practices can be exerted. With help of
mentioned functions user can get several distinct benefits which can help in attaining higher
efficiency in conducting complex practices in efficient manner. In order to coordinate with
changing circumstances excel upgrades and provide different new features and functions so that
modifying requirements of customers can be met. Recording, summarizing and analysing the
statical data becomes crucial in current business environment so that appropriate decision can
be taken.
In modern era, excel plays significant role in providing efficiency and accuracy by
conducting data evaluation in turn accurate decision can be taken. There are various distinct
kinds of functions available such as pivot table that provides ability to mold data as per
requirement. With help of pivot table it becomes possible to set relationship among two or
more variables so that significant result from this can be derived. Checking for miscreant data
become possible with this specified analytical tool. In order to store data excel provides higher

efficiency to drag and drop the set of information which can be easily summarized. For the
purpose of visualization, there are various types of chart, graph are present in excel that gives
larger emphasis on presenting information in understandable manner. Organizations usually
prefer to prepare reports on weekly, monthly and annually basis so that analysing data can
provide crucial factors to identifying prevailing irrelevant actions that can be eliminated in order
to have proper processing (Irafahmi and Williams, 2021). It provides users to get the proper
functioning by making reports with help of present features for deriving accurate & fair reports
in turn ascertainment of financial as well non monetary aspects can be recognised to boost
organizational performance.
The most crucial functions which are largely taken into practice are pivot table, v and h
lookup, conditional formatting, etc. Pivot table is used to summarize large data into short
manner. In addition to this, pivot table chart provides advanced patter of reflecting data set in
easily manner (Dunbar, 2020). Conditional formatting is as well one of the function utilized
analytics as it gives ability to provide information by highlighting according to set standard. In it
less or more than and equals requirement can be set to derive the data highlighted which aids
in formulating decisions in quicker manner. In addition to this, v & h look refers to vertical and
horizontal for looking information available in particular column and row respectively.
Irrespective of scale of operations conducted by companies these function largely contribute in
evaluating data in proper pattern. IF function gives ability to as well find that the particular
result or statement is true or false in turn less time need to be incurred for having accurate
assessment.
In organizational practices there are various changes occur due to different factors
which are required to focused for removing those practices that are not largely contributing in
success (Raubenheime, 2017). Excel can be used by firm to identifying prevailing trend by
setting relationship with various variables. There are distinct manner to reflect the data in
presentable manner like graphs, pie chart, etc so that visualization of complex data set can be
done in easy manner. In order to create pivot tables specified firm can take following steps:
 In order to create pivot table user is required to go to insert tab in which specific icon
will be reflected that will show a pop up will appear. Selection of table or range of data
will be done automatically as there is default feature in built in excel (Pivot Tables,2021).

In the dialogue box excel will ask location where you want to have pivot table such as
existing or new worksheet.
 The next step is concerned with appearing fields pane which will comprise row,
columns value and filters . Sorting option is as well available for filtering the data set in
turn summarized form of tabular format can be attained.
With help of these mentioned in instructions below table has been presented by filtering data
in quarters fro each years. The pivot table has been made made by dragging sales and
profitability in field pane and in values sum has been selected for getting proper knowledge
regarding the provided data set.
Column Labels
Row Labels <1/1/2009 Qtr1 Qtr2 Qtr3 Qtr4
<1/1/2009
Sum of Sales
Sum of Profit
2009
Sum of Sales 384000.468 583155.794
5
369589.
014
417315.
919
Sum of Profit 18509.72 47701.7 63265.3
6
22776.2
1
2010
Sum of Sales 338003.962
5
260170.541 337843.
7215
382849.
19
Sum of Profit 28030.26 17129.61 40106.6
4
46888.3
9
2011
Sum of Sales 366284.752 434479.037 291268.
3345
381322.
467
Sum of Profit 36982.13 61150.01 26314.7
7
36967.2
2
2012

Sum of Sales 472812.385
5
356108.784
5
363187.
272
409443.
6835
Sum of Profit 42316.3 36221.4 36743.1
2
15686.1
5
Total Sum of Sales 1561101.57 1633914.15
7
136188
8.342
1590931
.26
Total Sum of Profit 125838.41 162202.72 166429.
89
122317.
97
From the above presented table it can be concluded that entity is facing fluctuating
growth trend in which it is moving ups and down. In addition to this, it can be articulated that
profitability & sales for the year 2009 has inclined in initial three and then in fourth quarter
moved in upward direction. In four quarters of 2010 in second period it has decreased and
uplifted in both sales and profitability. 2011's quarters are presenting that in first two
mentioned set of period profitability and sales is enhancing then declined for rest two duration.
In 2012 the outcome movement is following same trend as 2011. On the basis of given
interpretation it can be articulated that organizations profits & revenue is not constant and
moving up & down which is reflecting ineffective financial performance.
This can be justified from the opinion of DaDalt and Wolf (2020)There are various types of
factors that are required to be considered as results in declining performance of company.
These factors includes all uncontrollable aspects which can largely emphasised on providing
both positive and negative impact on organizational growth (Morris, Deochand and Peterson,
2018). This includes interest, unemployment, purchasing power, political stability. GDP,
changing taste & preferences of customers, government rule, regulations, industrial legislation,
environmental practices, etc. These can not be easily controllable and required to be cope up
with changing circumstances of business environment so that sustainability can be gained. In
contrast to this, Alrousan and et.al., (2021) depicted that company largely get influenced from
its strategies of risk management, capital structuring, operational expenses, selection of
financing option, etc. These elements keeps on changing in turn its effect as well affect
organizational growth. In order to be prompt and effective it becomes crucial of the firms to pay

attention on prevailing circumstances so that better ability to assess consequences can be
become possible.
From this it can be interpreted that specified organization's performance is fluctuating
and reflecting downward sloping movement. It can be identified that there are various distinct
aspects which may influence the growth and sustainability. On the basis of given data it can
stated that entity is providing which is ultimately declining profit margin as well revenue
generating capacity. The reason behind providing discount is to motivate and spread positive
vibe among customers regarding offerings so higher revenue & profitability can be gained. In
addition to this, firm require to pay full attention on gathered data so that prevailing trend can
be taken into practice for measuring current position in industry. With help of analysed pattern
it can be interpreted that specified entity is performing ineffectively which requires to put
larger emphasis on company growth and sustainability.
PART 2
2.1
K Means

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

Iteration Historya
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 teh evaluation of above table it can be interpreted that rice consumption in both
cluster are equaivalent where as in gender and age categories in cluster two there is less
numbers as comapred to other.
Number of Cases in each
Cluster
Cluster 1 54.000
2 41.000
Valid 95.000
Missing .000

Descriptive

Gender
Frequency Percent
Valid male 50 50.0
Female 50 50.0
Total 100 100.0
Age
Frequency Percent
Valid 13.00 5 5.0
15.00 5 5.0
17.00 8 8.0
18.00 13 13.0

19.00 20 20.0
20.00 5 5.0
22.00 21 21.0
23.00 1 1.0
25.00 12 12.0
26.00 10 10.0
Total 100 100.0
Rice
Frequency Percent
Valid .00 40 40.0
yes 60 60.0

Total 100 100.0
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
From the above illustrated table it can be articulated that mean, mode and median are
are different in all mentioned categories like age, gender and rice utilization.
2.2 Describing data mining methods
There are various data mining methods which are taken into consideration by the
organization in turn higher ability to analyse data ca be derive. It includes association,
classifications, clustering analysis, prediction, sequential pattern , decision tree, , outlier
analysis, neural network, etc.
 Association is the method that can be used by organizations to get the details by setting
relationship among two or more variables. It is exerted by conducting correlation
between two ore more variables in order to evaluate and predict customers behaviour.
The specific method is highly done by using single association and multi dimensional
rule.
 Classification is another tactic that distinguish the items in the data set into classes or
groups. It is done by applying the two steps such as learning and classification phase.
In case of the health issue treating medical companies conduct this particular plan into
the their research & development processes so that they can analyse which medicine
would be suitable for patients in respect to treat specific disease.
 Cluster analysis is the technique which depends on the data items by implementing
few methods such as hierarchical, grid, partitioning and model based methods. For
instance – in company managers usually uses this technique as it allows to make
group on the basis of similarities so that skills and attributes can be highlighted for
deciding pay scale.
 Sequential pattern is kind of method of data mining that occur over a period of time so
that on the basis of prevailing circumstances the decision can be formed. In times of
festival bakery can use this approach to forecast sales as in increasing trend by
referring all years performance.

 Decision tree helps in showing attributes, result of test, class labels, etc as it contains
internal node, branches denotes, terminal and top most so that data can be properly
described fro taking specific decision.
 Outlier analysis identifies the data items that do not comply with expected behaviour.
In this data mining method, unexpected items as outlier as they are helpful in
identifying irrelevant practices like fraud, intrusion, fault detection, etc.
 Neural network is based on biological neural network that is collection of neurons
like processing units with weighted connections between them. Is is basically used to
model the relationship between inputs and outputs by using regression analysis, data
processing, etc.
These are the few of data mining method which helps in having significant approach to
take decision from the visualization, analysed data so that particular form of requirement can eb
met.
2.3 Describing the advantages and disadvantages of SPSS over excel
There are many ways in which SPSS provides benefits to users so that their objectives
can be accomplished. It is easier to use this for having statistical aspects through testing them.
It contains variety of in built function that allows the users to get higher efficiency by having
fully equipped with complete set of statical test which is not possible in excel. Visualizing data
in SPSS is more efficient as compared to excel (Cleff, 2019). SPSS allows users to have
detailed charts and graphs for creating tables so that missing values and preventing data entry
can be easily done. With help of this feature of SPSS it is easy to interpret the statistical
information in respect to have strategic decision making. The overall output sheet is provided
to suers which is not possible in other forms of data analysis for implementing market research.
SPSS is capable of handling large amount of data and has great user interface. Excel is not that
efficient as compared to SPSS as it is not possible to visualize data as effectively (Hinton and
McMurray, 2017). There are distinct characteristic in SPSS which is one of the most significant
as there is availability of unlimited rows where as in excel there is limitation.
In against to this, there are few cons as well which need to highlighted for having
proper knowledge so that users can identified prevailing threats. In addition to this, it is essential
to derive such factors for deriving data that can not fulfil business objectives. The foremost

demerit for users regarding the SPSS is concerned that with the higher expenses that need to be
incurred for obtaining the software (Simanjuntak, 2020). Excel is completely free for users
which has certain tools for data analysis which provides larger benefit to users. Data
recording, summarizing and analysing crucial factors can be exerted by implementing , etc.
without paying any cost which is advantageous as compared to SPSS. There is requirement of
learning methods and ways to sue SPSS where as in excel users can properly implement this
actions in excel.

REFERENCES
Books and journals
Alrousan, M.K and et.al.,2021. Factors affecting the adoption of E-Marketing by decision
makers in SMEs: Evidence from Jordan. In Research Anthology on Small Business
Strategies for Success and Survival (pp. 887-915). IGI Global.
Cleff, T., 2019. Applied statistics and multivariate data analysis for business and economics: A
modern approach using SPSS, Stata, and Excel. Springer.
DaDalt, P. J. and Wolf, J. G., 2020. Sensitivity and Simulation Analysis in Excel Without
Programming. Available at SSRN 3623496.
Dunbar, L., 2020. The Other Part of the Job: Rapid Data Analysis with Excel and
Sheets. General Music Today. 33(2). pp.83-86.
Hinton, P.R. and McMurray, I., 2017. Presenting your data with SPSS explained. Routledge.
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.
Morris, C.A., Deochand, N. and Peterson, S.M., 2018. Using Microsoft Excel® to build a
customized partial-interval data collection system. Behavior analysis in practice. 11(4).
pp.504-516.
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
literacy for open and big data (pp. 153-193). IGI Global.
Simanjuntak, S.D., 2020. Statistik Penelitian Pendidikan dengan Aplikasi Ms. Excel dan SPSS.
Jakad Media Publishing.
Zhou, H., 2020. After Excel. In Learn Data Mining Through Excel (pp. 211-213). Apress,
Berkeley, CA.
Online
Pivot Tables. 2021. [Online]. Available through
<https://www.excel-easy.com/data-analysis/pivot-tables.html>
1