D13: Detailed Tax Analysis

Suppose a town would like to raise money to support its volunteer fire department by imposing a new $0.50 tax on lattes and other high-end take out beverages. The town has two types of households, high income (type H) and low income (type L), and one type of seller (type S). The demand and supply curves for an individual buyer or seller of each type are given below, along with the number of each type in the town:

Use this information to complete the spreadsheet in Teams as described below.

Step 1: Basic Data

1. Add the data from above to the block of information about the individual demand and supply curves at the top of the sheet. For example, put 1000, -100 and 200 in for the H row. Note that the cells have already been named. For example, the cells for the H row are named int_h, slope_h, and pop_h.
2. Calculate and fill in the Pmax and Pmin cells with the Y intercepts of the corresponding demand and supply curves.

Step 2: Find the BAU Equilibrium

1. Now work down the BAU column. Wait to do the Policy, Change and Pct Change columns.
2. Add a starting guess to the `P^d ` cell, $0 to the `Tax` cell, and an appropriate formula to the `P^s` cell.
3. Add appropriate formulas to the demand cells. The individual cells will use the data above. Remember that the market demand will be `N^H*Q_i^H+N^L*Q_i^L` where the `N`s are the populations in the table.
4. Add an appropriate formulas in the supply section.
5. Add an appropriate formula to the market Qd-Qs cell.
6. Use goal-seek to find a value of `P^d` for the BAU column that sets Qd-Qs to zero in the BAU column.

Step 3: Find the Policy Equilibrium

1. Copy your cells in the BAU column to the Policy column.
2. Set the Tax in that column to 0.5.
3. Use goal seek to find a value of `P^d` for the Policy column that sets Qd-Qs to zero in the Policy column.
4. If all has gone well, you now have two solutions: one for BAU and one for the tax policy case.

Step 4: Calculate Welfare Variables

1. Now fill in the individual and market welfare cells in the BAU column with appropriate equations. Remember that SS  = CS+PS+Revenue.
2. Copy the BAU welfare column to the Policy column to compute the policy impacts.

Step 5: Compare the Cases

1. Add appropriate formulas to the "Change" column to compute the change in each variable from its BAU value (new minus old or policy column minus BAU column);
2. Add appropriate formulas to the "Pct Change" column to compute the percentage change in each variable from its BAU value (note that some cells have NA where percentages don't make sense).
3. If all has gone well, the change and percent change columns will provide all the data you would need to analyze the impact of the tax.

Save and submit the results.