Question

Bulloch County has never allowed liquor to be sold in restaurants. However, in three months, county residents are scheduled to vote on a referendum to allow liquor to be sold by the drink. Currently, polls indicate that there is a 60% chance that the referendum will be passed by the voters. Phil Jackson is a local real estate investor who is eyeing a closed restaurant building that is scheduled to be sold at a sealed-bid auction. Phil estimates that if he bids $1.25 million, there is a 25% chance he will obtain the property; if he bids $1.45 million, there is a 45% chance he will obtain the property; and if he bids $1.85 million, there is an 85% chance he will obtain the property. Phil’s intention is to sell the property after the vote is finalized and the results are made public. If he acquires the property and the referendum passes, Phil believes he could sell the restaurant for $2.2 million. However, if he acquires the property and the referendum fails, he believes he could sell the property for $1.5 million.

1. Develop and solve a decision tree to determine how much Phil Jackson should bid for the property.

a. NOTE: Due to the nature of this problem, a bid is considered a cost (not revenue). In other words, Phil is not winning an award (i.e. contract) that will pay him to complete a certain task. Phil is trying to place an offer that will result in him owning the property. Therefore, the bid is a cost.

2. Phil has limited confidence in the exit polls suggesting that there is a 60% chance that the referendum will pass. Thus, he would like to understand if he is about to make a risky decision. To further investigate this scenario, we would like to investigate how quickly the decision might change based on a changing probability of the referendum passing.

a. To answer this question, develop a Data Table in Excel that varies the probability of success from 0% to 100% using step sizes of 1%. Your table should not only contain this probability, but it should also output the decision that would be made based on the probability as well as the expected value given the probability.

3. After your table is developed, create a table that summarizes the number of instances that each bid amount decision would be made based on the Data Table.

a. In addition, record the minimum and maximum probability for each decision.

4. Finally, create an XY scatter plot for the expected value (y-axis) and probability of success (x-axis).

Answer 1

a The following is the decision tree diagram which is depicted in an excel format.

According to the above decision tree the optimal decision is to bid amount of $ 7 Million and EMV is $1.45 million
In order to determine the probability of 0% to 100% we can derive this in a sensitivity analysis method
	Passes	Bid 1.45
	0%	Bid Nothing
	10%	Bid 1.25
	20%	Bid 1.25
	30%	Bid 1.25
	40%	Bid 1.25
	50%	Bid 1.25
	60%	Bid 1.45
	70%	Bid 1.45
	80%	Bid 1.45
	90%	Bid 1.45
	100%	Bid 1.45

3	The following table will show the most sensitive EMV for different financial estimate
	$4	$5
		0.95	0.99	1.03	1.07	1.11	1.15	1.19	1.23	1.27	1.31	1.35
	1.8	1	1	1	1	1	1	1	1	1	1	1
	1.88	1	1	1	1	1	1	1	1	1	1	1
	1.96	1	1	1	1	1	1	1	1	1	1	2
	2.04	1	1	1	1	1	1	2	2	2	2	2
	2.12	1	1	1	2	2	2	2	2	2	2	2
	2.2	2	2	2	2	2	2	2	2	2	2	2
	2.28	2	2	2	2	2	2	2	2	2	2	2
	2.36	2	2	2	2	2	2	2	2	2	2	2
	2.44	2	2	2	2	2	2	2	2	2	2	2
	2.52	2	2	2	2	2	2	2	2	2	2	2
	2.6	2	2	2	2	2	2	2	2	2	2	2

Here the ones with the 1 are the most sensitive financial statements The following is the scatter diagram as per X axis and Y axis.