# Statistics help – I can’t use excel – I could do the math with my old trusty calculator. I need this as a spread sheet asap!

**Simple Regression in a spread sheet!!**

** **A boss of a big company would liketo determine ifhe should be placing more money allottedin thebudget nextyearfor television advertising ofa widget. She wonders whether there isa strong relationshipbetween theamount ofmoney spent on televisionadvertising for this new widget and the number of orders received. The manufacturing process of this widget is very difficult andrequiresadvanced quality controlsothey wouldprefer to generate a stable number of orders. The costof advertisingis always an important consideration in the first roll-out of a new widget. Data that have been collectedover the past 20 months indicatethe amount ofmoney spent of television advertising and the number of orders received.

The use of linear regression is acritical tool for amanager‘s decision–making ability.Please carefullyread the example below andtry toanswer the questions in termsof the problem context. Theresults are as follows:

Month |
AdvertisingCost |
Number of Orders |

1 |
$77,430.00 |
2,902,000 |

2 |
62,620 |
1,800,000 |

3 |
69,580 |
1,299,000 |

4 |
50,670 |
1,430,000 |

5 |
69,180 |
1,367,000 |

6 |
73,140 |
2,011,000 |

7 |
83,370 |
3,888,000 |

8 |
78,880 |
2,935,000 |

9 |
64,990 |
1,555,000 |

10 |
77,230 |
3,654,000 |

11 |
61,380 |
1,598,000 |

12 |
62,900 |
1,967,000 |

13 |
63,270 |
1,899,000 |

14 |
89,190 |
3,245,000 |

15 |
60,030 |
1,934,000 |

16 |
79,210 |
2,853,000 |

17 |
65,770 |
1,625,000 |

18 |
84,530 |
3,778,000 |

19 |
79,760 |
2,999,000 |

20 |
82,640 |
3,834,000 |

a. Set up a scatter diagramand calculate the associatedcorrelation

coefficient.Discuss how strong you think the relationshipis between the amount ofmoney spent on televisionadvertisingand the number of orders received. Please usetheCorrelation procedureswithin Excelunder*Tools*

*> Data Analysis. *The Scatterplot can more easilybe generated using the

Chart procedure.

**NOTE:**If you do not have the Data Analysis option under Tools you must install it.You need to go to Toolsselect Add-ins and thenchoose the 2data toolpak options. Itshould takeabout a minute.

b. Assuming thereis astatisticallysignificantrelationship, use the least squares method to find the regression equationto**predict the advertising costs**based on the number oforders received. Please usethe regression procedurewithin Excelunder*Tools> Data Analysis *to construct this equation.

c. Interpret the meaning ofthe slope,b1,in theregressionequation.

d. Predict the monthly advertising cost when the number of ordersis1,800,000. (Hint: Be very careful with assigningthe dependentvariable forthis problem)

e. Compute the coefficient ofdetermination, r2,and interpretits meaning. f. Compute the standarderrorof estimate, and interpret its meaning.

g. Do you think thatthecompany should use these results from the regression to baseany

corporate decisions on?….explain fully.