Analyzing Supermarket Sales and Profit Data with Excel and SPSS

This report presents an analysis of supermarket sales and profit data, utilizing Microsoft Excel for data preprocessing, analysis, and visualization. The report focuses on a four-year sales trend, employing pivot tables to interpret sales and profit figures across different modes of transport. It explores data mining techniques, including classification analysis, association rules, clustering, regression, and anomaly detection, to derive meaningful insights. The report also includes a specific example of clustering and frequency tables. Furthermore, it compares the advantages and disadvantages of using SPSS over Excel for data analysis. The analysis provides detailed interpretations and visualizations of the data to identify patterns, trends, and potential areas for business improvement, offering a comprehensive overview of data handling and its practical applications.

Data Handling
1

Table of Contents
INTRODUCTION...........................................................................................................................3
PART 1............................................................................................................................................3
PART 2............................................................................................................................................8
2.1 Specific example of clustering...............................................................................................8
2.2 Data mining methods that are used in business...................................................................13
2.3 Pros and cons of using SPSS over Ms- Excel.....................................................................14
CONCLUSION..............................................................................................................................16
REFERENCES..............................................................................................................................17
2

INTRODUCTION
Data set refers to collection of data and it consists of raw data. The businesses uses data in
order to obtain relevant outcomes from it (Chau and Phung, 2017). Then, decisions are taken on
basis of results obtained from it. In this data mining means process by which raw data is
transformed into useful info. This helps in gathering relevant info which is used by business.
There are many data mining techniques used. It depends in business needs. For example- Ms
Excel, SPSS, etc. are some tools for data mining. The data must be presented into graphs, tables,
etc. to present to audience.
The report will lay emphasis on analysing data of supermarket. Also, it will be explained
about data mining techniques and use of SPSS over excel.
PART 1
In this a data set is to be analysed of super store in order to find out decline in sales and
profits of store in all 4 years. Thus, for that MS excel will be used in which data will be analysed
and interpreted. Besides that, pivot tables will be used to analyse data in effective way. This is
because pivot table is easier to use and understand. It will also help in identifying pattern and
trend in sales and profits.
PIVOT TABLES
Year 2012
Interpretation- it is interpreted from data that in 2012 the avg sales from delivery truck was
5274.7 and from express air was 1312.5. In regular air mode of transport sales done was 1190.5.
Now, in terms of profit it delivery truck it was 137.5 and in express air was 218.6 and in regular
air profit was 160.4. Also, it is found that avg sales of 2012 from all three was 1767.9 and avg
profit was 162.9
3

Year 2011
Interpretation- the table shows that in 2011 avg sales through delivery truck was 5674.5 and
by express air it is 1000.0 and regular is 1145.9. The avg profit obtained from truck is 351.9 and
express air is 114.9 and regular is 175.4. Furthermore, avg total sales is 1716.6 and profit is 190.
3 from all 3 mode.
Year 2010
Interpretation- the above table state that avg sale from delivery truck is 5042.8 from express
air is 1276.6 and by regular is 1091.5. Also, avg profit from truck is 240.9 and by express is
198.0 and via regular is 153.0. Along with that, total avg sales is 1662.8 and in avg profit is
170.4 in 2010.
Year 2009
4

Interpretation- it is analysed that average sales during year 2009 is 1775 and profit earned is
181. However, average sales through delivery truck is 5373 and profit is 230. From express air
average sale is 1202 and profit is 148. Also, by regular air sales are 1199 and profit is 177.
Interpretation – from data it is examined that in 2009 the profit of delivery truck was 63,231
that increased to 71,795 in 2010. Also, it raise to 92,573 in 2011 but in 2012 profit declined to
40,032. However, in express air profit in 2009 was 23,273 which increased to 48,721 in 2010.
But in 2011 it declined to 31,616 and in 2012 it again increased to 44,173. In regular air the
profit in 2009 was 347,591 which decreased to 244,400 in 2010. In 2011 there was little rise in
profit as it was 256,120 and in 2012 it was 258, 238. Hence, it can be stated that delivery truck
was profitable in all three modes and regular air profits declined gradually.
Excel in pre processing of data
This is first step in data analysis where missing values or errors are determined and then
rectified. Here, data is arranged in such a way in various categories so that it becomes simple to
understand and analyse data (Chen, and et.al., 2018). For doing pre processing of data Excel is
common tool used in it. Here, basic calculation are done. From it graphs and tables are generated
as well. In this there are many functions which is performed that is defined as below
5

Look up – it is function is pre processing in which any value is identified within table. Thus,
concatenate is applied to merge two row or column. Furthermore, lower and upper function are
used in upper or lower pre processing of data. Trim function clear text in blank space in data set.
In look up it is used to verify statement that is true or not. Hence, data is cleaned in this step.
Pivot table- it is also a function in pre processing of data where data is sorted, reorganize, count,
sum, etc. Also, the data is included in form of rows and columns. Besides that, it helps in
analyzing data in effective way. Hence, for present data as well pivot tables are used. The
process is defined as
Step- 1 Select cells for which pivot table is to be created
Step- 2- Click on insert and then select pivot table
Step 3- choose data that is to be analysed in short template which appears. Then, select table or
range.
6

Step 4 – in table range verify range of cells
Step 5- then, select place where pivot table is to be created. Thus, select new or existing work
sheet.
Step 6- at last click on OK.
So, by following all these steps pivot table is created and data is pre processed. It enable
in analyzing data and generating results.
Excel in data analysis
The excel is also used in analyzing of data and generate results. Also, various other
calculation are done in it which allows in analyzing data (De, and et.al., 2020). Besides that,
sorting and filter of data is done to segregate data and then find out outcomes. The filtration is
done to examine data on certain criteria. Besides that, conditional formatting function highlight
cell in which it is dependent on another. The data needs to be arranged properly so that outcome
is obtained. Moreover, Excel contain various formula as well which is applied like average,
mean, mode, median, etc. hence, in this way data is analysed and accurate outcomes are
generated from it.
Besides that, it is also evaluated that in MS Excel there are other calculation as well
which is done in it. Moreover, there are some advance things in it such as regression, t test,
frequency, descriptive analysis, etc. they all are similar to SPSS as in this same function is
performed. The data analysis important in obtained outcomes. This is because it may impact on
7

visualization of data and overall results. Also, businesses decision making and strategies are
dependent on data analysis. However, graphs and tables are developed in Excel as well to make
data understand and interpreted. The analysis done has to be proper so that precise results are
generated.
MS excel in data visualization
It is important to visualize results and data in proper way so that it becomes easy to
communicate it to audience (Enders, 2017). Excel is also used for data visualization in which
there are many techniques used in it. This allows in showing or representing data with help of
tables, charts, graphs, etc. thus, it becomes easy and simple to visualize it and identify patterns
and trends from it. However, in Ms excel there are many options available of making charts and
graphs. It use depends on business needs and what type of data can be best used to present it. For
example- charts are bars, pie, columns, etc. thus, in all these data is presented in different way
that makes it easy to understand trends and patterns into it. Besides that, data is presented to
audience in visualize way. It helps in giving various views of data and representing outcomes in
effective way. Data visualization is also a part of excel in which charts are made.
Hence, it is found that Ms excel is used for pre processing, analysis and visualization of
data. This is useful in simplifying of data and then representing it to audience. So, these all are
some tools which is used in analysis of data and then obtaining outcomes (Hagenauer, and et.al.,
2019).
PART 2
2.1 Specific example of clustering
8

Interpretation- It can be interpreted that in cluster A there are those people who eat rice and
belong to age group of 16- 19. Also, in cluster B people belong to age group of 20 -22 who eat
rice.
Frequencies
Frequency Table
9

Interpretation- from above graph it can be evaluated that out of 100 samples, 50 are males
participants and 50 are female participants.
10

Interpretation- from graph it can be found that there were 60 samples who said that they eat
rice. But on other hand, there were 40 sample who said they do not eat rice.
11

Frequencies
Interpretation- by analysing data it is examined that the average age of customers is 20.35.
This means that many customers are youngsters and belong to age group of 19-22. Besides that,
median of age is 19 that is middle value of age.
12