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

HW
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. Please 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 .
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 .
Part 2: Solve the liner optimization model using excel solver. What is the optimal product mix?
Part 3: What will be the optimal product mix if the monthly supply for carrots reduces to 10,000?

Last Completed Projects