Use probability concepts to construct and solve a decision tree in Microsoft Excel to obtain the optimal
Question:
Use probability concepts to construct and solve a decision tree in Microsoft Excel to obtain the optimal decision. Problem description ABC Company owns land that has a 20% chance of containing coal. They have an offer to sell this land for $100,000, but they can also hold the land for themselves and drill for coal. The cost of drilling for coal is $150,000, and if coal is found, the resulting revenue will be $950,000. If no coal is found after drilling, there is an additional loss of $50,000. Prior to drilling, ABC Company has the option to conduct seismic soundings at a cost of $27,000. Historically, when there has been coal, tests have been positive 75% of the time. When there has not been coal, tests have been negative 68.75% of the time.
Please complete the following items using excel tools.
1. Use Bayes’ theorem to obtain appropriate probabilities needed for quantitative analysis.
2. Use Microsoft Excel to construct and solve a decision tree to give the optimal decision plan for ABC Company.
3. How much should ABC Company be willing to pay for the seismic soundings?
4. How much should ABC Company be willing to pay for certain information about the presence of coal? Deliverables
1. Excel workbook: file that contains the solved decision tree.
2. Written report: a brief summary report that addresses the items in the problem description.
Introduction to Operations Research
ISBN: 978-1259162985
10th edition
Authors: Frederick S. Hillier, Gerald J. Lieberman