QUESTION 8: Create a table in Microsoft Excel illustrating how the weighted average and total number of visits for break-even changes based on a given percentage change in the visits after 5pm. Create this table based on the assumptions for the Swansons in Table 1. For example, decrease the number of times the Swansons go after 5 by 1% and increase the number of times the Swansons go before 5 by 1%, then 2% up to 5%. Then, increase the number of times the Swanson go to the pool after 5% and decrease the number of times the Swansons go to the pool before 5 by 1%, then 2%, up to 5%. The purpose of this table is to illustrate how the weighted average contribution margin per unit and the break-even point change as the usage mix changes. The percentage of visits after 5pm should range from 20% to 30% and the percentage from Table 1 for two people (50%) should remain constant.

Your table should appear like Figure 1.When completing your table, use appropriate formulas to make your table as flexible as possible (use formulas and no-hard coding).

Table 1 YMCA Pool Membership Pricing

Pricing Type Price

Family pool membership $340

Per person, before 5pm $10

Per person, after 5pm $5

Figure 1: Number of visits after 5pm (20% to 30%)

IPeople people