The data for this assignment summarize annual yields, effort levels and dockside prices (in 1976 dollars) for mid-Atlantic yellowfin hake. Download this datafile and import it to Excel.

1. Create an Effort-squared column adjacent to your effort column. (Insert a column between Effort and Price if necessary.) Using the spreadsheet program's regression procedure (Tools-DataAnalysis-Regression in Excel), estimate a quadratic effort-yield function of the form YIELD = B₁EFFORT + B₂EFFORT² Under "Input" enter the Yield range in the first field; the Effort and Effort-Squared range in the second field. Be sure to force the Intercept term to zero (check Excel's "Constant is Zero" box). After executing the regression module, make sure the predicted yield points trace out a quadratic curve passing through the actual yield datapoints.  (Note: if you don't have DataAnalysis in Excel's Tools menu, use Tools-AddIns and check the AnalysisToolPak add-in.)
   
   
2. Use the estimated function to calculate the level of E that maximizes sustainable yield from this fishery. Plug the value of E_MSY into the Effort-Yield equation to solve for maximum sustainable yield (MSY). Show your calculations.


3. Use the regression utility to estimate an inverse demand function for yellowfin hake:
PRICE = C₀ + C₁YIELD. Do not force a zero intercept (C₀). Use the effort-yield and demand equations to develop a predictive bioeconomic model of the fishery. In a separate column of your spreadsheet, enter effort levels from 0 to 1000 in increments of 25. In adjacent columns calculate:
• the predicted yield (from your estimated effort-yield function) for each EFFORT level;
• the predicted demand price for that yield (from the estimated demand price equation);
• the predicted total revenue (predicted yield times predicted price) for the industry; and
• the total cost for the industry, assuming a cost of $0.55 per unit effort.


4. Create an X-Y graph of effort (horizontal axis) against yield, total revenue, total cost and profit (as lines); add appropriate title and legend). Print this graph.


5. In an adjacent column in your spreadsheet, calculate the average cost per pound of hake (total cost divided by yield). Create an X-Y graph of yield (horizontal axis) against demand price and average cost (as lines; add appropriate title and legend). Print this graph.


6. TR has (or should have) two separate peaks at two different yield levels. Why?
   TC intersects (at least it should) TR at three different points. Label these intersections A, B and C. Demand should intersect supply at three different points. Which intersection corresponds to point A on the TR-TC graph? Which corresponds to point B?  Which to point C?  Explain.


7. Calculate the consumer surplus associated with the highest-price/lowest-yield equilibrium point on the supply and demand graph.
   Calculate the consumer surplus associated with the lowest-price/highest-yield equilibrium point on the supply and demand graph.
   Explain why the middle intersection point is not a stable equilibrium for the industry.
   At what level of effort are industry profits maximized?