please answer correctly on excel and show calculation on excel

Q5. (15 points) Next month, Sarnia Chemical Industries (SCI) will need to purchase either 1,000, 2,000, or 3,000 gallons of a certain polymer for the upcoming quarter's production of a chemical compound. The company has been offered the compound from three manufacturers: Winslow, Barnett, or Frume. The price per gallon currently quoted to Sarnia Chemical Industries by the three manufacturers is a function of their order quantity and is as follows: Manufacturer Winslow Barnett Frume Sarnia's 1,000 $1.00 9.90 $.80 Order 2,000 $.70 $.80 $.75 Quantity 3,000 $.60 $.65 $.65 Sarnia Chemical can either "lock-in" these prices by immediately signing a contract with one of the three manufacturers or wait until next month to try to purchase the chemical on the spot market. If Sarnia signs a contract it must specify the amount it will order. Any chemicals unused by Allied can be sold for scrap at $.40 per gallon. Sarnia's management estimates that if it waits to purchase the chemical on the spot market, the price it will pay will be either $.70, S.90, or $1.10 per gallon. Sarnia Chemical estimates the likelihood of the spot price being 5.70 per gallon will be twice the likelihood that the spot price will be 5.90 per gallon and six times the likelihood that the spot price will be $1.10 per gallon. Sarnia Chemical management estimates that the following probabilities hold relative to next month's demand for the compound: P(Demand - 1,000 gallons) - .30 P(Demand - 2,000 gallons) -.50 P(Demand = 3,000 gallons) - 20 Required What is Sarnia's optimal strategy regarding purchasing the chemical compound? wnload Print Q5. (15 points) Next month, Sarnia Chemical Industries (SCI) will need to purchase either 1,000, 2,000, or 3,000 gallons of a certain polymer for the upcoming quarter's production of a chemical compound. The company has been offered the compound from three manufacturers: Winslow, Barnett, or Frume. The price per gallon currently quoted to Sarnia Chemical Industries by the three manufacturers is a function of their order quantity and is as follows: Manufacturer Winslow Barnett Frume Sarnia's 1,000 $1.00 9.90 $.80 Order 2,000 $.70 $.80 $.75 Quantity 3,000 $.60 $.65 $.65 Sarnia Chemical can either "lock-in" these prices by immediately signing a contract with one of the three manufacturers or wait until next month to try to purchase the chemical on the spot market. If Sarnia signs a contract it must specify the amount it will order. Any chemicals unused by Allied can be sold for scrap at $.40 per gallon. Sarnia's management estimates that if it waits to purchase the chemical on the spot market, the price it will pay will be either $.70, S.90, or $1.10 per gallon. Sarnia Chemical estimates the likelihood of the spot price being 5.70 per gallon will be twice the likelihood that the spot price will be 5.90 per gallon and six times the likelihood that the spot price will be $1.10 per gallon. Sarnia Chemical management estimates that the following probabilities hold relative to next month's demand for the compound: P(Demand - 1,000 gallons) - .30 P(Demand - 2,000 gallons) -.50 P(Demand = 3,000 gallons) - 20 Required What is Sarnia's optimal strategy regarding purchasing the chemical compound? wnload Print