ECO 240 Course Web site graphic
Previous page.link to main page.Next page.

Page Links

Regression Analysis: Estimating Market Demand

 

Instructions

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.

Steps:

1. Download the file ChickenData.xls, and open the file in Excel. Top of page.

 

2. Create a Scatterplot of Y = f(PC). Top of page.

  • 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 to create.
  • 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 created!

 

  • This would be a good time to save your work (press Ctrl-S).

 

Insert Trendline Top of page.

  • Click once on any one of the data points shown in the chart.
  • 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.

Excel dialog box showing information needed to add a trendline to a chart.

  • When you are finished, click OK. Save your work (Crtl-S).

 

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.

Need help downloading the Excel file?