Question

Omega Distributing Company

Omega Distributing Company specializes in supplying fragrances to retail outlets such as Goody’s and JCPenney. One of its products is Smelgud. Smelgud has generated significant revenue in the past, however top management is concerned as to whether its pricing and advertising policies are optimal for the demand. Partnering with some of its retail outlets Omega has undertaken to conduct a statistical study of consumer demand for Smelgud.

Omega’s analyst believe that the principal determinant of consumer demand for Smelgud are(1) the price of Smelgud, (2) the price of Antistink (a competing fragrance) and (3) advertising expenditures. The following data were collected from a group of representative outlets .

Quantity of Smelgud (thousands)	Price Smelgud	Price Antistink	Advert (10 thousand)
1027	14.50	14.91	3.37
1204	12.90	15.23	4.77
974	14.70	14.60	3.20
1111	13.30	15.02	2.80
1042	14.40	14.70	2.97
1304	13.20	15.44	3.63
1054	13.30	14.49	3.49
997	13.50	14.39	2.98
1223	13.10	15.02	3.37
1247	13.00	15.12	4.07
1049	14.60	15.02	4.19
1250	12.70	15.44	4.77
972	14.70	14.49	3.94
1184	13.20	15.33	3.48
1054	14.30	14.81	3.66

In the table the price of Antistink is the retail price charged customers while the price of Smelgud is the wholesale price charged by Omega to its customers. Since the retailers use markup pricing omega s price represents what retail customers pay for Smelgud.

It is hypothesized that a linear demand function like the following would describe the relation between quantity sold and price of Smelgud and the other independent variables.

Q = Bo + B1(Ps) + B2(Pa)+ B3(A)  

You have been hired by Omega after recent graduation from Missouri Valley College at a salary in the 95th percentile of new graduate hires. Because of your understanding of Economics and how to apply Statistics, one of your first assignments is to analyze this data and determine a demand function of the form shown. You have determined that once you have good estimates of the coefficients on the independent variables you could calculate various elasticity measures which could show insights into pricing, competition and advertising.    

You will need to report your findings to your immediate supervisor, Duncan Haynes. Mr. Haynes background is in Art History and Chemistry, he was promoted out of the the product inception area. Hence, your report will need to explain what you have done and what your findings mean in detail. You will make recommendations and defend them.    

Answer 1

The easiest way to solve the problem is using excel.

The solution to this problem cannot be done manually until the values of intercept and coefficients are provided.

I will lay down steps to get the results using excel:

Excel 16 has an add-in for Data Analysis, you will have to install the same on your excel software.

Then, first you will copy the data onto an excel sheet, without changing orientation or values.

Then, you need to go to the DATA tab and select DATA ANALYSIS.

Once you select DATA ANALYSIS, a new screen will pop-up and you will scroll down to chose REGRESSION from the new window that popped up

Now, in INPUT Y RANGE- You will select entire first column, named -Quantity of Smelgud

In input X-Range - You will Select the Three Columns -

Price Smelgud

Price Antistink

Advert (10 thousand)

Also click on labels and check the box

ANd in output options select wherever you want the output of regression analysis to appear and click OK

This will create the following Regression report

Now, firstly we will look at the R squared value which is 0.90 or 90%, this indicates that 90 percent of the model is explained by the model.

Then we will look at the P-Values-

For the intercept and the advertisement the p-values are higher than 0.05 and thus are not significant.

However, Price Smelgud and Price Antistink are statistically significant.

Thus, when price of smelgud increases by one unit, the quantity sold by the retail store decreases by 70.73 units on average

Similarly, when the price of Antistick increases by one unit, the quantity pf smelgud sold by the retail store, on average, increases by 195.02 units.

For understanding this answer, it will be helpful for you to read on normal regression.