Broadway Play Opening Inputs Development cost Shows per week Number of weeks Nightly theatre cost Unit ticket price Concession profit per ticket holder Theatre capacity Percent seats filled Part b Break-even number of weeks= Part c Good Taste Input data Unit cost Unit price Unit refund Expected demand Baking quantity Profit model Expected total revenue Total cost Expected total refund Expected total profit b) d) Demand 900 Baking Quantity c) Break-even demand 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 1000 1100 1200 1300 HW1 Problem1: (~ Problem 21 in 4th Edition) We are thinking of opening a Broadway play, I Love You, You're Mediocre, Now Get Better! It would cost $5 million to develop the show. There are eight shows per week, and we project that the show will run for 100 weeks (seven nights per week, we have two shows on Saturdays). It costs $1000 to open the theatre each night. Tickets sell for $50, and we earn an average of $1.50 profit per ticket holder from concessions. The theatre holds 800, and we expect 80% of the seats to be full. a) Set up a spreadsheet for the inputs and define range names for each input. You should paste a list of range names in your spreadsheet. Also, use color codes to separate inputs, outputs, and decision variables. b) Work on the spreadsheet to answer the following question: How many weeks will the play have to run for us to break even? c) Set up a one-way data table to determine how an increase in the percentage of seats full affects profit. Perform sensitivity analysis by changing percentage seats full from 60% to 100% with 5% increments. Plot the data in this table and fit the graph with a trend line. Explain your observations. d) Set up a two-way data table to determine how a joint change in the average ticket price (from $30 to $70) and the number of weeks the play runs (from 40 to 200 weeks) influence profit. Explain your observations. Page 1 Problem 2: Good Taste Bakery The owners of Good Taste, a bakery in Lewisburg, PA are attempting to determine how many loaves of their famous raisin bread to bake for the first day of the upcoming county fair. Company accounting records show that each loaf of raisin bread costs $1.30 to make. Good Taste plans to sell each loaf for $2.75. Unsold loaves can be sold on the county fair's second day as \"day-old\" products. The owners plan to sell such loaves for $1.00 each. Furthermore, they feel that county fair patrons are likely to buy 1,250 loaves on the first day. Their goal is to decide how many loaves to bake in order to maximize expected profit from these sales. a) Set up a spreadsheet for the inputs and define range names for each input. You should paste a list of range names in your spreadsheet. Also, use color codes to separate inputs, outputs, and decision variables. b) How many loaves do you recommend Good Taste to bake? Do a sensitivity analysis to answer (i.e. how does profit change with respect to the number of loaves baked, changing from 100 to 2500). c) Suppose the owners of Good Taste decide to make 1400 loaves (this is not necessarily the correct answer to part b). How many loaves must they sell to break-even; i.e. what is the demand at which Good Taste breaks-even? d) The demand for raisin bread is uncertain. So, the owners of Good Taste want to know the maximum profit they can make if demand is 900, 1000, 1100, 1200 or 1300. Build a two-way data table calculating the profit for different demand and baking quantity values (given in the template). Report the maximum profit for each demand value. Page 2