Business Intelligence: Data Analysis and Visualization with Excel and SPSS

This article discusses the effectiveness of Microsoft Excel and SPSS software in analyzing and visualizing data. It covers topics such as decline in sales, pre-processing analysis, data visualization, K mean clustering, and more. The article also explores how data analysis can help in decision-making and providing better suggestions to customers.

Business Intelligence

Contents
INTRODUCTION...........................................................................................................................3
Part 1................................................................................................................................................3
Decline in sales over the years with data analysis.......................................................................3
Pre-processing analysis and data visualising with excel.............................................................5
PART 2............................................................................................................................................7
Number of customers of smile clinic who eat rice in their daily meal for healthy diet...............7
Number of male and female customers of Smile clinic.............................................................10
Mean and median value of age of Smile clinic customers........................................................12
K mean clustering......................................................................................................................14
Different data mining methods, used by real world businesses.................................................15
Advantages and disadvantages of using excel and SPSS..........................................................16
CONCLUSION..............................................................................................................................16
REFERENCES..............................................................................................................................18

INTRODUCTION
Business intelligence can be known as technical infrastructure that helps companies in
collecting, analyzing and storing big data an improving decision-making process (Marian and
et.al., 2018). This study is going to discuss effectiveness of Microsoft excel tool and ways in
which it can help employees in analyzing and visualizing data. It will identify declining in sales
of superstore with the help of excel. Further, it will discuss effectiveness of SPSS software and
ways in which it can help employees in knowing relation between two factors and knowing the
number of females and males that have visited smile clinic for dental treatment. On the basis of
data analysis and making use of SPSS as well as excel, it becomes easier in taking decision and
providing better suggestions to customers.
Part 1
Decline in sales over the years with data analysis
Years Sales
2009 4209896
2010 3560087.04
2011 3429944.98
2012 3715671.65

Interpretation: Superstore wanted to know its sales of 2009-2012. The main reasons of knowing
sales is to take actions for improving sales as it can help it out in knowing decreasing in sales and
increasing in sales. Accordingly, it can know some reasons of decline as well as increasing in
sales. On the basis of reasons, it can take better decisions of taking actions for improving sales.
As this superstore operates to the great extent and have big data. It becomes difficult to analyse
big data but excel has number of great features and this feature has been used for analysing its
big data and knowing sales of this superstore of 2009-2012. On the basis of above graph, it can
clearly be said that sales in the year of 2010 declined. As compared to 2009, sales of superstore
decreased to 649809. It is the huge decline in sales. It is important for this superstore to identify
all those factors that has declined its sales (Pingping, 2017). There are number of internal and
external factors that may affect sales and performance of company. Internal factors can be
identified with number of ways. Feedback from customers and employees can be known as one
of the best ways. By doing so, superstore can know all those areas where it needs to make
improvements. It can help this superstore in increasing sales by improving all those areas in
which it is lacking behind. Along with this, it can make use of SWOT analysis. It can help this
superstore out in identifying internal factors that may create barriers in accomplishing goals.
With SWOT analysis, it can identify its own strengths, weaknesses, opportunities and threats as
well. Overall, it can be said that data analysis can help this supermarket out in increasing sales by
identifying reasons of decline in sales.
As compared to 2010, sales in 2011 again declined to the great extent. In 2010, sales of
this supermarket were: 3560087 and in 2011, it declined and sales were: 3429944. Sales declined

by 130143. In this regard, it can be said that sales is getting declined to the great extent in each
year. It is important for this supermarket to focus on identifying areas as soon as possible
otherwise; it may lose its position in the market. Competition is increasing to the great extent and
for being in the competition; it needs to satisfy customers’ needs. Sales can be declined for many
reasons such as: threats from external environmental factors, changes in internal factors,
increasing competition, changing needs of customers, lack of innovation and creativity in
production process. So, on this basis, it can be said that superstore must analyse external
environmental factors. PESTLE is one of the best tools or ways of analysing external
environmental factors. It can help this company out in knowing political changes and factors,
economic condition of countries, social changes, technological changes and others. On this
analysis, it can identify reasons of declining sales. Accordingly, it can take corrective actions and
can regain its image in the market. It can suggest to this superstore that it must use advanced
technology for easier access to customers for buying products and services. It can invest in
promotional activities. Social media marketing is one of the best ways of promoting its goods
and services and it can grab attention of customers by making them aware about effectiveness of
its products.
In the year of 2012, sales of this superstore were: 3715671 and in 2011, it was 3429944. On this
basis, it can be said that in the year of 2012, sales of this superstore increased and it increased to
the great extent. There was: 285727 increment in sales. This superstore needs to identify those
factors that increased its sales. It needs to identify its own strengths and strategies that it used in
the year of 2012. By analyzing both internal and external factors of 2012, it can know
effectiveness of its strengths and strategies (El Hammoumi and et.al., 2018). It can help this
superstore out in focusing on all these factors and improving more by which it does not suffer
more in the future. Overall, it can be said that this superstore was facing some number of
problems due to declining in sales. It can provide online buying opportunities to customers and
by delivering products at its home door, it can increase sales and can increase customers’
experience.
Pre-processing analysis and data visualising with excel
Companies collect big data and for making decision, they need to manage them. It is not possible
for them to manage big data manually and improving decision making process. For this reason,
they make use of number of effective software and implementation of software may be costly.

Excel is known as one of the best Microsoft office software that is cheap to implement and easier
to use for managing and processing data. Accounts and financing departments use this tool for
managing and evaluating data.
Data pre-processing: It is known as one of the best ways of data mining. It converts big raw
data in easier form and it makes easier for employees to understand data and analysing them.
Calculation of data in excels and its first string is one of the best examples of pre-processing of
data. There are number of steps that need to be followed by financial manager and employees for
data pre-processing. All steps need to be followed in a systematic manner. Some common and
important steps include: acquiring dataset, identifying missing values for protecting from
failures, encoding categorical data, splitting dataset and features scaling. By transforming raw
data into accurate data with excel, decision can be taken in an effective manner. Data pre-
processing include: data cleaning, normalisation, instance selection, encoding, feature extraction
and selection.
Data visualising: Excel is spreadsheet that has cells as well as functions. All these functions and
cells can help employees in converting complex and difficult big data into understandable. By
understanding data, employees can analyse them and can make decision accordingly. By
applying formulas of average, sum and others, employees can get outcomes as per their needs.
There is no need to recheck them and manually calculating them (Tierney, 2017). Formulas in
excel gives accurate outcomes. In addition, it can be said that it is a graphical representation of
data. It is easier for employees to make other understand about company’s information with
graphical representation. Overall, it can be said that it is one of the best ways of communication
with others and let them know about eh actual and current situation of company.
Data analysis: There are number of information that needs to be recorded by company on daily
basis such business expenses and business’ sales. Excel can be use for many reasons such as data
extraction, pre processing and visualising. Excel makes employees able in storing their big data.
Employees can maintain as well as analyse data when they have proper knowledge of managing
them. There is need to provide training to employees by which they can take decision and can
maintain data in an accurate manner (Delgado and et.al., 2018). There are number of features
available in excel such as: graph, tables and others. Data analysis can help employers out in
predicting sales. It can help them out in identifying difference in sales, profit and other factors.
On the basis of these, they can take appropriate actions and can accomplish goals.

PART 2
Number of customers of smile clinic who eat rice in their daily meal for healthy diet.
Statistics
Rice
N
Valid 100
Missing 0
Mean .6000
Median 1.0000

Rice
Frequency Percent Valid Percent Cumulative
Percent
Valid
N
o40 40.0 40.0 40.0
Y
e
s
60 60.0 60.0 100.0
T
o
t
a
l
100 100.0 100.0

Interpretation: Smile clinic is popular and has number of customers but it is not possible for
manager of this smile clinic to calculate female customers and male customers manually. SPSS
is one of the best tools that can help this clinic out in identifying male customers and female
customers who eat rice. One of the main reason of knowing the number of customers who have
rice is to suggest them best diet by which they can improve their health. It can help this clinic out
in identifying the reason behind poor health and dental problems that people are facing. It is
important to know if there is link of poor health with having rice. Average or mean value of this
data is .60.Median value is 1. On the basis of mean value it can be said that among 100
customers, 60 total customers have rice and 40 do not eat rice (Cleff, 2019). On this basis, it can

be said that there may be link between poor health and rice because more than 50% of customers
have rice.
Number of male and female customers of Smile clinic
Statistics
Gender
N
Valid 100
Missing 0
Gender
Frequency Percent Valid Percent Cumulative
Percent
Valid
Male 50 50.0 50.0 50.0
Female 50 50.0 50.0 100.0
Total 100 100.0 100.0

Interpretation: After knowing the number of customers who have rice. It is important to know
number of male customers and number of female customers of smile clinic. So, for this purpose,
this analysis is being used. On the basis of above graph, it can be said that 50% of female
customers of smile clinic have rice and 50% of male females have rice. There is equal number of
both male and female customers who have rice. On this basis, it can be said that both male and

female are suffering from poor health due to not having nutritional values of food. So, in this
context, it can be said that both male and females need to have nutritional values of food in order
to improve their overall health. One of the best things that healthcare professional of smile clinic
can suggest to its customers is not to eat rice to the great extent.
Mean and median value of age of Smile clinic customers
Statistics
Age
N
Valid 100
Missing 0
Mean 20.3500
Median 19.0000
Age
Frequency Percent Valid Percent Cumulative
Percent
Valid 13.00 5 5.0 5.0 5.0
15.00 5 5.0 5.0 10.0
17.00 8 8.0 8.0 18.0
18.00 13 13.0 13.0 31.0
19.00 20 20.0 20.0 51.0
20.00 5 5.0 5.0 56.0
22.00 21 21.0 21.0 77.0

23.00 1 1.0 1.0 78.0
25.00 12 12.0 12.0 90.0
26.00 10 10.0 10.0 100.0
Total 100 100.0 100.0

Interpretation: It is important for this clinic is to know as which age group of customers need to
take care the most for improving their health. By calculating mean and median value of age of
customers on the basis of given data, better decision can be taken. On the basis of above graph, it
can be said that mean value of age of its customers is 20 and it shows average number. Whereas,
median value is 19 which means, customers of 19-22 age group needs to take extra care of them.
Customers of this age group are the one who are suffering from poor health. They may suffer
critical problems in the future if they do not take care of themselves. So, on the basis of this
calculation and mean, median value, it can be suggested to customers of this age group that they
need to improve their health by adopting habits like having nutritional values of food.
K mean clustering
Interpretation: K means clustering is one of the popular and simplest unsupervised machine
learning algorithms. This K means algorithm helps employees in identifying K number of
centroids and allocating every data point to the nearest cluster. The main reason of using this
software and algorithm is to find groups which are not labeled in data. It can also be used to
confirm business assumption about types of groups exists and unknown group in data set.

Different data mining methods, used by real world businesses
In regard to data mining, it can be said that it is the process of uncovering those patterns and
valuable information from big and large data set. In regard to effectiveness, it can be said that
this technique has improved decision-making process of organizations through insightful data
analyses. There is fact that technology continuously evolves to handle data at large scale and data
mining can help companies in deciding what they can do for improvements and others.
Classification: It is one of the best techniques that can be used to retrieve important
information about big data in order to make decision and accomplishing goals. As with name
itself it can be said that this technique can be used to classify data in different classes by which
they can be managed and analyzed in an effective manner. Clustering also do the same but there
are some differences between clustering and classification data mining technique. In clustering,
data analyst needs to have knowledge of different cluster (Ketui, Wisomka and Homjun, 2019).
Whereas in this type of technique, data analyst need to apply algorithm in order to know the best
way of classifying data into different classes. Email and Outlooks are best common examples of
classification technique. By using algorithm, employees can segregate their email or classify
them as per their needs.
Regression: Regression is one of the common techniques that is being used in SPSS in
order to identify as well as analyze relationship between 2 and more than 2 variables. The reason
of using this technique is to understand characteristic of variables and knowing which factor is
dependent and which is independent. This can help in getting outcomes and to proof results. It is
also used for prediction and forecasting.
Sequential patterns mining: It is also other data mining technique that is being used to
find patterns between data examples where values can be delivered in sequential manner. It is an
order set of numbers that follow a rule. Data analyst can use formulas for calculation and getting
better outcomes (Rezig, Achour and Rezg, 2018).
So, on the basis of above discussed methods, it can be said that data analyst can improve
their decision making process by making use of one of the methods as per their needs.

Advantages and disadvantages of using excel and SPSS
SPSS and excel are 2 different software in which excel is a kind of spreadsheet software
and SPSS is statistical. Some advantages and limitations of excel and SPSS software includes:
Advantages
 By comparing with SPSS, it can be said that excel is better and easier to understand and
powerful.
 In regard to SPSS, it can be said that it consists of many statistical methods by which data
analyst can analyze data in an effective manner.
 SPSS can manage large amount of big data and excel cannot do the same.
Disadvantages
 In regard to SPSS, it can be said that it is expensive to install and tough to understand
methods of using as compared to excel.
 Excel has number of functions as compared to SPSS such as Lookup, IF and others.
Overall, it can be said that SPSS is better than excel if it is being compared on the basis of
functionality and usage as excel cannot analyze big data (Santoso, 2017). But employers need to
provide proper training to employees for making use of statistical functions in SPSS for better
outcomes and it may increase training cost as compared to excel.
CONCLUSION
It has been summarized from the above study that Microsoft excel and SPSS are two best
Tools that can help companies in analyzing data by converting big raw data into understandable.
This study has further discussed difference between excel and SPSS and reasons of using both of
these tools. It has discussed ways in which SPSS can make employees able in calculating total
number of customers that have visited their store. It saves their time and gives accurate
information. It can also help them out in calculating number of male customers and number of
female customers. Both of these software and tools help them out in improving decision-making
process and solving complex problems.

REFERENCES
Books and Journal
Cleff, T., 2019. Applied statistics and multivariate data analysis for business and economics: A
modern approach using SPSS, Stata, and Excel. Springer.
Delgado, H. and et.al., 2018, June. ASVspoof 2017 Version 2.0: meta-data analysis and baseline
enhancements. In Odyssey 2018-The Speaker and Language Recognition Workshop.
El Hammoumi, A. and et.al., 2018. Low-cost virtual instrumentation of PV panel characteristics
using Excel and Arduino in comparison with traditional instrumentation. Renewables:
Wind, Water, and Solar. 5(1).3 pp.1-16.
Ketui, N., Wisomka, W. and Homjun, K., 2019. Using classification data mining techniques for
students performance prediction. In 2019 Joint International Conference on Digital Arts,
Media and Technology with ECTI Northern Section Conference on Electrical, Electronics,
Computer and Telecommunications Engineering (ECTI DAMT-NCON) (pp. 359-363).
IEEE.
Mariani, M. and et.al., 2018. Business intelligence and big data in hospitality and tourism: a
systematic literature review. International Journal of Contemporary Hospitality
Management.
Pingping, J., 2017. Application Comparison of Excel Statistical Functions. Computer &
Telecommunication. (4). pp.66-68.
Rezig, S., Achour, Z. and Rezg, N., 2018. Using data mining methods for predicting sequential
maintenance activities. Applied Sciences, 8(11), p.2184.
Santoso, S., 2017. Menguasai Statistik dengan SPSS 24. Elex Media Komputindo.
Tierney, N., 2017. visdat: Visualising whole data frames. Journal of Open Source
Software, 2(16), p.355.