Strategic Car Sales using Finance Cluster Analysis

The assignment content presents a study on car sales and finance using cluster analysis and decision trees. The results show that few people are purchasing RS7 cars through bank financing, suggesting that providing more financial assistance may not be wise. It is recommended to sell TT cars only with financing options. Additionally, the importance of collecting data on consumer behavior and understanding social factors influencing car purchase decisions is emphasized.

DATA HANDLING AND BUISNESS INTELIGENCE

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................3
Part A...............................................................................................................................................3
Reasons responsible for reduction in sales of company..............................................................3
Pros and cons of 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) 8
Application of J48 algorithm on data set in Weka......................................................................9
Application of clustering method on Audi data.........................................................................11
Modification of existing data.....................................................................................................17
CONCLUSION..............................................................................................................................17
REFERENCES..............................................................................................................................18
Figure 1 Output table of decision tree in Weka...............................................................................8
Figure 2Decision tree in Weka........................................................................................................8
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 one of the fastest growing industry in the world. It is important to use business intelligence because it help one in
identifying reasons due to which business problem comes in existence. In this industry many software’s are used to analyze the facts
and figures. In the current research study analysis of facts and figures is done by using excel software. Factors due to which sales
decline are also identified in the report. Apart from this Weka is also applied on the data that is related to Audi. Interpretation of
results is done in proper manner. In this way entire work is done in the report.
Part A
Reasons responsible for reduction in sales of company
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 all facts and figures that were given in the data set it is identified that discount and profit margin are the two main
reason due to which firm earn low profit in its business. It must be noted that big variation does not come in the earned margin amount
when company is selling one product at high price and other one at low price. In some cases there is small of the product but margin is
high. Contrary to this, in some cases price of product is high but low margin is earned on same. Lack of balance in earning of margin
is responsible for declined in the firm sales. For example on row ID 2383 product is delivery truck and its sales price is 5472 and
earned margin on same is 0.59. Opposite to this, in case of delivery truck that is on ID 2384 product is available at 1810 and earned

margin on same is 0.77. This example proved that on high priced product firm is earning low margin and on low priced product high
margin is earned. Thus, it can be said that either firm must change its product price or either must reduce cost of same. By doing good
amount of margin can be earned in the business.
Discount is also not given in appropriate manner in different products. It can be observed that similar value of discount is given
across varied product categories. It can be observed from the table that on row ID 103 sales price of item is 2781 and discount on same
is 0.07. Contrary to this, on ID 107 sales price is 228 and discount on same is 0.07. No difference is identified among both products in
terms of discount but sales price is totally different. There are many products that are same but in their case also it is identified that
discount is not given in proper manner to the customers. There is a product regular air whose row ID is 203 and 204. Sales value of the
product on both ID are 663 and 884. But discount on both is equivalent to 0.06. It can be said that even product is same price is
different then also no difference comes in the discount value. This proved that firm is not making appropriate use of discount to attract
and retain customers in its business and due to this reason its profit declined consistently. It is recommended that business firm must
make adjustment in its cost or sales price of the product so that different amount of margin can be earned and discount can be given to
the customers. By doing so firm can earn high amount of profit and can retain customers in the business.
Pros and cons of excel
Strength of excel in relation to preprocessing of data
In excel huge set of figures are analyzed by an individual. It is very important to arrange the set of figures in a systematic way. This is
because if values will be arranged in proper manner then it will be easy to analyze the set of figures. There are many functions in excel
that facilitate preprocess of data. It is very important use functions like conditional formatting for preprocessing of the data set. It can
be said that processing of data have a significance for the data scientists. It must be noted that huge group of values are studied by an
individual in excel and same is processed to obtain specific result. It is very important to eliminate those values that are not required
for analysis purpose from the data set (Khalid, 2010). In other words it can be said that it is very important to identify the values in
huge set of figures that must be considered for analysis purpose. In order to solve this problem function of conditional formatting is

used by the data scientists. By using this function those values that are relevant are identified from set of figures are taken by an
individual for analysis. There are number of options that are available in respect to data analysis. One can identify specific value in
group of values. Research analyst if want then he can find out values in specific range from the data set. Apart from this, all values
that are greater or smaller then specific number can also be discovered from the set of figures. Thus, it can be said that there is huge
importance of the conditional formatting function in preprocessing of the set of values (Rajput and et.al., 2011). After identifying
relevant values same are arranged in proper manner by the analyst. Same is used to for analysis purpose by the mentioned entity.
Thus, it can be said that there is a great importance of the excel functions in preprocessing of the data set. In the current research study
conditional formatting is used before commencing an analysis by using IF function. By using conditional formatting function of the
excel discount and margin value for varied products is identified and on the basis of results conditions for the IF statement is
determined. In this entire analysis of the sales data is facilitate by the preprocessing of data. Thus, it can be said that there is a great
importance of excel in relation to preprocessing of data. This is because by using excel even set of figures is large in size before
processing analysis is done in systematic way. There are many formulas in excel that can be used to analyze the data and
understanding relationship among them (Cleophas and Zwinderman, 2014). On the basis of identification of relationship columns are
added or removed from the data set and further analyzed to arrive at specific conclusion. Thus, it is clear that excel is the one of the
most important tool that is used for preprocessing of data. In the huge data set it is very difficult to identify the specific value. Thus, in
order to solve this problem LOOKUP function is used in excel. In current report also mentioned function is applied on data set to find
out value of discount that is given on each product produced by the firm. Thus, preprocessing of data is made easy by excel.
Strength of excel in analysis of data
There are various functions in excel that are used to analyze the set of values. All these functions can be classified in to the
varied categories like normal function and advanced functions. In case of normal functions one can use formulas like sum, mean,
median and mode etc. Contrary to this, in case of advanced function some specific functions that comes are IF, VLOOKUP and
HLOOKUP. It depends on the individual that which method same used for analyzing the set of figures (Häring, 2015). Functions that

are excel can be classified in to two categories statistical and non-statistical. In the statistics many functions are included like
histogram, regression, correlation, T- test and Z-test. Whereas, in non-statistical functions IF, LOOKUP and INDEX functions are
used to analyze the data. Thus, it can be said that there are various features in excel that can be used to analyze the data. For analytics
and non-analytic work one can easily use excel to obtain specific results from the analysis of data. Varied function of excel that are
used for analysis purpose are given below. IF function: IF function is widely used by the researchers because in this specific condition is created in excel mentioned
function and on satisfaction of same specific output is determined. The main significance of this function is that huge set of
figures can be analyzed in single attempt and on the basis of specific condition comparison among the variables can be done
(Cleophas and Zwinderman, 2015). It can be said that this is the function of excel that help one broadly understanding the set
of figures. In the current report IF function is employed to develop broad understanding about the data set variables by
identifying interrelationship among them in terms of changes that are taking place in same. Difference that is between varied
products of same or different category are identified by using IF function. Same thing is done in case of other variables and
reason due to which sales declined are identified. Lookup: Many times in the large set of figures one wants to identify the value that is related to the specific variable. It is not
possible manually to discover values of the specific variable. If one will try to do so then it will lead to wastage of time. In
order to prevent this problem LOOKUP function is used in excel. One needs to specify the column or row in which it wants to
find out specific number in respect to the variables. By entering relevant values in the LOOKUP function values for the
specific variable is identified. Thus, it can be said that use of mentioned function make work easy for an individual. Pivot table: There are number of variables in the excel sheet. Many times it is not possible for one to identify the specific
variables which only must be considered for analysis purpose. There are lots of chances that wrong variable may be considered
for analysis purpose. In order to prevent origination of this problem pivot table is prepared by an individual. By doing so only

those variables that must be considered for analysis purpose can be viewed in the screen of MS Excel. Thus, it can be said that
excel is the one of the most powerful tool that make analysis of data easy task for an individual.
Strength of excel in visualization of data
In excel software charts and diagrams can be prepared by an individual. In charts and graphs values of the variables are plotted
in systematic way. By viewing chart easily analysis of data set can be done by an individual. In the excel variety of charts can be
prepared and in each type of chart varied category of diagram can be prepared (Agh, 2012). Thus, by using excel visualization of data
can be done in better way.
Disadvantage of excel
The only limitation of excel is that big group of values cannot be analyzed in excel in short duration. One needs to spend lots
of time in order to analyze large data set in excel. Thus, on use Weka instead of excel for data analysis.
Part 2
Reasons for using Weka to gain competitive advantage
In current time period there are many tools that are used in the analytics for analyzing the set of facts and figures. On
comparison of the Weka with other tools it can be identified that there are many positive points of the mentioned software in
comparison to other one. Excel is the software that is widely used to do analysis of the data. It must be noted that in excel large size
data cannot be analyzed conveniently (Häring, 2015). Same problem is not observed in case of the Weka. As I the mentioned software
any size of data can be analyzed by an individual. There are many other powerful tools like R and SAS that are used by the individuals
for analysis purpose. It must be noted that in case of R and SAS one needs to make use of the programing language. It is very difficult
to keep in mind coding for each and every algorithm. In case of Weka there is no such kind of problem. This is because in the
mentioned software one only need to select an option of algorithm and data set. All calculations automatically happened in the
mentioned software. Hence, this lead to saving of lots of time of an individual. It can be said that Weka is the one of the main software

in which in less time complicated calculations can be done. Hence, on these grounds Weka have a competitive advantage over other
softwares.
Merits and dermis of Weka in comparison to excel (Pros and cons of using Weka for data analysis)
Merits Size of data: Size of the data set is the factor that greatly affects the choice of software for analysis purpose. It must be noted
that in case of excel on up to specific limit data set can be analyzed conveniently. Large set of values cannot be analyzed easily
in case of excel. This limitation of excel is removed by Weka. Hence, it is the one the positive point of Weka over excel. Statistical functions: This is the factor that greatly influence the use of excel. It must be noted that in case of excel only up to
certain extent statistical tools can be applied on the data set (Cleophas and Zwinderman, 2015). Same thing is not observed in
case of Weka as any size of data can be analyzed in same. Hence, in terms of handling a large size data Weka is better than
Excel.
Demerits
 Calculation other than statistics: There are some limitations of the Weka software. In the mentioned software only statistics
related calculation can be done by an individual. This means that one cannot do other than statistics calculation in the Weka.
On other hand, in the Excel all sort of calculation can be done by an individual. Hence, on this front Weka have superiority
over Excel.
Application of J48 algorithm on data set in Weka
In order to prepare a decision tree there are varied algorithms that are available and can be used by the researchers
(Classification via decision trees in Weka, 2016). All these algorithms prepare decision tree in different manner. Out of all available
algorithms J48 is one that is commonly used by the analysts. In this diagram order in which activities may takes place are determined.
In order to do analysis of the data that is related to Audi J48 algorithm is used in the Weka software.

Figure 6 Output table of decision tree in Weka

Figure 7Decision tree in Weka
Interpretation
Chart that is given above indicate that that in case of Audi there are two possibilities. Either purchase is made by the customers
before month of August of after the mentioned months. Analysis of diagram reflects that those customers who make purchase of Audi
before relevant month of 2005 does not take benefit of the extended warranty that is provided by the business firm to them. Contrary
to this, in other case of if first purchase is made by the customers after the mentioned month than in that case people are making use of
extended warranty which is availed by the company. This scenario further break down in two conditions. In the first case car of Audi
if last purchased in the 12th month of the 2005 and in that case people does not take advantage of the warranty policy of the business
firm. Results reflects that those customers who make last purchase of the car after last month of the above mentioned financial year

then people are using extended warranty policy. Overall, conclusion of the analysis of diagram is that customers of the Audi are
making use of the extended warranty only after the last month of the 2005.
Application of clustering method on Audi data

Figure 8 Cluster of finance and TT

Interpretation
Screen shot that is pasted above revealed that there are four clusters in the screen. It can see from the above image that big
group of values is on right side of the image. Chart revealed that in the mentioned cluster there is a very nearness among the values of
the variables. Hence, in this cluster values are to great extent linked to each other. Overall conclusion is that in order to boost sales of
the TT car finance must be given to the customers. This means that if car TT will not be available on finance then it is possible that
sales of same declined.

Figure 9 Cluster of finance and A4

Interpretation
In the upper part of image and on right side there is a large size cluster. On analysis of chart it is discovered that majority
number of customers of Audi are buying A4 car without taking any finance from the business firm. It can be said that people are
buying A4 car without taking any amount of debt. Firm must formulate a business strategy and under this it must sale all its cars
without giving finance to the customers.

Figure 10 Cluster of finance and RS7
Interpretation
Arrangement of clusters reflects that in case of car RS7 there are few people who are making purchase by taking a finance
from the bank or any other financial institute. So, on this basis it can be stated that it will not be wise decision to provide more and

more amount of finance to the people in order to purchase car RS7. Audi must create a strategy under which it should sales its TT car
only on the basis of finance. Apart from this on other cars no financial assistance must be given to the customers. This is because
people are not interested in purchasing a car A4 and RS7 by taking advantage of finance facility. Hence, by using above mentioned
strategy firm will be able to make best use of cash in its business in terms of generating higher number of sales in the business.
Modification of existing data
There are lots of information that are available in the current data set but some changes or additions are required in same. Firm
must try to collect data on consumer behavior of the people. By collecting relevant data in better way understanding about the
customers can be developed by the business firm. Hence, data can be collected about influence of social factor and reference group in
respect to car purchase decisions on individuals.
CONCLUSION
It is concluded that by using methods like cluster analysis and decision tree better business decisions can be made by the analysts.
On the basis of results of the mentioned methods better business strategies can be formulated by the managers. It is also concluded
that Weka must be used in comparison to other software’s because by using same calculation can be done in proper manner.

REFERENCES
Books & journals
Bhuyar, V., 2014. Comparative analysis of classification techniques on soil data to predict fertility rate for Aurangabad district. Int. J.
Emerg. Trends Technol. Comput. Sci. 3(2). pp.200-203.
Cleophas, T.J. and Zwinderman, A.H., 2014. Bayesian Networks for Cause Effect Modeling (600 Patients Assessed for Longevity
Factors). In Machine Learning in Medicine-Cookbook Three. Springer International Publishing.
Cleophas, T.J. and Zwinderman, A.H., 2015. Bayesian Networks for Cause Effect Modeling (600 Patients). In Machine Learning in
Medicine-a Complete Overview (pp. 439-444). Springer International Publishing.
Häring, I., 2015. More Elaborate Database Analysis for Risk and Resilience Analysis. In Risk Analysis and Management: Engineering
Resilience. Springer Singapore.
Khalid, H., 2010. Citarasa engineering for modeling affective product design. Advances in Ergonomics Modeling and Usability
Evaluation. pp.269-279.
Rajput, A. and et.al., 2011. J48 and JRIP rules for e-governance data. International Journal of Computer Science and Security
(IJCSS). 5(2). p.201.
Vagh, Y., 2012. The application of a visual data mining framework to determine soil, climate and land use relationships. Procedia
Engineering. 32. pp.299-306.
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].