Regression Analysis: Estimating Market Demand
The goal of this assignment is to use Excel to estimate
the demand for chicken under two assumptions about what
changes the quantity of chicken demanded. Initially we will
assume that only changes in price (PC) affect the quantity
demanded (Y). Under this assumption we will use Excel to
generate a straight line demand equation that offers the
best "fit" the data you have on chicken consumption.
Then, you will decide what additional variables should
be added to the model according to the theory of demand.
You will use Excel to conduct a regression analysis to estimate
a new demand equation. A comparison of the two estimates
of demand will lead to a discussion of problems caused by
omitting crucial variables.
Finally, you will transform the data and re-estimate the
demand equation, using it to calculate and interpret price,
cross-price, and income elasticities.
1. Download the file ChickenData.xls,
and open the file in Excel.
2. Create a Scatterplot of Y = f(PC).
- Move your cursor to cell A3 on the worksheet containing
the original data. Now hold down the shift key and click
on cell B38. This will highlight the range A3:B38 which
contains the data on Y and PC. From the Insert
menu, choose Chart...
- The ChartWizard will then take over and ask you
four sets of questions about the chart you want Excel
- Step 1 (see below) asks about the type of chart you
want. Highlight the XY (Scatter) option in the "Chart
type:" column, and, in the "Chart sub-type:"
column, choose the format that does not connect the data
points. Then press Next >.
- Step 2 (see below) gives you an opportunity to change
the source data if needed. You must change the ordering
of the data so that Excel will plot the independent variable
(PC) on the x-axis and the dependent variable (Y) on the
y-axis. To do so, select the Series tab and change
the data ranges for the X Values and Y Values as shown.
Now press Next >.
- In Step 3 (see below) you will be asked to set the options
for the chart. You must include Titles, specify
that both Axes appear on the chart, show Gridlines,
and hide the Legend (you should check OFF the Show
Legend button). When you have finished formatting the
chart to your liking, then press Next >.
- The fourth Step (see below) allows you to specify where
the chart will be located. Choose "As object in:
Chicken Data". Now press the Finish button
and take a look at the beautiful scatterplot you have
- This would be a good time to save your work (press Ctrl-S).
- Click once on any one of the data points shown in the
- From the Chart menu, select Add Trendline...
- On the Type tab, choose Linear. Select
the Options tab and fill it in exactly as shown
in the image below.
- When you are finished, click OK. Save your work
Now it's time to
"do the thing".
Click on the following link
to download the Chicken
Demand Workbook. Answer the Step 2 Questions
1 - 5.
Return here when you have finished.
downloading the Excel file?