Use Solver to create a Sensitivity Report for, and answer the following questions: a. Are there...

Transcribed Image Text:

Use Solver to create a Sensitivity Report for, and answer the following questions: a. Are there alternate optimal solutions to this problem? Explain. b. What is the highest possible octane rating for regular gasoline, assuming the company wants to maximize its profits? What is the octane rating for supreme gasoline at this solution? c. What is the highest possible octane rating for supreme gasoline, assuming the company wants to maximize its profits? What is the octane rating for regular gasoline at this solution? d. Which of the two profit-maximizing solutions identified in parts b and c would you recommend the company implement? Why? e. If the company could buy another 150 barrels of input 2 at a cost of $17 per barrel, should the company do so? Why? f. Create a Spider Plot showing the change in the total profit obtained by changing the availability of the inputs from 90% to 110% in 2% increments. What does this chart reveal, and what are the managerial implications? Question Riverside Oil Company in eastern Kentucky produces regular and supreme gasoline. Each barrel of regular sells for S21 and must have an octane rating of at least 90. Each barrel of supreme sells for $25 and must have an octane rating of at least 97. Each of these types of gasoline are manufactured by mixing different quantities of the following three inputs: Input Cost per BarrelOctane Rating Barrels Available (in 1000s) $17.25 100 150 $15.75 87 350 S17.75 110 300 Riverside has orders for 300,000 barrels of regular and 450,000 barrels of supreme. How should the company allocate the available inputs to the production of regular and supreme gasoline to maximize profits? a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem, and solve it using Solver. c. What is the optimal solution? 2. 3. Use Solver to create a Sensitivity Report for, and answer the following questions: a. Are there alternate optimal solutions to this problem? Explain. b. What is the highest possible octane rating for regular gasoline, assuming the company wants to maximize its profits? What is the octane rating for supreme gasoline at this solution? c. What is the highest possible octane rating for supreme gasoline, assuming the company wants to maximize its profits? What is the octane rating for regular gasoline at this solution? d. Which of the two profit-maximizing solutions identified in parts b and c would you recommend the company implement? Why? e. If the company could buy another 150 barrels of input 2 at a cost of $17 per barrel, should the company do so? Why? f. Create a Spider Plot showing the change in the total profit obtained by changing the availability of the inputs from 90% to 110% in 2% increments. What does this chart reveal, and what are the managerial implications? Question Riverside Oil Company in eastern Kentucky produces regular and supreme gasoline. Each barrel of regular sells for S21 and must have an octane rating of at least 90. Each barrel of supreme sells for $25 and must have an octane rating of at least 97. Each of these types of gasoline are manufactured by mixing different quantities of the following three inputs: Input Cost per BarrelOctane Rating Barrels Available (in 1000s) $17.25 100 150 $15.75 87 350 S17.75 110 300 Riverside has orders for 300,000 barrels of regular and 450,000 barrels of supreme. How should the company allocate the available inputs to the production of regular and supreme gasoline to maximize profits? a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem, and solve it using Solver. c. What is the optimal solution? 2. 3.

Answer rating: 100% (QA)

Solution Consider the provided details of Riverside Oil Company According to the details write down the information into the screenshot of the data in excel spreadsheet is shown below Now introduce th... View the full answer