Data Analysis Using Excel and Weka

This assignment delves into data analysis methods, comparing Excel and Weka. It highlights Excel's suitability for basic analysis but emphasizes Weka's advanced capabilities for statistical modeling and strategic decision-making. The text proposes modifying existing customer data to explore purchasing behavior and form targeted advertising strategies based on cluster and decision tree analyses in Weka.

DATA HANDLING AND BUSINESS
INTELLIGNECE

TABLE OF CONTENTS
Part A...............................................................................................................................................3
Reasons for decline in sales of the business firm........................................................................3
Pros and cons of using excel........................................................................................................5
Part 2................................................................................................................................................8
Reasons for using Weka to gain competitive advantage.............................................................8
Merits and dermis of Weka in comparison to excel (Pros and cons of using excel for data
analysis).......................................................................................................................................9
Application of J48 algorithm on data set in Weka......................................................................9
Application of clustering method on Audi data.........................................................................12
Modification of existing data.....................................................................................................17
CONCLUSION..............................................................................................................................17
REFERENCES..............................................................................................................................18
Figure 1 Output table of decision tree in Weka...............................................................................8
Figure 2Decision tree in Weka........................................................................................................9
Figure 3 Cluster of finance and TT................................................................................................11
Figure 4 Cluster of finance and A4................................................................................................12
Figure 5 Cluster of finance and RS7..............................................................................................14

INTRODUCTION
Analytics is the industry that is growing at fast rate across the globe. It is necessary to do
data analysis because one by using it identify reasons and solution of the business problems. In
the current research study sales data of the firm is analyzed by using Excel software. Reasons
due to which sales of the firm declined is identified. In the second part of the report, in Weka
software data is analyzed and results are interpreted. Along with this, strategy that Audi must
follow in order to enhance sales is also described in the report. At end of the report,
modifications that are required in the current data set are determined and reasons due to which
such modifications must be done in the data set is also explained
Part A
Reasons for decline in sales of the business firm
Figure 1 Calculation of unit cost
Figure 2 Margin calculation per unit
Figure 3IF statement formula

Figure 4 IF statement formula
Figure 5Lookup formulae
On analysis of the data set it is identified that there are two basic reason due to which sales
of the business in reduced in the specific duration. One of the main reason that is responsible for
reduction in sales is discount and other one is profit margin on units that are old to the customers.
It is identified on analysis of figures that there are few products whose price is very high but
there margin is very low. This means that firm is even sell its product at very high price then also
it is not earning higher amount of profit on sales. Contrary, to this in case of some products it is
find out that there price is very low but then also high margin is earned on them. For instance for
on delivery truck whose row ID 2383 sales value is 5472 and profit on same is 0.59. Contrary to
this, item with ID number 2484 profit of 0.77 is gained and sales price of the relevant product is
1810. On analysis of the example that is given above it can be find out that higher amount of
margin is earned when product is sold at low price. Inverse to this when price of product is low
then in that case high amount of margin is earned. Second thing that comes in light on analysis of
facts and figures is that even products are sold at high and low price big difference does not
come in the margin that is earned on the varied products. These are the basic factors or mistakes
that are made by the firm and consequently loss is faced in the business. There must be balance
between sales price, margin and discount in respect to specific product. Discount is another
factor that is also responsible for the poor performance of the business firm.
There are different items that have varied price but similar amount of discount is given on
them. This is the big mistake that is made by the firm in its business. For instance on the product
that is in the row ID 103 product value is equivalent to 2781. Discount on relevant product is
0.07. Inverse to this case it can be observed that on the product that is in the row ID is 107 price

of item is 228 and on it value of discount is of 0.07. On comparison of both cases it can be
identified that there is huge difference in both products price but then also there is no difference
in the discount value. Hence, it can be said that this is the big mistake that is made by the firm in
its business. It must be noted that products that are compared on above illustration are different
from each other. However, it does not mean that discount of same value can be given on both.
Even both products are different from each other their sales value is also different. Hence, there
must be a difference in the discount that is given by the firm on both products. This does not
happened and it can be said that firm is following a wrong policy in its business. Case that is
discussed above does not comes in existence by chance. This is further evident from the other
facts and figures. In row ID 203 and 204 there is same product which is regular air. It can be
observed that price of products on both row ID is 663 and 834 and discount on them is merely
0.06. This indicate that there are many products on which business firm is giving same discount
even price is different and products are similar or different from each other. Hence, it can be said
that firm is not giving discount in appropriate manner to the customers. This is another reason
due to which firm product sales declined. It is advised that firm must bring variation in its
product price and discount policy so that profitability can be enhanced in the business and
customers can be satisfied and retained in the business.
Pros and cons of using excel
Strength of excel in relation to preprocessing of data
There is a great importance of excel for preprocessing a data. Usually, in excel huge data
set is analyzed by the data scientists. Before commencing analysis of the data it is very important
to preprocess it so as to arrange it in systematic manner for data analysis. Facts and figures are
analyze be keeping specific things in mind. Means that in the entire set of data all set of figures
are not analyzed in single attempt. Some variables are picked from the data set for analysis
purpose. It is very important to arrange relevant set of variables in legitimate way so that analysis
can be made in better way. Thus, preprocess of data set is required by an individual. There are
many functions in excel that are used to preprocess the data set. In this regard conditional
formatting is the function in excel that is widely used by the individuals. By using conditional
formatting specific variables can be identified in the data set in order to identify the pattern in
which values of the variables are moving. By making use of the conditional formatting function

relevant values are highlighted in single attempt. In order to analyze the superstore sales data it
was important to identify that specific variables that are responsible for decline in the firm
performance. In this regard conditional formatting tool is used on the sales data. By using this
tool the discount and margin as well as sales value that is for different products are identified by
determining specific criteria in conditional formatting dialog box. After different products are
highlighted in varied colors comparison between them is made to identify the sort of analysis
that must be done in respect to the variable. Thus, it can be said that conditional formatting is the
one of the most important tool that is used to preprocess the data set.
In order to understand and identify the relationship among the variables excel is widely used by
the data scientists. By using this tool data set is arranged in the specific manner that is finally
used for analysis purpose by the data scientists. Huge set of figures are available for the internet
and secondary sources of information which are company records. It is very important to identify
the variables that must be considered for analysis purpose. Due to this reason when data is
entered in the spreadsheet emphasis is given on the cleaning of same. Under this unusual values
that are in the set of figures are removed. In this regard specific parameter is set by the analyst. If
on comparison with standard it is identified that specific value is unusual then it is removed from
the set of figures. In this regard charting of data set is done and unusual trend is identified. These
non-usual values are traced in the excel sheet and removed. In this was set of values is prepared
for analysis purpose and by doing this it is ensured that accurate prediction or reliable results will
be obtained on analysis of data. Thus, it can be said that there is a great importance of
preprocessing of data before it is finally used for analysis purpose by the data scientists. Lookup
formula used to identify the value of the specific variable in the huge data set. Mentioned
formula is used for preprocessing of set of figures. In the excel sheet mentioned formula is
applied and value of discount for specific product is identified.
Strength of excel in analysis of data
In analysis of data there are number of strengths in excel because there are varied functions
that are available in the mentioned software that facilitate data analysis. In the statistics all
functions can be classified in to two categories namely statistical and non-statistical. Functions
like regression and correlation are included in the statistical functions. Whereas, IF, LOOKUP
and other functions are included in the non-statistics category. Varied function of excel that are
used for analysis purpose are given below.

 IF function: It is the most powerful function of excel because by using same outcome
that can come in existence on occurrence of the specific situation is identified. In this
function in the formula specific situation is reflected by entering a formula. Then if such
condition get satisfied then what must be outcome is determined (Siemens, 2010). Thus,
by using IF function of excel huge data set is analyzed in better way. In analysis of the
sales data IF function is used and row ID’s where discount rate is high or low for same
product at different sales price is find out. On other variables also IF function is used to
analyze the data in better way. Lookup: This is another function that is used at large scale by the data scientists. By
using LOOKUP function values for the specific variable is identified. For example if one
wants to identify specific value vertically or horizontal for the variable regular air in
respect to specific column or row than in that case by using LOOKUP function relevant
value can be identified by an individual. Pivot table: Pivot table is another important tool of excel. In the pivot table specific
variable that one wants to analyze can only take in to spreadsheet (Trkman. and et.al.,
2010). Thus, on screen one can abstain from revealing variables that are not required for
data analysis. It can be said that mentioned function of excel help one in completing data
analysis in less time period. Charts and graphs: Charts and graphs can be generated in excel and by analyzing same
analysis of the variables is done by the analyst. Thus, it can be said that charts and graphs
are very important tool that facilitate analysis of data in better way. Statistics: There are some specific statistical tools that are used for analysis of data in
excel (Siegel, 2013). Some advanced analysis tools of statistics that are in excel are
regression and correlation excel. By using these tools relationship among the variables is
identified. Linear programing: Linear programing is another tool that is used for identifying the
allocation of the resources that must be made among different products (Davenport,
Harris and Shapiro, 2010). Thus, Excel solver help on making proper allocation of
resources.
Strength of excel in visualization of data

In excel all sort of charts and diagrams can be prepared by an individual. From simple
column to candlestick chart that is related to the stock market are prepared in excel. Apart from
this in different formats specific chart can be prepared by an individual (Siemens and Gasevic,
2012). Hence, excel facilitate better visualization of the variables. This is big strength of excel in
respect to visualization of data set.
Disadvantage of using excel for data analysis
Large set of values cannot be studied with ease in excel. Company’s data set usually
contain 3000 to 4000 rows in the data set. Thus, excel cannot be used to analysis such kind of
large data set. This is major limitation of excel.
Part 2
Reasons for using Weka to gain competitive advantage
There are number of reasons due to which Weka is used by the business firms at the
workplace for the data analysis. It must be noted that in excel only up to specific limit data can
be analyzed. Above that limit it is not possible to evaluate data in excel. This limitation of excel
is removed by the Weka. In the mentioned statistical software easily huge set of data that contain
4000 or more rows can be analyzed in easy way. This is the one reason due to which Weka must
be used by the data scientists in order to obtain competitive advantage. The other major
limitation of excel is that in same only some specific statistical tools can be applied on set of
figures (Baepler and Murdoch, 2010). Advanced method such as cluster market basket analysis
cannot be done in excel. But in Weka software easily these techniques can be applied on relevant
variables. The other reason due to which Weka is used to gain competitive advantage is that in
simple way data can be analyzed by an individual. In powerful software’s like R and SAS one
needs to do programing. Moderate amount of time is spend on programing by using which
statistics are computed. In the Weka one only needs to select option and within second entire
calculation is done. Thus, it can be said that Weka software save a lot of time of an individual.
Merits and dermis of Weka in comparison to excel (Pros and cons of using Weka for data
analysis)
Merits Size of data: In the Weka any size of data can be analyzed but in case of excel; only the
data up to specific size can be analyzed in proper manner by an individual. Moreover, if

data set is of huge size then one need to spend higher amount of time on data analysis. In
comparison to excel in Weka in short time period analysis of data can be done by an
individual. This is the merit of Weka over excel. Statistical functions: In the Weka all type of complicated statistical calculations can be
done easily by the data scientist (Hu and et.al., 2014). However, in excel software only
few statistical tools and methods can be applied on set of values. Thus, advanced
statistics cannot be computed in excel but same can be done in Weka. This is another
strength of Weka over excel.
Demerits
 Calculation other than statistics: The main demerit of the Weka is that in same
calculations that are non-statistical in nature cannot be performed. Whereas, in excel
calculations that are related to statistics and not related to same domain can easily
performed. Hence, it can be said that wide variety of work can be done in Weka relative
to Excel.
Application of J48 algorithm on data set in Weka
J48 is the algorithm that is used to prepare a decision tree in which sequence of varied
activities is determined by following a specific approach (Classification via decision trees in
Weka, 2016). Below calculations of the decision tree are given that is done by using mentioned
algorithm

Figure 6 Output table of decision tree in Weka
Figure 7Decision tree in Weka

Interpretation
It can be seen from the chart given above that there is a possibility under which either
purchase is made before the month of August of 2005 or after this month. If Audi first purchase
is made after month of August 2005 there is a possibility that no one make use of the extended
warranty offer. On other hand, in case last purchase of Audi is made before the month of
December in the year 2005 there is possibility that no one will use warranty policy. However, if
last purchase is made after December month of 2012 than people will make use of warranty
policy. Thus, it can be assumed that customers are making use of extended warranty only when
their last purchase happened after December month.
Application of clustering method on Audi data

Figure 8 Cluster of finance and TT
Interpretation
There is a large size cluster on the right side corner of the screen. It can be said that in
this cluster all variables values are nearby to centroid. It can be said that by using finance
customers are buying TT car of Audi. In order to increase sale of mentioned car finance must be
provided to the customers as much as possible.

Figure 9 Cluster of finance and A4

Interpretation
Again in this case big cluster is seen at top of the chart. Cluster reflects that higher
number of people are buying A4 car without obtaining finance from relevant institute. Thus, it
can be said that in order to increase sale of A4 it is not necessary to give finance. Entire finance
can be made available to the customers that intends to buy TT car.

Figure 10 Cluster of finance and RS7
Interpretation
It can be observed from the diagram that there are less number of customers who are
making purchase of RS7 by taking a finance. Thus, firm must make available less amount of
finance to buy RS7. It can be said that majority of amount of finance must be given to those who
intends to buy TT car. In other words, it can be state that firm must give more and more finance
to the customers to make purchase of TT car. Overall charts of clusters that are prepared above
collectively indicate that majority of customers are purchasing TT car on finance. Due to this
reason firm must formulate a strategy under which it must try to sale maximum number of TT
car by making available finance to the customers.

Modification of existing data
Some modifications can be made to the current data set and under this response of
customers can be added in respect to the factors that influence their purchase decision in respect
to Audi (El-Nasr, Drachen and Canossa, 2013). On the basis of results of cluster and decision
tree specific strategy can be formulated about way in which advertising message must be craft in
respect advertisement. By doing number of customers can be increase in the business at rapid
pace.
CONCLUSION
On the basis of above discussion it is concluded that excel is the software that have high
importance for the business firms in terms of analysis of data. However, if one wants to use
statistics then same cannot use excel. There are many advanced functions in the Weka by using
which data can be analyzed and business strategy can be formulated in right direction in respect
to increasing sales revenue. Thus, for applying statistical tool on the data set one must use Weka
instead of excel.

REFERENCES
Books & journals
Baepler, P. and Murdoch, C.J., 2010. Academic analytics and data mining in higher education.
International Journal for the Scholarship of Teaching and Learning. 4(2). p.17.
Davenport, T.H., Harris, J. and Shapiro, J., 2010. Competing on talent analytics. Harvard
Business Review. 88(10). pp.52-58.
El-Nasr, M.S., Drachen, A. and Canossa, A., 2013. Game analytics: Maximizing the value of
player data. Springer Science & Business Media.
Hu, H., Wen, Y., Chua, T.S. and Li, X., 2014. Toward scalable systems for big data analytics: A
technology tutorial. IEEE Access. 2. pp.652-687.
Siegel, E., 2013. Predictive analytics: The power to predict who will click, buy, lie, or die. John
Wiley & Sons.
Siemens, G. and Gasevic, D., 2012. Guest Editorial-Learning and Knowledge Analytics.
Educational Technology & Society. 15(3). pp.1-2.
Siemens, G., 2010. What are learning analytics. Retrieved March, 10, p.2011.
Trkman, P. and et.al., 2010. The impact of business analytics on supply chain performance.
Decision Support Systems. 49(3). pp.318-327.
Online
Classification via decision trees in Weka, 2016. [PDF]. Available through<
http://facweb.cs.depaul.edu/mobasher/classes/ect584/weka/classify.html>. [Accessed on
24th December 2016].