View the step-by-step solution to:

Question

# Hello, I need help with the bottom section only. ( the last 2 boxes at the bottom) />NOTESData 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 Attachment 1 Attachment 2 ATTACHMENT PREVIEW Download attachment 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. ATTACHMENT PREVIEW Download attachment 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&gt;= 500 , 150 ,IF(B3&lt;= 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&gt; 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.)

### 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