Excel Homework #1: Supply and Demand and Regression Analysis

Dr. Westerhold

Econ 332

Excel Homework #1: Supply and Demand and Regression Analysis

Part I: Demand and Supply Basics

1. Prepare an excel worksheet with a column for price ranging from $0 to $50 in $5 increments. Using the following demand and supply relationships to compute the data (creating columns) for the quantity demanded and quantity supplied at each price point:

Qd= 110 -2P

Qs= -10 +2P

1. Using the excel spreadsheet determine the equilibrium price and equilibrium quantity. Highlight this row of data on your worksheet.

1. Algebraically, solve for the equilibrium price and quantity to verify your answers. Show your work.

1. Graphing demand and supply: In order to prepare the data for a graph you will need to determine the inverse demand (P=f(Qd) and inverse supply equations (P=f(Qs)). After algebraically determining your new equations create three new columns in your excel spreadsheet for Q, P=f(Qd) and P=f(Qs). Create data for quantities 0 to 130 in increments of 10.

1. Create a graph in excel with price on the vertical axis and quantity demanded and quantity supplied on the horizontal axis. You should be able to visually see the equilibrium price and quantity.

1. Printout a copy of your data, the worksheet viewed with formulas (hit Ctrl and ` which is located in the upper left-hand corner of most keyboards to show your formulas), the supply and demand graph, and provide your work from part C.

Part II: Regression Analysis

Prepare a new worksheet to estimate the demand for Ben & Jerry’s ice cream. The demand for Ben & Jerry’s ice cream depends on its price (P), the price of competing brands (Pc), the temperature (T), income (I), and advertising expenditures (A).

Suppose you have the following sales data:

1. Copy this table of data into an excel spreadsheet. Run a regression using quantity as the dependent variable.

1. (We haven’t covered everything on statistics yet….so don’t worry about any of the output other than the variable coefficients . Also, don’t worry about the statistical significance of these coefficients). Write out your demand equation using the estimated coefficients.

1. According to your output, if Ben & Jerry’s lowered its price by $0.50 what would be the impact on sales?

1. According to your output, if Ben & Jerry’s increased its advertising budget by $10,000 what would be the impact on sales?

1. Printout a copy of your spreadsheet along with a copy of your regression analysis and provide your work form parts B-D.