Question

Product Mix Problem

Your company grows two types of plants, A and B (e.g., roses and begonias).

Both plant types require two types of fertilizer throughout the growing season, Fertilizer 1 and Fertilizer 2.

A single plant A will require 2 pounds of fertilizer 1 and 1 pound of fertilizer 2.

A single plant B will require 1 pound of fertilizer 1 and 2 pounds of fertilizer 2.

Your distributer has 4000 pounds of fertilizer 1 and 5000 pounds of fertilizer 2 available for delivery at the beginning of the season.

You know that every plant A you grow will generate $2.25 profit, and every plant B you grow will generate $2.60 profit.

1. How many of each plant should you grow if you want to maximize profit for the season?

2. What amount of change in profit for plant A and plant B will change your decision about product mix?

3. How much will you be willing to pay for additional amounts of fertilizer?

4. If your fertilizer storage capacity is 8000 pounds, what is your new product mix, and how much of each fertilizer should you order? What would you be willing to pay for additional storage capacity?

Please explain how how to setup constraints and solve using solver

Answer 1

LP model is following:

Let A and B be the number of plants A and B to grow

Max 2.25A+2.6B (total profit)

s.t.

2A+1B <= 4000 (fertilizer 1)

1A+2B <= 5000   (fertilizer 2)

A, B >= 0

----------------

Setup Excel model as follows:

Enter Solver Parameters as follows:

Click Solve to generate the solution.

After that, values appear automatically in variable cells (highlighted in yellow color)

On the Solver Results window, select Sensitivity to generate Sensitivity Report

Click OK

Sensitivity report

---

a)

Optimal solution:

Number of plants A to grow = 1000

Number of plants B to grow = 2000

Total profit = $ 7,450

-----------------------------------------------------------------------------

b)

Refer Variable Cells section of the Sensitivity Report,

If the change in profit for plant A and B is more than the allowable increase or decrease as shown in the above table, then then the decision about product mix will change.

If profit for plant A is increased by more than $ 2.95, i.e. if it becomes greater than $ 5.2 (=2.25+2.95), then decision about product mix will change. Also, if it is decreased by more than $ 0.95, i.e. if it becomes lesser than $ 1.3 (=2.25-0.95), then the decision about product mix will change.

If profit for plant B is increased by more than $ 1.9, i.e. if it becomes greater than $ 4.5 (=2.6+1.9), then decision about product mix will change. Also, if it is decreased by more than $ 1.475, i.e. if it becomes lesser than $ 1.125 (=2.6-1.475), then the decision about product mix will change.

-----------------------------------------------------------------------------

c)

Refer Constraints section of the Sensitivity report,

Shadow price denote the quantum of increase in total profit, for additional unit of resource. Therefore, it is the maximum that should be paid for additional quantity of that fertilizer

Shadow price of Fertilizer 1 is 0.633, which means a maximum of $ 0.633 should be paid for each additional pound of fertilizer 1

Shadow price of Fertilizer 2 is 0.983, which means a maximum of $ 0.983 should be paid for each additional pound of fertilizer 2

-----------------------------------------------------------------------------

d)

Following additional constraint would be added to represent the restriction of total space used for storage of both fertilizers

(2+1)A+(1+2)B <= 8000

Resulting Excel model is as follows:

Note that a row is inserted under fertilizer 2 to add the new constraint and formula is dragged and copied from D4 to D5

Solver Parameters are updated to add this constraint as shown below

Click Solve to generate the solution

Optimal solution:

Number of plants A to grow = 333

Number of plants B to grow = 2333

Total profit = $ 6816.67

----

We see that total profit decreased by $ 633.33 (=7450-6816.67)

Therefore, yes, we would be willing to pay for additional storage capacity, as long as the cost is less than $ 0.633 per plant.

-----------------------------------------------------------------------------