Question

Your company has been granted an exclusive license to sell ice cream. No one has ever sold ice cream here before, so you have no idea what the demand will look like. You suspect that people like to buy more ice cream on hotter days, but you are very unsure about what price you should charge to maximize your profit.

Over your first season selling ice cream, you vary your price each week for the 10 weeks your license allows you to operate. You collect data including price you charged that week (giving you 7 data points at each price), the high temperature for that day, and the average number of cones sold per hour each day.  

You have paid a fixed fee of $10,000 to the state which covers materials (both the costs of the cones and the fixed costs associated with the food truck) to supply the ice cream cones that doesn’t vary depending on how many units are sold. A single employee, making $15/hour can handle up to 40 cones per hour, while a second employee would bring your maximum production up to 100 cones per hour.

Instructions

Use the data below to perform a multiple regression analysis, with Sales per Hour as your dependent variable and ‘Price’ and ‘Avg Temp’ as independent variables.  

Q2) Assume the average high temperature will be 80 next year. Use Excel to draw the demand curve. Be careful to put P on the vertical axis and Q on the horizontal axis.   Please label the vertical intercept (above what price will 0 units be sold) and the horizontal intercept (if P=0, how many units are ‘sold’)

Ice cream sales data                                     

Day        Sales per hour Price     Avg Temp

1             30.65    0.75      77

2             30.55    0.75      92

3             25.89    0.75      74

4             31.84    0.75      91

5             24.09    0.75      67

6             27.81    0.75      92

7             25.27    0.75      73

8             28.21    1.50      92

9             28.15    1.50      89

10           23.07    1.50      79

11           31.15    1.50      93

12           19.76    1.50      70

13           26.00    1.50      75

14           29.37    1.50      91

15           28.47    1.25      95

16           23.82    1.25      71

17           24.11    1.25      76

18           29.19    1.25      90

19           28.73    1.25      91

20           24.20    1.25      91

21           24.91    1.25      77

22           21.76    2.00      87

23           21.15    2.00      71

24           20.31    2.00      73

25           16.54    2.00      67

26           20.18    2.00      69

27           23.53    2.00      93

28           21.01    2.00      77

29           26.85    1.00      79

30           27.12    1.00      81

31           28.93    1.00      74

32           22.91    1.00      65

33           27.33    1.00      81

34           24.27    1.00      80

35           26.63    1.00      81

36           17.93    2.25      69

37           20.65    2.25      90

38           15.97    2.25      74

39           23.55    2.25      92

40           20.25    2.25      67

41           19.13    2.25      73

42           19.72    2.25      90

43           30.66    0.50      82

44           28.68    0.50      92

45           24.97    0.50      71

46           34.21    0.50      94

47           25.64    0.50      71

48           31.66    0.50      93

49           26.87    0.50      72

50           18.42    1.75      69

51           25.74    1.75      79

52           20.36    1.75      72

53           22.15    1.75      70

54           28.66    1.75      85

55           26.42    1.75      94

56           24.34    1.75      74

57           21.93    1.35      73

58           23.51    1.35      67

59           26.99    1.35      82

60           32.10    1.35      86

61           25.21    1.35      84

62           27.59    1.35      93

63           19.63    1.35      69

64           23.63    1.65      81

65           20.76    1.65      66

66           24.57    1.65      79

67           29.27    1.65      91

68           24.32    1.65      68

69           19.85    1.65      66

70           25.29    1.65      91

Answer 1

Regression equation :

sales/hour = A + B1 (price) + B2(average temp)

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.86807048
R Square	0.753546357
Adjusted R Square	0.746189532
Standard Error	2.050715262
Observations	70
ANOVA
	df	SS	MS	F	Significance F
Regression	2	861.5098818	430.7549409	102.4281999	0.000
Residual	67	281.7640168	4.205433087
Total	69	1143.273899
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	12.34846457	2.276892837	5.42338417	0.000	7.803767447	16.8931617	7.803767447	16.8931617
price	-4.686618718	0.475346459	-9.859374415	0.000	-5.635414222	-3.737823215	-5.635414222	-3.737823215
avg temp	0.239457557	0.026131621	9.163517152	0.000	0.187298623	0.29161649	0.187298623	0.29161649

From the summary output of regression ran on MS excel, we draw the following conclusions.

Our model is statistically significant as P value for F test is 0.000 which is < 0.05.

Intercept, Price and Average temperature are also statistically significant as P values are 0.000 which is < 0.05

This means, Price and Average temperature has an impact on Sales.

ADJUSTED R² is considered as this is a Multiple Regression model. The value is 0.746. This means 74.6% of variation in sales is explained by price and average temperature. Thus, the model is a GOOD FIT.

Coefficient of price is -4.686. This implies for one dollar increase in Price, sales/hour go down by -4.686

Coefficient of average temp. is 0.239. This implies for one unit increase in average temperature, sales/hour increase by 0.239

2)

At temperature 80
Sales(X axis)	Price(Y axis)
29.16	0.5
26.82	1
24.48	1.5
22.13	2
19.79	2.5
17.45	3
15.10	3.5
12.76	4
10.42	4.5
8.07	5

At Price = 0 , 31.51 units of ice cream will be sold.

Above Price of 4.5, 0 units of ice cream will be sold.