D15: Completing the Tax Analysis

Complete the spreadsheet in Teams and use it to analyze a $1 latte tax using $80,000 and $40,000 as the incomes of high and low income households, respectively.

Step 1: Basic Data

1. Add the income data to the income cells at the top of the form (note that some of the other data is already entered);

Step 2: BAU and Policy Equilibria

1. Fill in formulas in the BAU column down through the Individual and Market Quantities section using the same steps as in the previous exercise. Use Goal Seek to find the equilibrium when the tax is 0.
2. Copy the formulas to the Policy column and use Goal Seek to find the policy case equilibrium when the tax is $1.
3. Add formulas for the Change and Pct Change cells.

Step 3: Calculate Distributional Impacts

1. Add formulas at the far right to calculate the buyer and seller tax burdens in dollar terms and as percentages of the overall tax;
2. Add formulas to compute the tax revenue contributed by an individual agent of each type (the cells labeled "Ind Rev H" through "Ind Rev S");
3. Add formulas to calculate the effective tax rates (ETRs) on high and low income households and format the cells as percentages with two decimal places;
4. Fill in the cell next to "Progressive or regressive?" with the appropriate term;
5. Add formulas to calculate the total tax revenue contributed by each group (the cells labeled "Tot Rev H" through "Tot Rev S");
6. Add a formula to the "Grand Total" cell that sums the revenue;
7. Add formulas that compute the share of total tax revenue contributed by each group;
8. Add a formula to the "DWL per Dollar" cell that computes the DWL per dollar of revenue. Format the cell as a percentage with two decimal places.

Step 4: Calculate Welfare Variables

1. Fill in the wefare variables at the bottom of the BAU and Policy columns.

Save and submit the results.