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 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)