Multiple regression is a powerful technique for forecasting future outcomes based on multiple explanatory variables. However, it also comes with some challenges, such as dealing with missing data, outliers, and multicollinearity. In this article, you will learn how to handle these issues in Excel and improve the accuracy and reliability of your forecasts.
Missing data can occur for various reasons, such as errors in data collection, non-response, or incomplete records. Missing data can affect the quality of your regression analysis, as it can introduce bias, reduce sample size, and distort the relationships among variables. To handle missing data in Excel, you have two main options: deleting or imputing. Deleting means removing the rows or columns that contain missing values, while imputing means replacing them with some reasonable estimates, such as the mean, median, or mode. The choice depends on the amount and pattern of missing data, as well as the research questions.
Roger Kirk (then of Baylor) wrote in 1968 that "No completely satisfactory method exists for estimating a cell mean when all data within the cell are missing." More than five decades later he's still right. If an effect exists and _can_ be estimated despite missing data, there still may be a good argument for reassessing the model. (Consider the UC Berkeley sexual discrimination contretemps, for example.) No statistical technique can make up for deficiencies in a model. Certainly not the practice of substituting a zero for a regression coefficient if its vector in turn causes a matrix to go singular on you.
Outliers are extreme or unusual values that deviate significantly from the rest of the data. Outliers can affect the results of your regression analysis, as they can inflate the variance, skew the distribution, and distort the coefficients and standard errors. To take care of outliers in Excel, you can identify, transform, or remove. Identifying means using graphical or statistical methods to detect the outliers, such as boxplots, histograms, or z-scores. Transforming involves applying mathematical functions, like logarithms, square roots, or winsorization, to reduce the impact of outliers. Removing means deleting the outliers from the data set, but only if they are clearly erroneous or irrelevant.
Influential observations usually deserve more attention than univariate outliers. A univariate outlier lying perfectly on a regression line does not distort the estimated relationship and must not be removed.
Multicollinearity occurs when two or more explanatory variables are highly correlated with each other, meaning that they share some common information or variation. Multicollinearity can impact your regression analysis by increasing the standard errors, reducing the significance, and inflating the variance inflation factor (VIF). To handle multicollinearity in Excel, you have two main options: detecting or reducing. Detecting requires using statistical methods to measure the degree of multicollinearity. Methods include correlation matrix, variance inflation factor, or tolerance. Reducing means modifying the data or the model to minimize the multicollinearity, such as dropping, combining, or centering the variables.
In my analysis, I look for multicollinearity by checking the Tolerance and VIF figures. If they fall outside of > 0.10 or < 10, respectively, then I look to adjust.
Excel provides various tools to assist with multiple regression analysis and address issues. The Data Analysis Toolpak is an add-in that offers statistical procedures like regression, correlation, and ANOVA. You can access it under the Analysis group by clicking on Data Analysis. Additionally, the Solver add-in, which enables users to optimize the values of their variables based on certain criteria, can be accessed from the same Data tab, under the Analysis group, by clicking on Solver. The LINEST function returns the coefficients, standard errors, and statistics of a linear regression, and can be entered as an array formula in a range of cells using the syntax =LINEST(known_y's,known_x's,const,stats). Also, the TREND function returns the predicted values of a linear regression, and can be entered as an array formula in a range of cells using the syntax =TREND(known_y's,known_x's,new_x's,const). Excel provides a comprehensive suite of tools to help users perform multiple regression analysis and tackle any challenges.
To ensure the accuracy of your multiple regression forecasting in Excel, it is important to follow some tips and best practices. Start by examining the assumptions of linear regression, such as linearity, normality, homoscedasticity, and independence of errors. You can use graphical or statistical methods, such as scatterplots, histograms, residuals plots, or normal probability plots, to test these assumptions. It is also important to explore and understand your data before running the regression. You can use descriptive statistics, such as mean, standard deviation, range, or quartiles, to summarize your data and identify potential outliers or missing values. Pivot tables, charts, or slicers can be used to visualize and filter your data as well. Also, choose the appropriate variables and model for your forecasting. You can use domain knowledge, theory, or research to select the relevant explanatory variables for your outcome variable. Model selection criteria, such as R-squared, adjusted R-squared, or Akaike information criterion (AIC), can compare different models. Finally, it is essential to interpret and communicate your results clearly. Tables or charts can be useful to present your regression output, such as coefficients, standard errors, p-values, confidence intervals, or predicted values. Use plain language and include context to explain your findings and recommendations.
"To ensure the accuracy" is an absurd way to start any paragraph on forecasting best practices. The only thing you can be assured of is the inaccuracy of your forecast. The goal here isn't to "ensure accuracy" but to minimize the errors and mitigate the downside risk. The fact that this article doesn't make any serious mention of the need first to validate the predictive capacity of the inputs being used in the regression model, and focuses instead on 'making sure the outputs are accurate' is one of the several reasons that exactly zero people are going to make use of this article in practice. Good job LinkedIn, on pumping out zero value regurgitation of existing zero value fluff content.
A la hora de presentar los resultados es importante comunicar claramente los resultados del análisis de regresión múltiple, destacando la relación entre las variables independientes y la variable dependiente, así como las limitaciones y posibles mejoras del modelo.
Above should probably clarify that the main assumptions for linear regression are satisfied before you even use Excel. It's stated, but kind of glossed over. 1. There is in fact a linear relationship between dependent variable and independent variables. 2. Error terms are homoskedastic (constant variance) across the observations, error terms are normally distributed, and error terms are uncorrelated across the observations. Linear regression is not appropriate, or the approach will need to be modified if one or more of those assumptions are violated.
Best tip I can provide: Concentrate on your core business. Don’t waste your time and energy trying to be an amateur demand planning software programmer / mathematician / statistician when all that effort could be put into your core business and generate the revenue that you are looking for. If you need a demand planning solution, there are hundreds of software offerings that have a software development team already in place. There are SaaS subscription services available, at a wide price range, that can provide algorithmic based solutions developed by true mathematicians & statisticians. Think about who is going to support your home-grown solution when you move on…..