Multiple Regression Analysis With Excel

Learn multiple regression analysis main concepts from basic to expert level through a practical course with Excel.

Last updated 2022-01-10 | 4.1

- Define stocks dependent or explained variable and calculate its mean
- standard deviation
- skewness and kurtosis descriptive statistics.
- Outline rates
- prices and macroeconomic independent or explanatory variables and calculate their descriptive statistics.
- Analyze multiple regression statistics output through coefficient of determination or R square
- adjusted R square and regression standard error metrics.

What you'll learn

Define stocks dependent or explained variable and calculate its mean

standard deviation

skewness and kurtosis descriptive statistics.

Outline rates

prices and macroeconomic independent or explanatory variables and calculate their descriptive statistics.

Analyze multiple regression statistics output through coefficient of determination or R square

adjusted R square and regression standard error metrics.

Examine multiple regression analysis of variance through regression

residuals and total degrees of freedom

sum of squares

mean square error

regression F statistic and regression p-value.

Review multiple regression coefficients through their values

standard errors

t statistics and regression coefficients p-values.

Evaluate regression correct specification through individual coefficients statistical significance and correct it through backward elimination stepwise regression.

Assess regression no linear dependency through multicollinearity test and correct it through correct specification re-evaluation.

Appraise regression correct functional form through Ramsey-RESET test and correct it through non-linear quadratic

logarithmic or reciprocal variables transformations.

Evaluate residuals no autocorrelation through Breusch-Godfrey test and correct it by adding lagged dependent variable data as independent variables to original regression.

Assess residuals homoscedasticity through White test and correct it through heteroscedasticity consistent standard errors estimation.

Appraise residuals normality through Jarque-Bera test.

Evaluate regression forecasting accuracy by comparing it with random walk and arithmetic mean benchmarks through mean absolute error

root mean square error and mean absolute percentage error metrics.

* Requirements

* Spreadsheet software such as Microsoft Excel® is required.
* Practical example spreadsheet provided with the course.
* Prior basic spreadsheet software knowledge is useful but not required.

Description

Define stocks dependent or explained variable and calculate its mean, standard deviation, skewness and kurtosis descriptive statistics.
Outline rates, prices and macroeconomic independent or explanatory variables and calculate their descriptive statistics.
Analyze multiple regression statistics output through coefficient of determination or R square, adjusted R square and regression standard error metrics.
Examine multiple regression analysis of variance through regression, residuals and total degrees of freedom, sum of squares, mean square error, regression F statistic and regression p-value.
Review multiple regression coefficients through their values, standard errors, t statistics and regression coefficients p-values.
Evaluate regression correct specification through individual coefficients statistical significance and correct it through backward elimination stepwise regression.
Assess regression no linear dependency through multicollinearity test and correct it through correct specification re-evaluation.
Appraise regression correct functional form through Ramsey-RESET test and correct it through non-linear quadratic, logarithmic or reciprocal variables transformations.
Evaluate residuals no autocorrelation through Breusch-Godfrey test and correct it by adding lagged dependent variable data as independent variables to original regression.
Assess residuals homoscedasticity through White test and correct it through heteroscedasticity consistent standard errors estimation.
Appraise residuals normality through Jarque-Bera test.
Evaluate regression forecasting accuracy by comparing it with random walk and arithmetic mean benchmarks through mean absolute error, root mean square error and mean absolute percentage error metrics.

Course content

5 sections • 35 lectures

Course Overview 6 lectures

Course Description Preview 05:25

In this lecture you will view course disclaimer and learn which are its objectives, how you will benefit from it, its previous requirements and my profile as instructor.

Course Overview Preview 03:39

In this lecture you will learn that it is recommended to view course in an ascendant manner as each section builds on last one and also does its complexity. You will also study course structure and main sections (course overview, variables definition, multiple regression, multiple regression assumptions and multiple regression forecasting).

Multiple Regression Analysis Preview 08:36

In this lecture you will learn multiple regression analysis definition, course bibliography, course calculations with Microsoft Excel spreadsheet software and course file downloading.

Multiple Regression Analysis Data Preview 17:08

In this lecture you will learn multiple regression analysis data definition, training and testing data ranges delimiting and Microsoft Excel® Add-in for multiple linear regression calculation (Analysis ToolPak).

Course File Preview 00:03

Before starting course please download .XLSX Microsoft Excel file as additional resources.

Course Overview Slides Preview 00:02

You can download .PDF section slides file as additional resources.

Variables Definition 7 lectures

Variables Definition Slides Preview 00:02

You can download .PDF section slides file as additional resources.

Variables Definition Overview Preview 02:52

In this lecture you will learn section lectures’ details and main themes to be covered related to variables definition (independent or explained variable, independent or explanatory variables and variables descriptive statistics).

Dependent Variable Preview 03:14

In this lecture you will learn dependent variable definition and main calculations.

Rates Independent Variables Preview 06:48

In this lecture you will learn rates independent variables definitions and main calculations.

Prices Independent Variables Preview 05:56

In this lecture you will learn prices independent variables definitions and main calculations.

Macroeconomic Independent Variables Preview 04:43

In this lecture you will macroeconomic independent variables definitions and main calculations.

Variables Descriptive Statistics Preview 09:11

In this lecture you will learn variables descriptive statistics definition and main calculations (AVERAGE(), STDEV(), SKEW(), KURT() functions).

Multiple Regression 5 lectures

Multiple Regression Slides Preview 00:02

You can download .PDF section slides file as additional resources.

Multiple Regression Overview Preview 11:21

In this lecture you will learn section lectures’ details and main themes to be covered related multiple regression (regression statistics, analysis of variance and regression coefficients analysis).

Regression Statistics Preview 07:34

In this lecture you will learn regression statistics definition and main calculations (SQRT(), COUNT(), LINEST(), ARRAY{} functions, Regression Data Analysis ToolPak Add-in).

Analysis of Variance Preview 17:05

In this lecture you will learn analysis of variance definition and main calculations (COUNT(), SUM(), FDIST(), LINEST(), ARRAY{} functions, Regression Data Analysis ToolPak Add-in).

Regression Coefficients Analysis Preview 11:32

In this lecture you will learn regression coefficients analysis definition and main calculations (T.DIST.2T(), ABS(), LINEST(), ARRAY{} functions, Regression Data Analysis ToolPak Add-in).

Multiple Regression Assumptions 9 lectures

Multiple Regression Assumptions Slides Preview 00:02

You can download .PDF section slides file as additional resources.

Multiple Regression Assumptions Overview Preview 11:28

In this lecture you will learn section lectures’ details and main themes to be covered related to multiple regression assumptions (correct specification, no linear dependence, correct functional form, residuals no autocorrelation, residuals homoscedasticity and residuals normality).

Correct Specification Preview 12:21

In this lecture you will learn correct specification definition and main calculations (Regression Data Analysis ToolPak Add-in).

No Linear Dependence Preview 08:16

In this lecture you will learn no linear dependence definition and main calculations (CORREL(), MINVERSE() and ARRAY{} functions).

Correct Functional Form Preview 09:39

In this lecture you will learn correct functional form definition and main calculations (Regression Data Analysis ToolPak Add-in).

Residuals No Autocorrelation Preview 09:15

In this lecture you will learn residuals no autocorrelation definition and main calculations (Regression Data Analysis ToolPak Add-in).

Residuals Homoscedasticity Preview 08:55

In this lecture you will learn residuals homoscedasticity definition and main calculations (Regression Data Analysis ToolPak Add-in).

Heteroscedasticity Consistent Standard Errors Preview 16:26

In this lecture you will learn heteroscedasticity consistent standard errors definition and main calculations (SUM(), MINVERSE(), MMULT(), TRANSPOSE(), SQRT(), COUNT(), FDIST(), T.DIST.2T(), ABS(), ARRAY{} functions).

Residuals Normality Preview 06:28

In this lecture you will learn residuals normality definition and main calculations (SKEW() and KURT() functions).

Multiple Regression Forecasting 8 lectures

Multiple Regression Forecasting Slides Preview 00:02

You can download .PDF section slides file as additional resources.

Multiple Regression Forecasting Overview Preview 18:20

In this lecture you will learn section lectures’ details and main themes to be covered related to multiple regression forecasting (forecasting correct specification, forecasting correct functional form, forecasting residuals no autocorrelation, forecasting residuals homoscedasticity, forecasting residuals normality and forecasting accuracy metrics).

Forecasting Correct Specification Preview 16:18

In this lecture you will learn forecasting correct specification definition and main calculations (COUNT(), FDIST(), T.DIST.2T(), ABS(), INDEX(), TRANSPOSE(), LINEST() and ARRAY{} functions).

Forecasting Correct Functional Form Preview 13:13

In this lecture you will learn forecasting correct functional form definition and main calculations (COUNT(), FDIST(), T.DIST.2T(), ABS(), INDEX(), TRANSPOSE(), LINEST() and ARRAY{} functions).

Forecasting Residuals No Autocorrelation Preview 07:53

In this lecture you will learn forecasting residuals no autocorrelation definition and calculation (COUNT(), FDIST(), T.DIST.2T(), ABS(), INDEX(), TRANSPOSE(), LINEST() and ARRAY{} functions).

Forecasting Residuals Homoscedasticity Preview 04:33

In this lecture you will learn forecasting residuals homoscedasticity definition and main calculations (COUNT(), FDIST(), T.DIST.2T(), ABS(), INDEX(), TRANSPOSE(), LINEST() and ARRAY{} functions).