Explore BrainMass

What is the linear regression and 5-month moving average forecast for the sales data?

See attached file for data.

There is debate within the production planning management team as to which forecasting technique to use. The team members want to test to see which method would be more accurate. They have assigned the new planning director to assess which of the following forecasting techniques would have been more accurate to predict sales in the future.

Using the following sales data for the last 10 quarters, perform a linear regression, and 5-month moving average forecasting techniques to see which is most accurate for the new product if the actual sales for Quarter 11 are 164.

Make sure to include the equation for the line in the linear regression calculation.

Need charts and an explanation to better help me to understand the concept.


Solution Preview

The Excel file has all the answers.

Regression Analysis

Regression analysis includes any techniques for modeling and analyzing several variables, when the focus is on the relationship between a dependent variable and one or more independent variables. More specifically, regression analysis helps us understand how the typical value of the dependent variable changes when any one of the independent variables is varied, while the other independent variables are held fixed.

There are several ways you can run the regression analysis. Manual calculations are exhaustive and take a lot of time. The best way is to run in Excel. There is a special module which will give you more detailed results. I have run that as a separate worksheet. There you can see all the detailed results for the Regression model. However, for your purposes, all you need to do is to draw a scatter plot and add a trend line with the equation to tell you what the regression equation is. These are the steps you follow to accomplish that.

1. Select the entire data range (quarter and units sold in each quarter)
2. Click on a scatter plot diagram. In the new version of Excel this is all you need to ...

Solution Summary

This solution explains the concepts in 810 words and includes an Excel file with all the answers.