Spreadsheet #3: Competitive Model

Create a spreadsheet illustrating the determination of a competitive firm's profit maximization decision.

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

 p = TR - TC Profit Equation TR = P * Q Total Revenue Equation TC = F + cQ + dQ2 Total Cost Equation ATC = F/Q + c + dQ Average Total Cost Equation AVC = c + dQ Average Variable Cost Equation MC = c + 2dQ Marginal Cost Equation P = MR Market Price = Marginal Revenue

Begin by choosing your own values for the following parameters:

F = Fixed Cost
c = cost parameter, c > 1
d = cost parameter, d > 1
P = market price.

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

• In Chart 1 you will plot profit (on the vertical axis) versus quantity (on the horizontal axis).
• In Chart 2 you will plot price, ATC, AVC, and MC (all on the vertical axis) versus quantity (on the horizontal axis).

3. Create an area on your spreadsheet that illustrates the calculation of the profit-maximizing quantity and each of the equations from Step 1 above. Your equations for these values should refer to the "anchored" parameter values chosen in Step 1.

4. Answer the following questions.

Q1: What is the long run equilibrium price? How much output would the firm produce?

Q2: If price fell 25% below the zero-profits level (the one calculated above in Q1), how much output would the firm produce in the short run? What are profits equal to? What would profits equal if the firm did not produce any output?

Q3: Return to the zero profits price determined in Q1 above. If fixed costs increase by 25%, how much output would the firm produce in the short run? What are profits equal to?

Q4: Return to the zero profits price determined in Q1 above. If parameter c increases by 25%, what happens to the profit maximizing output level? What are profits equal to?

Q5: Return to the zero profits price determined in Q1 above. If parameter d decreases by 25%, what happens to the profit maximizing output level? What are profits equal to?