Explore BrainMass

Regression in Excel - Determining Demand for Bass Drums

The operations manager of a musical instrument distributor feels that demand for bass drums may be related to the number of television appearances by the popular rock group Green Shades during the preceding month. The manager has collected the data shown below.
Demand for Bass Drums Green Shades TV Appearance
3 3
6 4
7 7
5 6
10 8
8 5
a. Graph these data to see whether a linear equation might describe the relationship between the group's TV shows and bass drum sales.
b. Using the equations presented in this chapter, compute the SST, SSE, and SSR. Find the least squares regression line for this data.
c. What is your estimate for bass drums sales if the Green Shades performed on TV six times last month?
d. Using EXCEL, find the least squares regression line for the data above. Based on the F - Test is there a statistically significant relationship between the demand for drums and the number of TV appearances.

© BrainMass Inc. brainmass.com June 18, 2018, 5:57 am ad1c9bdddf

Solution Preview


See the attached Excel spreadsheet for complete details.

a. Using the Excel Data Analysis Add-In and the Regression option with Line Fit Plot checked I output the "Regression" tab. Note on the data tab that indication is that a linear model does NOT fit the data very well, since the R^2 is a low 0.5932, however that is about a 60% fit and not so bad. I would say that a linear equation might describe ...

Solution Summary

Excel spreadsheet with complete details using the Excel Data Analysis Add-In. Goodness of fit is covered as well as the optionally output ANOVA details: "Regression" (R), "Residual" (E), and "Total" (T), SSR, SSE, and SST.

The ANOVA table's F-statistic and p value is interpreted.