Format Please send your Excel file Part A 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 31 st 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 31 st 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 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 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 Estimate the probability that the theme park will generate a daily profit of at least $100,000 Estimate the probability that the theme park will not breakeven on any given day Approximately how many patrons must visit the theme park on any given day in order for the theme park to at least breakeven 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 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) Part B You task is to simulate in Excel an actual game of lotto involving 45 numbered balls (from 1 to 45) The objective is to pick out 6 balls from the 45 balls without replacement In other words, once you pick out a certain ball (e g number 24), you cannot pick it again because it has already been picked Your task is to simulate the picking of six winning balls (assuming without replacement) and display in six separate cells what those 6 numbers are To receive full marks, your model must have zero chance of the same numbers being picked You must simulate this in Excel without using macros or VBA Excel

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 17, 2024

Format: Please send your Excel file . Part A You are the Financial Manager at a local theme park See-World and have been asked by

Format: Please send your Excel file .

Part A

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 31^st 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 31^st 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:

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

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

Estimate the probability that the theme park will generate a daily profit of at least $100,000

Estimate the probability that the theme park will not breakeven on any given day

Approximately how many patrons must visit the theme park on any given day in order for the theme park to at least breakeven?

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.

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)

Part B

You task is to simulate in Excel an actual game of lotto involving 45 numbered balls (from 1 to 45).

The objective is to pick out 6 balls from the 45 balls without replacement. In other words, once you pick out a certain ball (e.g. number 24), you cannot pick it again because it has already been picked.

Your task is to simulate the picking of six winning balls (assuming without replacement) and display in six separate cells what those 6 numbers are.

To receive full marks, your model must have zero chance of the same numbers being picked. You must simulate this in Excel without using macros or VBA Excel.