Question

A company has recorded data on the weekly sales for its product (y) ($10), the unit price of the competitor's product (x₁), and advertising expenditures (x₂) ($10). The data resulting from a random sample of 7 weeks follows. Use Excel's Regression Tool to answer the following questions:

Week	Price	Advertising	Sales
1	.33	5	20
2	.25	2	14
3	.44	7	22
4	.40	9	21
5	.35	4	16
6	.39	8	19
7	.29	9	15

1. What is the estimated weekly sales if the unit price is $0.40, and advertising is $50? How does sales ($10) units and advertising ($10) fit into this answer?

2. What is the marginal contribution of price to sales? Again, how does the units being in terms of $10 for advertising and sales affect the overall answer?

Answer 1

1.

Week	Price(X1)	Advertising(X2)	Sales(Y)
1	0.33	5	20
2	0.25	2	14
3	0.44	7	22
4	0.4	9	21
5	0.35	4	16
6	0.39	8	19
7	0.29	9	15

Sum of X₁ = 2.45
Sum of X₂ = 44
Sum of Y = 127
Mean X₁ = 0.35
Mean X₂ = 6.2857
Mean Y = 18.1429
Sum of squares (SS_X1) = 0.0262
Sum of squares (SS_X2) = 43.4286
Sum of products (SP_X1Y) = 1.09
Sum of products (SP_X2Y) = 23.7143
Sum of products (SP_X1X2) = 0.56

Regression Equation = y = b₁X₁ + b₂X₂ + a

b₁ = ((SP_X1Y)*(SS_X2)-(SP_X1X2)*(SP_X2Y)) / ((SS_X1)*(SS_X2)-(SP_X1X2)*(SP_X1X2)) = 34.06/0.82 = 41.32002

b₂ = ((SP_X2Y)*(SS_X1)-(SP_X1X2)*(SP_X1Y)) / ((SS_X1)*(SS_X2)-(SP_X1X2)*(SP_X1X2)) = 0.01/0.82 = 0.01324

a = M_Y - b₁M_X1 - b₂M_X2 = 18.14 - (41.32*0.35) - (0.01*6.29) = 3.59762

y = 41.32X₁ + 0.01324X₂ + 3.598

Also using excel the equation found was same

Predicted sales for X1 = 0.4 and X2 = 5 ( 50$/10$ = 5 .. factor 10$)

Sales y = 20.1918

Now 10$ factor so sales will be 20.19*10$ = $201.9

Now if unit not taken 10 $ so

Week	Price(X1)	Advertising(X2)	Sales(Y)
1	0.33	50	200
2	0.25	20	140
3	0.44	70	220
4	0.4	90	210
5	0.35	40	160
6	0.39	80	190
7	0.29	90	150

y = 413.20022X₁ + 0.01324X₂ + 35.97615

But again for X1 = 0.4 and X2 = 50$ sales value is same 201.9$ so it won't effect the sales

2. Marginal Contribution of Price is 41.32

But if not 10$ then marginal contribution is 413.2$ which is 10 times the first one.

If any doubt please feel free to ask through the comments section and if no doubt please give a like. Thank You