Please solve this Model question on excel and provide the formula on how to do it:

You are thinking of opening a Broadway play, I Love You, Youre Mediocre, Now Get Better! It will cost $7 million to develop the show. You must spend the entire $7 million before opening night. You can just treat this as an expense, you do not have to depreciate it. Only after opening night will you know if the show is a Flop, if it will Perform, or if it is a Blockbuster.

There is a 30% chance that the show will be a Flop. If it is a Flop, your average ticket price is $30 each to fill 80% of the theater.

There is a 50% chance that the show will Perform. If it Performs, you can maintain an average ticket price of $50 each to fill 80% of the theater.

There is a 20% chance that the show is a Blockbuster. If it is a Blockbuster, your average ticket price will be $100 and you will fill 80% of the theater.

In terms of operations, your theater seats 800 people. It costs $15,000 per show to operate the theater, and you host 8 shows per week. In addition to ticket revenue, you have sold the concession stand rights to an independent company that pays you $1.50 for every person that attends the show.

Create an input for Show Status. Show Status can have the values Flop, Perform, or Blockbuster. Make Excel determine ticket price using the Show Status input. If you set the Show Status to Perform, Excel should know the average ticket price is $50.00. If you change it to a Flop, the ticket price should automatically update to $30.00.

Calculate the total number of shows and tickets assuming the show runs for 100 weeks.

Create an income statement assuming the show has status Perform. You dont need to

separate it out by week or show, just show one set of numbers for the duration of the

shows run. Include the development cost in your income statement.

Create a two-way data table with the following inputs:

o Weeks:Showthenetincomeat10,26,52,and100weeks.

o Show Status: Show the net income at: Flop, Perform, and Blockbuster.

Use the data table to find the expected value of profit at 10, 26, 52, and 100 weeks.

An angel investor is willing to loan you $7,000,000 to develop the show, but only if you can

repay the investment after 1 year (52 weeks). Does your model support the angel investor funding the play?

o Secondary question: If the show is a Flop, should you halt the show as soon as possible? Why or Why not?

Complete your model showing the calculations in an Excel file. Use proper color-coding and do not hardcode.

Check your model If the show is a Blockbuster, your play will make $3,391,680 if the show runs for 26 weeks.

Based on the excel sheet please answer these questions too

QA. Set your show status to Perform, and the length of the show's run to 100 weeks. What is the number of shows?

Select one:

a.800

b.100

c.720

d.640,000

e.512,000

QB. With the show set to Perform and 100 weeks, what is the total revenue from ticket sales?

Answer to the nearest dollar with no additional words or punctuation. Examples of accepted answers: 12345 -9876543

The following format would be marked incorrect due to formatting: 12,345 $12345 $12,345

Answer:

QC. Set your show status to Blockbuster, and the length of the show's run to 26 weeks. How many tickets are sold?

Select one:

a.166,400

b.26,624

c.208

d.133,120

e.166

QD. With the show set to Blockbuster and 26 weeks, how much profit is there from concessions?

Answer to the nearest dollar with no additional words or punctuation. Examples of accepted answers: $12,345 -$9,876,543

The following format would be marked incorrect due to formatting: $12,345.43 The answer is $12,345. 12345

Answer:

Q E . Set your show status to Flop, and the length of the show's run to 13 weeks. What is the cost to open the theater, not including the development cost?

Select one:

a.$1,560,000

b.$1,248,000

c.$960,000

d.$1,040,000

e.$104,000

Qf. With the show set to Flop and 13 weeks, what is the total profit for the duration of the run?

Answer to the nearest dollar with no additional words or punctuation. Examples of accepted answers:

12345 -9876543

The following format would be marked incorrect due to formatting: 12,345 $12345 $12,345

Answer :

QG. Set your show status to Flop, and the length of the show's run to 52 weeks. What is the total ticket revenue?

Select one:

a.$9,984,000

b.$13,312,000

c.$7,987,200

d.$16,640,000

e.$26,624,000

QH. What is the expected value of a 52-week run?

Answer to the nearest dollar with no additional words or punctuation. Examples of accepted answers: $12,345 -$9,876,543

The following format would be marked incorrect due to formatting: $12,345.43 The answer is $12,345. 12345

Answer : ?

Qi. Should the angel investor fund the show?

Select one:

a.Yes, the expected value is more than the amount borrowed.

b.Yes, the expected value is above 0, so we expect the show to earn enough money to pay back the angel investor.

c.No, the expected value is less than the amount borrowed.

d.No, the expected value is negative.

e.Yes, the show will always make enough money to pay back the angel investor in a year.

f.No, the show might not make enough to pay back the angel investor in a year.

g.No, it is impossible for the show to make enough money to pay back the angel investor in a year.

Qj. If the show is a Flop, should you halt the show as soon as possible?

Select one:

a.Yes

b.No