Data Mining Techniques: Analyzing Sales and Profit with Excel & Weka

This report provides a comprehensive analysis of data handling techniques using Excel and Weka. It begins by detailing how Excel can be used for preprocessing data, specifically focusing on calculating sales and profit over the years using functions like IF, LOOKUP, pivot tables, charts, and graphs. The report then transitions to data mining methods applicable within a business context, highlighting the advantages and disadvantages of using Weka compared to Excel. Specific methods discussed include association, classification, clustering analysis, prediction, pattern tracking, decision trees, neural networks, and outlier analysis. The report uses examples to illustrate each method, such as predicting customer behavior in supermarkets and identifying credit risk in the banking industry. Figures and tables are included to illustrate data interpretation and clustering techniques. Finally, the report concludes by summarizing the key findings and emphasizing the importance of data mining in modern organizations.

DATA HANDLING
1

CONTENTS
INTRODUCTION...........................................................................................................................3
PART 1............................................................................................................................................3
Determine the sales and profit over years and evaluate the use of Excel for pre-processing the
data...............................................................................................................................................3
Demonstrate that how it can practically with use of Excel function such as if, Lookup, Pivot
table, charts and graphs................................................................................................................6
PART 2............................................................................................................................................9
Common types of data mining methods that can be used within a business...............................9
Advantages and disadvantages of Weka over Excel.................................................................12
CONCLUSION..............................................................................................................................13
REFERENCES..............................................................................................................................14
2

INTRODUCTION
Data mining is based on the process of finding the patterns, correlations and anomalies
within large data set. In order to predict accurate result or outcome. The data mining process will
support for marketing assistant to increase the revenue of information, cut cost while improving
the customer relationship. Afterwards, it will reduce the various risks and so on. Data mining is
consider as important factor which consider the large volume of data or information. In another
word, data mining is exploring and analyzing the large amount of data which easily discover
meaningful pattern, rules and facts. It will consider as a discipline under specific data science
and offer from predictive analytics. Furthermore, it will described the sales information while
primary aim is to predict future result or outcome. In additional, data mining is an appropriate
technique to build machine learning model that power in term of artificial intelligence.
PART 1
Determine the sales and profit over years and evaluate the use of Excel for pre-processing the
data.
It is based on the data mining technique always support for calculating the sales and
profit over years. It provides a brief idea about the critical success of modern data drive
organization. It is powerful tool that transform enterprise, however, it is also implementing the
process to meet the specific need of business. Sometimes, it is frequently stand in the better way
to successful process data mining.
Excel is consider as standard for spreadsheet software that enable to calculate the budget,
financial forecasting and various data entries. The primary function is to organise large amount
of data or information in proper manner. Furthermore, it can easily analyse the data to create
graphs, charts. In order to represent in visualise manner.
Row Labels Average of Profit Sum of Sales
Furniture 68.11660673 5178590.542
2009 140.1369955 1469508.194
2010 20.65391403 1250043.046
2011 115.326226 1258336.514
2012 -5.173357143 1200702.788
Office Supplies 112.3690738 3752762.1
3

2009 153.4285381 1031244.56
2010 97.14263473 885095.79
2011 80.42802855 816902.13
2012 117.6447423 1019519.62
Technology 429.2075157 5984248.182
2009 337.0125974 1668572.052
2010 474.5130402 1416503.546
2011 518.2162105 1380213.417
2012 398.3725568 1518959.168
Grand Total 181.1844243 14915600.82
Figure 1
Data Interpretation
By using excel, it can be calculating the Average profit and sum of sales. It can be
identified the profit and sales of furniture. Furthermore, it also identified the average profit in
year wise so that it become easier to identify the accurate information of furniture.
4

Figure 2
Data Interpretation
By using excel, it can be calculating the Average profit and sum of sales. It can be
identified the profit and sales of Office supplier. Furthermore, it also identified the average
profit in year wise so that it become easier to identify the accurate information of supplier.
Figure 3
5

Data Interpretation
By using excel, it can be calculating the Average profit and sum of sales. It can be
identified the profit and sales of technology. Furthermore, it also identified the average profit in
year wise so that it become easier to identify the accurate information of technology.
Demonstrate that how it can practically with use of Excel function such as if, Lookup, Pivot
table, charts and graphs.
Look up: The Look up function is one of lookup and reference function. It is mainly used
to return the value from selected range. It is consider as value to search the particular data into
large data set. Lookup vector is single row or column which containing sorted data in ascending
order. At the most of level. Look up function in excel search a particular value in one column,
rows which return a matching value from same position in another column. In another context,
vector refer to one column and one row range. As consequently, it can be used the vector from of
Lookup to search particular row or column for specified valued.
Syntax:
Lookup (lookup_value, lookup_vector, [result_vector])
6

Pivot table: it can be summarised the data by using Excel tool and extract data in proper manner.
Pivot table is consider as most suitable for summarise, recognise, count and calculate average of
data stored in the database. It allows for user to transform column into rows in proper manner.
Calculate the Sum of shipping cost, sum or product base margin and sum of sales for Furniture.
Row Labels
Sum of Shipping
Cost
Sum of Product Base
Margin
Sum of
Sales
Furniture 53243.69 1006.77 5178590.542
Bookcases 8646.07 122.09 822652.04
Chairs & Chairmats 15512.69 228.46 1761836.55
Office Furnishings 8402.72 414.31 698093.81
Tables 20682.21 241.91 1896008.142
Figure 4
Calculate the Sum of shipping cost, sum or product base margin and sum of sales for office
supplies.
Office Supplies 36095.51
Appliances 6854.11
Binders and Binder Accessories 6633.52
Envelopes 1682.77
Labels 288.66
Paper 7914.41
Pens & Art Supplies 2041.81
Rubber Bands 225.21
7

Scissors, Rulers and Trimmers 670.51
Storage & Organization 9784.51
Figure 5
Calculate the Sum of shipping cost, sum or product base margin and sum of sales for office
supplies.
Technology 18491.84 1148.77 5984248.182
Computer Peripherals 4067.34 449.67 795875.94
Copiers and Fax [phone] 1130361.3
Office Machines 7135.91 149.75 2168697.14
Telephones and Communication 4841.71 512.98 1889313.802
Figure 6
8

Calculate Sum of region By Profit
Row Labels
Sum of
Profit
Atlantic 238960.66
North Carolina 2841.11
Northwest
Territories 8307.05
Ontario 439214.57
Prarie 321160.12
Quebec 140426.65
West 297008.61
Yukon 73849.21
Grand Total 1521767.98
Figure 7
Calculate region wise order date, customer name and sum of order id
Region Order Date Customer Name
Sum of Order
ID
Atlantic 13/01/2011 Cynthia Voltz 55815
Atlantic 13/02/2009 Sally Matthias 33224
Atlantic 13/02/2010 Maureen Gastineau 59619
Atlantic 13/02/2011 Astrea Jones 10245
Atlantic 13/02/2011 Chad Cunningham 59045
Atlantic 13/02/2011 Jennifer Braxton 19074
Atlantic 13/03/2010 Carlos Meador 7938
Atlantic 13/04/2012 Jim Epp 102404
Atlantic 13/05/2009 Carlos Meador 13941
9

Atlantic 13/05/2011 Erica Bern 80654
Atlantic 13/05/2012 Benjamin Venier 15399
Atlantic 13/05/2012 Fred McMath 11011
Atlantic 13/05/2012 Michael Kennedy 35396
Atlantic 13/06/2009 Maribeth Dona 37348
Atlantic 13/06/2011 Helen Abelman 30048
Atlantic 13/07/2012 Karl Brown 14215
Atlantic 13/08/2010 Sean Wendt 5061
Atlantic 13/09/2011 Joy Smith 81742
Atlantic 13/10/2010 Dave Poirier 26401
Atlantic 13/12/2010 Sarah Bern 46849
Atlantic 14/01/2010 Dianna Wilson 58599
Atlantic 14/01/2011 Charles Sheldon 21379
Atlantic 14/02/2010 Muhammed Yedwab 24518
Atlantic 14/02/2011 Bradley Talbott 50276
Atlantic 14/02/2012 Alex Grayson 54053
Atlantic 14/02/2012 Thea Hendricks 1154
Atlantic 14/04/2009 Becky Martin 8422
Atlantic 14/04/2009 David Flashing 32999
Atlantic 14/04/2011 Roy French 9216
Atlantic 14/04/2012 Jack Lebron 38310
Atlantic 14/05/2009 Brad Thomas 36609
Atlantic 14/05/2010 Bill Tyler 22338
Atlantic 14/05/2010 Karen Seio 21004
Atlantic 14/06/2011 Frank Atkinson 44292
Atlantic 14/07/2011 Barry Franz 20676
Atlantic 14/07/2012 Lori Olson 14980
Atlantic 14/08/2009 Elizabeth Moffitt 15971
Atlantic 14/08/2011 Ionia McGrath 47810
Atlantic 14/08/2012 Katherine Hughes 102350
Atlantic 14/09/2009 Debra Catini 7297
Atlantic 14/09/2009 Muhammed Yedwab 7269
Atlantic 14/09/2012 Maureen Gastineau 84612
10

PART 2
Audileadership data in conjunction with Weka with the help of clustering
Figure 8 Current relationship of Audileadership
Figure 9 farthest first Clustering
11

Figure 10 kmeans Algorithm for Clustering
12