Question

Develop a Crystal Ball model for the garage band in Problem with the following assumptions. The expected crowd is normally distributed with a mean of 3,000 and a standard deviation of 400 (minimum of 0). The average expenditure on concessions is also normally distributed with mean $15, standard deviation $3, and minimum 0. Identify the mean profit, the minimum observed profit, maximum observed profit, and the probability of achieving a positive profit. Develop and interpret a confidence interval for the mean profit for a 5,000-trial simulation.

Problem

A garage band wants to hold a concert. The expected crowd is 3,000. The average expenditure on concessions is $15. Tickets sell for $10 each, and the band’s profit is 80% of the gate, along with concession sales, minus a fixed cost of $10,000. Develop a spreadsheet model to find their expected profit. Define and run some reasonable scenarios using the Scenario Manager to evaluate profitability for variations in the estimates.

Answer 1

Follow the steps provided below:

1) Enter the provided data as shown in the excel sheet below as per data provided in problem.

2) Now Select the Average concessions cell->Crystal ball in the menu bar, and then define Assumption. Select the Distribution of average concessions as Normal and enter the provided values of mean and standard deviation, then click on Ok.

The Average concessions cell will now be highlighted in green.

3) In the Model section, enter the Ticket price as $10.

4) Enter the Crowd as 3000. Click on Define Assumptions and select the Distribution as Normal and enter the provided values of mean and standard deviation. Click on Ok.

5) Enter the Revenue from Ticket sales value as where B9 is the crowd cell and B8 is the ticket price cell.

6) Enter the Revenue from concessions value as where B5 is the Average concessions cell.

7) Enter the Total Revenue as where B10 is the revenue from ticket sales cell and B11 is the revenue from concessions cell.

8) Enter the Total fixed costs cell as where B4 is the fixed cost cell.

9) Enter profit as where B12 is the total revenue cell and B13 is the total fixed cost cell.

10) Select the Profit cell and click on Define Forecasts in Crystal bar menu. The default name appears as Profit. Click on Ok.

11) In the Run section of crystal ball, set the number of trials to 5000.Click on Start.

The profit chart is as shown below:

To identify the confidence interval of the mean value of $49,993, click on the left range box in the chart and change the value to the mean value. The value is. This means that the probability of getting a profit of at least $49,993 is 48.46%.