CSEN 2141 : DATA ANALYTICS: DESCRIPTIVE,

PREDICTIVE, PRESCRIPTIVE

Module-1
Introduction: Decision Making, Business Analytics Defined, A Categorization of
Analytical Methods and Models, Big Data, Business Analytics in Practice, Legal and
Ethical Issues in The Use of Data and Analytics.
Descriptive Statistics: Overview of Using Data: Definitions and Goals, Types of
Data, Modifying Data in Excel, Creating Distributions from Data, Measures of
Location, Measures of Variability, Analyzing Distributions, Measures of Association
Between Two Variables.
 PART-1

• Introduction: Decision Making, Business Analytics Defined, A

Categorization of Analytical Methods and Models, Big Data, Business

Analytics in Practice, Legal and Ethical Issues in The Use of Data and

Analytics.
DATA ANALYTICS
?
 DATA ANALYTICS
• Data analytics is the process of examining raw data to draw
conclusions about that information. It involves collecting,
transforming, and organizing data to reveal patterns, trends, and
insights that can be used to make informed decisions.
DATA ANALYTICS
 DECISION MAKING
• Decision making is the process of identifying a problem or
opportunity, gathering information, evaluating alternatives, and
selecting the best course of action.
• It’s the responsibility of the managers to plan, coordinate, organize,
and lead their organization to better performance.
 Decision Making
1. Strategic Decisions:
Involve higher-level issues concerned with the overall design of the organization
Define the organization's overall goals and aspirations for the future
2. Tactical Decisions:
Concern about how the organization should achieve the goals and objectives set
by its strategy
Focus on 1-2 years planning.
Are usually the responsibility of midlevel management
[Link] Decisions:
Affect how the firm is run from day to day
Are the domain of operations managers, who are the closest to the customer
• Decision-Making Process:
1. Identify and define the problem
2. Determine the criteria that will be used to evaluate alternative
solutions
3. Determine the set of alternative solutions
4. Evaluate the alternatives
5. Choose an alternative
• Common Approaches to Making Decisions:
-Tradition -Intuition
-Rules of thumb
-Using the relevant data available
 Business Analytics Defined
• What makes decision-making difficult?
-Dearth of data
-Enormous number of alternatives and we cannot evaluate them all .

• Business Analytics:
It is a scientific process of transforming data into insight for making
better decisions.
-Used for data-driven or fact-based decision-making, which is often
seen as more objective than other alternatives for decision-making
Tools of Business Analytics Can Aid Decision Making by:

-Creating insights from data .

-Improving our ability to forecast for planning more accurately .

-Helping us quantify risk .

-Yielding better alternatives through analysis and optimization.

 Business Analytics in Practice
• Business analytics involves tools as simple as reports and graphs to those that
are as sophisticated as optimization, data mining, and simulation.

-Financial Analytics

-Human Resource (HR) Analytics

-Marketing Analytics

-Health Care Analytics

-Supply Chain Analytics

-Sports Analytics

-Web Analytics
Companies that apply analytics often follow a trajectory similar to the
graph shown
A Categorization of Analytical Methods and Models:
1. Descriptive Analytics
2. Predictive Analytics
3. Prescriptive Analytics

• Descriptive Analytics: Encompasses the set of techniques that

describe what has happened in the past;
• Examples include: Data queries, reports, descriptive statistics, data
visualization including data boards, some data mining techniques
etc.
 Descriptive Analytics

• A data query is a request for information with certain characteristics from a

database.
• Data dashboards are collections of tables,charts,maps and summary statistics
that are updated as new data become available. Dashboards are used to help
management monitor specific aspects of the company's performance related
to their decision-making responsibilities.
• Data mining is the use of analytical techniques for better understanding
patterns and relationships that exist in large data sets. For example , by
analyzing text on social network platforms like Twitter, data mining techniques
are used by companies to better understand their customers.
Predictive Analytics: Consists of techniques that use models
constructed from past data to predict the future or ascertain the impact
of one variable on another.
-Survey data and past purchase behavior may be used to help predict the
market share of a new product
Techniques used in Predictive Analytics include :
-Linear regression
-Time series analysis
-Data mining is used to find patterns or relationships among elements of
the data in a large database; often used in predictive analytics
-Simulation involves the use of probability and statistics to construct a
computer model to study the impact of uncertainty on a decision
Prescriptive Analytics: Indicates the best course of action to take
-The output of a prescriptive model is the best decision

-A forecast or prediction, when combined with a rule, becomes a prescriptive

model.

-Prescriptive models that rely on a rule or set of rules are often referred to as
rule-based models

-Another type of modeling in the prescriptive analytics category is simulation

optimization which combines the use of probability and statistics to model
uncertainty with optimization techniques to find good decisions in highly
complex and highly uncertain settings.
 Big Data
-There is no universally accepted definition of big data. Big data is any set of

data that is too large or too complex to be handled by standard data-processing

techniques and typical desktop software.

-IBM describes the phenomenon of big data through the

four Vs: Volume, Velocity, Variety, and Veracity.

 Legal and Ethical Issues in the Use of Data and Analytics
• Data privacy laws are designed to protect individual’s data from being used
against their wishes.
• One of the strictest data privacy laws is the General Data Protection
Regulation(GDPR) which went into effect in the European Union in May 2018.
• INFORMS (Institute for Operations Research and the Management Sciences)
provide ethical guidelines for analysts.
• INFORMS also offers a set of Ethics guidelines for its members which covers
ethical behavior for analytics professionals in three domains:
1. Society
[Link](Businesses, government, nonprofit organization and
universities)
3. Profession(operational research and analytics).
 PART-2

• Descriptive Statistics: Overview of Using Data: Definitions and

Goals, Types of Data, Modifying Data in Excel, Creating Distributions

from Data, Measures of Location, Measures of Variability, Analyzing

Distributions, Measures of Association Between Two Variables.

 Descriptive Statistics: Overview of Data
• The role of descriptive analytics is to collect and analyze data to gain a better
understanding of variation and its impact on the business setting.

• Data are the facts and figures collected, analyzed, and summarized for
presentation and interpretation.

• A characteristic or a quantity of interest that can take on different values is

known as a Variable.

• In general, a quantity whose values are not known with certainty is called a
Random variable or uncertain variable.

• An observation is a set of values corresponding to a set of variables.

• In this table variables are
symbol, industry, share
and price and volume.

Each row in table

corresponds to
observation
 Types of Data

• 1. Population and Sample Data

• 2. Quantitative and Categorical data

• 3. Cross-sectional and Time series Data

 Types of Data
1. Population and Sample Data
• Data be categorized in several ways based on how they are collected
and the type of data collected.

• It is not feasible to collect data from the population of all elements of

interest.

• In such instances, we collect from a subset of the population known

as a sample.
 Types of Data
2. Quantitative and Categorical data

• Data are considered quantitative data if numeric and arithmetic

operations, such as addition, subtraction, multiplication, and division,
can be performed on them.

• If arithmetic operations cannot be performed on the data, they are

considered categorical data. We can summarize categorical data by
counting the number of observations or computing the proportions
of observations in each category.
 Types of Data
3. Cross-sectional and Time series Data
• For statistical analysis, it is important to distinguish between cross-sectional data and

time series data. Cross-sectional data are collected from several entities at the same, or

approximately the same, point in time. The data in Table 2.1 are cross-sectional because

they describe the 30 companies that comprise the Dow at the same point in time (June

2019).

• Time series data are collected over several time periods. Graphs of time series data

are frequently found in business and economic publications.

Sources of Data

• Data necessary to
analyze a business
problem or
opportunity can often
be obtained with an
appropriate study.
• In this table variables are
symbol, industry, share
and price and volume.

Each row in table

corresponds to
observation
 Modifying Data in Excel:- Sorting and filtering Data in excel
• Excel’s Sort function, as shown in the following steps.
• Step 1. Select cells A1:F21
• Step 2. Click the Data tab in the Ribbon
• Step 3. Click Sort in the Sort & Filter group
• Step 4. Select the check box for My data has headers
• Step 5. In the first Sort by dropdown menu, select Sales (February 2018)
• Step 6. In the Order dropdown menu, select Largest to Smallest (see Figure
2.4)
• Step 7. Click OK
After sorting is performed
 Modifying Data in Excel:- Conditional Formatting of Data in Excel
• Conditional formatting in Excel can make it easy to identify data that
satisfy certain conditions in a data set.
• Step 1. Starting with the original data shown select cells F1:F21
• Step 2. Click the Home tab in the Ribbon
• Step 3. Click Conditional Formatting in the Styles group
• Step 4. Select Highlight Cells Rules, and click Less Than . . . from the
dropdown menu
• Step 5. Enter 0% in the Format cells that are LESS THAN: box
• Step 6. Click OK
Data Bars from the Conditional Formatting dropdown menu in the Styles Group of the Home tab in the
Ribbon.
Creating Distributions from Data:
• Distributions help summarize many characteristics of a data set by describing
how often certain values for a variable appear in that data set.

• Distributions can be created for both categorical and quantitative data, and
they assist the analyst in gauging variation.

1. Frequency Distributions for Categorical Data

2. Relative Frequency and Percent Frequency Distributions

3. Frequency Distributions for Quantitative Data

4. Histograms

5. Cumulative Distributions
Creating Distributions from Data:
1. Frequency Distributions for Categorical Data

• It is often useful to create a frequency distribution for a data set.

• A frequency distribution is a summary of data that shows the number

(frequency) of observations in each of several nonoverlapping classes,
typically referred to as bins.
Creating Distributions from Data:
2. Relative Frequency and Percent Frequency Distributions
• The relative frequency of a bin equals the fraction or proportion of items
belonging to a class. For a data set with n observations, the relative
frequency of each bin can be determined as follows:

• A relative frequency distribution is a tabular summary of data showing the

relative frequency for each bin.
• A percent frequency distribution summarizes the percent frequency of
the data for each bin.
 3. Frequency Distributions for Quantitative Data

Table 2.6. These data show the time in days required to complete year-end
audits for a sample of 20 clients of Sanderson and Clifford, a small public
accounting firm.
The three steps necessary to define the classes for a frequency distribution with
quantitative data are as follows:
[Link] the number of nonoverlapping bins.
2. Determine the width of each bin.
3. Determine the bin limits.
1. Number of Bins Bins are formed by specifying the ranges used to group the data.
As a general guideline, we recommend using from 5 to 20 bins.(5)

2. Width of the bins

Approximate bin width of (33 -12)/5 =4.25 We therefore decided to round up and use
a bin width of five days in the frequency distribution.
3. Frequency Distributions for Quantitative Data
3. Bin Limits must be chosen so that each data item belongs to one and only one
class. The lower bin limit identifies the smallest possible data value assigned to the
bin. (10) The upper bin limit identifies the largest possible data value assigned to
the class. (34).
• Download the audit data in excel.

• We can use the FREQUENCY function in Excel to count the number of

observations in each bin.

Step 1. Select cells D2:D6

Step 2. Type the formula FREQUENCY(A2:A21, C2:C6). The range A2:A21 defines

the data set, and the range C2:C6 defines the bins.

Step 3. Press CTRL+SHIFT+ENTER after typing the formula in Step 2

4. Histograms
A common graphical presentation of quantitative data is a histogram. This
graphical summary can be prepared for data previously summarized in either a
frequency, a relative frequency, or a percent frequency distribution.
Histograms can be created in Excel using the Data Analysis Tool Pak.
Step 1. Click the Data tab in the Ribbon
Step 2. Click Data Analysis in the Analyze group
Step 3. When the Data Analysis dialog box opens, choose Histogram from the list of
• Analysis Tools, and click OK
• In the Input Range: box, enter A2:A21
• In the Bin Range: box, enter C2:C6
• Under Output Options:, select New Worksheet Ply:
• Select the check box for Chart Output
• Click OK
4. Histograms
To remove the gaps between the columns in the histogram created by Excel, follow
these steps:

Step 1. Right-click on one of the columns in the histogram

Select Format Data Series…

Step 2. When the Format Data Series pane opens, click the Series Options
button, Set the Gap Width to 0%

One of the most important uses of a histogram is to provide information about the
shape, or form, of a distribution. Skewness, or the lack of symmetry, is an
important characteristic of the shape of a distribution.
5. Cumulative Distributions
A variation of the frequency distribution that provides another tabular summary of quantitative
data is the cumulative frequency distribution.
Measures of Location
• Mean(Arithmetic Mean)
• Median
• Mode
• Geometric mean
 • Suppose we also compute the median value for the 12 home sales in
Table 2.9. We first arrange the data in ascending order.

Because n 5 is even, the median is the average of the middle two values: 199,500
and 208,000.

The median of a data set can be found in Excel using the function MEDIAN. In
Figure 2.16, the value for the median in cell E3 is found using the formula
MEDIAN(B2:B13)
• A third measure of location, the mode, is the value that occurs most
frequently in a data set.
• The Excel [Link] function will return only a single most-often-
occurring value.
• To find both of the modes in Excel, we take these steps:
• Step 1. Select cells E4 and E5
• Step 2. Type the formula [Link](B2:B13)
• Step 3. Press CTRL+SHIFT+ENTER after typing the formula in Step 2.
• The geometric mean is often used in analyzing growth rates in
financial data. In these
• types of situations, the arithmetic mean or average value will
provide misleading results.
Measures of Variability

• It is often desirable to consider measures of variability or dispersion.

• Range: The simplest measure of variability is the range. The range can be
found by subtracting the smallest value from the largest value in a data set.
The range can be calculated in Excel using the MAX and MIN functions
• Variance: The variance is a measure of variability that utilizes all the data. The
variance is based on the deviation of the mean, which is the difference
between the value of each observation (xi ) and the mean. The variance in cell
E8 is calculated using the formula VAR.S(B2:B13)
Measures of Variability
• Standard Deviation: The standard deviation is the positive square root of the
variance.

• Coefficient of Variation: In some situations, we may be interested in a

descriptive statistic that indicates how large the standard deviation is relative
to the mean. This measure is called the coefficient of variation and is usually
expressed as a percentage. Excel calculation for the sample standard deviation
of the home
• sales data, which can be calculated using Excel’s STDEV.S function. The sample
standard deviation in cell E9 is calculated using the formula STDEV.S(B2:B13).
Analysing Distributions:
• Distributions are beneficial for interpreting and analyzing data. A distribution describes
the overall variability of the observed values of a variable.
• Percentiles : A percentile is the value of a variable at which a specified (approximate)
percentage of observations are below that value.

The pth percentile can also be calculated in Excel using the function [Link]
• Quartiles: It is often desirable to divide data into four parts, with each
part containing approximately. one-fourth, or 25 percent, of the
observations. These division points are referred to as the quartiles and
are defined as follows:
• Q1 first quartile, or 25th percentile
• Q2 second quartile, or 50th percentile (also the median)
• Q3 third quartile, or 75th percentile
The difference between the third and first quartiles is often referred to as
the interquartile range.
A quartile can be computed in Excel using the function [Link].
• z-Scores : A z-score allows us to measure the relative location of a value in the
data set. More specifically,
a z-score helps us determine how far a particular value is from the mean
relative to the data set’s standard deviation. The z-score can be calculated in
Excel using the function STANDARDIZE.
• Empirical Rule :The empirical rule can be used to determine the percentage of data values
that are within a specified number of standard deviations of the mean.
• Identifying Outliers :Sometimes a data set will have one or more observations with
unusually large or unusually small values. These extreme values are called outliers.
• Boxplots : A boxplot is a graphical summary of the distribution of data.
Measures of Association Between Two Variables:
• Scatter Charts : It is a useful graph for analyzing the relationship between two
variables.
• Covariance :It is a descriptive measure of the linear association between two
variables. For a sample of size n with the observations (x1 , y1), (x2 , y2 ), and
so on, the sample covariance is defined as follows:

The covariance is calculated in cell B17 using the formula

COVARIANCE.S(A2:A15, B2:B15).

Note: If the covariance is near 0, then the x and y variables are not linearly related. If the
covariance is less than 0, then the x and y variables are negatively related, which means that as x
increases, y generally decreases.
 • Correlation Coefficient: The correlation coefficient measures the relationship between two
variables, and, unlike covariance, the relationship between two variables is not affected by the
units of measurement for x and y. For sample data, the correlation coefficient is defined as
follows:

The correlation coefficient is computed using the formula CORREL(A2:A15,

B2:B15), where A2:A15 defines the range for the x variable and B2:B15 defines the
range for the y variable.
Modifying data in excel Analysing Distributions
• Sort, Filter, conditional Formatting, • Percentile
Data bars =[Link](range,percentilevalue)
Creating Distributions from data • Quartile = [Link](range,quartile
• Frequency( range),Histogram, number)
• Z-score = STANDARDIZE(range),
Measures of location • Boxplot
•Mean =AVERAGE(range).
•Median =MEDIAN(range) Measures of association between two
•Mode =[Link](range) variables
•Geometric mean =GEOMEAN(range) • Scatter charts
• Covariance =COVARIANCE.S(range,range).
Measures of Variability • correlation coefficient = CORREL(range,
• Range =MAX(range) − MIN(range). range)
• Variance =VAR.S(range)
• Standard variation =STDEV.S(range)
• Coefficient of variance (Standard
deviation/mean)*100