You are the Financial Manager at a local theme park See-World and have been asked by the General Manager to report on the profitability of the business. Specifically, the General Manager wishes to know what the average daily profit is for the business overall, as well as for each of the major sources of revenue (namely ticket sales, revenue from the sale of food and beverages, and revenue from the sale of merchandises). In addition, the General Manager wishes to estimate the risk of those daily profits. To assist you in your analysis, your assistant has collected data on: - the number of patrons entering the park each day over a one-year period (and the break down of the ticket types sold on each day) - the dollar amount of transactions from the sale of food and beverages on 31st December 2021 (your assistant believes that these dollar amounts are typical of what happens each day) - the dollar amount of transactions from the sale of merchandises on 31st December 2021 (your assistant believes that these dollar amounts are typical of what happens each day) Furthermore, your assistant believes that: - on any given day, approximately 90% of the patrons will buy either food and/or beverages at the park (of this 90%, approximate 75% will make one purchase and 25% will make two purchases) - on any given day, only approximately 20% of the patrons will buy merchandises at the park The variable cost on food and beverages is estimated to be approximately 20% of the sales made that day, while the variable cost on merchandises is estimated to be approximately 10% of the sales made that day. The daily fixed costs of running the theme park are approximately $100,000 a day. It is allocated across each source of revenue by their proportion relative to the total revenue. Ticket prices charged by the theme park are $55 for adults, $45 for concessions, and $35 for children. Given the above information, answer/do the following questions/things: 1. Estimate (using simulation with no more than 500 replications) what the average daily overall profit is for the theme park, as well as the profits of each major sources of revenue (as well as their standard deviation) - Note you will need to simulate the internal processes of the profit model, not just the final profit figure 2. Explain briefly (in a text box inserted somewhere on your spreadsheet) as to why it is necessary to use simulation in this scenario to estimate the average daily profits (and the standard deviation of those profits) and why it is not possible to simply take the average of the actual profits each day to be the average daily profits 3. Estimate the probability that the theme park will generate a daily profit of at least $100,000 4. Estimate the probability that the theme park will not breakeven on any given day 5. Approximately how many patrons must visit the theme park on any given day in order for the theme park to at least breakeven? 6. The General Manager wishes to increase the ticket prices above to $60 for adults, $50 for concessions, and $40 for children. However, he suspects that the price increase would result in a 10% reduction in patronage. Use your model to analyse this proposal and advise the General Manager as to whether or not the price increase is a good idea. 7. The Marketing Manager wishes to reduce the ticket prices down to $50 for adults, $40 for concessions, and $30 for children. She suspects that the price reduction would result in a 15% increase in patronage. Use your model to analyse this proposal and advise the Marketing Manager as to whether or not the price reduction is a good idea. Tips: - Try and layout the profit system of the theme park in terms of equations and figure out which variables need to be simulated - You will need to segment the data you have been provided with into various bin ranges in order to model the scenario as close to reality as possible (i.e. you need to consider the underlying probability distribution of the data provided) - Be creative and make assumptions if necessary (they should be logical)