Question

Exxon is developing a new faster pump design. They've narrowed the development to two design options and are wondering how the pump design might affect daily gas sales (Sales). In a test in 31 stations, they try out the new design A in some stations (code = 2), the new design B in other stations (code = 3) and for control they have stations that have not had a change (code = 1). From prior research, Exxon knows that three other factors are crucial in predicting gas sales for any particular station: advertising amount in the market (Ad), relative pricing (relprice), and the number of competing stations and their density (compet). For any changes, 95% confidence

output: regression results tables

a) run a multiple regression analysis to assess the effect of the new pump designs & Interpret b) are the new designs better than the current design (ie leading to higher sales) c) Assume a 1% profit margin and an investment of $1M for 100 stations for the change to a new design; will any change be profitable within the first year?

Data:

Store	Pump Design	Sales	ad	relprice	compet
1	2	29100	25410	1.18	9.4
2	3	25620	26400	1.14	9.4
3	1	23850	25950	1.18	9.7
4	1	25200	27010	1.20	11.9
5	2	21420	27850	1.24	13.4
6	3	21300	25090	1.46	9.6
7	1	21900	25700	1.54	9.2
8	1	23700	26670	1.48	13.6
9	2	22080	28780	1.48	14.4
10	3	21960	28350	1.48	15.3
11	1	17580	28970	1.48	15.1
12	1	19440	27440	1.66	11.8
13	2	20940	25820	1.76	12.8
14	3	19110	26130	1.88	12.4
15	1	20310	25290	2.00	9.3
16	1	20460	25440	2.14	7.9
17	2	25020	26330	2.08	7.8
18	3	22380	28780	2.02	8.4
19	1	23940	30510	1.88	9.1
20	1	25860	32740	1.70	8.8
21	2	28980	35940	1.58	9.2
22	3	24480	37740	1.50	9.8
23	1	24600	38610	1.50	10.3
24	1	26460	39190	1.44	8.8
25	2	29880	40400	1.48	8.2
26	3	29670	41330	1.46	7.5
27	1	24390	43030	1.42	7.1
28	1	25980	43930	1.40	7.2
29	2	30450	45600	1.42	8.9
30	3	32130	45870	1.42	7.7
31	1	26850	47160	1.38	7.4

Answer 1

a) Carrying out the regression with Daily Sales as dependent variable and Pump design (PD), ad amount (AD), relative pricing (REL) and competing stations (COMP), we obtain the regression model:

sales = 31242.8234 + 843.82*PD + 0.175686221*AD - 4695.443378*REL - 677.2780472*COMP

b) As the Pump Design (PD) has a +ve coefficient of 843.82, the new designs which are represented with a higher code (2 or 3) in the data indeed lead to higher sales than the older design.

c) If the design is changed to design code 2 at all stations, the change in sales per station is simply the coefficient of PD in the above regression model (all other factors remaining unchanged). Hence, the change (increase) in sales per station is 843.82. Similarly, the increase in sales per station is 2*843.82$ for change to design 3.

The average daily sale for original design from the given data can be calculated to be 24356.13$.

So, total daily sale upon change to design code 2 = 24356.13 + 843.82 = 25200$

=> Total yearly profit for 100 stations with 1% profit margin = 25200*100*.01*365 = $9.2M

Similarly, profit upon change to design code 3 at all stations works out to be:

(24356.12 + 843.82*2)*100*.01*365 = $9.5M

Given a total investment of $1M for 100 stations, this definitely means any change to design 1 or 2 at the 100 stations will hit profitability in the first year itself!