Finance 316
Problem Set 4
Palisade Decision Tools Problems
1. Using the answer template from problem 1-5, change the spreadsheet to account for the following:
a. There is a 60% chance that a competitor will open up a store in your proposed market.
b. If there is no competitor entry, the price and quantity projections given in problem 1-5 are unchanged. However, if there is entry, the following table gives the estimated prices and quantities:
Year / 1 / 2 / 3 / 4 / 5 / 6 / 7 / 8Price / $65 / $82 / $107 / $99 / $89 / $84 / $79 / $74
Quantity / 2500 / 2800 / 3500 / 3100 / 3000 / 3000 / 2900 / 2850
c. The Operating Expense Factor will be triangularly distributed with the lowest value of 13%, the most likely value of 15%, and the highest value of 17%.
d. The annual increase in unit production costs is uniformly distributed with a lower bound of 3% and an upper bound of 5%.
e. Cost of capital will be normally distributed with a mean of 10% and a standard deviation of 2%. However, it is not expected to go below 6% or above 16%.
f. The capital investment required could be as low as $280,000 and as high as $400,000. It is still expected that it most likely will be $300,000.
Set up the spreadsheet and run @RISK to answer the following:
a. What is the expected NPV for this project?
b. What is the probability that the project will not break even?
c. What are the factors to which NPV is most sensitive?
2. The following problem generated a fair amount of controversy a few years ago:
You are a contestant on "Let's Make a Deal." The host tells you that behind one of three curtains is a $10,000 prize; behind the other two curtains there is nothing. You have to choose a curtain. After you do so, the host does NOT open the curtain you chose, but, instead opens one of the curtains you did NOT choose. The curtain that is opened will always be one of the curtains with no prize. So, now there are two curtains left, one of which has the prize. The host now asks you if you wish to change your choice from the curtain you originally chose to the other unopened curtain. Should you switch or stay with your original choice, or does it matter? Simulate this "game" to estimate the probability of winning the prize if you switch, and the probability of winning if you do not switch.
3. Star-crossed soap opera lovers Noah and Julia have had a big argument. Julia's sister, Maria, wants Noah and Julia to make up, so she has told them both to go to the romantic gazebo at 1 p.m. Unfortunately, neither Noah nor Julia are punctual. Each is independently likely to show up at the gazebo any time between 1 and 2 p.m. How long will each have to wait for the other in order for there to be a 75% chance that they will meet? How long would they have to wait to insure a 50% chance that they will meet?
4. An investor is considering investing $7,000 for 10 years. At the end of ten years the expected value of the investment will be $20,000 with a uniform spread of +/- $13,000. The investor would like to know the expected rate of return on his investment as well as the likelihood of achieving a rate of return less than 8% (simple interest).
a. What is the expected value of the investment in 10 years? What is the rate of return implied by that expected value?
b. Develop an @RISK model to answer the following questions:
1. What is the expected rate of return to this investor?
2. What is the probability that the investor will achieve a rate of return less than 8%?
Use at least 10,000 iterations in arriving at your answer.
c. Notice that in part a. you determined the rate of return for the expected cash flow; in part b.1. you determined the expected rate of return of the possible cash flows. If these answers are different, explain why this might be true.
5. Assume that a class of investment instruments has shown an average growth rate of 10% under market conditions that are expected to prevail for the next 10 years. On a year-to-year basis, however, these investments have had a std. dev. in their value equal to 15% of their beginning-of-year value and the possible values are normally distributed. This means, for example, that if the value of invested funds is $1,000 at the beginning of a year, at the end of the year, the value will be normally distributed with mean $1,100 (reflecting the expected 10% growth) and std. dev. of $150 (reflecting the 15% standard deviation. Alternatively, if the beginning value is $1,500, the end of year value will be normally distributed with mean $1,650 and std. dev. of $225.
a. If you invest $12,000 now, what is the expected value of the investment after 10 years? What annual rate of return would you have earned on that expected return?
b. What is the expected rate of return on this investment if you hold it for 10 years?
c. What is the probability of achieving a rate of return less than 7%?
NOTE: There is the possibility that this investment will go bust—meaning that it will hit $0 or less sometime. Model this so that you do not get negative balances. If you hit $0 anywhere, the investment ends and you’ve lost your principal with no chance of recovery.
6 A merchant is considering how many Christmas trees he should purchase. Because of the shortness of the season, only one order can be placed and that order must be placed well before the Christmas season begins. The trees cost the merchant $12.00 and they can be sold for $30.00. They have no salvage value. But any unsold trees must be disposed of at a cost of $5.00 per tree. The seasonal sales license required by the city to sell trees in the city is $250 and rental of the parking lot space for the tree lot be $300. Demand over the past seasons indicates that a good way to model the quantity that will sell is by using a normal distribution with a mean of 480 trees sold and a standard deviation of 77 trees. What is the optimal number of trees for this merchant to order? What will be his expected profit?
7. Along with other flights, CutRate Airlines operates a shuttle between Washington DC and Chicago. Their planes each have the capacity for 160 passengers. Currently the fare is $79.99 each way. Base fuel, maintenance, and crew costs run $5,650 per flight. Since this is a no-frills airline, the marginal cost per passenger consists of a soda pop and a cracker and the extra fuel burned due to added weight. This is estimated to be $32 per passenger.
With such low fares, the airline must find other ways to maximize profits and wants to come up with an "overbooking" policy regarding how many reservations it should accept for the shuttle flights. At the current fare, it is estimated that the minimum demand will be 100 passengers, with the "most likely" demand at 152 and the maximum demand at 190. Furthermore, the airline has found that on the average, 92% of the people who make reservations will fly (with a std. deviation of 6% of the reservations made--use the RiskTnormal distribution). If a flight is "overbooked," the airline incurs a cost of $155 for each passenger that it overbooks.
What is the maximum number of reservations the airline should set for each flight if it wants to maximize average profits for the shuttle? Is it even worth operating the shuttle under these conditions?
8. Baseball’s World Series is a maximum of 7 games, with the winner being the first team to win 4 games. Assume that the Toronto Blue Jays are in the World Series and that the first two games are to be played in Toronto, the next three (the third only if necessary) at the opponent’s ball park, and the last two games, only if necessary, back in Toronto. Taking into account the projected starting pitchers for each game and home field advantage that favors the home team, the probabilities of Toronto winning each game are as follows:
Game / Probability1 / 0.60
2 / 0.55
3 / 0.48
4 / 0.45
5 / 0.47
6 / 0.55
7 / 0.50
What are the probabilities that the World Series will be exactly 4 games long? 5 games long? 6 games long? 7 games long?
What is the probability that Toronto will win the series?
9. The historical sales data for a door-to-door magazine salesperson shows that if the salesperson talks to the woman of the house, there is a 15% chance of making a sale. Furthermore, if the salesperson convinces the woman of the house to purchase some magazines, the relative frequency distribution for the number of the subscriptions ordered is as follows:
Number of Subscriptions / 1 / 2 / 3Relative Frequency / 0.60 / 0.30 / 0.10
However, if the man of the house answers the door, the salesperson's chances of making a sale are 25%. In addition, the relative frequency distribution for the number of subscriptions ordered is a follows:
Number of Subscriptions / 1 / 2 / 3 / 4Relative Frequency / 0.10 / 0.40 / 0.30 / 0.20
The salesperson has found that no one answers the door at about 30% of the houses contacted. However, of the people who do answer the door, 80% are women and 20% are men. The salesperson's profit is $6 for each subscription sold.
a. Use simulation to show the house-by-house results for 1000 calls.
b. What is the total and average profit projected for the 1000 calls?
c. Based on your results from part (b), how many subscriptions should the salesperson expect to sell by calling on 100 houses per day? What is the salesperson's expected daily profit?
10. Shown below is the probability distribution for the number of pins a bowler knocks down with a first ball.
Number of Pins / 6 / 7 / 8 / 9 / 10Probability / 0.02 / 0.08 / 0.20 / 0.30 / 0.40
The probability table showing the number of pins obtained on a second ball is as follows:
If Number of Pinson First Ball Is / Number of Pins on
Second Ball Is
0 / 1 / 2 / 3 / 4
6 / 0.01 / 0.03 / 0.20 / 0.26 / 0.50
7 / 0.04 / 0.10 / 0.36 / 0.50
8 / 0.05 / 0.25 / 0.70
9 / 0.15 / 0.85
a. Using this information, simulate a game of bowling. What is the bowler's score?
b. Develop a computer program for this problem, and simulate several games of bowling. What is an estimate of the bowler's average score?
11. A particular tennis player has a 58% probability of winning a service point when she serves, but a 48% probability of winning it if her opponent serves. Construct a spreadsheet model and use @Risk to estimate the probability that she will win any particular game, set, and match. (Sets are a minimum of 6 games; a player must win by 2 games; Match is 2 out of 3 Sets.)
1