Spreadsheet #1: Supply & Demand Model

Create a spreadsheet illustrating the determination of market equilibrium and the calculation of consumer and producer surplus. [Here's an example: S&D Spreadsheet ]

Please put all work on a single spreadsheet page!

Please make all attempts to create a visually appealing spreadsheet!

1. To begin, chose your own values for the parameters below in order to create your inverse supply and demand curves.

P = a - bQ Demand Equation
P = c + dQ Supply Equation

where:

a = demand price intercept
b = demand slope
c = supply price intercept (be sure that a > c)
d = supply slope.

2. Create a table showing quantity and the various prices that consumers are willing to pay and prices that producers are willing to accept. Use these tables to create an X-Y chart showing the supply and demand curves. In creating your table, please make sure that your "anchor" the parameter values to ones chosen in Step 1. (By doing this, your graphs and calculations will adjust automatically whenever you change any of the parameter values).

3. Create an area on your spreadsheet that illustrates the calculations of equilibrium price and quantity, consumer and producer surplus and social welfare, and point elasticity. Your equations for these values should refer to the "anchored" parameter values chosen in Step 1. For example:

Equilibrium Price =                
Equilibrium Quantity =  
Consumer Surplus =  
Producer Surplus =  
Social Welfare =  
Elasticity of Demand =  

The second column will contain the formula for each of the variables using the anchored parameter values.

4. Answer the following questions.

Q1: Solve the above equations for the equilibrium price and quantity. (This is part of Step 3.)

Q2: What is consumer surplus at the competitive equilibrium? What is producer surplus at the competitive equilibrium? What is social welfare at the competitive equilibrium? (This is part of Step 3.)

Q3: What is the point elasticity of demand at the competitive equilibrium? (This is part of Step 3.)

Q4: What happens to your answers to Q1, Q2, and Q3 above if your demand intercept increases (ie, shifts upward) by 25%?

Q5: What happens to your answers to Q1, Q2, and Q3 above if your supply intercept increases (ie, shifts upward) by 25%?

Q6: Suppose the government imposes a price ceiling at 50% of the competitive equilibrium price. Recalculate quantity demanded and quantity supplied. Recalculate consumer and producer surplus. What is the deadweight loss?