Hi, I could really use some help here with setting up Excel and using its Rand f
ID: 3375647 • Letter: H
Question
Hi, I could really use some help here with setting up Excel and using its Rand function to solve these problems. Thanks
Every home game for Eastern State University over the past eight years has been sold out. The
revenues from ticket sales are significant, but the sale of food, beverages, and souvenirs has
contributed greatly to the overall profitability of the football program. One particular souvenir is the
football program for each game. The number of football programs sold at each game is described by
the following probability distribution:
Number of Programs Sold
(in 100s)
Probability
23
0.15
24
0.22
25
0.24
26
0.21
27
0.18
Historically, Eastern has never sold fewer than 2,300 programs or more than 2,700 programs at one game. Each program costs $0.80 to produce and sells for $2.00. Any programs that are not sold are donated to a recycling center and do not produce any revenue.
1. Simulate the sales of programs at 10 football games. *Use the RAND function in Excel to generate the random numbers for your simulation.
2. If the university decided to print 2,500 programs for each game, what would be the average profits for the 10 games simulated in part a?
3. If the university decided to print 2,600 programs for each game, what would be the average profits for the 10 games simulated in part a? (Hint: treat this as What-If scenario; your response only needs to consist of the number that was generated.)
4. Compute the average profits for a 10-game season using the expected value formula; use the policy of printing 2,500 programs per game at $0.80 per program and a sales price of $2.00 for your cost estimate. Why is this value different from the value generated in part b? Explain your answer.
PART B: Suppose the probability distribution given in Part A only applied to days when the weather is good. When poor weather occurs on the day of the football game, the crowd that attends the game is only half of capacity. When this occurs, sales of programs decrease, as given in the following table:
Number of Programs Sold
(in 100s)
Probability
12
0.25
13
0.24
14
0.19
15
0.17
16
0.15
Programs must be printed 2-days prior to game day. The university is trying to establish a policy for determining the number of programs to print based on the weather forecast.
5. Simulate the demand for programs at 10-games in which the weather is bad. What profits would be generated if the university decides to print 2500 programs? What is the minimum and maximum profit that can be made using these guidelines?
Number of Programs Sold
(in 100s)
Probability
23
0.15
24
0.22
25
0.24
26
0.21
27
0.18
Explanation / Answer
Answer
Part 1)
In order to determine the number of program sales in a given year, we use the numbers in the last column of the random number table and compare this number to the cumulative probability distribution (CPD) of program sales, as given by the following table:
# of programs sold CPD
2300 0.15
2400 0.37
2500 0.61
2600 0.82
2700 1.00
Thus for instance, since the first number in the first column is 07, this would correspond to 2300 program sales for the first year.
Year Program sales
1 2300
2 2500
3 2600
4 2500
5 2600
6 2700
7 2500
8 2400
9 2300
10 2700
Part 2)
The following table shows the profits for each year assuming the university decides to print 2500 programs for each game.
Year Profits
1 $2600
2 $3000
3 $3000
4 $3000
5 $3000
6 $3000
7 $3000
8 $2800
9 $2600
10 $3000
Total $29,000
Thus, if the university decides to print 2500 programs for each game, they will make an average yearly profit of $2900.
Part 3)
The following table shows the profits for each year assuming the university decides to print 2600 programs for each game.
Year Profits
1 $2520
2 $2920
3 $3120
4 $2920
5 $3120
6 $3120
7 $2920
8 $2720
9 $2520
10 $3120
Total $29,000
Thus, if the university decides to print 2600 programs for each game, they will also make an average yearly profit of $2900.
For other parts please put question separately
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.