Problem: Broadway

We are thinking of opening a Broadway play, Days Not Ordinary! It would cost $10 million to develop the show. There are eight shows per week (two shows on Saturdays). It costs $1000 to open the theatre each night (seven nights a week). We earn an average of $1.75 profit per ticket holder from concessions. The theatre capacity is 800. Weekly total payroll is $70,000. There are two decision variables: i) Number of weeks to run the show, and ii) Unit ticket price. Suppose, the Percent seats filled on average is a function of these two decision variables, and it is given as follows.

= 100% __/200 __/1000

Start with the following initial values for the decision variables.

Number of weeks = 100 weeks, and unit ticket price = $50. (Note: decision variables can change throughout your solutions.) Modeling 1)Define range names for the inputs, variables, and the Total Profit. Paste a list of range names in the spreadsheet. 2)Write the Total Profit function use the names defined in Part 1). (Hint: Total Profit = Revenues Costs)

Goal seek, break-even analysis 3)When the unit ticket price is $50, how many weeks will the play have to run for us to break even?

4)How many weeks will the play have to run so that the Total Profit is exactly equal to $10 million? Sensitivity analysis, 1-way and 2-way data table

5)Fix the Number of weeks at 100, set up a one-way data table to determine how a change in Unit ticket price affect the Total Profit, by changing Unit ticket price from $20 to $100 with increments of $10.

6)Display the data from table in 5) using a chart (x-axis: Unit ticket price, y-axis: Total Profit). Find the best-fitting built-in function/trendline. Report its equation and R- squared.

7)Set-up a two-way data table to determine how changes in Unit ticket price AND the Number of weeks the play runs affect Total Profit. (Changing Unit ticket price from $20 to $100 with increments of $10; Changing Number of weeks from 50 to 400 with increments of 50 weeks.)

8)Plot a 3D plot to visualize the data in your two-way table. INDEX and MATCH

9)Use MAX, INDEX, MATCH function to find the Number of weeks that gives the maximum Total Profit for each value of the Unit ticket price in your two-way table.

10)Use MAX, INDEX, MATCH function to find the optimal decision variables, i.e., the best Number of weeks AND the best Unit ticket price that provide the maximum Total Profit in your two-way table.