π Lecture 28 β MEASURES OF DISPERSION CORRELATION PART 1
π Overview: This lecture marks the beginning of Module 6 and introduces two core statistical concepts. First, it covers key measures of dispersion, including variance, standard deviation, coefficient of variation, and mean deviation, explaining how they quantify the spread of data around the central value. Second, it introduces the fundamentals of regression analysis and correlation, including the use of scatter diagrams to visualize the association between variables. These tools are essential for moving from simple description of data to understanding relationships between different data sets, which is critical for business forecasting and analysis.
ποΈ Topics Covered
This lecture reviews key measures of dispersion, including how to calculate variance and standard deviation for both raw data and grouped data, using the example of motorway fatalities. It then demonstrates how to compare variability across different data sets using the coefficient of variation (CV) and calculates the mean deviation about the mean and median. The second part of the lecture introduces regression analysis, focusing on creating a scatter diagram and interpreting positive and negative linear relationships between variables.
π Lecture Summary
VARIANCE
Variance is one of the most important measures of dispersion, as it gives the average of the squared deviations from the mean. For a population, the sum of squared deviations is divided by the total number of values in the population (N). For a sample, the sum is divided by the number of observations minus one (n-1).
STANDARD DEVIATION
Standard deviation is the most important and widely used measure of dispersion. It is the positive square root of the variance, which brings the measure back into the original unit of measurement.
π Definition β Standard Deviation: A measure of the spread of data around the mean, calculated as the square root of the variance.
π Formula (Sample): s = β[ Ξ£(x - xΜ)Β² / (n - 1) ] β The square root of the average squared deviation from the mean for a sample.
π Example: For the number of fatalities (4, 6, 2, 0, 3, 5, 8), the mean is 4. The sum of squared deviations is 42. For a sample, Variance = 42 / (7-1) = 7. Standard Deviation = β7 β 2.65 fatalities.
For grouped data in a frequency distribution, each squared deviation around the mean must be multiplied by the appropriate frequency (f) before summation.
COMPARING STANDARD DEVIATIONS
In many situations, it is necessary to calculate a population standard deviation based on a sample's standard deviation. The sample SD uses n-1 for the division. When a dataset of (3, 4, 5, 6, 7, 10) is treated as a sample, the SD is 4.2426; when treated as a population, the SD is 3.9686. This shows that using a sample SD as a direct estimate for the population will overestimate the true population spread.
π‘ Why this matters: Datasets with the same mean can have very different standard deviations. For example, sets A, B, and C all have a mean of 15.5 but have SDs of 3.338, 0.9258, and 4.57, respectively. Mean and SD together form a complete description of the central tendency and spread of data.
COEFFICIENT OF VARIATION
The Coefficient of Variation (CV) shows the dispersion of the standard deviation relative to the mean, expressed as a percentage. This is crucial for comparing variability between different datasets with different units or means.
π Formula: CV = (s / xΜ) * 100 β The standard deviation divided by the mean, multiplied by 100 to get a percentage.
π Example: If Stock A has a CV of 10% and Stock B has a CV of 5%, there is much greater variation in the price of Stock A relative to its mean.
π Example (Earnings): Country 1 has mean earnings of $19.50 with an SD of $4. CV = (4/19.5) * 100 = 20.5%. Country 2 has a mean of Rs. 75 with an SD of Rs. 28. CV = (28/75) * 100 = 37.3%. This shows that Country 2 has greater variability in earnings.
MEAN DEVIATION
The mean deviation of a set of data is defined as the arithmetic mean of the absolute deviations measured either from the mean or from the median.
π Formula for Sample (about mean): M.D. = Ξ£ | x - xΜ | / n β The sum of the absolute differences between each data point and the mean, divided by the number of data points.
π Example: For marks (45, 32, 37, 46, 39, 36, 41, 48, 36), the Mean = 40 and Median = 39. The sum of absolute deviations from the mean is 40. Mean Deviation from Mean = 40 / 9 β 4.4 marks. The sum of absolute deviations from the median is 39. Mean Deviation from Median = 39 / 9 β 4.3 marks.
π Formula for Grouped Data (about mean): M.D. = Ξ£ f_i | x_i - xΜ | / Ξ£ f_i β The sum of each frequency multiplied by the absolute deviation of its midpoint from the mean, divided by the total number of data points (N).
REGRESSION ANALYSIS
The primary objective of regression analysis is the development of a regression model to explain the association between two or more variables in a given population. A regression model is a mathematical equation that provides a prediction of the value of a dependent variable based on known values of one or more independent variables.
Key questions addressed in regression analysis include:
- Determining the simple linear regression equation.
- Understanding different measures of variation in regression and correlation.
- Understanding the assumptions of regression and correlation.
- Conducting residual analysis.
- Making inferences about the slope and estimating predicted values.
- Identifying pitfalls and ethical issues.
SCATTER DIAGRAM
The first step in regression analysis is to plot the values of the dependent and independent variables in the form of a scatter diagram. The pattern formed by the scatter of the points indicates whether and what degree of association exists between them. If the points appear to cluster around a straight line, there is a distinct linear correlation.
Types of Regression Models
There are two primary types of linear models: positive linear relationships and negative linear relationships.
- Positive Linear Relationship: The value of the dependent variable increases as the value of the independent variable increases.
- Negative Linear Relationship: The value of the dependent variable decreases as the value of the independent variable increases.
β Key Takeaways
A student must first understand that variance and standard deviation are the primary measures of spread, with the standard deviation being the square root of the variance and expressed in the original data units. The coefficient of variation is critical for comparing the relative variability of different datasets, especially when their means are vastly different or in different units. The concept of mean deviation provides a simpler, though less statistically powerful, measure of spread by averaging absolute deviations. Crucially, the lecture marks the beginning of regression and correlation analysis, which requires plotting a scatter diagram as the first step to visually inspect a relationship, and distinguishing between positive and negative linear relationships is fundamental to interpreting the association between two variables.
π§ Quick Revision Questions
- What is the key difference between calculating the variance for a sample versus a population?
- Why is the standard deviation considered a more useful measure than the variance for describing data?
- A stock has a mean price of $50 and a standard deviation of $10. Another stock has a mean price of $200 and a standard deviation of $30. Calculate the Coefficient of Variation for each and state which stock has greater relative variability.
- Given the dataset {10, 12, 8, 10, 15}, calculate the mean deviation about the mean (show your steps).
- When looking at a scatter diagram of two variables, what pattern would indicate a negative linear relationship?
π Lecture 29 β MEASURES OF DISPERSION CORRELATION PART 2
π Overview: This lecture introduces correlation analysis, a statistical method used to measure the strength and direction of the relationship between two random variables. It explains the difference between correlation and regression, how to calculate the correlation coefficient (r) using both the covariance formula and a shortcut formula, and how to interpret its value. The lecture also demonstrates practical computation through a real-world example and shows how to perform correlation analysis in Excel, which is essential for identifying associations before conducting regression analysis.
ποΈ Topics Covered
The lecture reviews concepts from Lecture 28, then delves into the definition and purpose of correlation analysis, distinguishes it from regression analysis, explains the interpretation of the correlation coefficient through positive, negative, and zero correlation cases with graphical illustrations, provides the mathematical formulas for calculating r (including covariance and a shortcut formula), presents a detailed step-by-step example calculating correlation between Mathematics and Statistics marks, discusses important warnings about causation versus correlation, and finally demonstrates how to calculate correlation coefficients using Excel tools and functions.
π Lecture Summary
CORRELATION
Correlation is a measure of the strength or the degree of relationship between two random variables. When do we use correlation? It will be used when we wish to establish whether there is a degree of association between two variables. If this association is established, then it makes sense to proceed further with regression analysis. Regression analysis determines the constants of the regression. You cannot make any predictions with results of correlation analysis. Predictions are based on regression equations.
CORRELATION ANALYSIS
To analyze the strength of the relationship or co-variation between two variables, we use correlation analysis. Correlation analysis contributes to the understanding of economic behavior, aids in locating the critically important variables on which others depend, may reveal to the economist the connections by which disturbances spread, and suggest to them the path through which stabilizing forces may become effective.
SIMPLE LINEAR CORRELATION VERSUS SIMPLE LINEAR REGRESSION
The calculations for linear correlation analysis and regression analysis are the same. In correlation analysis, one must sample randomly both X and Y. Correlation deals with the association (importance) between variables, whereas Regression deals with prediction (intensity).
The slide shows three types of correlation for both positive and negative linear relationships. In the first figure (r = 0.9), the data points are practically in a straight line. This kind of association or correlation is near perfect. This applies to negative correlation also. The graphs where r = 0.5, the points are more scattered; there is a clear association, but this association is not very pronounced. In graphs where r = 0, there is no association between variables.
CORRELATION COEFFICIENT
For calculation of the correlation coefficient:
-
A standardized transform of the covariance (sxy) is calculated by dividing it by the product of the standard deviations of X (sx) and Y (sy).
-
It is called the population correlation coefficient and is defined as: r = sXY / sX sY
Or r = Cov(X,Y) / β[Var(X) Var(Y)]
Where covariance of X and Y is defined as: Cov(X,Y) = Ξ£ (X β XΜ)(Y β Θ²) / n
This formula is a bit cumbersome to apply. Therefore, we may use the following shortcut formula:
π Definition β Correlation Coefficient (r): A pure number that lies between -1 and 1, measuring the strength and direction of a linear relationship between two variables.
π Formula (Shortcut): r = [Ξ£XY β (Ξ£X)(Ξ£Y)/n] / β[ [Ξ£XΒ² β (Ξ£X)Β²/n] [Ξ£YΒ² β (Ξ£Y)Β²/n] ]
β Plain English meaning: This formula calculates the standardized measure of how X and Y vary together, adjusted for their individual variabilities.
It should be noted that r is a pure number that lies between -1 and 1 i.e. -1 < r < 1.
Case 1: Positive correlation: 0 < r < 1 In case of a positive linear relationship, r lies between 0 and 1. The closer the points are to the UPWARD-going line, the STRONGER is the positive linear relationship, and the closer r is to 1.
Case 2: No correlation: r = 0 In such a situation, X and Y are said to be uncorrelated. The points show no linear pattern.
Case 3: Negative correlation: -1 < r < 0 The points cluster around a DOWNWARD-sloping line.
π‘ Why this matters: The sign of r tells you the direction of the relationship (positive or negative), while the magnitude tells you the strength (closer to 1 = stronger).
Warning
- Existence of a high correlation does not mean there is causation, which means there may be a correlation, but it does not make things happen because of that.
- There can exist spurious correlations. Correlations can arise because of the action of a third unmeasured or unknown variable. In many situations, correlation can be high without any solid foundation.
EXAMPLE
Suppose that the principal of a college wants to know if there exists any correlation between grades in Mathematics and grades in Statistics. A random sample of 9 students is selected:
| Student | Marks in Mathematics (X) | Marks in Statistics (Y) |
|---|---|---|
| A | 5 | 11 |
| B | 12 | 16 |
| C | 14 | 15 |
| D | 16 | 20 |
| E | 18 | 17 |
| F | 21 | 19 |
| G | 22 | 25 |
| H | 23 | 24 |
| I | 25 | 21 |
A scatter diagram is plotted with Marks in Mathematics on the X-axis and Marks in Statistics on the Y-axis, showing an upward trend.
To compute the correlation coefficient, the following calculations are made:
| X | Y | XΒ² | YΒ² | XY |
|---|---|---|---|---|
| 5 | 11 | 25 | 121 | 55 |
| 12 | 16 | 144 | 256 | 192 |
| 14 | 15 | 196 | 225 | 210 |
| 16 | 20 | 256 | 400 | 320 |
| 18 | 17 | 324 | 289 | 306 |
| 21 | 19 | 441 | 361 | 399 |
| 22 | 25 | 484 | 625 | 550 |
| 23 | 24 | 529 | 576 | 552 |
| 25 | 21 | 625 | 441 | 525 |
| 156 | 168 | 3024 | 3294 | 3109 |
π Example: Using the shortcut formula: r = [3109 β (156)(168)/9] / β[ [3024 β (156)Β²/9] [3294 β (168)Β²/9] ]
Step 1: Calculate numerator = 3109 β 2912 = 197 Step 2: Calculate denominator: [3024 β 2704] = 320 [3294 β 3136] = 158 β(320 Γ 158) = β50560 = 224.86
Step 3: r = 197 / 224.86 = 0.88
There exists a strong positive linear correlation between marks in Mathematics and marks in Statistics for these 9 students.
EXCEL Tools
- For summary of sample statistics, use: Tools / Data Analysis / Descriptive Statistics
- For individual sample statistics, use: Insert / Function / Statistical and select the function you need.
EXCEL Functions
- In EXCEL, use the CORREL function to calculate correlations.
- The correlation coefficient is also given on the output from TOOLS, DATA ANALYSIS, CORRELATION or REGRESSION.
Scatter Diagram Two Variables
You can develop a scatter diagram using EXCEL chart wizard. A scatter diagram of Advertisement and Sales over the years shows that you cannot always draw conclusions about the degree of association. However, a scatter diagram for sales versus advertisement shows a fairly high degree of association, with a positive and linear relationship.
CORRELATION COEFFICIENT USING EXCEL
The correlation coefficient for two streams of data was calculated using the formula Cov(x,y)/Sx.Sy. For a dataset with variables x (cells A67 to A71) and y (cells B67 to B71), calculations for xΒ², yΒ², xy, XΜ, Θ², and Cov(x,y) were made in columns C, D, E, F, and G respectively. Key Excel formulas used:
- Cell A72: Sum of x (=SUM(A67:A71))
- Cell B72: Sum of y (=SUM(B67:B71))
- Cell F72: Mean of x (=A72/5)
- Cell G72: Mean of y (=B72/5)
- Cell F73: Sx (=SQRT(C72/5 β F72*F72))
- Cell G73: Sy (=SQRT(D72/5 β G72*G72))
- Cell H73: Cov(x,y) (=E72/5 β F72*G72)
- Cell H74: Correlation coefficient (=H73/(F73*G73))
CORREL function syntax: CORREL(array1, array2)
- Array1 is a cell range of values.
- Array2 is a second cell range of values.
Remarks:
- Arguments must be numbers or references containing numbers.
- Text, logical values, or empty cells are ignored.
- Arrays must have the same number of data points, or #N/A error results.
- If either array is empty or standard deviation is zero, #DIV/0! error results.
In an Excel example, with X and Y arrays in cells A79 to A83 and B79 to B83 respectively, the formula =CORREL(A79:A83, B79:B83) entered in cell D84 gives a correlation coefficient (r) of 0.8.
β Key Takeaways
The correlation coefficient (r) is a pure number between -1 and 1 that measures the strength and direction of a linear relationship between two random variables, with sign indicating direction and magnitude indicating strength. You must remember the shortcut formula r = [Ξ£XY β (Ξ£X)(Ξ£Y)/n] / β[ [Ξ£XΒ² β (Ξ£X)Β²/n] [Ξ£YΒ² β (Ξ£Y)Β²/n] ] for manual calculation. Critically, correlation does not imply causation β a high r may result from a third unmeasured variable or be spurious. Correlation analysis determines the degree of association between variables, while regression analysis is used for prediction. Finally, you can efficiently compute r in Excel using either the CORREL function or the Data Analysis Toolpak.
π§ Quick Revision Questions
- What is the range of possible values for the correlation coefficient r, and what do the extreme values (-1, 0, +1) indicate?
- Why does a high correlation coefficient (e.g., r = 0.9) not necessarily mean that one variable causes the other?
- In the example calculating correlation between Mathematics and Statistics marks, what was the computed value of r and what does it tell us about the relationship?
- What is the difference between correlation analysis and regression analysis?
- What Excel function would you use to directly calculate the correlation coefficient between two arrays?
π Lecture 30 β Measures of Dispersion
π Overview: This lecture covers the process of line fitting and introduces how to use Microsoft Excel's built-in statistical analysis tools, including the Analysis ToolPak, to compute sample statistics, the slope, and the intercept of a linear regression line. Understanding these techniques is crucial for analyzing relationships between variables and making predictions from data.
ποΈ Topics Covered
The lecture begins with a review of sample statistics and instructions for using Excel to generate them via the Data Analysis ToolPak. The main focus is on fitting a straight line to data, with detailed explanations of the SLOPE and INTERCEPT functions, including their syntax, equations, and practical examples.
π Lecture Summary
EXCEL SUMMARY OF SAMPLE STATISTICS
To obtain a comprehensive set of descriptive statistics for a dataset, Excel provides a dedicated tool. You can access this by navigating to Tools > Data Analysis > Descriptive Statistics. This generates a summary table including mean, median, mode, standard deviation, and more. For a single, specific statistic (e.g., just the mean), you can use Insert > Function > Statistical and then choose the specific function you require.
π‘ Why this matters: The Descriptive Statistics tool automates the calculation of many key measures, saving time and reducing calculation errors compared to manual computation.
EXCEL STATISTICAL ANALYSIS TOOL
Excel's Data Analysis add-in provides access to various statistical procedures, including regression analysis. To use it: first click Data Analysis on the Tools menu. If it is not visible, you need to load the Analysis ToolPak. In the Data Analysis dialog box, select the tool you need (e.g., Regression) and set the required analysis options. The Help button provides further details on each option.
LOAD THE ANALYSIS TOOLPAK
If the Data Analysis command is not on the Tools menu, you must load the Analysis ToolPak add-in. To do this: click Add-Ins on the Tools menu. In the Add-Ins available list, check the box for Analysis ToolPak, and then click OK. Follow any subsequent setup instructions from the installation program.
SLOPE
The SLOPE function returns the slope of the linear regression line that best fits a set of data points. The slope represents the rate of change of the dependent variable (y) with respect to the independent variable (x). It is calculated as the vertical distance divided by the horizontal distance between any two points on the line.
π Definition β Linear Regression Line: The best-fitting straight line through a set of data points, used to model the relationship between two variables.
π Formula: The equation for the slope ((b)) of the regression line is:
[ b = \frac{n\sum xy - (\sum x)(\sum y)}{n\sum x^2 - (\sum x)^2} ]
- (n) = number of data points
- (x) = independent variable
- (y) = dependent variable
β Plain-English Meaning: This formula calculates the steepness and direction of the best-fit line by measuring how much y changes for each unit change in x.
Syntax: SLOPE(known_y's, known_x's)
- known_y's: The array or cell range of numeric dependent data points.
- known_x's: The array or cell range of independent data points.
Remarks:
- Arguments must be numbers or references containing numbers. Text, logical values, or empty cells are ignored; zero values are included.
- If
known_y'sandknown_x'sare empty or have a different number of data points,SLOPEreturns the#N/Aerror value.
π Example:
Given known y-values in cells A4:A10 and known x-values in cells B4:B10, the formula =SLOPE(A4:A10, B4:B10) is entered in cell A11. The result displayed in cell B12 is 0.305556, representing the slope of the regression line.
INTERCEPT
The INTERCEPT function calculates the point where a line will intersect the y-axis (the y-intercept) using existing x-values and y-values. The intercept point is determined by the best-fit regression line. Use this function to find the value of the dependent variable when the independent variable is 0 (zero). For example, it can predict a metal's electrical resistance at 0Β°C when data points were taken at room temperature and higher.
π Definition β y-Intercept: The value of y when x equals 0; the point where the regression line crosses the y-axis.
π Formula: The equation for the intercept ((a)) of the regression line is:
[ a = \bar{y} - b\bar{x} ]
where the slope (b) is calculated as:
[ b = \frac{n\sum xy - (\sum x)(\sum y)}{n\sum x^2 - (\sum x)^2} ]
- (\bar{y}) = mean of y-values
- (\bar{x}) = mean of x-values
β Plain-English Meaning: The intercept is calculated by taking the mean of y and subtracting the product of the slope and the mean of x.
Syntax: INTERCEPT(known_y's, known_x's)
- known_y's: The dependent set of observations or data.
- known_x's: The independent set of observations or data.
Remarks:
- Arguments must be numbers or references containing numbers. Text, logical values, or empty cells are ignored; zero values are included.
- If
known_y'sandknown_x'scontain a different number of data points or no data points,INTERCEPTreturns the#N/Aerror value.
π Example:
Given y-values in cells A18:A22 and x-values in cells B18:B22, the formula =INTERCEPT(A18:A22, B18:B22) is entered in cell A24. The answer displayed in cell B25 is 0.048387, representing the y-intercept of the regression line.
β Key Takeaways
Students must remember how to load and use Excelβs Analysis ToolPak for data analysis and specifically the Descriptive Statistics tool for a quick summary. The SLOPE and INTERCEPT functions are the core of line fitting, providing the coefficients (b and a) for the linear regression equation (y = a + bx). The formulas for slope and intercept are essential, as is understanding that slope measures the rate of change and intercept gives the predicted y-value when x=0. Always ensure the x and y data ranges have the same number of data points to avoid #N/A errors.
π§ Quick Revision Questions
- Which menu path do you use in Excel to access the Descriptive Statistics tool?
- What does the SLOPE function calculate and what does it represent in a linear regression?
- Write the formula for the y-intercept of a regression line, using the sample means and the slope.
- What error value does the INTERCEPT function return if
known_y'sandknown_x'scontain a different number of data points? - Describe a practical scenario where you would use the INTERCEPT function to make a prediction.
π Lecture 31 β Line Fitting Part 2
π Overview: This lecture continues the study of line fitting by introducing population and sample linear regression models. It covers the mathematical representation of linear relationships, calculation of regression coefficients, and the use of EXCELβs Regression Tool for data analysis, along with testing the significance of correlation coefficients.
ποΈ Topics Covered
The lecture reviews types of regression models including population and sample linear regression, the linear function equation with Y-intercept and slope, and the interpretation of these parameters. It provides a complete regression equation example with calculations, demonstrates EXCELβs Data Analysis Regression Tool with step-by-step instructions, explains EXCEL output components like Multiple R, R Square, and P-value, and concludes with sampling distribution in r and significance testing using critical value tables.
π Lecture Summary
Objectives
This lecture reviews concepts from Lecture 30 and continues the topic of Line Fitting. The main objective is to learn about regression models and how to fit a straight line to data.
Types of Regression Models
There are different types of regression models. The simplest is the Simple Linear Regression Model, which represents a relationship between variables that can be represented by a straight line equation. To determine whether a linear relationship exists, a Scatter Diagram is developed first.
In linear regression, two types of models are considered. The Population Linear Regression represents the linear relationship between the variables of the entire population (all data). It is customary to carry out sample surveys and determine linear relationships between two variables based on sample data. Such regression analysis is called Sample Linear Regression.
Relationship between Variables
The relationship between variables is described by a Linear Function. The change of one variable causes the other variable to change. The relationship describes the dependency of one variable on the other.
If the relationship between the variables is exactly linear, the mathematical equation describing the linear relation is written as:
Y = a + bX
Where:
- Y represents the dependent variable
- X represents the independent variable
- a represents the Y-intercept (the value of Y when X is equal to zero)
- b represents the slope of the line (the value of tan ΞΈ, where ΞΈ represents the angle between the line and the horizontal axis)
Interpretation of βaβ and βbβ
A very important point is that MANY lines can be drawn through the same scatter diagram.
In contrast to exact relationships, some situations involve non-exact linear relationships. For this, an unknown random error variable is added:
Yα΅’ = a + bXα΅’ + eα΅’
The distance of the points from the regression line (obtained by inserting values of X into the equation) is the random error. The intercept is shown on the Y-axis.
For the sample regression equation, the intercept is denoted as Bβ, the slope as Bβ, and the random error as eβ. These different notations distinguish between population regression and sample regression.
Regression Equation Example
Problem: Compute the least square regression equation of Y on X for the following data:
| X | 5 | 6 | 8 | 10 | 12 | 13 | 15 | 16 | 17 |
|---|---|---|---|---|---|---|---|---|---|
| Y | 16 | 19 | 23 | 28 | 36 | 41 | 44 | 45 | 50 |
Solution:
The estimated regression line of Y on X is:
ΕΆ = a + bX
From the previous lecture, the equation for the slope of the regression line is:
b = [nβXY - (βX)(βY)] / [nβXΒ² - (βX)Β²]
And the equation for the intercept is:
a = Θ² - bXΜ
From the given data:
XΜ = βX/n = 102/9 = 11.33
Θ² = βY/n = 302/9 = 33.56
b = [nβXY - (βX)(βY)] / [nβXΒ² - (βX)Β²] = 2.831
a = Θ² - bXΜ = 33.56 - (2.831)(11.83) = 1.47
Hence, the desired estimated regression line is:
ΕΆ = 1.47 + 2.831X
π Definition β Least Squares Regression Line: The line that minimizes the sum of the squared vertical distances between the observed data points and the line itself.
π Formula: ΕΆ = a + bX β The predicted value of the dependent variable equals the Y-intercept plus the slope multiplied by the independent variable.
π Example: For X = 10, ΕΆ = 1.47 + 2.831(10) = 29.78. For X = 17, ΕΆ = 1.47 + 2.831(17) = 49.60.
EXCEL Regression Tool
Regression Analysis can be carried out easily using EXCELβs Regression Tool. The process involves:
- Going to the Tools menu and selecting the Data Analysis menu
- Selecting the Regression analysis tool and clicking OK
- In the Regression dialog box, specifying the Input Y Range and Input X Range
- Optionally specifying labels, confidence level, and output range
- Clicking OK to generate the SUMMARY OUTPUT
The EXCEL Regression Tool generates detailed output including:
Multiple R β The Correlation Coefficient R Square β The Coefficient of determination Standard Error of Mean (STEM) β Standard deviation of population/sample size T-Statistic β (sample slope β population slope) / Standard error
RSQ Function
There is a separate function RSQ in EXCEL to calculate the coefficient of determination (square of r). The r-squared value can be interpreted as the proportion of the variance in y attributable to the variance in x.
Syntax: RSQ(known_yβs, known_xβs)
Example: =RSQ(A2:A8,B2:B8) returns 0.05795
P-Value
In the EXCEL Regression Tool, the P-value is defined as the probability of not getting a sample slope as high as the calculated value. The smaller the value, the more significant the result. In the example, P-value = 0.000133, meaning the slope is very significantly different from zero.
π‘ Why this matters: A small P-value indicates that X and Y are strongly associated, and the relationship is statistically significant.
Sampling Distribution in r
It is possible to construct a sampling distribution for r similar to those for sampling distributions for means and percentages. Tables give minimum values of r (ignoring sign) for a given sample size to demonstrate a significant non-zero correlation at various significance levels.
Note: v = degrees of freedom = n - 2 in all these calculations.
Sampling Distribution in r β Example 1
Problem: Sample size n = 5. Null hypothesis: r = 0. Calculated coefficient = 0.8. Test significance at 5% confidence level.
Solution: Look in the table at row with v = n - 2 = 3 and column headed by 0.05.
The tabulated value from the Pearson Product-Moment Correlation Coefficient Table of Critical Values = 0.878.
Sample value of 0.8 is less than 0.878.
Conclusion: Correlation is not significantly different from zero at 5% level. Variables are not strongly associated.
Sampling Distribution in r β Example 2
Problem: Sample size n = 5. Null hypothesis: r = 0. Calculated coefficient = -0.95. Test significance at 5% confidence level.
Solution: Look at row with v = 3 and column headed by 0.05. Tabulated value = 0.878.
Sample value of 0.95 (ignoring sign) is greater than 0.878.
Conclusion: Correlation is significantly different from zero at 5% level. Variables are strongly associated.
β Key Takeaways
The estimated regression line follows the form ΕΆ = a + bX, where the slope b measures the change in Y per unit change in X and the intercept a is the value of Y when X is zero. The least squares method minimizes error by calculating b using the formula [nβXY - (βX)(βY)] / [nβXΒ² - (βX)Β²] and a using Θ² - bXΜ. EXCELβs Regression Tool provides key outputs including Multiple R (correlation coefficient), R Square (coefficient of determination), and P-value (significance measure). For testing whether a correlation is significant, compare the calculated r value (ignoring sign) to the critical value from the table at degrees of freedom n-2; if the calculated r exceeds the tabulated value, the correlation is significantly different from zero.
π§ Quick Revision Questions
- What is the formula for the slope b in least squares regression, and what do βXY, βX, βY, and n represent?
- In the regression equation ΕΆ = 1.47 + 2.831X, what is the predicted value of Y when X = 10?
- How do you interpret the R Square value in EXCEL regression output?
- For a sample size of n = 7, what is the degrees of freedom (v) when testing significance of correlation?
- If the calculated correlation coefficient is 0.6 and the tabulated critical value at 5% level is 0.754, what conclusion can you draw?
π Lecture 32 β Time Series and Exponential Smoothing Part 1
π Overview: This lecture begins with a review of simple linear regression, including calculation and interpretation of the regression equation using a store sales example. It then introduces time series analysis, focusing on graphical examination of trends and seasonal variations, and explains the moving averages method for extracting trends from data with repeating patterns.
ποΈ Topics Covered
The lecture reviews simple linear regression with a complete worked example using store square footage and sales data, including scatter diagram preparation and regression equation calculation using both manual formulas and Excelβs Regression Tool. It then covers time series concepts including examination of graph trends, extraction of trend from data, moving averages calculation, and analysis of seasonal variations by comparing actual values to trend values.
π Lecture Summary
SIMPLE LINEAR REGRESSION EQUATION. EXAMPLE
The lecture reviews regression analysis using data from 7 stores showing square footage (X) and annual sales (Y). A scatter diagram prepared using Excel Chart Wizard shows a clear positive linear relationship between area and sales, confirming that regression analysis is appropriate.
The estimated regression line is: ΕΆ = a + bX
From the given data, calculations yield:
- Ξ£X = 16452, Ξ£Y = 35913, Ξ£XY = 104841549, Ξ£XΒ² = 52413218
- XΜ = 16452/7 = 2350.29
- Θ² = 35913/7 = 5130.429
π Definition β Slope (b): The change in Y for a one-unit change in X π Formula: b = [nΞ£XY β (Ξ£X)(Ξ£Y)] / [nΞ£XΒ² β (Ξ£X)Β²] β b = 1.4866 This means for every increase of 1 sq. ft., sales increase by 1.4866 units.
π Definition β Intercept (a): The value of Y when X equals zero π Formula: a = Θ² β bXΜ β a = 5130.43 β (1.4866)(2350.29) = 1636.489
π Example: The estimated regression line is ΕΆ = 1636.489 + 1.4866X Using Excelβs Regression Tool confirms these results. The interpretation is that for every increase of 1 sq. ft., there is a sale of 1.487 units or 1407 Rs (since each unit was equal to 1,000). This equation can now be used to estimate sales for stores of other sizes.
π‘ Why this matters: The regression equation enables prediction of sales based on store size, which is valuable for business planning and investment decisions.
CHART WIZARD
The lecture demonstrates using Excelβs Chart Wizard through 4 steps to create various graphs. The process involves selecting data, choosing chart type (Column Graph), entering chart title and axis labels, and selecting output location.
π Example: A column graph was created to visualize sales data. Additionally, a side-by-side chart and a line graph were prepared, with the line graph clearly showing seasonal variations in sales values.
EXAMINATION OF GRAPH TREND
The graph reveals several components of time series data:
- Trend: General upward or downward steady behavior of figures
- Seasonal Variations: Variations that repeat regularly over short term (less than a year)
- Random Effect: Variations due to unpredictable situations
- Cyclical Variations: Alternation of upward and downward movement
EXTRACTING THE TREND FROM DATA
Given data: 170, 140, 230, 176, 152, 233, 182, 161, 242
First step: Plot figures on graph with horizontal axis as period 1 and vertical axis as period 2.
Conclusion: There is a marked pattern that repeats itself, and a well-established method exists to extract trend with strong repeating patterns.
MOVING AVERAGES
Moving averages is a method to extract trend from data with seasonal patterns. For sales data across morning, afternoon, and evening for 3 days:
π Example: First Average (Day 1) = (170 + 140 + 230)/3 = 180 Next Average (Morning, dropping 170, adding 176) = (140 + 230 + 176)/3 = 182 Alternative method: Last average + (176 β 170)/3 = 180 + 2 = 182
β οΈ Caution: The alternative mental arithmetic method can lead to errors. It is recommended to use Excel worksheets for accurate calculations.
The moving averages were plotted, showing that seasonal variation disappears and a clear trend of increase in sales emerges. This demonstrates that moving averages can be used for forecasting purposes.
π‘ Why this matters: Moving averages smooth out short-term fluctuations to reveal underlying trends, making them valuable for forecasting.
ANALYSING SEASONAL VARIATIONS
To find how much each period differs from trend: Seasonal Variation = Actual β Trend
π Example: Day 1, Afternoon Actual = 180, Trend = 140 Seasonal Variation = 140 β 180 = -40
Similarly, other seasonal variations can be calculated for each period.
β Key Takeaways
The regression equation ΕΆ = 1636.489 + 1.4866X allows prediction of annual sales from store square footage, with each additional square foot corresponding to 1.4866 units (or 1407 Rs) in sales. Time series data consists of four components: trend, seasonal variations (repeating within a year), cyclical variations (longer-term up/down movements), and random effects. Moving averages effectively remove seasonal variations to reveal the underlying trend, as demonstrated with the three-period moving average example. Seasonal variations are calculated as the difference between actual values and trend values, with negative values indicating periods below trend. Excelβs Chart Wizard and Regression Tool are powerful for automating these analyses.
π§ Quick Revision Questions
- Using the regression equation ΕΆ = 1636.489 + 1.4866X, what would be the predicted sales for a store with 2000 square feet?
- What are the four components of a time series, and how do seasonal variations differ from cyclical variations?
- If the first three values in a moving average calculation are 170, 140, and 230, what is the first moving average, and what value is added/dropped to calculate the next moving average?
- How is seasonal variation calculated, and what does a negative seasonal variation indicate?
- What advantage does using Excel worksheets provide over mental arithmetic when calculating moving averages?
π Lecture 33 β Time Series and Exponential Smoothing Part 2
π Overview: This lecture continues the study of time series analysis, focusing on extracting trend, seasonal variations, and random variations from moving averages. It demonstrates how to forecast future values by combining trend estimates with seasonal adjustments, and introduces the concept of exponential smoothing for unpredictable data patterns.
ποΈ Topics Covered
The lecture reviews trend calculation as the difference between moving average and actual data, followed by extracting seasonal variations by averaging these differences for each time period. It then covers extracting random variations, forecasting using trend plus seasonal adjustment, and handling seasonal variations with centred moving averages. Finally, it addresses forecasting in unpredictable situations and introduces the basic concept of exponential smoothing.
π Lecture Summary
TREND
As discussed briefly in the handout for lecture 32, the trend is given by the moving average minus the actual data. Look at the slide shown below. The average of the morning, afternoon and evening of the first day is 180. This value is written in cell I179, which is the middle value for first day. The next moving average is written in cell I180. This means that the last moving average will be written in cell I185 as the moving average of the morning, afternoon and evening of 3rd day will be written against the middle value in cell F185. Now that all the moving averages have been worked out we can calculate the trend as difference of moving average and actual value.
The actual trend figures are now written as shown in the slide below with M for morning, A for afternoon and E for evening. The titles Day 1, day 2 and Day 3 were written on the left hand side of the table. Further Total for each column was calculated. The total was divided by the non-zero values in the column. For example, in column M, there are 2 non-zero values. Hence, the total 20 was divided by 2 to obtain the average -10. Similarly, the averages in column A and E were calculated. This data is the seasonal variation and can now be used for estimating trend and random variations.
π‘ Why this matters: The seasonal variation values are constant adjustments that will be added to or subtracted from trend values to make forecasts.
EXTRACTING RANDOM VARIATIONS
Day 1 Afternoon:
- Trend = 180
- Seasonal variation = 36
- Trend β variation = 180 β 36 = 144
- Actual value = 140
- Random variation = 140 β 144 = -4
Conclusion: Expected = Trend + Seasonal. Random = Actual β Expected.
FORECAST FOR DAY 4
= Trend for afternoon of day 4 + Seasonal adjustment for afternoon period.
Trend = 180 to 195 (6 intervals) = 15/6 = 2.5 per period
- Figure for evening of day 3 = 195 + 2.5 = 197.5
- Morning of day 4 = 197.5 + 2.5 = 200
- Afternoon of day 4 = 200 + 2.5 = 202.5
After adjustment of seasonal variation = -36 = 202.5 β 36 = 166.5 or 166
π Example: The trend increases by 2.5 per period. Starting from 195 in the evening of day 3, after 3 periods we reach afternoon of day 4 at 202.5. Subtracting the seasonal adjustment of 36 gives a final forecast of 166.
SEASONAL VARIATIONS
Seasonal Variations are regarded as constant amount added to or subtracted from the trends. This is a reasonable assumption as seasonal peaks and troughs are roughly of constant size. In practice Seasonal variations will not be constant. These will themselves vary as trend increases or decreases. Peaks and troughs can become less pronounced. Seasonal variations as well as the trend are shown in the graph below. You can see that the trend clearly shows a downward slide in values.
In the following slide, the actual values are for 4 quarters per year. Here there is no middle value per year. The moving averages were therefore summarised against the 3rd quarter. As this does not reflect the correct position, the average of the first two moving averages was calculated and written as centred moving average in column H. The first centred moving average is the average of 141 and 138 or 139.5. This is used as the trend and the value Actual-Trend is the difference of Actual β Centred Moving Average. Here also the last row does not have a value as the moving average was shifted one position upwards.
The data from the previous slide was summarised as in the following slide using the approach described earlier. It may be seen that the average seasonal variation for Spring, Summer, Autumn and Winter is -8, -88.8, 29.5 and 65.3 respectively.
The expected value now is the sum of centred moving average and random variation. The random variation is the difference between the Actual and Expected value. This gives us a complete table with all the values.
FORECASTING APPLE PIE SALES
Sale steadily declined from 139.0 to 130.5. Over 4 quarters, the sales declined by = 139.0 β 130.5 = 8.5. Trend in Spring 1995 was 133.5. We can assume annual decrease as on the basis of decline over the last 4 quarters = 8.5. Trend in 1996 = trend in 1995 less decline = 133.5 β 8.5 = 125. Seasonal variation as already worked out = -8. Hence: Final forecast = 125 β 8 = 117.
π Example: To forecast Apple Pie sales for Spring 1996, start with the 1995 Spring trend of 133.5, subtract the annual decline of 8.5 to get 125, then subtract the Spring seasonal variation of 8 to get 117.
FORECASTING IN UNPREDICTABLE SITUATIONS
Two methods were studied above. Each one has certain features. If there is steady increase in data and repeated seasonal variations, there are many cases that do not conform to these patterns. There may not be a trend. There may not be a short term pattern. Figures may hover around an average mark. How to forecast under such conditions? Data for sales over a period of 8 weeks is summarized and plotted in the slide below. You may see that the values hover around an average value without any particular pattern. This problem requires a different solution.
FORECAST
Let us assume that the forecast for week 2 is the same as the actual data for week 1, that is 4500.
| Week no. | Actual sales | Forecast |
|---|---|---|
| 1 | 4500 | - |
| 2 | 4000 | 4500 |
The Actual sale was 4000. Thus, the Forecast is 500 too high.
Another approach would be to incorporate the proportion of error in the estimate as follows:
π Definition β Exponential Smoothing: new forecast = old forecast + proportion of error Ξ±. Or new forecast = old forecast + Ξ± Γ (old actual β old forecast).
This method is called Exponential Smoothing. We shall learn more about this method in lecture 34.
π Formula: new forecast = old forecast + Ξ± Γ (old actual β old forecast) β The new forecast adjusts the old forecast by adding a fraction (Ξ±) of the forecasting error from the previous period.
β Key Takeaways
The lecture systematically demonstrates how to decompose a time series into trend, seasonal, and random components by first computing moving averages to establish the trend, then averaging the differences between actual values and moving averages to find seasonal variations. Forecasts are made by projecting the trend forward using the average per-period change and then adding the seasonal adjustment for the relevant period. For data without clear trends or patterns, simple forecasting methods like using the previous period's actual value as the forecast are unreliable, leading to the introduction of exponential smoothing where the new forecast is the old forecast plus a proportion of the error. The key formulas and steps for computing centred moving averages when there is no natural middle point are also essential.
π§ Quick Revision Questions
- How is the trend value calculated from a moving average and actual data?
- What is the formula for extracting random variation from a time series?
- How do you forecast a future value when you have the projected trend and the seasonal variation?
- What is a centred moving average and when is it necessary to use one?
- What is the basic formula for exponential smoothing and what does the proportion of error (Ξ±) represent?