Transcript
210 ? Test Item File
Chapter Ten ? 211
Excel Problems
Cheap Rentals has collected the following information on the demand for compact cars over the last 30 days.
Daily Demand 10 11 12 13 14
Number of Days 5 7 8 5 5
a. Use the historical data to estimate the probability distribution of demand for compact cars?
b. What is the theoretical expected demand?
c. Use Crystal Ball to simulate 1000 sample demand values?
d. Compare the mean of the 1000 sample values with the theoretical expected mean?
An assembly plant has experienced the following daily absenteeism over the past 30 days.
Number of Employees Absent 0 1 2 3 4
Number of Days 4 6 10 4 6
a. Use the historical data to estimate the probability distribution of absenteeism?
b. What is the theoretical expected absenteeism?
c. Use Crystal Ball to simulate 1000 sample absenteeism values?
d. Compare the mean of the 1000 sample values with the theoretical expected mean?
Consider the event of tossing three coins. You are interested in computing the probability of getting three heads. Use Crystal Ball to simulate the event of tossing three coins 1000 times.
a. What is the theoretical probability of getting three heads?
b. What is the percentage of getting three heads using the 1000 simulated values?
4. Consider the following game involving a single die. Someone offers to give you $1 if you toss a 1, $2 if you toss a 2, $3 if you toss a 3, etc. In other words, your earnings will correspond to the face value of the die. However, to play this game, each toss will cost you $2.50. You decide to use Crystal Ball to simulate your net earnings per toss based on 1000 simulation runs. What is your mean net earning per toss based on the simulated sample values?
5. In a game of backgammon, your movement is based on the outcome that you get from rolling a pair of dice. Tossing a pair of sixes on the very first roll will give you a significant initial advantage over your opponent. Use Crystal Ball to determine the odds of rolling a pair of sixes in 1000 simulation runs.
6. An insurance company derives its revenues from its three primary business units: home insurance, auto insurance, and life insurance. The revenues for the year ending 2005, along with the potential growth rates per business unit for 2006 are illustrated below.
Business Unit 2005 Revenues 2006 Growth Rates
Home Insurance $500,000 Normal; µ = 7%, ? = 2%
Auto Insurance $450,000 Triangular, min=2%, max=6%, most probable=4%
Life Insurance $550,000 Uniform, min=4%, max=10%
a. Use Crystal Ball to simulate total revenues for 2006. Run 1000 replications of the model to find the average revenues.
b. What is the probability that 2006 revenues will exceed $1.6 millions?
7. Joe Smith is a handyman who owns his own plumbing business. In a typical month, the number of service calls that he gets varies according to the following distribution:
Number of Calls 15 16 17 18 19
Probability 0.3 0.3 0.2 0.1 0.1
His revenues per service call are normally distributed with a mean of $100 and a standard deviation of $20. His monthly expenses can range between a minimum of $1000 and a maximum of $1500, where all values between the minimum and maximum occur with equal likelihood.
a. Use Crystal Ball to simulate Mr. Smith’s monthly profits. Run 1000 replications of the model.
b. What is the average monthly profit?
8. Consider a single deck of cards. Use Crystal Ball to determine the odds of drawing 2 aces without replacement. Use 1000 replications.
9. A handyman specializes in fixing plumbing and electrical problems. Assume that 40% of the service calls that he gets relate to electrical problems, while 60% of the service calls relate to plumbing problems. On average, the time it takes to fix an electrical problem can range between 15 and 45 minutes, where all values between the minimum and maximum occur with equal likelihood. Moreover, the time it takes to fix a plumbing related problem can take a minimum of 15 minutes and a maximum of 60 minutes, where 30 minutes is the most probable time.
a. Use Crystal Ball to find the average time it takes the handyman to fix a given service call. Use 1000 replications.
b. What is the probability that it will take the handyman more than 45 minutes to complete a given service call?
10. The Gambler Hotel in Las Vegas has 300 rooms that each rents for $175 per night. For each reservation accepted, there is a 7% chance that the guest will not arrive. If the hotel overbooks, it incurs $220 for each reservation that is not honored. Suppose that the hotel’s manger believes that accepting 320 nightly reservations is a policy that the hotel should implement. Use Crystal Ball to estimate the average daily profits using the manager’s policy. Use 1000 replications.
11. Refer to problem 10. Suppose that the manger wishes to examine the effect of different reservation levels on average profitability. In specific, the manager wishes to consider reservation levels of 280, 290, 300, 310, and 320. Use Crystal Ball’s Decision Table to find the reservation level that maximizes average profitability. Use 1000 replications.
12. Consider the following PERT network, along with the completion times for each activity, in days. Use Crystal Ball to enumerate the completion times of all paths in the network. Typically, the longest path in the network dictates the project completion time. Use 1000 replications.
Activity Optimistic Most Likely Pessimistic
1-2 3 5 7
2-4 15 20 30
4-6 16 18 21
1-3 4 5 6
3-5 10 15 19
5-6 16 20 25
a. What is the average project completion time?
b. What is the probability that the project completion time will exceed 50 days?
13. For the daily lottery in a given state, participants select three numbers between 0 and 9. You feel lucky and buy 1000 tickets from a Seven-Eleven store. Suppose that the winning number is 999. Using Crystal Ball, simulate the outcomes of 1000 tickets to compute the odds of winning the lottery. Note that it is possible for 2 or more of your tickets to have the winning numbers.
14. The probability that a sniper hits his target is 80%. If three snipers aim at the same target, use Crystal Ball to compute the odds that all three snipers will hit their target. Use 1000 replications.
15. When rolling a die once, what is the probability that the face of the die is odd? Use Crystal Ball to simulate the event of rolling a single die 1000 times in order to compute the requested probability.
16. A supermarket stocks 150 apple pies each week. Weekly demand for apple pies ranges between 90 and 160 pies, all occurring with equal probability. Each unsold pie incurs an inventory carrying cost of $2.00. Unmet demand costs the supermarket $4.00 per pie. Use Crystal Ball to find the average weekly costs (i.e., shortage and carrying costs) if the supermarket stocks 150 pies weekly.
17. Refer to problem 16. Suppose that the manger wishes to examine the effect of different stocking levels on the average weekly total costs. In specific, the manager wishes to consider stocking levels of 90 pies, 110 pies, 130 pies, and 150 pies. Use Crystal Ball’s Decision Table to find the stocking level that minimizes average weekly total costs. Use 1000 replications.
18. Suppose that someone offers you a job at a casino in Las Vegas with the following 2 options. You can either earn $68.00 per night working behind a change counter, or you can serve cocktail drinks. If the latter is chosen, your earnings potential are based on the number of patrons frequenting the casino each night. You can make $100 in tips on a busy night, $75 in tips on a normal night, and $50 in tips on a slow night. The probabilities of a busy, normal, or slow night are, respectively, 0.4, 0.3, and 0.3.
a. Which option maximizes your long run average nightly earnings? Use Crystal Ball with 1000 replications.
b. How would your answer change if you can earn $85 per night working behind a counter?
19. The manager of an opera theater is concerned about overbooking one of his upcoming concerts. The theater has 300 seats, but sometimes there are empty seats. Tickets cost $50 per seat. Historical records demonstrate that about 9% of reservation holders do not show up. If the manager overbooks and more than 300 individuals show up, some of them will be given guaranteed reservations and VIP seats to the next coming show. Moreover, to keep his customers happy, the manager gives each individual who is bumped $20 worth of gift certificates. The manager wishes to explore the impact on profitability if 310 reservations are accepted. Use Crystal Ball with 1000 replications.
20. Refer to problem 19. Suppose that the manger wishes to examine the effect of different reservation levels on the average weekly total costs. In specific, the manager wishes to consider levels of 290, 300, 310, and 320. Use Crystal Ball’s Decision Table to find the reservation level that maximizes average profit. Use 1000 replications.
21. You have just graduated with an MBA degree and accepted your first job at the age of 25. You are thinking ahead for early retirement and you plan on saving $5000 at the end of each year. You expect each year's return to be modeled as a normal distribution with a mean of 10% and standard deviation of 2.5%. Suppose you intend to retire at the age of 55. Use Crystal Ball to simulate the ending investment value. Use 1000 replications.
a. What is the average amount of money that you will have at retirement?
b. What is the probability of having more than $1,000,000 at retirement?
22. A call center receives an average of 20 calls per minute. You may assume that the numbers of calls are independent of each other, and that the average number of calls remains the same for each minute interval.
a. What type of distribution describes the number of calls received?
b. Use crystal Ball to simulate the number of calls received per minute. Use 1000 replications.
c. What is the probability that the call center will receive more than 30 calls per minute?
d. What is the probability that the call center will receive less than 25 calls per minute?
23. Refer to problem 22. Suppose that the call center is interested in describing the time between successive telephone calls. You may assume that time has no effect on future telephone calls.
a. What type of distribution describes the time between successive telephone calls.
b. Use Crystal Ball to simulate the time between successive telephone calls. Use 1000 replications.
c. What is the average time between successive telephone calls?
24. Suppose that IQ scores are normally distributed with a mean of 100 and a standard deviation of 15.
a. Suppose that 1000 individuals took the IQ examination. Use Crystal Ball to simulate their scores.
b. What is the mean of the simulated IQ scores?
c. Suppose that being in the top 2% qualifies an individual as a genius. Using the simulated IQ scores, what should the IQ score be to qualify as a genius?
25. Suppose that the time needed to complete a final examination in a particular course is normally distributed with a mean of 60 minutes and a standard deviation of 10 minutes.
a. Use Crystal Ball to simulate 1000 test scores.
b. What is the probability of completing a test in less than 60 minutes?
c. What is the probability of completing a test between 50 minutes and 70 minutes?
