D15: Completing the Tax Analysis

Complete the spreadsheet in Teams and use it to finish the analysis of the latte tax using $80,000 and $40,000 as the incomes of high and low income households, respectively. Note that parts of the sheet are done: you only need to fill in the missing information.

1. Add the income data to the income cells at the top of the form;
2. Fill in formulas in the Results column (B) for the welfare impacts. Check your formulas by making sure your results match the filled-in values in the BAU column (D).
3. Use Goal Seek to find the policy case equilibrium when the tax is $0.50.
4. Copy the results column (B) and use paste-values (right click before pasting and select "Paste Special" and then "Paste Values". That will save a frozen copy of the numbers and is the way the BAU column was built.
5. Add formulas at the far right to calculate the buyer and seller shares of the tax burden and format the cells as percentages;
6. 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");
7. 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 and use data bars to show the comparison;
8. Fill in the cell next to "Progressive or regressive?" with the appropriate term;
9. Add formulas to calculate the total tax revenue contributed by each group (the cells labeled "Tot Rev H" through "Tot Rev S");
10. Add a formula to the "Total Revenue" cell that sums the revenue so you can check that it matches the policy run's results;
11. Add formulas that compute the share of total tax revenue contributed by each group and format the cells as percentages and use data bars;
12. 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.

Save and submit the results.