The SweetTooth Candy Company knows it will need 10 tons of sugar 6 months from now to implement its production plans. Jean Dobson, SweetTooth’s purchasing manager, has essentially two options for acquiring the needed sugar. She can either buy the sugar at the going market price when she needs it, 6 months from now, or she can buy a futures contract now. The contract guarantees delivery of the sugar in

6 months but the cost of purchasing it will be based on today’s market price. Assume that possible sugar futures contracts available for purchase are for 5 tons or 10 tons only. No futures contracts can be purchased or sold in the intervening months. Thus, SweetTooth’s

possible decisions are: (1) purchase a futures contract for 10 tons of sugar now, (2) purchase a futures contract for 5 tons of sugar now and purchase 5 tons of sugar in 6 months, or (3) purchase all 10 tons of needed sugar in 6 months. The price of sugar bought now for delivery in 6 months is $0.0851 per pound. The transaction costs for 5-ton and 10-ton futures contracts are $65 and $110, respectively. Finally, Ms. Dobson has assessed the probability distribution for the possible prices of sugar 6 months from now (in dollars per pound).

Possible Sugar Prices in 6 Months ($/pound)	Probability
0.078	0.05
0.083	0.25
0.087	0.35
0.091	0.2
0.096	0.15

a) Create an opportunity cost matrix

b) Use a one-way data table to show the optimal strategy as the current price of sugar varies from 64 cents to 94 cents in increments of 2 cents. The reported “value” in the data table should be the name of the optimal strategy. Use the INDEX(MATCH) function to implement this