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 smartphone 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 eightequally-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 a 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. Forbothproducts separately, do/answer the following:

a. First create a 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

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.

Foreach good, do/answer the following (d-l):

d. Carry out a multiple regression using the 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, including the intercept)?

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 theprice elasticity of demand for each productusing 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 these particular numbers mean?). Is demand for either product elastic or inelastic?

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? (No calculations needed.)

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? (No calculations needed.)

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 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 the total predicted units sold in a month.

Predicted monthly sales:

Regular (Q1)

Organic (Q2)

78 degrees

90 degrees

032 X V fx B C D E F G H K Q1 Q2 Td p2 income temp 1046.0 569.0 $2.25 $3.75 23 59 Notes: Q1 is quantity of regular cones sold 610.0 473.0 $4.50 $4.75 29 59 879.0 196.0 $3.00 $4.25 35 59 Q2 is quantity of organic cones sold 678.0 976.0 $3.00 $3.50 40 59 p1 is price is regular cones 1426.0 706.0 $2.00 $5.00 48 59 p2 is price of organic cones 636.0 407.0 $3.75 $4.25 62 59 income is average houshold income in $1,000's 958.0 645.0 $3.50 $4.00 78 59 temp is mean high temperature within the month 1272.0 634.0 $3.00 $4.50 120 59 10 1072.0 684.0 $2.25 $4.25 23 70 29 11 1043.0 788.0 $2.25 $2.25 70 12 1297.0 934.0 $2.50 $4.50 35 70 13 528.0 645.0 $4.00 $4.00 40 70 14 1028.0 1026.0 $3.75 $4.00 48 70 15 1160.0 564.0 $3.50 $4.25 62 70 16 990.0 694.0 $3.25 $3.25 78 70 17 1359.0 1329.0 $2.75 $4.50 120 70 18 770.0 276.0 $4.25 $4.75 23 81 19 1266.0 1118.0 $3.50 $3.50 29 81 1242.0 842.0 35 81 20 $3.00 $3.25 21 1385.0 463.0 $2.25 $4.75 40 81 22 1044.0 816.0 $3.25 $4.25 48 81 23 834.0 548.0 $3.75 $3.75 62 81 24 894.0 1052.0 $3.25 $4.75 78 81 25 1639.0 1452.0 $2.25 $2.75 120 81 26 724.0 814.0 $3.25 $5.00 23 84 1224.0 1141.0 $2.75 $3.25 29 84 28 886.0 1171.0 $4.75 $5.00 35 84 29 578.0 614.0 $4.00 $4.50 40 84 30 1737.0 279.0 $2.25 $5.00 48 84 31 701.0 1070.0 $4.50 $4.50 62 84 32 1470.0 1112.0 $3.00 $4.00 78 84 33 1478.0 1603.0 $2.00 $2.00 120 84 34 1131.0 798.0 $3.25 $4.50 23 82 35 896.0 917.0 $4.25 $4.25 29 82 36 1054.0 494.0 $2.50 $3.25 35 82 37 1179.0 643.0 $3.25 $4.00 40 82 38 1119.0 921.0 $3.00 $4.75 48 82 39 1369.0 779.0 $2.25 $3.25 62 82 40 1066.0 762.0 $2.00 $3.50 78 82 41 1119.0 1114.0 $3.75 $5.00 120 82 21.0 822.0 $4.75 $5.00 23 75 43 764.0 277.0 $4.50 $4.75 29 75 44 142.0 901.0 $3.50 $3.75 35 75A B C D E F G 45 1137.0 697.0 $2.00 $4.00 40 75 46 144.0 390.0 $4.75 $5.00 48 75 47 652.0 1100.0 $3.50 $3.50 62 75 48 728.0 952.0 $3.75 $4.50 78 75 49 1589.0 833.0 $2.50 $4.25 120 75 50 873.0 74.0 $3.00 $3.75 23 63 51 18.0 938.0 $4.75 $4.75 29 63 52 543.0 367.0 $2.75 $4.75 35 63 53 631.0 556.0 $4.00 $4.75 40 63 54 913.0 820.0 $4.50 $4.50 48 63 55 1322.0 967.0 $2.00 $4.50 62 63 56 726.0 1201.0 $3.00 $3.00 78 63 57 984.0 730.0 $2.75 $5.00 120 63 58 228.0 606.0 $4.75 $5.00 23 48 59 886.0 5.0 $3.75 $4.75 29 48 60 842.0 239.0 $2.00 $2.25 35 48 61 676.0 781.0 $4.25 $4.75 40 48 62 251.0 155.0 $4.50 $4.75 48 48 63 192.0 1029.0 $4.75 $4.75 62 48 64 608.0 859.0 $4.50 $4.50 78 48 65 853.0 706.0 $2.50 $2.50 120 48 66 835.0 65.0 $2.50 $4.75 23 36 67 116.0 44.0 $3.50 $4.75 29 36 68 604.0 877.0 $3.25 $3.50 35 36 69 20.0 802.0 $2.75 $2.75 40 36 70 670.0 283.0 $4.25 $4.25 48 36 71 795.0 250.0 $2.75 $3.50 62 36 72 436.0 446.0 $4.25 $5.00 78 36 73 578.0 677.0 $4.00 $4.25 120 36 74 795.0 28.0 $2.75 $4.00 23 30 75 136.0 36.0 $4.25 $4.75 29 30 76 531.0 704.0 $3.25 $3.75 35 30 77 418.0 497.0 $2.75 $4.25 40 30 78 523.0 45.0 $4.50 $5.00 48 30 79 54.0 730.0 $3.50 $4.50 62 30 80 294.0 476.0 $3.50 $5.00 78 30 81 563.0 1022.0 $2.00 $3.00 120 30 82 179.0 644.0 $2.50 $3.75 23 34 83 414.0 115.0 $2.50 $3.00 29 34 84 210.0 678.0 $3.75 $4.50 35 34 85 589.0 677.0 $4.00 $4.25 40 34 86 156.0 373.0 $4.75 $4.75 48 34 87 469.0 952.0 $4.50 $5.00 62 34 88 644.0 156.0 $4.00 $4.50 78 3467 116.0 44.0 $3.50 $4.75 29 36 68 604.0 877.0 $3.25 $3.50 35 36 69 20.0 802.0 $2.75 $2.75 40 36 70 670.0 283.0 $4.25 $4.25 48 36 71 795.0 250.0 $2.75 $3.50 62 36 72 436.0 446.0 $4.25 $5.00 78 36 73 578.0 677.0 $4.00 $4.25 120 36 74 795.0 28.0 $2.75 $4.00 23 30 75 136.0 36.0 $4.25 $4.75 29 30 76 531.0 704.0 $3.25 $3.75 35 30 77 418.0 497.0 $2.75 $4.25 40 30 78 523.0 45.0 $4.50 $5.00 48 30 79 54.0 730.0 $3.50 $4.50 62 30 80 294.0 476.0 $3.50 $5.00 78 30 81 563.0 1022.0 $2.00 $3.00 120 30 82 179.0 644.0 $2.50 $3.75 23 34 83 414.0 115.0 $2.50 $3.00 29 34 84 210.0 678.0 $3.75 $4.50 35 34 85 589.0 677.0 $4.00 $4.25 40 34 86 156.0 373.0 $4.75 $4.75 48 34 87 469.0 952.0 $4.50 $5.00 62 34 88 644.0 156.0 $4.00 $4.50 78 34 89 464.0 1030.0 $4.00 $5.00 120 34 90 728.0 71.0 $4.25 $4.25 23 46 91 875.0 657.0 $2.50 $4.00 29 46 92 620.0 544.0 $4.75 $5.00 35 46 93 608.0 402.0 $2.00 $2.50 40 46 94 372.0 381.0 $2.25 $2.50 48 46 95 334.0 330.0 $3.75 $4.25 62 46 96 282.0 358.0 $4.25 $5.00 78 46 97 788.0 658.0 $4.00 $4.00 120 46