Data Analytics

HW (Optimization)

Purpose: To become familiar with the process of modelling a linear optimization problem, and solve the model using Excel Solver.

Note 1: Type your answers in this file and submit one MS Word file in Canvas. Copy/paste your results from Excel for each part of the question, and provide clear explanations of your results. You may also submit your Excel file for your record.

Note 2: Do not leave results from Excel without your explanations (Show your works).

A food company produces four different mixes of frozen ready-to-eat vegetables. The mixes consist of five different vegetables: carrots, mushrooms, green peppers, broccoli, and corn. The company manufactures four different mixes each sold in10 oz. bags. The mixes are Stir Fry, Barbecue, Hearty Mushrooms, and Veggie Crunch, and their contributions to earnings (per bag) are $0.22, $0.20, $0.18, and $0.18, respectively. The monthly supplies of carrots, mushrooms, green peppers, broccoli and corn are 150,000 oz., 80,000 oz., 135,000 oz., 140,000 oz., and 150,000 oz. per month, respectively. The compositions of the mixes are shown as follows.

  Stir Fry Barbecue Hearty Mushrooms Veggie Crunch
Carrots 2.5 2.0 0.0 2.5
Mushrooms 3.0 0.0 4.0 0.0
Green Peppers 2.5 2.0 3.0 2.5
Broccoli 2.0 3.0 3.0 2.5
Corn 0.0 3.0 0.0 2.5

For example, on bag of Stir Fry mix contains 2.5 oz. of carrots, 3.0 oz. of mushrooms, 2.5 oz. of green peppers 2.0 oz. of broccoli, and no corn. The company can sell all of the mixes that they produce.

Part 1: Construct a linear optimization model to determine the optimal product mix (i.e., how many bags of each mix to produce in order to maximize the contribution to earnings).

[(10 points) Decision Variables; (10 points) Objective Function; and (20 points) Constraints]

 

Part 2: Solve the liner optimization model using excel solver. What is the optimal product mix?

[(20 points) Structure the problem in Excel Solver; (20 points) solve the structured problem in Excel; and (10 points) provide the optimal answer]

 

Part 3: What will be the optimal product mix if the monthly supply for carrots reduces to 10,000?