Cheyenne Montgomery is a massage therapist that is opening her own practice. She is trying to do some analysis on her business plan. Based on the current market, she thinks she can charge $80 for a one hour massage (price). Cheyenne is pretty sure she already has enough clients lined up to complete 50 massages a month (though she has capacity and a desire to do much more than that). Currently variable cost is $45 per massage and fixed cost is $2,100 per month. Complete the calculations for a month. Required: 1. Enter this information into the "ITEM TABLE" in your CVPTemplate that is available below 2. Set up formulas to calculate, Revenue, Cost, \& Income 3. Use formulas to complete the contribution margin income statement. 4. Use scenario manager to save this original data that you added to the "ITEM TABLE". [name this scenario: Original] Hint: only save the four variables in the item table. If you put a formula in any of these cells, allow scenario manager to change these values to constants. 5. Use Goal Seek to find out how many massages are necessary to breakeven. (Hint: you will need to set income to 0 ) 6. Use scenario manager to save this breakeven information [name this scenario: BE Original] *note if massages are not whole round up in all scenarios 7. Evaluate the following changes and report their affects on income. (Each scenario is independent, so return to the original scenario before making changes) 1. Cheyenne raises the price to $90, but the number of massages she expects for a month will decrease by 10% (use scenario manager to save this as: Scenario One). 2. There is one fixed expense of $300 that could be eliminated and replaced by a new variable cost of $3 per massage (use scenario manager to save this as: Scenario Two). 3. There is one variable cost that is currently $3 per massage, but could be replaced by a monthly subscription (fixed cost) of $300 (use scenario manager to save this as: Scenario Three). 8. Use goal seek to find out how many massages are necessary to breakeven under scenario two (use scenario manager to save this as: BE Scenario Two ) 9. Use goal seek to find out how many massages are necessary to breakeven under scenario three (use scenario manager to save this as: BE Scenario Three) 10. Evaluate massage income on scenario two 1. Using scenario two change number of massages to 75 (use scenario manager to save this as: 75 Scenario Two) 2. Using scenario two change number of massages to 150 (use scenario manager to save this as: 150 Scenario Two) 11. Evaluate massage income on scenario three 1. Using scenario three change number of massages to 75 (use scenario manager to save this as: 75 Scenario Three) 2. Using scenario Three change number of massages to 150 (use scenario manager to save this as: 150 Scenario Three) 12. Create scenario summary, include revenue, cost, and income in the results (set result cells as the cells that contain this information) 13. Set up the scenario summary table to print on one page. 14. Create a table in a new worksheet (using the data in the scenario summary table) that highlights the tradeoffs between fixed and variable cost (cost structure). Hint: you will need to use the massage combinations of scenario two and three. Note you do not need to create a pivot table or a data table, just a simple table that illustrates the tradeoffs. Look at the practice problem under cost structure on Canvas. Cheyenne Montgomery is a massage therapist that is opening her own practice. She is trying to do some analysis on her business plan. Based on the current market, she thinks she can charge $80 for a one hour massage (price). Cheyenne is pretty sure she already has enough clients lined up to complete 50 massages a month (though she has capacity and a desire to do much more than that). Currently variable cost is $45 per massage and fixed cost is $2,100 per month. Complete the calculations for a month. Required: 1. Enter this information into the "ITEM TABLE" in your CVPTemplate that is available below 2. Set up formulas to calculate, Revenue, Cost, \& Income 3. Use formulas to complete the contribution margin income statement. 4. Use scenario manager to save this original data that you added to the "ITEM TABLE". [name this scenario: Original] Hint: only save the four variables in the item table. If you put a formula in any of these cells, allow scenario manager to change these values to constants. 5. Use Goal Seek to find out how many massages are necessary to breakeven. (Hint: you will need to set income to 0 ) 6. Use scenario manager to save this breakeven information [name this scenario: BE Original] *note if massages are not whole round up in all scenarios 7. Evaluate the following changes and report their affects on income. (Each scenario is independent, so return to the original scenario before making changes) 1. Cheyenne raises the price to $90, but the number of massages she expects for a month will decrease by 10% (use scenario manager to save this as: Scenario One). 2. There is one fixed expense of $300 that could be eliminated and replaced by a new variable cost of $3 per massage (use scenario manager to save this as: Scenario Two). 3. There is one variable cost that is currently $3 per massage, but could be replaced by a monthly subscription (fixed cost) of $300 (use scenario manager to save this as: Scenario Three). 8. Use goal seek to find out how many massages are necessary to breakeven under scenario two (use scenario manager to save this as: BE Scenario Two ) 9. Use goal seek to find out how many massages are necessary to breakeven under scenario three (use scenario manager to save this as: BE Scenario Three) 10. Evaluate massage income on scenario two 1. Using scenario two change number of massages to 75 (use scenario manager to save this as: 75 Scenario Two) 2. Using scenario two change number of massages to 150 (use scenario manager to save this as: 150 Scenario Two) 11. Evaluate massage income on scenario three 1. Using scenario three change number of massages to 75 (use scenario manager to save this as: 75 Scenario Three) 2. Using scenario Three change number of massages to 150 (use scenario manager to save this as: 150 Scenario Three) 12. Create scenario summary, include revenue, cost, and income in the results (set result cells as the cells that contain this information) 13. Set up the scenario summary table to print on one page. 14. Create a table in a new worksheet (using the data in the scenario summary table) that highlights the tradeoffs between fixed and variable cost (cost structure). Hint: you will need to use the massage combinations of scenario two and three. Note you do not need to create a pivot table or a data table, just a simple table that illustrates the tradeoffs. Look at the practice problem under cost structure on Canvas