# 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 amanagers decisionmaking 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 ExcelunderTools

> 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 equationtopredict the advertising costsbased on the number oforders received. Please usethe regression procedurewithin ExcelunderTools> 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.