Hi! Please help with this problem, I know i can only submit one question, but i just have no idea where to start! Any help is appreciate :)

Companies often need to determine the quantity of each product to produce on a monthly basis. In its simplest form, the product mix problem involves the determination of the amount of each product that should be produced during a month to maximize profits.

Now, solve the following product mix problem using the Prodmix.xlsx starting file.

You work for a drug company that produces six products at its plant.

• Production of each product requires labor and raw materials. Row 3 in the starting file

  shows the hours of labor needed to produce a pound of each product, and row 4 shows the

  pounds of raw material needed to produce one pound of each product.

• For each drug, the price per pound is given in row 5, the unit cost per pound is given in

  row 6, and the profit contribution per pound is given in row 7.

• The months demand for each drug is given in row 8.

• This month, 4,500 hours of labor and 1,600 pounds of raw material are available

  How can this company optimize its product mix to maximize its overall monthly profit (i.e., across different products)?

  You have the following constraints:

  • Do not use more labor or raw material than is available for the month

  • Do not produce more of a drug than is in demand

  • You cant produce a negative amount of any drug

Product 5 190 2.5 0.7 Pounds made Labor Hrs Needed Per Pound of Product Raw Material Needed Per Pound of Product Unit price per pound of product Variable cost per pound of product Unit profit contribution Demand for product (in pounds) Product 1 Product 2 150 160 6 5 3.2 2.6 12.50 $ 11.00 6.50 $ 5.70 6.00 $ 5.30 $ 960 928 Product 3 170 4 1.5 9.00 $ 3.60 $ 5.40 $ 1041 Product 4 180 3 0.8 7.00 $ 2.80 4.20 $ 977 Product 6 200 1.5 0.3 3.00 1.20 1.80 1055 A A A A A A 6.00 $ 2.20 $ 3.80 $ 1084 Total Labor Total Raw Material Available 4500 1600 Product 5 190 2.5 0.7 Pounds made Labor Hrs Needed Per Pound of Product Raw Material Needed Per Pound of Product Unit price per pound of product Variable cost per pound of product Unit profit contribution Demand for product (in pounds) Product 1 Product 2 150 160 6 5 3.2 2.6 12.50 $ 11.00 6.50 $ 5.70 6.00 $ 5.30 $ 960 928 Product 3 170 4 1.5 9.00 $ 3.60 $ 5.40 $ 1041 Product 4 180 3 0.8 7.00 $ 2.80 4.20 $ 977 Product 6 200 1.5 0.3 3.00 1.20 1.80 1055 A A A A A A 6.00 $ 2.20 $ 3.80 $ 1084 Total Labor Total Raw Material Available 4500 1600