Simple Regression?

Question

A CEO of a large pharmaceutical company would like to determine if he should be placing more money allotted in the budget next year for television advertising of a new drug marketed for controlling asthma. He wonders whether there is a strong relationship between the amount of money spent on television advertising for this new drug called XBC and the number of orders received. The manufacturing process of this drug is very difficult and requires stability so the CEO would prefer to generate a stable number of orders. The cost of advertising is always an important consideration in the phase I roll-out of a new drug. Data that have been collected over the past 20 months indicate the amount of money spent of television advertising and the number of orders received.
The use of linear regression is a critical tool for a manager's decision-making ability. Please carefully read the example below and try to answer the questions in terms of the problem context. The results are as follows:


Month Advertising Cost (thousands of dollars) Number of Orders
1 $55.93 4,102,000
2 70.62 3,893,000
3 79.58 5,299,000
4 58.67 4,130,000
5 69.18 4,367,000
6 70.14 4,111,000
7 73.37 3,923,000
8 68.88 4,935,000
9 82.99 5,276,000
10 75.23 4,654,000
11 71.38 4,398,000
12 52.90 2,967,000
13 61.27 3,999,000
14 79.19 4,345,000
15 60.03 3,934,000
16 78.21 4,653,000
17 93.77 5,625,000
18 62.53 3,978,000
19 88.76 4,999,000
20 92.64 5,834,000
a. Set up a scatter diagram and calculate the associated correlation coefficient. Discuss how strong you think the relationship is between the amount of money spent on television advertising and the number of orders received. Please use the Correlation procedures within Excel under Tools > Data Analysis. The Scatterplot can more easily be 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 Tools select Add-ins and then choose the 2 data toolpak options. The original Excel CD will be required for this installation. It should take about a minute.
b. Assuming there is a statistically significant relationship, use the least squares method to find the regression equation to predict the advertising costs based on the number of orders received. Please use the regression procedure within Excel under Tools > Data Analysis to construct this equation.
c. Interpret the meaning of the slope, b1, in the regression equation.
d. Predict the monthly advertising cost when the number of orders is 4,999,000. (Hint: Be very careful with assigning the dependent variable for this problem)
e. Compute the coefficient of determination, r2, and interpret its meaning.
f. Compute the standard error of estimate, and interpret its meaning.

Answer 1

If you do not know how to use excel tools get someone to show you or read the instructions. There is a wizard to help, so it is not that difficult.
Advertising Cost (A) Number of Orders(N)
a) to get scatter plot select A and N then click on chart icon, when box appears select XY scatter, then press next
Look at chart and see if you can picture a straight line that would fit the trend of the data points. If their are any points that fall far from the trend line, find the months they correspond to, and think of a possible reason, remembering people want to buy more in some seasons. You could as do a chart selecting line to see seasonal variations in both variables.
The correlation coefficient is obtained by selection A and N and then selecting correlation option in the data analysis box. It tells you what fraction of the of the variation over time is synchronized and how much is random.

b) To do the regression select regression in the the box. The number of orders N is the dependent variable (at the top), and the the ad costs A is the independent variable ( at the bottom). Thea answer will appear in a box with a labels const and coef.(r2 and SD will also be shown)
Then the equation for the fit will be
N=const+coef*A where coef is b1 or the slope of the line. It tells you how many new orders you can expect per $ of ad cost. the standard deviation SD is the error , that is N+ or - SD

To do d) I would just put 4999000 in the above equations for N and solve for A. because orders to not cause advertising, but the question is confusing.


Edit: if there is a strong seasonal effect it should show in the residuals of the regression. There is an option in excel to show them. You can plot them and then and look for a pattern. http://en.wikipedia.org/wiki/Random_error

Answer 2

This is a really simple question. It is also impossible to answer over Yahoo! Answers. It is too complex.

Set up the scatter diagram using the advertising and sales data alone. Ignore the other data.

You just need to find the regression equation commands.

Write the equation out of the slope, variables and constants. It will help you think through the meaning of the entire equation.

You need to look at the equation using the outcome being $4,999,000. This is just simple algebra from high school.

The standard error of the estimate is more difficult. It is based upon how wide the spread of the errors are in estimating the true slope b1. In your case, the errors are quite large so it will impact the quality of your estimate. Again, look this up in your stats book.

Answer 3

Ufff, in this moment i dont have the time and the tools to answer your questions but after i had seen the data i can see that you need to introduce a seasonal variable because there is not exist a strong link between the variable mentioned.
Remember the television advertising is an intangible asset and the conduct of that is a lot difficult to predict.
The result of the regression must reflect these facts.

Answer 4

Your question is quite interesting and would be a welcome challange to a progessional.The way y ou have elaborated the question suggests that you are well conversant with the process involving the use of computer. I am,howeer,afraid that hardly would any such professional would bother about the matter as a YQA. I find that nobody has touched the question so far. It would,therefore, advisable for you to take the question to the proper professional who can tackle it.