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 by doing the following:

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.
3. Add appropriate formulas or data to the `P^d `, `Tax`, `P^s` and quantity cells in the "Results" column. Wait to do the welfare cells.
4. Use goal-seek to solve for case 1, the BAU equilibrium when the tax is 0.
5. Once your BAU equilibrium is OK, fill in the individual and market welfare cells in the Results column with appropriate equations.
6. Copy the entire Results column and use paste-values to save it in the BAU column;
7. Set the tax to $0.50 and solve for case 2, the policy equilibrium;
8. Copy the Results column and use paste-values to save it in the Policy column;
9. Add appropriate formulas to the "Change" column to compute the change in each variable from its BAU value;
10. 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);
11. Use data bars to the "Pct Change" column to help illustrate the results.

Save and submit the results.