IMPORTANT: This is an individual assignment, you are NOT allowed to receive or give assistance of any kind

$($do not share your file with anyone$)$

A beverage company produces four types of bottled juice: Apple $($A$),$ Banana $($B$),$ Grape $($G$),$ Mixed $($M$).$ Each case of juice uses limited sugar and limited production$$line hours. In addition, there are marketing $($demand$)$ limits on how many cases of each juice can be sold. Finally, the company has found that sales strategy requires Apple juice to be produced in a certain ratio relative to Grape juice $($to satisfy $$bundle$$ promotions$).$ All production quantities must be whole$$number cases $($no fractional cases$).$

The goal is to determine how many cases of each type of juice to make $($A$,$ B$,$ G$,$ and M$)$ so as to maximize total weekly profit.Sugar Required $($lb$/$case$)$ & Total

Available:

Apple $($A$)$

$($lb $/$ case$)$Banana $($B$)$

$($lb $/$ case$)$Grape $($G$)$

$($lb $/$ case$)$Mixed $($M$)$

$($lb $/$ case$)$Total Available

$($lb$)$Sugar $=($respectively$)$

$2\mathrm{.}00$

$3\mathrm{.}00$

$4\mathrm{.}00$

$5\mathrm{.}00$

$1500\mathrm{.}00$

Production Time $($hours$/$case$)$ & Total Available:

Apple $($A$)$

$($hr $/$ case$)$

Banana $($B$)$

$($hr $/$ case$)$

Grape $($G$)$

$($hr $/$ case$)$

Mixed $($M$)$

$($hr $/$ case$)$

Total Available

$($hr$)$

Production Time

$=($respectively$)$

$0\mathrm{.}50$

$0\mathrm{.}65$

$0\mathrm{.}75$

$1\mathrm{.}00$

$130\mathrm{.}00$

Maximum Demand $($cases$/$week$)$:

Apple $($A$)$

$($cases$/$week$)$

Banana $($B$)$

$($cases$/$week$)$

Grape $($G$)$

$($cases$/$week$)$

Mixed $($M$)$

$($cases$/$week$)$

Demand

$=($respectively$)$

$200\mathrm{.}00$

$20\mathrm{.}00$

$35\mathrm{.}00$

$40\mathrm{.}00$

Ratio Constraints $($Apple vs$.$ Grape$)$:

At least $2$ cases of Apple for each case of Grape

At most $4$ cases of Apple for each case of Grape

All production quantities A$,$B$,$G$,$M must be whole numbers $($integer values$).$Profit per Case:

Apple $($A$)$

$($$$/$case$)$

Banana $($B$)$

$($$$/$case$)$

Grape $($G$)$

$($$$/$case$)$

Mixed $($M$)$

$($$$/$case$)$

Profit

$=($respectively$)$

$5\mathrm{.}00$

$6\mathrm{.}00$

$7\mathrm{.}50$

$8\mathrm{.}00$

Formulate the problem below $($enter the mathematical model$)$

$0\mathrm{.}5$ points

Formulate the Objective Function

Subject To:

Formulate the Constraints:

$0\mathrm{.}5$ points

Sugar Required

$0\mathrm{.}5$ points

Production Time

$0\mathrm{.}5$ points

Demand Apple

$0\mathrm{.}5$ points

Demand Banana

$0\mathrm{.}5$ points

Demand Grape

$0\mathrm{.}5$ points

Demand Mixed

$0\mathrm{.}5$ points

At Least $2$ cases of A for each G

$0\mathrm{.}5$ points

At Most $4$ cases of A for each G

$0\mathrm{.}5$ points

Non$-$Negative, Integer Constraints

$1$ point

Set up the problem on a different sheet

$1$ point

Solve the problem using SOLVER with the integer constraints. Make sure to generate the ANSWER Report

$0\mathrm{.}5$ points

How much is the total profit?

What is the optimal solution?

Apple $($A$)$

$($cases$/$week$)$Banana $($B$)$

$($cases$/$week$)$Grape $($G$)$

$($cases$/$week$)$Mixed $($M$)$

$($cases$/$week$)0\mathrm{.}5$ points$1$ point

The manager has additional funds to expand production$-$line capacity. However, he is unsure how much additional production time should be added to maximize profits.

Tasks:

$1.$ Using SOLVER, determine the minimum increase in available production time that would allow for a more profitable solution, given the new demand constraint.

$2$ Generate an Answer Report$\mathrm{.}1$ point

How much additional production time would you recommend?