Demand Estimation Assignment - iScream Ice Cream Co.

iScream is a Chicago start-up that sells ice cream in several mobile ice cream trucks. The main innovative feature of their business is the implementation of a smart phone app that allows customers to locate trucks and request service. iScream has been in business for a little over a year, and offers just two products: regular vanilla ice cream cones and organic, low-fat ice cream cones (each with several toppings that are available at no extra cost). Based on your previous recommendation, the firm has frequently (for a month at a time) experimented with changing the prices of each of their products in eight equally-populated regions of the city throughout the first year, and has kept monthly data on their sales and prices of both products. They would now like you to help them gain some insights from the data.

While you know relatively little about the ice cream business, you are well aware that factors other than prices are likely to affect demand for a product. Because, through the app, they have information on time and location of sale, you are able to get data on the average household income (in thousands of $) in the region of sale, as well as the average daily high temperature during the month of sale.

The available data can be found in the excel file, "iScream_data.xlsx". Your goal is to carry out a multiple regression analysis of the data (using Excel), with particular focus on estimating the price elasticities of demand for both the firm's products. You will be led through this analysis below. Please carry out the following steps and answer the associated questions. Please clearly answer each question below, with the answers or relevant Excel output, like graphs and regression output, inserted into this document (you can copy and paste from Excel).

i) Begin by looking at the relationship between each product and its own price. For both products separately, do/answer the following:

a. First create scatter plot of price (on the vertical axis) and quantity sold (on the horizontal axis). Insert a trend line into each graph and display the equation of the line. This is a crude measure of the inverse demand curve. From the equation of the trendline, fill in the equation of the inverse demand curve for each good in the box below.

Insert separate graphs for each good here

Regular (good 1): Inverse demand equation: P1 = _________ - _________ Q1

Organic (good 2): Inverse demand equation: P2 = _________ - _________ Q2

c. Is there a negative relationship between quantity sold and the product's price? Provide an economic (or managerial) interpretation of the slope of these lines. (ie., what do these specific numbers mean?)

We are now interested in estimating the demand function, with Quantity (Q) as the dependent variable and price (P) as the explanatory variable (as opposed to the inverse demand function, with P as the dependent variable and Q as the explanatory variable, graphed above). We know that demand for a product is not only influenced by its price, but also the price of the other product, among other variables. So instead of just graphing the relationship between Q and P, similar to what we did above, we will use Excel's Regression tool, as this will allow us to control for other variables. (See section 3.2 in the textbook for an example of using this tool to estimate a simple inverse demand curve. See also the example "Example Demand Estimation" file in folder Unit 1F). We will use the results from the regressions to answer several questions. For each good, do/answer the following (d-l):

d. Carry out a multiple regression using linear specification, with quantity as the dependent (left-hand side, or y) variable and own price, other good's price, income, and temperature as explanatory (right-hand side or x) variables. Because you are including more than one variable as explanatory variables, you will need to highlight all the cells of explanatory variables when you indicate the X-range using the regression tool. I recommend having the columns for each explanatory variable right next to each other in the spreadsheet for this purpose.

Insert the regression output for each good here

e. What are your estimated demand equations for each good (ie., Q as a function of all the variables you included in the regression)?

Regular (good 1): Q1 =

Organic (good 2): Q2 =

f. What are the R-squared statistics from each regression? For which product does the demand function explain more of the variation in sales?

g. Let's now focus on the effect of a good's own price. What is the coefficient estimate for that same good's price? Provide an economic interpretation of those numbers (eg., by how much does quantity vary with a change in price)? Given the standard errors or the p-values given in the regressions, are these coefficient estimates statistically significant at the 5% significance level?

h. We are interested in how responsive consumers are to changes in price for each good. To quantify their responsiveness, calculate the price elasticity of demand for each product using the regression estimates from part d (see equation 3.6 or refer to Q&A 3.2 in the textbook). As you know, elasticity will vary along the linear demand curve, so you'll need to pick a point at which to calculate the elasticities. Choose the product's average Q and average P in the dataset. Interpret these elasticities (ie., quantitatively, what do they mean?). Is demand for either product elastic or inelastic?

Regular (good 1):

Average P1 in data set =

Average Q1 in data set =

Elasticity (1) =

Interpretation of elasticity:

Organic (good 2):

Average P1 in data set =

Average Q1 in data set =

Elasticity (1) =

Interpretation of elasticity:

i. Based on the elasticities from part h, is demand for either product "elastic" or "inelastic"? The owners of iScream are interested in knowing what would happen to revenue from their original ice cream cones if they increase its price slightly from the average price of the good in the data (holding everything else constant). Based on the estimated elasticity, would revenue increase, decrease, or stay about the same? Similarly, they are interested in knowing how revenue from their organic product would change if they were to increase its price slightly from the average (holding everything else constant). Would revenue increase, decrease, or stay about the same?

j. Let's now look at the effects of some of the other variables in the demand equation. What is the effect of consumer average income on sales? Are the goods normal or inferior goods?

k. How is demand for one good affected by the price of the other good? Which good is more affected by the price of the other good? Are the goods substitutes or complements?

l. The summer is fast approaching, and experts are disagreeing over how hot the summer is going to be. Some are forecasting an unusually hot summer, with average high temperatures predicted to reach 90 degrees in July. Others Organic (good 2): Average P2 in data set = Average Q2 in data set = Elasticity (2) = Interpretation of elasticity: are predicting an unusually cool summer, with an average high temperature in July of only 78. The owners of iScream would like to know how this difference in possible temperatures will affect their sales. At the averages in the dataset of all other variables, use the regression equations (part e) to predict the number of units sold in a month of each good under two scenarios: i) the monthly high averages 90 degrees; ii) the monthly high averages 78 degrees. Note that because the data are in monthly sales by region (of which there are 8), you'll need to multiply the number of units predicted from the regression equation by 8 in order to get total predicted units sold in a month.

Predicted monthly sales:

Regular (Q1) Organic (Q2)

78 degrees

90 degrees

Excel Data is:

Q1 831 317 701 837 1145 603 793 917 1057 1029 971 722 753 844 830 1182 752 1113 911 1208 1110 786 1118 1439 974 1095 665 766 1236 711 1232 1551 1062 881 1204 1089 1139 1344 1373 1264 533 758 832 1270 605 855 947 1257 807 286 890 580 406 1074 954 1238 290 384 862 336 150 335 413 767 610 325 353 499 169 347 167 464 388 206 295 438 102 274 225 863 611 386 340 248 52 223 427 450 255 740 234 812 732 568 496 577

Q2 358 412 579 531 465 636 707 979 465 556 530 683 546 719 870 1095 571 701 724 630 771 864 998 1197 640 753 722 761 706 817 888 1353 595 660 762 718 737 788 907 1238 523 576 657 591 792 755 865 1043 515 562 466 506 570 622 892 992 280 303 399 453 370 532 668 918 80 281 294 290 333 488 492 834 117 187 197 191 237 411 497 827 95 227 299 252 300 433 484 823 339 358 262 399 469 562 648 914

p1 2.25 4.5 3 3 2 3.75 3.5 3 2.25 2.25 2.5 4 3.75 3.5 3.25 2.75 4.25 3.5 3 2.25 3.25 3.75 3.25 2.25 3.25 2.75 4.75 4 2.25 4.5 3 2 3.25 4.25 2.5 3.25 3 2.25 2 3.75 4.75 4.5 3.5 2 4.75 3.5 3.75 2.5 3 4.75 2.75 4 4.5 2 3 2.75 4.75 3.75 2 4.25 4.5 4.75 4.5 2.5 2.5 3.5 3.25 2.75 4.25 2.75 4.25 4 2.75 4.25 3.25 2.75 4.5 3.5 3.5 2 2.5 2.5 3.75 4 4.75 4.5 4 4 4.25 2.5 4.75 2 2.25 3.75 4.25 4

p2 3.75 4.75 4.25 3.5 5 4.25 4 4.5 4.25 2.25 4.5 4 4 4.25 3.25 4.5 4.75 3.5 3.25 4.75 4.25 3.75 4.75 2.75 5 3.25 5 4.5 5 4.5 4 2 4.5 4.25 3.25 4 4.75 3.25 3.5 5 5 4.75 3.75 4 5 3.5 4.5 4.25 3.75 4.75 4.75 4.75 4.5 4.5 3 5 5 4.75 2.25 4.75 4.75 4.75 4.5 2.5 4.75 4.75 3.5 2.75 4.25 3.5 5 4.25 4 4.75 3.75 4.25 5 4.5 5 3 3.75 3 4.5 4.25 4.75 5 4.5 5 4.25 4 5 2.5 2.5 4.25 5 4

income 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120 23 29 35 40 48 62 78 120

temp 59 59 59 59 59 59 59 59 70 70 70 70 70 70 70 70 81 81 81 81 81 81 81 81 84 84 84 84 84 84 84 84 82 82 82 82 82 82 82 82 75 75 75 75 75 75 75 75 63 63 63 63 63 63 63 63 48 48 48 48 48 48 48 48 36 36 36 36 36 36 36 36 30 30 30 30 30 30 30 30 34 34 34 34 34 34 34 34 46 46 46 46 46 46 46 46

Notes: Q1 is quantity of regular cones sold Q2 is quantity of organic cones sold p1 is price is regular cones p2 is price of organic cones income is average household income in $1,000's temp is mean high temperature within the month

document:

3.2 Regression Analysis (1 of 6) A regression analysis is a statistical technique used to estimate the relationship between a dependent variable and explanatory variables. A Demand Function Example Demand Function: Q = a + bp + e - Quantity is a function of price; Q to the left is the dependent variable; p to the right is the explanatory variable; e is the random error (unpredictable and unobservable effects on dependent variable). - It is a linear demand and the estimated sign of b must be negative - If a manager surveys customers about how many units they will buy at various prices, he is using data to estimate the demand function.

Inverse Demand Function: p = g + hQ + e - Price is a function of quantity; p to the left is the dependent variable; Q to the right is the explanatory variable; e is the random error. - Based on the previous demand function, so g a b h b = = / 0 and 1 / 0 and has a specific linear form. The sign of h must be negative - If a manager surveys how much customers were willing to pay for various units .

3.2 Regression Analysis (2 of 6) Regression Analysis Using Microsoft Excel Portland Fish Inverse Demand Function: p = g + hQ + e - g and h are true coefficients. Inverse Demand Function Estimation: p g hQ = + - The OLS regression provides estimates of these coefficients, g h and , which we can use to predict the expected price, p , for a given quantity. It is assumed e=0. - Use Microsoft Excel Trendline option for scatterplots to estimate g h, and using OLS and get the respective graph and function (steps next). - The estimated inverse demand curve is p Q = 1.96 0.15 - The estimated change in price needed to induce buyers to purchase one more unit (1,000 libr as) is h = = $0.15 15. Ordinary Least Squares (OLS) Regression

OLS is the most common regression method. It fits the line to minimize the sum of the

3.2 Regression Analysis (3 of 6) Microsoft Excel Trendline Option for Scatterplots Steps Inverse Demand Function Estimation: p g hQ = + 1. Enter the quantity data in column A and the price data in column B 2. Select the data, click on the Insert tab, and select the "Insert Scatter (X, Y) or Bubble Chart" option in the Chart area of the toolbar. A menu of scatterplot types will appear (Excel Screenshots, panel a) 3. Click "Scatter." A chart appears in the spreadsheet. 4. Click on the plus sign to obtain the Chart Elements menu. 5. Place the cursor over the Trendline option and click on the arrow beside it to show an additional menu. Click on More Options. A Format Trendline dialog box opens to the right. 6. Select the options "Linear," "Display Equation on chart," and "Display R-squared value on chart" (Excel Screenshots, panel b). 7. The estimated regression line appears in the diagram. By default, Excel refers to the variable on the vertical axis as y (which is our p) and the variable on the

3.2 Regression Analysis (4 of 6) Multivariate Regression: p = g + hQ + i 3Y + e Multivariate Regression is a regression with two or more explanatory variables. The inverse demand function above incorporates both quantity Q and income Y as explanatory variables. g, h, and i are coefficients to be estimated, and e is a random error. Corresponding Estimated Regression: p Y =g+hQ+ I g,h,and are the estimated coefficients and p is the predicted value of p for any given levels of Q and Y. The objective of an OLS multivariate regression is to fit the data so that the sum of squared residuals is as small as possible. A multivariate regression is able to isolate the effects of each explanatory variable

3.2 Regression Analysis (6 of 6) Managerial Implication: Focus Groups Managers interested in estimating market demand curves often can obtain data from published sources, as in our Portland Fish Exchange example. However, if managers want to estimate the demand function for their own individual firm, they must collect information about how many units customers would demand at various prices. - They can hire a specialized marketing firm to recruit and question a focus group (a number of actual or potential consumers). - Alternatively, the marketing firm might conduct an online or written survey of potential customers designed to elicit similar information. - Managers should use a focus group if it's the least costly method of