This solution describes how to do multiple regression using Excel and the Megastat plugin. Students are given step-by-step instructions on how to use the MegaStat plugin for Excel and told why Excel's Data Analysis Toolpak isn't reliable for rigorous statistical analysis. Attachments include example spreadsheets, a video tutorial, and some brief textbook material.
In order to do regression analysis on a multivariate data set, a dependent variable must be identified. This can either be one of the variable columns, or a new column, say, Annual Totals by category, or Monthly Totals for all categories, etc. Before doing the regression analysis, these parameters need to be identified. In other words, the following questions need to be answered first:
1] What is the independent variable(s), i.e., the input data
2] What is the dependent variable, i.e., the resulting data
The number of data points in each independent variable's range must equal the number of resulting data points in the dependent variable's output range.
Once this is done, you need only use a reliable statistics package within Excel to do the multi-variate ...
This solution provides a step-by-step "how to" on doing multiple regression, forecasting, and time series seasonal smoothing by moving average using Excel and the Megastat add-in. It describes why using Excel alone isn't the best approach for more reliable statistical results. Attachments and download links include example spreadsheets, a video tutorial, the Megastat add-on for Excel, and some brief textbook material.