37. PEARSON CORRELATION

OBJECTIVE

Find out which quantitative variables are related to each other and define the degree of correlation between pairs of variables.

DESCRIPTION

This method estimates the Pearson correlation coefficient, which quantifies the strength and direction of the linear association of two variables. It is useful when we have several variables that may be correlated with each other and we want to select the ones with the strongest relationship. Correlation can be performed to choose the variables for a predictive linear regression.

Correlation Matrix

With the Excel Data Analysis complement, we can perform a correlation analysis resulting in a double-entry table with correlation coefficients (Pearson’s coefficients). We can also calculate correlations using the Excel formula “=CORREL().” The sign of the coefficient (Pearson correlation coefficient) represents the direction (if x increases then y increases = positive correlation; if x increases then y decreases = negative correlation), while the absolute value from 0 to 1 represents the strength of the correlation. Usually above 0.8 it is very strong, from 0.6 to 0.8 it is strong, and when it is lower than 0.4 there is no correlation (or it is very weak).

The figure above shows that there is a very strong positive correlation between X1 and Y and a strong positive correlation between X1–X3 and X3–Y. X3 and X4 have a weak negative correlation.

TEMPLATE

Download the Pearson Correlation template

36. INTRODUCTION TO REGRESSIONS

Regressions are parametric models that predict a quantitative outcome (dependent variable) from one or more quantitative predictor variables (independent variable). The model to be applied depends on the kind of relationship that the variables exhibit. 

Regressions take the form of equations in which “y” is the response variables that represent the outcome and “x” is the input variable, that is to say the explanatory variable. Before undertaking the analysis, it is important that several conditions are met:

• -          Y values must have a normal distribution: this can be analyzed with a standardized residual plot, in which most of the values should be close to 0 (in samples larger than 50, this is less important), or a probability residual plot, in which there should be an approximate straight line (Figure 31);
• -          Y values must have a similar variance around each x value: we can use a best-fit line in a scatter plot (Figure 32);
• -          Residuals must be independent; specifically, in the residual plot (Figure 33), the points must be equally distributed around the 0 line and not show any pattern (randomly distributed).

Figure 31: Normal Probability Plot

Figure 32: Best-Fit Line Scatter Plot

If the conditions are not met, we can either transform the variables[1] or perform a non-parametric analysis (see 47. INTRODUCTION TO NON-PARAMETRIC MODELS).

In addition, regressions are sensitive to outliers, so it is important to deal with them properly. We can detect outliers using a standardized residual plot, in which data that fall outside +3 and -3 (standard deviations) are usually considered to be outliers. In this case we should first check whether it was a mistake in collecting the data (for example a 200-year-old person is a mistake) and eliminate the outlier from the data set or replace it (see below how to deal with missing data). If it is not a mistake, a common practice is to carry out the regression with and without the outliers and present both results or to transform the data. For example, we may apply a log transformation or a rank transformation. In any case we should be aware of the implications of these transformations.

Figure 33: Standardized Residuals Plot with an Outlier

Another problem with regressions is that records with missing data are excluded from the analysis. First of all we should understand the meaning of a missing piece of information: does it mean 0 or does it mean that the interviewee preferred not to respond? In the second case, if it is important to include this information, we can substitute the missing data with a value:

• -          With central tendency measures, if we think that the responses have a normal distribution, meaning that there is no specific reason for not responding to this question, we can use the mean or median of the existing data;
• -          Predict the missing values using other variables; for example, if we have some missing data for the variable “income,” maybe we can use age and profession to for the prediction.

Check the linear regression template (see 38. LINEAR REGRESSION), which provides an example of how to generate the standardized residuals plot.

---

[1] To improve normality and variance conditions, we can try applying a log transformation to the response (dependent) variable. We can also use other types of transformations, but we must remember that the interpretation of the results is more complex when we transform our variables.

71. PICKUP MODELS

OBJECTIVE

Forecast the demand for the next periods.

DESCRIPTION

Pickup models are advanced forecasting models used in revenue management, and they can be applied in businesses that book the service in advance (airlines, hotels, theaters, etc.). The forecast is calculated using the current bookings for a certain future period and estimating the incremental bookings (pickup) from now until this future period (i.e. departure date, check-in date, etc.).

The pickup is calculated using past data, and it can be the average pickup with a specific anticipation (x days before) if we are summing it with the current bookings or it can be an average pickup ratio (total bookings / bookings x days before) if we are multiplying it by the current bookings:

In the first formula (additive pickup), the bookings on anticipation day 0 (B_DB0) are equal to the current bookings (B_DBX) plus the average pickup bookings between anticipation day x and anticipation day 0 (PU_DB(X,O)). In the second formula (multiplicative pickup), the forecast is calculated by multiplying the current bookings by the average pickup ratio (PUR_DB(X,0)).

To estimate good forecasts, it is very important to calculate carefully either the average pickup or the pickup ratio. Take as an example an airline company. If we are using the average pickup, then we should take into consideration seasonality at different levels: time of the day, day of the week, month, holidays, and so on. The amount of incremental bookings depends heavily on the demand for a specific departure time, so we should calculate the average using similar days. However, if we are using the average pickup ratio, we do not have this problem but may discover that the booking pace is different for different departure periods; for example, more people are anticipated to book during summer periods. In this case it is important to calculate the average pickup ratio from similar departure periods.

We can also take a step further and expect that the booking pace may change over time, either because the customer behavior has changed or because we have widened the booking horizon. For example, we can calculate a trend in the pickup ratio and adjust it for the prediction of future periods.

TEMPLATE

Download the Pickup Models Template

35. DESCRIPTIVE STATISTICS

OBJECTIVE

Analyze the distribution of one or several variables in a data set.

DESCRIPTION

In statistical analysis the first step is to analyze the available data. This step is also useful to check for outliers or for the assumption of normality to use these data for a particular statistical model or test (see 36. INTRODUCTION TO REGRESSIONS). Since the analysis of these assumptions is included in the chapter introducing regressions, here I will focus on the descriptive statistics that are useful for describing numeric variables:

Statistic	Description
Mean	Arithmetic mean of the data
Standard Error	Represents the difference between the expected value and the actual value
Median	Central value (the value that divides the data in two – in the case of an even number of values, the median is the mean of the two central values)
Mode	Most frequent value
Standard Deviation	A measure of how values are spread out. Mathematically, it is the square root of the variance
Sample Variance	Average of the squared differences between each value and the mean (it is also a measure of how values are spread out)
Kurtosis	A measure of the “peakedness” and flatness of the distribution *. “0” means that the shape is that of a normal distribution, a flatter distribution has negative kurtosis, and a more peaked distribution has positive kurtosis
Skewness	A measure of the symmetry of the distribution. “0” means that the distribution is symmetrical. If the value is negative, the distribution has a long tail on the left, and if it is positive, it has a long tail on the right. As a rule of thumb, a distribution is considered to be symmetrical if the kurtosis is between 1 and -1
Range	The difference between the largest and the smallest value
Minimum	The smallest value
Maximum	The largest value
Sum	The sum of values
Count	The number of values

As shown in the template, these statistics can be calculated either using the Excel complement “Data Analysis” or using the Excel functions. The same is valid for creating a histogram, with which we can analyze the frequency of values and gain an idea of the type of distribution. In Figure 30 a sample including age data is represented in a histogram. On the right a box plot provides more information, dividing our data into quartiles (grouping the values into 4 groups containing 25% of the values). The plot shows that 50% of people are aged approximately between 33 and 46 years, while the rest are spread across a bigger range of ages (25% from 46 to 64 and 25% from 18 to 33).

Histogram and Box Plot

In the template we can see how the two graphs have been created. For the histogram we need to decide which age groups we want to use and fill a table with them. Then we can use the formula “=FREQUENCY” by selecting all the cells on the right of the age groups and pressing “SHIFT + CONTROL + ENTER,” and the formula will provide the frequencies. For the box plot we have to make some calculations and perform some tricks using a normal column chart if we have an older version than Excel 2016. The template and several tutorials can be consulted on the Internet.

Finally, we may have to identify which kind of distribution our data approximate the most (for example to conduct a Monte Carlo simulation). There is no specific method, but we can start by using a histogram and comparing the shape of our data with the shapes of theoretical distributions. The following URL provides 22 Excel templates with graphs and data of different distributions: http://www.quantitativeskills.com/sisa/rojo/distribs.htm.

If our variables are categorical, we can analyze them using a frequency table (count and percentage frequencies). We can also analyze the distribution of frequencies. In the case that our variables are ordinal, we should use the same method for categorical variables (for example if the categories are the answer to a satisfaction question with ordinal answers like “very bad,” “bad,” etc.). However, in some cases we may want to analyze ordinal variables with statistics used for numerical ones (for example, if we are analyzing answers to a question about the quality of services on a scale from 1 to 10, it can be interesting to calculate the average score, range, etc.).

TEMPLATE

Download the Descriptive Statistics Template

* Even if Kurtosis has been traditionally explained in terms of peakedness/flatness it has been demonstrated that this is incorrect since it’s the tails that mostly account for it, not the central peak. https://www.ncbi.nlm.nih.gov/pmc/articles/PMC4321753/

16. SWOT ANALYSIS

OBJECTIVE

Concerning a company, identify the main strengths to maintain, opportunities to exploit, threats to reduce, and weaknesses to manage.

DESCRIPTION

The SWOT analysis is the consolidation of internal and external analyses, and it is used for strategy definition, usually in its first stage, to establish the bases of several strategic actions:

• -          External analysis: analysis of the external opportunities and threats resulting from the external environment (PEST, PESTEL); for example, the increasing use of mobile devices can be an opportunity to exploit or the increasing cost of energy can be a potential threat.
• -     Internal analysis: internal strengths and weaknesses resulting from the internal analysis (MOST, resource audit, etc.) and from competitive analysis (competitive map, importance–performance matrix). For example, a well-known brand is a strength and a poor company strategy definition is a weakness.
• 
  

SWOT Analysis

From this matrix the analyst can define strategic actions that exploit existing opportunities, using the company’s strengths as critical success factors and reducing the risks that can be provoked by potential threats and the company’s weaknesses. Since this is a consolidation of several other analyses, the sources of data depend on previous analyses and on several gathering techniques, such as brainstorming, the Delphi method, and surveys. The consolidation process can be performed directly by the analyst, but it is usually a good practice to consolidate the results or submit the analysis to other company members, for example through workshops or think tanks.

TEMPLATE

Download the SWOT Analysis Template

42. BAYESIAN APPROACH TO HYPOTHESIS TESTING

OBJECTIVE

Identify the probability of a hypothesis using the probability of related events.

DESCRIPTION

This probabilistic approach is often used in logic tests, which may require a statement such as the following to be solved:

0.5% of the population suffers a certain disease and those with this disease that take a clinical test are diagnosed correctly in 90% of cases. You also know that you have on average 10% of false positive tests on people who do not have the disease. A person has been diagnosed as positive; what is the probability that he actually has the disease?

This problem is solved by finding the percentage of true positives (the test is positive and the person has the disease) in the total number of positive tests, and it can be approached by drawing the following matrix and calculating the missing percentages:

Matrix of the Bayesian Inference Method

The answer is given by dividing 0.45% by 10.45%, giving a 4.31% possibility of that person being sick. More formally, the problem is resolved by the following equation:

where P is the probability, A is having the disease, and B is when the test is positive; therefore, P(A|B) is the probability of having the disease when the test is positive and P(B|A) is the probability of obtaining a positive test when the patient has the disease. The template shows how the values have been calculated starting from the proposed problem.

TEMPLATE

Download the Bayesan Hypothesis Testing Template

79. SALES FUNNEL ANALYSIS

OBJECTIVE

Improve the conversion of prospective customers buy optimizing the sales funnel.

DESCRIPTION

Models that estimate demand usually focus on a specific aspect of the sales channel. For example, a pricing model usually defines the choice of customers in front of a set of possible products or services; however, it does not take into consideration the awareness of a product in the market (it usually assumes full awareness to simplify the analysis).

Ideally, these pricing models (see PRICING AND DEMAND models) have to be included in a broader framework called the sales funnel. A sales funnel consists of four main elements:

• -          Market: the total number of potential customers;
• -          Awareness: the people in the market who know the product;
• -      Consideration: the people who know the product and take it into consideration for purchasing;
• -          Conversion: of the people who consider the product, those who actually buy it.

These elements are related to each other, for example:

• -          70% of people in the market are aware of the product (market awareness);
• -          80% of people who are aware of the product consider purchasing it;
• -          10% of people considering it actually purchase it.

Sales Funnel

We can calculate that 56% of all potential customers know the product (70% * 80%) and that 5.6% buy the product (70% * 80% * 10%). We also can calculate that 8% of people who know the product will buy it (5.6% / 70%).

An increase in the number of people in the market will result in an increase in purchases, since (usually) we assume that the relationships or ratios remain the same: 70%, 80%, and 10%. Data on market changes can be derived from public population data. For example, if our potential clients are males with an annual income between €40,000 and €80,000 and this sector of the population increases, then our “market” increases.

Besides the market, we can act on the relationships between market awareness, awareness consideration, and consideration conversion to increase the sales.

Market Awareness

In our example 70% of people know our product, but we can increase this ratio by investing in advertising, public relations, special offers, and so on. The effect on sales of an increase in people’s awareness can be calculated using the other ratios, which we assume do not change (80% awareness consideration and 10% consideration conversion). However, we have to consider that this assumption may not be completely true and either use corrective measures or just be cautious in interpreting the results. In fact, the remaining 30% of the people in the market who do not know the product may have lower consideration and conversion ratios, for example because they are more loyal to a specific brand and they do not know us or because they are not interested in looking for an alternative product. In this case, even if we make them aware of our product, it is probable that the awareness consideration ratio of these people will be far lower than our 80%.

Awareness Consideration

Out of the people who know our product, some of them will not consider it for a purchase either because they are loyal to a brand or because they think that our quality and/or price levels are not acceptable. There are two main ways to improve consideration:

• -    If the price or quality are good but the low consideration is due to people having a worse perception than the reality, we should invest in communication;
• -   If people have a correct perception of the price and quality, we can improve them (higher quality and lower price).

Investing in communication will improve the number of aware people who consider our product, and we can calculate the increase in sales using the consideration conversion ratio of 10%. We assume that the conversion ratio does not change, but, as in the abovementioned example, this assumption may not be completely accurate.

If we improve the quality or lower the price, it is probable that the consideration conversion rate will also change, since the product is more attractive to those people who have already considered it.

Consideration Conversion

In this case price and quality are the main drivers, but here they are compared with other alternatives (this is a similar concept to the reference price vs maximum price explained in the introduction to PRICING AND DEMAND models). The effect of a price modification can be derived from several pricing and elasticity models.

Optimization

With the proposed model, we calculate the impact on volume, more specifically the number of additional customers, but we should include other elements to decide which strategy to pursue:

• -      Revenues: revenues depend on the average number of purchases and on the average price. Take into consideration that if we lower the price we may also have a positive effect on the number of purchases (depending on the product) but there will also be a negative effect since we are selling cheaper;
• -      Costs: the costs of advertising, communications, and quality improvements have to be included in the model.

Once we have defined the structure of the costs and revenues associated with the model, we can use Excel Solver to find the optimal investment plan (see 80. OPTIMIZATION).

TEMPLATE

Download the Sales Funnel Analysis Template