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

Step 4: Calculate Welfare Variables

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

Step 5: Additional Big-Picture Variables

The cells at the lower right of the template calculate some additional variables that are useful in assessing taxes.

1. Add formulas to calculate the total tax revenue contributed by each group (the cells labeled "Tot Rev H" through "Tot Rev S"). It's the tax burden in dollars for an individual in the group (calculated above) multiplied by the number of people in the group;
2. Add a formula to the "Grand Total" cell that sums the revenues you just calculated and check that the answer is the same as the one in the Policy column;
3. Add formulas that compute the share of total tax revenue contributed by each group and format the cells as percentages. This shows which group(s) are responsible for most of the tax revenue and is important for understanding the political impact of the policy.
4. Finally, add a formula to the "DWL per Dollar" cell that is equal to the DWL divided by the total revenue raised by the tax. Format the cell as a percentage with two decimal places. This is an important measure of the inefficiency of the tax: it's the amount of surplus wasted per dollar of revenue earned by the government.

Save and submit the results.