$[9$:$35$ pm$,24/04/2024]$ Saif Abuqasheh: Consider the Problem $4\mathrm{.}42$ from the textbook $($attached as a $.$png file below$).$

solve this problem using Excel Solver, and submit the model and solution worksheet. What is the optimum profit?

Answer the following questions using the sensitivity report regenerated for the optimum solution:

Does the optimum solution change if the selling price per barrel of Diesel reduces to $$8\mathrm{.}55?$

If the purchasing department finds a new supplier that sells an extra $1000$ barrels of Component $1$ for $$10,5$ a barrel, what would be your decision? To buy from this new supplier, too or no$?$

$[9$:$36$ pm$,24/04/2024]$ Saif Abuqasheh: A refinery blends four petroleum components into three grades of gasoline regular, premium, and diesel. The maximum quantities available of each component and the cost per barrel are as follows:

Component

Maximum Barrels

Available$/$Day

Cost$/$Barrel

$1$

$5,000$

$ $9$

$2$

$2,400$

$7$

$3$

$4,000$

$12$

$1,500$

$6$

To ensure that each gasoline grade retains certain essential characteristies, the refinery has put limits on the percentages of the components in each blend. The limits as well as the selling prices for the various grades are as follows:

Grade

Component Specifications

Selling Price$/$Barrel

Regular

Not less than $40\%$ of $1$

$$12$

Not more than $20\%$ of $2$

Not less than $30\%$ of $3$

Premium

Diesel

Not less than $40\%$ of $3$

$18$

Not more than $50\%$ of $2$

$10$

Not less than $10\%$ of $1$

The refinery wants to produce at least $3,000$ bartels of each grade of gasoline. Management wishes to determine the optimal mix of the four components that will maximize profit.