Please using Excel

A. Mr. Maloy has just bought a new $50,000 sport utility vehicle. Given his health conditions, he believes that there is about a 20% chance of being in an accident (that he caused) in the forthcoming year. If he is involved in an accident (he causes), the damage to his new vehicle depends on the severity of the accident (see table). Mr. Maloy is trying to decide whether he is willing to pay $800 each year to buy collision insurance with a $2,000 deductible (i.e. He pays the first $2,000 in damages if he causes an accident and the insurance company pays the remainder.) Should he buy the insurance? (Note that if the accident is caused by another person that other person is responsible to pay for the damages to Mr. Maloy's vehicle.) Therefore there are only two cases - 1. An accident he causes 2. No accident or an accident caused by someone else. Probability of an accident (he causes) 20% Cost of Insurance $800 Deductible $2,000 Type of Accident Cost Cond. Prob Total $50,000 5% Very Bad $12,000 15% Unpleasant $4,000 25% Little Damage $1,000 55% This problem description is for Parts B and C The senior executives of an oil company are trying to decide whether or not to drill for oil in a particular field in the Gulf of Mexico. It costs the company $600,000 to drill in the selected field. Company executives believe that if oil is found in this field its estimated value will be $3,400,000. At present, this oil company believes there is a 45% chance that the selected field actually contains oil. Before drilling, the company can hire a geologist at a cost of $55,000 to prepare a report that contains a recommendation regarding drilling in the selected field. In many similar situations in the past where this geologist has been hired, the geologist has predicted oil on 75% of all fields that have contained oil, and has predicted no oil on 85% of all fields that have not contained oil. B. First consider the case when hiring the geologist is not an option. Should the company drill for oil? 1. Construct a decision table to represent this problem. Also, construct the regret table. 2. Using an EXCEL spread sheet determine: a. What should the company do if senior management: i. is risk prone (optimistic)? ii. is risk averse (pessimistic)? iii. wants to maximize his average amount of money it makes? b. What is the expected value of perfect information (of whether or not oil is really there)? C. Now consider the case when hiring the geologist is an option. What should the company do? 2. Draw the decision tree for this problem. Include all of the final payoffs. 3. Compute the probabilities needed to solve the decision tree? (Hint - there are prior, test and posterior probabilities.) 4. Solve the tree for the decision strategy that maximizes the average profit for the company. Clearly indicate what action he should take at each decision point. D. Pizza King (PK) and Noble Greek (NG) are competitive pizza chains. PK believes there is a 25% chance that NG will charge $12 per pizza, a 50% that NG will charge $16 per pizza, and a 20% chance that NG will charge $20 per pizza. If PK charges price pl and NG charges price p2, PK will sell 250 + 25(p2 - pl) pizzas. It costs PK $8 to make a pizza. PK is considering charging $10, $12, $14, $16, $18, $20, or $22 per pizza. To maximize its expected profit, what price should PK charge for a pizza? A. Mr. Maloy has just bought a new $50,000 sport utility vehicle. Given his health conditions, he believes that there is about a 20% chance of being in an accident (that he caused) in the forthcoming year. If he is involved in an accident (he causes), the damage to his new vehicle depends on the severity of the accident (see table). Mr. Maloy is trying to decide whether he is willing to pay $800 each year to buy collision insurance with a $2,000 deductible (i.e. He pays the first $2,000 in damages if he causes an accident and the insurance company pays the remainder.) Should he buy the insurance? (Note that if the accident is caused by another person that other person is responsible to pay for the damages to Mr. Maloy's vehicle.) Therefore there are only two cases - 1. An accident he causes 2. No accident or an accident caused by someone else. Probability of an accident (he causes) 20% Cost of Insurance $800 Deductible $2,000 Type of Accident Cost Cond. Prob Total $50,000 5% Very Bad $12,000 15% Unpleasant $4,000 25% Little Damage $1,000 55% This problem description is for Parts B and C The senior executives of an oil company are trying to decide whether or not to drill for oil in a particular field in the Gulf of Mexico. It costs the company $600,000 to drill in the selected field. Company executives believe that if oil is found in this field its estimated value will be $3,400,000. At present, this oil company believes there is a 45% chance that the selected field actually contains oil. Before drilling, the company can hire a geologist at a cost of $55,000 to prepare a report that contains a recommendation regarding drilling in the selected field. In many similar situations in the past where this geologist has been hired, the geologist has predicted oil on 75% of all fields that have contained oil, and has predicted no oil on 85% of all fields that have not contained oil. B. First consider the case when hiring the geologist is not an option. Should the company drill for oil? 1. Construct a decision table to represent this problem. Also, construct the regret table. 2. Using an EXCEL spread sheet determine: a. What should the company do if senior management: i. is risk prone (optimistic)? ii. is risk averse (pessimistic)? iii. wants to maximize his average amount of money it makes? b. What is the expected value of perfect information (of whether or not oil is really there)? C. Now consider the case when hiring the geologist is an option. What should the company do? 2. Draw the decision tree for this problem. Include all of the final payoffs. 3. Compute the probabilities needed to solve the decision tree? (Hint - there are prior, test and posterior probabilities.) 4. Solve the tree for the decision strategy that maximizes the average profit for the company. Clearly indicate what action he should take at each decision point. D. Pizza King (PK) and Noble Greek (NG) are competitive pizza chains. PK believes there is a 25% chance that NG will charge $12 per pizza, a 50% that NG will charge $16 per pizza, and a 20% chance that NG will charge $20 per pizza. If PK charges price pl and NG charges price p2, PK will sell 250 + 25(p2 - pl) pizzas. It costs PK $8 to make a pizza. PK is considering charging $10, $12, $14, $16, $18, $20, or $22 per pizza. To maximize its expected profit, what price should PK charge for a pizza