Hints

1. Use the template on the Solution 2a-b tab. Complete the cells in accordance with the color

codes. Be sure and leave any revenue from donations OUT of the breakeven calculation (ticket

revenue only).

2. Copy your completed schedules on the Solution 2-a-b tab to the Solution 2c tab and then

a. Change the number of shows to 3 instead of 4

b. Confirm that all of your previous formulas are linked to the proper cells so you can reply

on the results

6. Copy your completed schedules on the Solution 2a-b tab to the Solution 2d tab

a. Now include the revenue from donations in the breakeven calculation

b. Confirm that all of your previous formulas are linked to the proper cells so you can reply

on the results

7. Copy your completed schedules on the Solution 2c tab to the Solution 2e tab

a. Now include the revenue from donations in the breakeven calculation

b. Confirm that all of your previous formulas are linked to the proper cells so you can reply

on the results

8. Enter your explanation to question 2f on the Solution 2f tab.

9. Use the template on the Solution 3a-c tab. Complete the cells in accordance with the color

codes.

10. Please show all the work in excel

A B D E F 1 Exhibit 1. Summary of the Contract with Mosesian Center for the Arts Black Box Theater 2 Hours per show 3 Service 4 Hall rental per week, including practices and performance 5 One-time ticket set-up fee 6 Technical support and lighting (4-hour minimum) 7 Sound techinician (4-hour minimum) 8 House management 9 Facility fees 10 Ticket convenience charges Fee $1,000 $30 $30 per hour $30 per hour $60 per show $2 per ticket $3 per ticket 1.5 1.5 11 *SETU would have to pay a minimum of 4 hours ($120) even if SETU does not use 4 hours of technical support and lighting. 12 **SETU would have to pay minimum of 4 hours ($120) even if SETU does not use 4 hours of sound technician. A B D 4 1 Exhibit 3. Assumptions for Estimating Ticket Sales and Receipts 2 3 Estimated number of shows 4 Price per ticket - adult $25 including $5 in fees 5 Price per ticket - student and senior $22 including $5 in fees 6 Estimated percentage of adult ticket 70% 7 Estimated percentage of student and senior ticket 30% 8 Maximum capacity 90 seats 9 Average occupancy per show 50% 10 B61 fx =SUM(B45:360) B D E F 1 j K L M N From Case Exhibit 1 Get information from Case Exhibit 1 (use formulas, do not retype amounts) Get information from Case Exhibit 3 (use formulas, do not retype amounts) hrs per show Use information on this sheet to prepare a formula to compute this amount ** per hour per hour per show Get information from the text of the case (you'll need to type these amounts) per ticket per ticket From Case Exhibit 3 A 1 Appendix TN2. Solutions for Questions 2a and 2b 2 3 Service 4 Fixed Costs assumptions 5 Hall rental per week, including practices and performance 6 One-time ticket set-up fee 7 8 Step costs assumptions 9. Technical support and lighting (4-hour minimum) 10 Sound techinician (4-hour minimum) 11 House management 12 13 Variable costs assumptions 14 Facility fees 15 Ticket convenience charges 16 - 17 Ticket sales assumptions 18 Estimated number of shows 19 Price per ticket - adult 20 Price per ticket - student and senior 21 Estimated percentage of adult ticket 22 Estimated percentage of student and senior ticket 23 Maximum capacity 24 Average occupancy per show 25 26 Budget 27 Ticket sales - adult 28 Ticket sales - student and senior 29 Total number of tickets 30 31 Receipts 32 Ticket receipts - adult 33 Ticket receipts - student and senior 34 Total Receipts 35 36 Expenditures 37 Variable costs 38 Facility fees 39 Ticket convenience charges 40 Total Variable Costs 41 42 Contribution margin seats Calculated based on information provided in Case Exhibits 1 and 3 0 0 $0 $0 $0 Total Receipts - Total Variable Costs The Show Must Go On Mkin the Numbers Work for a Not-for-Profit TheaterSETU Template Q Search Sheet ( Home Insert Page Layout Formulas Data Review View + Share A Insert A- A- Wrap Text Currency AY Calibri (Body) - 11 B I U- Paste A == .00 Merge & Center % .00 Conditional Format Formatting as Tabla TX Delete - Format Cell Styles Sort & & Filter Check for Updates Y 0 5 T U V X Y Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. 361 Xfx =SUM(B45:360) 4 E F 6 H 1 j K L M N N i Appendix TN2. Solutions for Questions 2a and 2b 2 3 Service From CTxhibit 1 4 Fixed Couts sumptions 5 Hall rental per wees. Including practices and performance Get information from Case Exhibit Iluse formulas, do not retype amounts 6 One-time ticket sel-up lee Get intommation from Case Exhibitluse tomu do not retype amounts Rep costssumptions hrs pershow 9 Technical support and win (4 hour minimum per hour useinformation on this set to prepare a formulate compute this amount 10 Sound technician (4-hour minimum) per hour 11 House rent per show Gut information from the rest of the case you to lype the amounts) 12 11 Voblems 14 Falys 15 Ticket cowenience charges per ticket From Case Exhibit 3 4 seats Calculated based on information provided in Case Exhibits 1 and 3 17 Ticket sales assumptions 18 Estimated number of shows 19 Price partidat-adult 20 Price per ticket - student and senior 21 Estimated percent of adult ticket 22 Estimated percentage of student and senior taket 21 Mimum capacity 24 Average occupancy pershaw 25 26 Budget 27 Ticket sales adut 28 Ticket sales - Student and senior 29 Tocal number of tickets 30 31 Recep 32 Ticket receipts adult 33 Ticke rrepts-student and senior 34 Tocal Recep 35 36 panditures 37 Variable costs 38. Facity 39 Ticket cowolence charges 40 Total Variable Costs $0 $0 Total Receipts Total Vanable Costs 42 Contrbution margin 19 44 Step costs 15 Touran hour minim Case Exhibit 1 Case Exhibit 3 From hin1 1530 Thnur x 1 hrnec XI IS Exhibit TN1 Solution 2a-b Solution 2c Solution 2d Solution 20 Solutions 21 Solutions 3a-3c + Ready B - - 80% A13 A XV fx fx Variable costs D E F H 1 3 L M N 0 Get information from Case Exhibit 1 (use formulas, do not retype amounts) Get information from Case Exhibit 3 (use formulas, do not retype amounts) per hour per hour per show hour per show hour per show Use information on this sheet to prepare a formula to compute this amount Get information from the text of the case you'll need to type these amounts) per ticket per ticket 1 1 Appendix TN6. Solution to Question 3a 2 3 Contract costs with Black Box Theater 4 Fixed Costs assumptions 5 Hall rental per week, including practices and performance 6 One-time ticket set-up fee 7 8 Step costs 9 Technical support and lighting (4-hour minimum) 10 Sound techinician (4-hour minimum) 11 House management 12 13 Variable costs 14 Facility fees 15 Ticket convenience charges 16 17 Hidden costs 18 Transporting stage dcor furniture 19 Bookkeeping for production 20 Marketing on social media service 21 Graphic design for Kamala poster 22 Rental cost of furniture 23 Stage decoration service 24 Total Hidden Costs 25 25 26 27 Appendix TN7. Solution to Question 3b 28 Estimated number of shows 29 Price per ticket - adult 30 Price per ticket - student and senior 31 Estimated percentage of adult ticket 32 Estimated percentage of student and senior ticket 33 Maximum capacity 34 Average occupancy per performance 35 36 Budget 37 Ticket sales - adult 38 Ticket sales - student and senior 39 Total number of tickets 40 $0