Goal: The purpose of this assignment is to help you better understand demand models and elasticity. The assignment will walk you through some of the "steps" involved in demand analysis. You will have the opportunity to use Excel's Analysis Toolpak to estimate demand models using linear regression.
Project Submission: You will submit your answers for this project using the Dropbox feature in D2L. I expect to receive TWO files from each student: (1) an Excel spreadsheet that shows all relevant work, calculations, and estimation & (2) a Word of PDF document that contains the answers and output for all questions. You should present enough information in the Word/PDF document so that I could grade your assignment even without having access to your Excel spreadsheet.
Data: The data needed to perform all parts of this assignment are in the provided Excel spreadsheet: demand_project.xlsx. The first worksheet ("Data") has three columns of data. "Quantity" represents the quantity of the good sold, "Price" represents the unit price, and "HHI" represents a measure of median household income. The second worksheet ("Plot demand") contains only various prices points and will be used for question #6.
QUESTIONS
1. Create an X-Y scatter diagram ("Scatter with only Markers") with price on the vertical axis and quantity on the horizontal axis.
a. Based only on the shape of the plot, does the relationship between price and quantity seem to be linear or more nonlinear in nature? This matters because it helps inform our decision to use a linear demand specification versus a non-linear demand specification (such as the constant elasticity demand form - Figure 4.1).
2. Using Excel, estimate the following linear demand curve:
Q = a+b*P + c*HHI
where P is the price and HHI is the measure of household income.
a. Report the regression coefficients. Are any of them statistically significant? Are the coefficients the expected sign?
b. How is the fit of the model (R2)?
3. Using the estimated coefficients, calculate the price elasticity of demand for this product. In your calculation, you may use the average quantity and the average price from the Excel "Data" worksheet. Is demand elastic or inelastic?
4. Now, using Excel, estimate the constant elasticity demand equation ("log-linear demand"):
In(Q) = a+b* In(P) + c*In(HHI)
Note: when performing the necessary steps in Excel, you should use the "natural log" function (which is "ln" in Excel). Do NOT use "log" in Excel.
a. Report the regression coefficients. Are any of them statistically significant? Are the coefficients the expected sign?
b. How is the fit of the model? How does the fit compare to the linear version (question #2)?
5. Using the estimates from question #4, what is the price elasticity of demand? Is demand elastic or inelastic? What is the income elasticity of demand? Based on this value, what type of good is this product (normal, inferior, etc.)?
6. Textbooks talk about how changes in some factors (such as income) can "shift" the demand for a product. We are going to demonstrate that now. For this question, you will use the second worksheet in your Excel file ("Plot demand"). Steps:
i. Using the parameter estimates from question #4 and the given prices and incomes, fill in the predicted quantity columns for both demand curves. Be VERY careful - remember that we want sales, NOT logged sales. Think carefully about how you transform your estimates to get the predicted sales numbers.
ii. Using an X-Y scatter plot ("Scatter with Straight Lines") draw two separate demand curves - one for each of the two income levels.
iii. Do the curves resemble how textbooks might draw income increases?
7. Now we are going to think about the relationship between price elasticity of demand and optimal prices. Given the price elasticity of demand from question #5, we want to calculate the firm's optimal price. Suppose that the marginal cost of producing this product is constant at $1.50. What price is optimal (exactly)? Hint: you should not use the data in the Excel table provided. Hint #2: You may need to read ahead - or look at other sources (including my old material) - but the relationship between optimal "mark-ups" and price elasticity of demand will tell you decide what price should be charged.
Attachment:- Demand-Estimation.xlsx