D18: Cigarette Tax Analysis

Use the spreadsheet in Teams to carry out an analysis of a proposed cigarette tax. The template has named cells and you'll need to use Goal Seek, so be sure to open it in the full Excel app.

There are two groups of buyers, Type A and Type B, and one type of seller. Initially, there is no tax and the price of a pack of cigarettes, `P_1=P_1^d=P_1^s`, is $5. The initial quantities bought or sold by an individual of each type are shown in the table at the top in the column "Ind Q1", along with the individual's elasticity, "Elast", and the total number of agents of that type, "Pop". The proposed tax is $5 per pack.

1. As before, we'll use goal seek to find the buyer price, "P2d", that makes supply and demand balance, so leave that cell blank;
2. Add the accounting rule to calculate the supply price under the policy, "P2s", in terms of "P2d" and the tax (many of the cells are already named, which may help);
3. Add formulas to calculate the changes  (`\Delta Pd` and `\Delta Ps`) and percentage changes (`%\Delta Pd` and `%\Delta Ps`) in the buyer and seller prices;
4. Add formulas to compute the values in the "Individual Outcomes" section to show what happens to an individual agent of each type;
5. Add formulas to the "Group Totals" section to scale up the individual outcomes to each group as a whole;
6. Add a formula in the yellow "Qd-Qs" cell that is equal to market demand minus market supply;
7. Use goal seek to set the "Qd-Qs" cell equal to 0 by changing the "P2d" cell;
8. Add appropriate formulas to the "Overall Market" row to report the total value of each variable, and to compute the DWL per dollar of revenue;
9. Answer the questions at the bottom of the sheet.

Save and submit the results.