View the step-by-step solution to:

Question

Hello, I need help with the bottom section only. ( the last 2 boxes at the bottom)

/>

Q-6.png


NOTES

Notes  Q6.png


Data

65280

883200

424000

1773440

1916160

501120

899200

1842720

384960

1752160

1515840

764160

964320

642400

2204480

415200

1120320

1236000

603680

1279520

468480

814560

74080

1160000

787840

931680

726880

748320

1976640

900160

1229440

15520

944480

264320

1132640

1369440

1807040

1413280

1336480

873120

1187040

1860320

964480

1885600

978880

793600

1790880

1200160

1257440

239200

Notes  Q6.png

To perform a Monte Carlo simulation with 50 trials using the annual net profit, enter Trial in cell 12 and enter
=D15 in cell J2. Then enter 1, 2, 3, ..., 49, 50 in cells 13 through 152. Highlight cells 12 through J52, and then
select Data - Data Tools - What-If Analysis - Data Table.
The Data Table dialog box will be as shown here. Select an empty, unused cell for the Column input cell and
click OK to simulate the 50 trials in cells J3 through J52.
In this exercise, assume the simulation resulted in the sample of 50 trials results provided in the problem
statement. Use Excel to construct a frequency distribution using the provided sample of 50 simulation trial
results.

Q-6.png

The Kelly Theater produces plays and musicals for a regional audience. For a typical performance, the theater sells at least 250 tickets and occasionally reaches its capacity of 600
seats. Most often, about 450 tickets are sold. The fixed cost for each performance is normal with a mean of $2,550 and a standard deviation of $260. Ticket prices range from $30 to
$70 depending on the location of the seat. Of the 600 seats, 150 are priced at $70, 200 at $55, and the remaining at $30. Of all the tickets sold, the $55 seats sell out first. If the total
demand is at least 500, then all the $70 seats sell out. If not, then between 50% and 75% of the $70 seats sell, with the remainder being the $30 seats. If, however, the total demand
is less than or equal to 350, then the number of $70 and $30 seats sold are usually split evenly. The theater runs 160 performances per year and incurs an annual fixed cost of $2
million. Develop a simulation model to evaluate the profitability of the theater using 50 trials. What is the distribution of annual net profit and the risk of losing money over a year?
Click the icon to view a sample of 50 simulation trial results.
Say the values of the number of tickets sold, fixed cost per performance, and performances per year are entered in cells B3, B4, and B5, respectively, the cost of a seat
for the $70 seats, the $55 seats, and the $30 seats are entered in cells A8, A9, and A10, respectively, the number of seats available for the $70 seats, the $55 seats,
and the $30 seats are entered in cells B8, B9, and B10, respectively, and the number of seats sold for the $70 seats, the $55 seats, and the $30 seats are entered in
cells C8, C9, and C10, respectively. Then, for the Monte Carlo simulation, the number of $70 seats sold is randomly generated using the Excel formula
ROUND(IF(B3>= 500 , 150 ,IF(B3<= 350 ,0.5*(B 3 -C 9 ),(50%+25%*RAND())*B B )),0), the number of $55 seats sold is 200 , and the number of $30 seats sold is
=IF(B3> 350),B 3 -B 8 -B 9,0.5*(B 3)-B 9 )). The fixed cost per performance is randomly generated using the Excel formula =ROUND(NORM.INV(RAND(). .|),2).
(Type whole numbers. Use ascending order.)

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask Expert Tutors You can ask You can ask ( soon) You can ask (will expire )
Answers in as fast as 15 minutes