Intermediate Microeconomics
Excel Project 2: Demand Estimation.
DUE 10/25/20 via Canvas
Question 1:
In Lecture 3 we discussed demand and its importance in business decision-making. With this exercise, you will use Excel to estimate a demand curve using SKU data on 24-packs of coke products from 36 different retail stores across the country. The ability to conduct such analysis and use the statistical results is a very valuable (and marketable) skill for economists to possess.
The data includes the quantity of 24-packs of coke products sold (product x) in a given period of time, the average price per coke pack, the average price of a 24-pack of Pepsi products (product y), average personal income in each market area, and the market size (population) of each market area.
Open the Excel spreadsheet called “Demand Curve Function.xls”.
1. The first spreadsheet is called “Marshall.” The data is in columns AB through F. In columns H ,I, J, and K, use Excel to calculate the natural log (LN) values of qx, px, py, and I.
2. Run the following regression in Excel (you may need to add in the “Data Analysis Pak” – see me and I’ll help you get started):
ln(qx) = constant + βx*ln(px) + βy*ln(py) + βI*ln(I)
Place the output starting in cell M2 of the same spreadsheet.
3. Explain what the R2 value is indicating about our equation. Explain what the F statistic is indicating.
4. Explain what the t statistics indicate about each of our variables px, py, and I.
5. Inspect the coefficient values on each of our variables. What is the relationship between goods x and y? Is x a normal or inferior good?
6. Suppose the price of x were to increase by 10 percent. What would happen to sales of x quantitatively? What will happen to revenues from sales of x (up or down)? Explain.
The above model is more consistent with Marshall’s demand analysis. One way to look at how Hicks would likely proceed, we will adjust income in the following way. In the second spreadsheet called “adj income” the data from the first spreadsheet is replicated. Follow the calculation directions as indicated in the spreadsheet to calculate data for columns G, H, I, J, and K. Copy, column K into the spreadsheet called “Hicks” in column E
7. In “Hicks”, calculate the LN values for your data (just as before) in columns G, H, I, and J. Run the following regression in Excel:
ln(qx) = constant + βx*ln(px) + βy*ln(py) + βI*ln(Inc adj)
Place the output starting in cell M2 of the same spreadsheet.
8. Compare the coefficient on px now with the coefficient on px from Marshall’s demand curve. What do these two elasticities suggests about good x? Is it normal or inferior. Explain.
Question 2
In the spreadsheet named “long and short run” you will find data on US consumption expenditures per capita on gasoline in Column B, a price index for gasoline prices in Column C (indexed to equal 100 in 2012), and a general price index for all commodities in the economy in Column D (indexed to equal 100 in XXXXXXXXXXIn columns F, G, and H I have calculated the log values of your consumption data (F), lagged consumption data (G), and gas prices divided by general prices (H – this reflects the “real price” of gasoline as it controls for inflation in the economy and address the relative price of gas as compared to other goods).[footnoteRef:1] [1: Just a quick word on this data as to many students it may seem strange. First, indexing data can very helpful and is very common when using real world time series data. When I say the gas prices are indexed to 2012, what I mean is that we take our time series of data and divide it by gas prices in 2012. So if the 2012 value was 3.5, and the 2015 value was 4.2, then the indexed value for gas prices in 2015 is (4.2/3.5 = XXXXXXXXXXAlso, because prices for any commodity can move over time simply because of general price inflation, it is very commonplace (and frankly best practice) to divide your price index data by a measure of overall price changes. The variable pc is a measure of general price inflation for all consumer goods. When we estimate demand, we want to isolate the effect that changes in gas prices specifically has on gas consumption. So we divide by pc to get a measure of how gas prices are moving relative to overall prices). When we divide a price series by another price series, we need to have both data indexed to the same year. That is why I also index pc to its 2012 value. Finally, you will notice that natural log value of gas prices to pc is negative. That’s okay and again common in real world practice. All this is saying is that the price index values for gasoline are less than the price index values for total consumer prices. So or example, if pgas is 1.2 and pc is 3.5, then the ln(pgas/pc) = XXXXXXXXXXin fact the ln of any fraction will be a negative number).]
1. Use Excel to estimate the following equation (put results starting in J2):
ln(qt)= α + β0ln(pt)+ ρln(qt-1)
2. Use your course notes to determine
a. the short run price elasticity of demand
b. the long run price elasticity of demand
3. compare the two elasticities. What do these results suggest?
Intermediate Microeconomics
Excel Project 2: Demand Estimation.
DUE 10/25/20 via Canvas
Question 1:
In Lecture 3 we discussed demand and its importance in business decision-making. With this exercise, you will use Excel to estimate a demand curve using SKU data on 24-packs of coke products from 36 different retail stores across the country. The ability to conduct such analysis and use the statistical results is a very valuable (and marketable) skill for economists to possess.
The data includes the quantity of 24-packs of coke products sold (product x) in a given period of time, the average price per coke pack, the average price of a 24-pack of Pepsi products (product y), average personal income in each market area, and the market size (population) of each market area.
Open the Excel spreadsheet called “Demand Curve Function.xls”.
1. The first spreadsheet is called “Marshall.” The data is in columns AB through F. In columns H ,I, J, and K, use Excel to calculate the natural log (LN) values of qx, px, py, and I.
2. Run the following regression in Excel (you may need to add in the “Data Analysis Pak” – see me and I’ll help you get started):
ln(qx) = constant + βx*ln(px) + βy*ln(py) + βI*ln(I)
Place the output starting in cell M2 of the same spreadsheet.
3. Explain what the R2 value is indicating about our equation. Explain what the F statistic is indicating.
4. Explain what the t statistics indicate about each of our variables px, py, and I.
5. Inspect the coefficient values on each of our variables. What is the relationship between goods x and y? Is x a normal or inferior good?
6. Suppose the price of x were to increase by 10 percent. What would happen to sales of x quantitatively? What will happen to revenues from sales of x (up or down)? Explain.
The above model is more consistent with Marshall’s demand analysis. One way to look at how Hicks would likely proceed, we will adjust income in the following way. In the second spreadsheet called “adj income” the data from the first spreadsheet is replicated. Follow the calculation directions as indicated in the spreadsheet to calculate data for columns G, H, I, J, and K. Copy, column K into the spreadsheet called “Hicks” in column E
7. In “Hicks”, calculate the LN values for your data (just as before) in columns G, H, I, and J. Run the following regression in Excel:
ln(qx) = constant + βx*ln(px) + βy*ln(py) + βI*ln(Inc adj)
Place the output starting in cell M2 of the same spreadsheet.
8. Compare the coefficient on px now with the coefficient on px from Marshall’s demand curve. What do these two elasticities suggests about good x? Is it normal or inferior. Explain.
Question 2
In the spreadsheet named “long and short run” you will find data on US consumption expenditures per capita on gasoline in Column B, a price index for gasoline prices in Column C (indexed to equal 100 in 2012), and a general price index for all commodities in the economy in Column D (indexed to equal 100 in XXXXXXXXXXIn columns F, G, and H I have calculated the log values of your consumption data (F), lagged consumption data (G), and gas prices divided by general prices (H – this reflects the “real price” of gasoline as it controls for inflation in the economy and address the relative price of gas as compared to other goods).[footnoteRef:1] [1: Just a quick word on this data as to many students it may seem strange. First, indexing data can very helpful and is very common when using real world time series data. When I say the gas prices are indexed to 2012, what I mean is that we take our time series of data and divide it by gas prices in 2012. So if the 2012 value was 3.5, and the 2015 value was 4.2, then the indexed value for gas prices in 2015 is (4.2/3.5 = XXXXXXXXXXAlso, because prices for any commodity can move over time simply because of general price inflation, it is very commonplace (and frankly best practice) to divide your price index data by a measure of overall price changes. The variable pc is a measure of general price inflation for all consumer goods. When we estimate demand, we want to isolate the effect that changes in gas prices specifically has on gas consumption. So we divide by pc to get a measure of how gas prices are moving relative to overall prices). When we divide a price series by another price series, we need to have both data indexed to the same year. That is why I also index pc to its 2012 value. Finally, you will notice that natural log value of gas prices to pc is negative. That’s okay and again common in real world practice. All this is saying is that the price index values for gasoline are less than the price index values for total consumer prices. So or example, if pgas is 1.2 and