3: PRODUCT MIX PROBLEM Formulate and then solve a linear programming model of this problem, to determine how many containers of each product to produce tomorrow to maximize profits. The company makes four juice products using orange, grapefruit, and pineapple juice. Product Orange Juice Retail Price Per Quart $1.00 $0.90 $0.80 $1.10 Grapefruit juice Pineapple juice All-in-One The All-in-One juice has equal parts of orange, grapefruit, and pineapple juice. Each product is produced in a one-quart size (there are four quarts in a gallon). On hand are 400 gallons of orange juice, 300 gallons of grapefruit juice, and 200 gallons of pineapple juice. The cost per gallon is $2.00 for orange juice, $1.6 for grapefruit juice, and $1.40 for pineapple juice. In addition, the manager wants grapefruit juice containers to be no more than 30 percent of the number of containers produced. She wants the ratio of the number of containers of orange juice to the number of containers of pineapple juice to be at least 7 to 5. 1. Refer to the Product-Mix Problem (Juice Problem) that is discussed on iLearn. Both the mathematical formulation and the Excel Solver solution to that problem are posted on iLearn. There are also videos posted showing step by step on how to develop the mathematical formu- lation and then how to use Excel Solver efficiently to obtain the optimal solution. Complete those lab exercises before solving this modified version of the Juice Problem. Now assume that the products are produced in one-gallon sized containers instead of quart size containers. All other parameters of the problem stay the same except the retail price for each type of product, given in Table 1 below: Product Retail Price Per Gallon Orange Juice Grapefruit juice Pineapple juice All-in-One $3.00 $2.70 $2.40 $3.30 Table 1: Retail price for each juice product sold in gallon size containers. NOTE that the retail price of the new gallon-size products is three times the price of the old quart-size products. The cost per gallon of each type of juice however stays the same. Do you expect to make more profit or less profit? Explain your answer without re-solving the problem. This step allows you to do some sanity check on the optimal solution that you will obtain later on, using Excel Solver. (5 points) 2. What are the decision variables? Write your answers in complete sentences (meaning there should be a verb). Your answers should also include the units of the products. (5 points) 3. Now, also assume that the recipe for the all-in-one juice is also modified such that half of it is orange juice, one eights is grapefruit juice, and three eights is pineapple juice. What is the contribution margin of each new gallon-size container of juice, in other words, the unit profit from each product? Show your calculation. (5 points) 4. Write down the mathematical formulation for each of the resource constraints that is for the available orange juice, grapefruit juice, pineapple juice. All modifications made to the juice problem above nee(5 points) 5. Modify the Excel worksheet to address all the modifications explained thus far. HINT 1: The Excel Solver solution to the original problem is made available on iLearn so you can choose to modify that one. HINT 2: You will need to hit solve button every time