The Excel 5 Assignment is related to the case study included in the Week 5: Course Material. I recommend you complete this assignment after you have read the course textbook, the course material and the case study. The Excel 5 workbook has three separate tabs that must be completed. Breakeven/Sensitivity Analysis Tab Complete the breakeven point for SLS for May 2013 in Column D. In Column F, assume the contribution margin increases by $3 and complete the breakeven point. In Column H, assume the contribution margin decreases by $3 and complete the breakeven point. Hint: One fixed cost is notated with a "?" so you will need to identify the missing fixed cost in the case study. All other fixed costs are listed. Profit By Class Tab Calculate the profit by class type (sales mix). You will need to complete the percentage of client visits that is blank to make it easier to allocate the costs by a formula in Excel. Number of classes and contribution margins are provided. 3-Month Profit Analysis Tab Calculate the monthly profit for March 2013, April 2013, and May 2013. Hint: The total profit (or loss) on the Profit By Class tab will equal the May 2013 profit because the Profit by Class calculation is also for May 2013. Revenue for each month is provided SLS Yoga Group Classes Breakeven Analysis May 2013 May 13 May 13 (CM:31) May13CM3 Use Median Rent Per Sq Ft Provided Red Costs Monthly Rent Monthly Shared Services 2 Teacher Costs Total Fed Costs Due Averal Provided $21 Contribution Martin Per Client Visit Total Number of Client Visits Required for Group Comes Number of Active Clients in May 2012 Monthly Number of Vints to Group Classes Required by Clients BreakevenSensitivity Analysis Profit By Class 3-Month Pront Analysis + D 1 2 3 4 5 6 2 SLS Yoga Profit By Class May 2013 Group Private Salt Cove Total Number of Client Visits Percentage 18 9 10 11 12 13 14 15 16 17 IN 19 20 21 22 Number of Clans Percentage 155 87% 6 3X 179 100% 10% Contribution Margin $21 $130 $70 Total Revenue 0 Les Fixed Costs Monthly Rent Monthly Shared Services 2 Teacher Costs Nlocate by Classes Conducted (Hint: Salt Cove Rentals Separate from Monthly Rent) Allocate by Clients Allocate by Client Vis Profit By Clans 0 24 25 20 27 23 20 30 31 32 33 30 35 BreakevenSensitivity Analysis Profit By Class 3. Month Profit Analysis N10 x D 2 SLS Yoga 3-Month Profit Analysis March May 2013 Mar 13 Apr 13 May 13 Revenue $ 19,400 $ 20,487 25,188 Costs Monthly Rent Monthly Shared Services ? Cost of Group Classes Cost of Private Classes Cost of Salt Cave Classes Total Costs $ $ 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 $ Monthly Profit 5 19,400 20,487 S 25,188 Breakeven Sensitivity Analysis Profit By Class 3-Month Profit Analysis + EXHIBIT 2 SPACE & LIGHT STUDIOS - YOGA PRICE LIST IN 2013 Package Price (SS) Price Per Class (SS) Notes 19 First-time Trial 50% off 1st class Single Class Regular Full-time Student 39 28 12-class package 3-month validity 6-month validity 378 398 31.50 33.16 628 24-class package 3-month validity 6-month validity 12-month validity 698 26.16 29.08 34.50 828 36-class package 3-month validity 6-month validity 868 1,018 24.11 28.27 Unlimited packages 1 month 3 months 368 858 1,598 2.400 21.64 20.42 19.02 14.28 6 months (at 4 classes/week) (at 3 classes/week) (at 3 classes/week) (at 3 classes/week) 1 year 6 months 1,598 2,400 19.02 14.28 (at 3 classes /week) (at 3 classes/week) 1 year Min 2, Max 5 pax Salt packages (booking required) Single (60 min) 3-class package (60 min) 5-class package (60 min) 70 205 330 68.33 Min 2, Max 5 pax 2-month validity 3-month validity 66.00 Private Yoga (One-on-one) 1 class (60 min) 5-class package (60 min) 10-class package (60 min) 150 675 135 125 2-month validity 3-month validity 1,250 Private Yoga (Duo) 1 class (60 min) 190 5-class package (60 min) 860 (430/pax) 10-class package (60 min) 1,540 (770 pax) Source: Space & Light Studios Brochure, 2013. 95/pax/class 86/pax/class 77/pax/class 2-month validity 3-month validity NUMBER OF GROUP, PRIVATE & SALT STRETCH CLASSES CONDUCTED MONTHLY IN 2013" Salt 0 6 No. of Classes Group Private Dec 2012 112 7 Jan 2013 137 21 Feb 2013 113 15 Mar 2013 33 Apr 2013 135 10 May 2013 155 18 ol 143 1 1 6 *Note: The figures have been adjusted by the Authors for teaching purposes. EXHIBIT 4 MONTHLY NUMBER OF CLIENT VISITS FOR GROUP, PRIVATE & SALT STRETCH CLASSES IN 2013" Client Visits Dec 2012 Jan 2013 Feb 2013 Mar 2013 Apr 2013 May 2013 Group Private 296 7 1,009 21 699 15 998 33 907 10 1,048 18 Salt 0 18 0 NN 12