Spreadsheet #4: Monopoly Model

Create a spreadsheet illustrating the determination of a monopoly firms' profit maximization decision.

Please put all work on a single spreadsheet page!

Please make all attempts to create a visually appealing spreadsheet!

1. Below are the equations necessary for the monopolistic firm's profit maximization decision.

TC = F + cQ Total Cost Equation
ATC = F/Q + c Average Total Cost Equation
AVC = c Average Variable Cost Equation
MC = c Marginal Cost Equation
P = a - bQ Market Demand
MR = a - 2bQ Marginal Revenue

Begin by choosing your own values for the following parameters:

a = demand intercept, a > 0
b = demand slope,
F = Fixed Cost
c = cost parameter, c > 1

2. Create a table showing Q, ATC, AVC, MC, MR, and P. Use this table to create your X-Y chart. In creating your table, please make sure that you "anchor" the parameter values to ones chosen in Step 1. (By doing this, your graph and calculations will adjust automatically whenever you change any of the parameter values).

3. Create an area on your spreadsheet that allows the calculation of the following values to be calculated based on the equations from Step 1 above. Your equations for these values should refer to the "anchored" parameter values chosen in Step 1 such that, if a user were to change any of the parameter values, then the spreadsheet will automatically recalculate the optimal values:

4. Answer the following questions.

Q1: What is the monopoly equilibrium price and quantity for your model? Calculate the values for CS and PS at the monopoly equilibrium. What is the deadweight loss equal to?

Q2: Ceteris paribus, if fixed costs increase by 25%, how much output would the firm produce in the short run and what price would it charge? How does this answer compare to the price and quantity derived in Step 3 above?

Q3: Ceteris paribus, if parameter c increases by 25%, how much output would the firm produce in the short run and what price would it charge? What is the new price elasticity of demand?

Q4: Ceteris paribus, what is the optimal two-part tariff for your model? How much output will the monopolist produce under the two-part tariff? What does CS and PS equal in this case?

Q5: Ceteris paribus, if the government imposes a price ceiling that is 50% below the profit-maximizing price determined in Step 3 above, how much output would the monopolist produce? Is there a shortage at this price? Why or why not?