In: Finance
I can do it in excel and show you the output.
a. Calculate AND interpret the Regression Equation. You are welcome to use Excel to check your calculations, but you must first do them by hand. Show your workings.
As manager, which variable do you think is the one that affects the other variable? In other words, which one is independent, and which variable’s value is dependent on the other variable? The independent variable is always x.
Attendance is the variable that impacts the chocolates sold. Hence, attendance is the independent variable and chocolates sold is the dependent variable. Hence, X is Attendance and Y is Number of chocolate bars sold.
The standard equation of regression will be:
Hence, Y = aX + b
What happens when Holmes are closed?
When Homes are closed, X i.e. attendance will be zero. Hence number of chocolate bars sold = Y = b. Hence "b" represents the number of chocolates that will be sold even if Holmes is closed.
What happens when 10 extra students show up?
The coefficient of X that is "a" shows the sensitivity of Y with respect to X. That is, if X changes be 1 unit, Y will change by a units. Thus, if 10 extra students turn up, X will increase by 10 and hence Y = number of chocolate bars sold will increase by 10a.
b. Calculate AND interpret the Coefficient of Determination.
Summary of regression output as produced by excel is shown here:
SUMMARY OUTPUT | ||||||||
Regression Statistics | ||||||||
Multiple R | 0.967992639 | |||||||
R Square | 0.93700975 | |||||||
Adjusted R Square | 0.9244117 | |||||||
Standard Error | 224.5951736 | |||||||
Observations | 7 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 1 | 3751816.754 | 3751816.754 | 74.37736352 | 0.000346012 | |||
Residual | 5 | 252214.9601 | 50442.99201 | |||||
Total | 6 | 4004031.714 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
Intercept (b) | 1628.688985 | 605.9000187 | 2.688049074 | 0.043399872 | 71.17340223 | 3186.20457 | 71.17340223 | 3186.20457 |
X Variable 1 (a) | 10.67723382 | 1.23805051 | 8.624231184 | 0.000346012 | 7.494723664 | 13.859744 | 7.494723664 | 13.859744 |
Coefficient of determination is given by R square. Please see the table above.
In the output summary, R square = 0.93700975
Interpretation: Coefficient of determination or R square is a measure of (square of) correlation between X and Y variables. A zero value means X and Y are not correlated at all and hence Y can't be predicted based on X. A high value implies high correlation and hence data shows a good fit between X and Y and Y can be predicted based on X.
In this case R square value is very good. A value of 0.93 means a good fit between the data and we can therefore safely say that: