D27: Revising a Cross Subsidy at a Gym

A nonprofit organization runs a gym (think of the Y). It has two kinds of members: low users (L) who intend to exercise a lot but don’t actually get around to it much, and high users (H) who are training more seriously and use the gym a lot. The L users are relatively inexpensive to serve because they don’t require much staff time or cause much wear and tear on the equipment. The H users are much higher cost, but the organization is committed to charging both types the same price.

The current price is $100 a month and the organization is running a deficit. It is considering raising the price in order to bring the budget back into balance but isn't sure how much to raise it, or what the impacts will be. However, it does have the following information:

Use the spreadsheet in Teams to find a price that would work and the impacts it would have. Here are some tips on how to fill in the formulas:

1. Cells in the Total column should be the sum of the values in the Low and High columns.
2. The cells in the Tax1 and Tax2 rows should be the net tax rate on each type of user. The rate will be positive for the taxed group and negative for the subsidized group.
3. The cells in the Rev1 and Rev2 rows should be the corresponding revenue raised from the taxed group or paid out in subsidies to the subsidized group.
4. Use goal seeking to find a value of Price 2 that moves the Case 2's total revenue (the yellow cell in the Total column of the Rev2 row) to 0.
5. The gym wants the price to be a whole number (no cents) so round the number you just found upward to the nearest integer. This will be the new price. The gym's total revenue should then be slightly positive.
6. The cells under Percent of Gain should be the percentage of the total change in revenue that is accounted for by the revenue change on that row.
7. With luck the other cells are self-explanatory.
8. Answer the questions at the bottom.

Save and submit the results.