Blending and production models fail in Excel Solver when capacity constraints are mis‑written, quality or proportion constraints are incorrect, SUMPRODUCT ranges reference the wrong cells, or decision variables are not properly defined. This page shows how to build correct blending and production models, how to fix common structural errors, and walks through multiple complete numerical examples.

1. Typical structure of blending and production models

A correct model usually has:

• Decision variables for each ingredient or product.
• Capacity constraints for each resource (material, machine, labor).
• Total production or demand constraints.
• Quality or proportion constraints (for blends).
• An objective cell using SUMPRODUCT with costs or profits.
• Nonnegativity (and sometimes integer) constraints.

2. Common errors in blending and production models

2.1 Wrong SUM or SUMPRODUCT ranges

Objective or constraints reference the wrong rows/columns (e.g., missing one ingredient or product).

2.2 Missing or incorrect capacity constraints

Without proper capacity limits, the model may be unbounded or unrealistic.

2.3 Incorrect quality or proportion constraints

Blending models often require weighted averages; forgetting to multiply out the denominator is a common source of infeasibility.

2.4 Decision variables contain formulas

Decision variable cells must be plain numbers (0 is fine). Formulas prevent Solver from adjusting them.

2.5 Missing nonnegativity or integer restrictions

Without these, Solver may use negative or fractional values where they make no sense.

3. Numerical example 1 – Single‑product blending model

A company blends three oils to produce 100 units of a final product. Each oil has a different cost and purity. The final blend must have an average purity of at least 85%.

3.1 Decision variables

x1 = amount of Oil 1
x2 = amount of Oil 2
x3 = amount of Oil 3
  

3.2 Data layout in Excel

        B       C       D
3     Oil 1   Oil 2   Oil 3
4     x1      x2      x3          (decision variables)
6   Cost      5       4       3   (per unit)
7   Purity    90      80      70  (percent)
8   Max Cap   50      40      30
  

3.3 Constraints

Total production:

\[ x_1 + x_2 + x_3 = 100 \]

Excel (in a total cell, say B10): =SUM(B4:D4)

Capacity constraints:

\[ x_1 \le 50,\quad x_2 \le 40,\quad x_3 \le 30 \]

Purity constraint:

Average purity ≥ 85%: \[ \frac{90x_1 + 80x_2 + 70x_3}{x_1 + x_2 + x_3} \ge 85 \]

Multiply both sides: \[ 90x_1 + 80x_2 + 70x_3 \ge 85(x_1 + x_2 + x_3) \]

Excel (in a purity LHS cell, say B11): =90*B4 + 80*C4 + 70*D4 Solver constraint: B11 >= 85*B10

Nonnegativity: \(x_1, x_2, x_3 \ge 0\)

3.4 Objective function

Minimize total cost: \[ \text{Cost} = 5x_1 + 4x_2 + 3x_3 \]

Excel (objective cell, say B12): =SUMPRODUCT(B4:D4, B6:D6)

3.5 Typical mistakes and fixes

• Mistake: Total production cell uses =SUM(B4:C4) (missing x3). Fix: Change to =SUM(B4:D4).
• Mistake: Purity constraint written as = (90*B4+80*C4+70*D4)/100 >= 85. Fix: Use the multiplied form with B10 as total.
• Mistake: Decision variables B4:D4 contain formulas. Fix: Replace with numeric starting values (e.g., 0).

3.6 Example solution

Suppose Solver returns:

• \(x_1 = 40\)
• \(x_2 = 30\)
• \(x_3 = 30\)

Total = 100, and \[ \frac{90(40) + 80(30) + 70(30)}{100} = 85 \] so the purity constraint is exactly satisfied.

4. Numerical example 2 – Multi‑product production planning

A factory produces two products using machine time and labor. Each product has a profit per unit and uses different amounts of each resource. There are weekly capacity limits on both resources.

4.1 Decision variables

x1 = units of Product 1
x2 = units of Product 2
  

4.2 Data layout in Excel

        B           C
3     Prod 1     Prod 2
4      x1         x2          (decision variables)

6   Profit/unit   40         30
7   Machine hrs   2          1
8   Labor hrs     1          1.5

10  Machine cap   100
11  Labor cap     90
  

4.3 Constraints

Machine time:

\[ 2x_1 + x_2 \le 100 \]

Excel (machine LHS cell, say B13): =2*B4 + 1*C4

Solver constraint: B13 <= B10

Labor time:

\[ x_1 + 1.5x_2 \le 90 \]

Excel (labor LHS cell, say B14): =1*B4 + 1.5*C4

Solver constraint: B14 <= B11

Nonnegativity: \(x_1, x_2 \ge 0\)

4.4 Objective function

Maximize profit: \[ Z = 40x_1 + 30x_2 \]

Excel (objective cell, say B15): =SUMPRODUCT(B4:C4, B6:C6)

4.5 Typical mistakes and fixes

• Mistake: Machine constraint cell typed as 100 instead of =2*B4+C4. Fix: Put the formula in the LHS cell and use 100 as RHS in Solver.
• Mistake: Labor coefficient for Product 2 entered as 15 instead of 1.5. Fix: Correct the parameter in the data row, not in the constraint cell.
• Mistake: Objective cell contains =B4+C4 (ignores profit per unit). Fix: Use =SUMPRODUCT(B4:C4, B6:C6).

4.6 Example solution

Solve the system when both constraints bind:

2x1 + x2   = 100
x1 + 1.5x2 = 90
  

From the first: \(x_2 = 100 – 2x_1\).

Substitute into the second:

x1 + 1.5(100 - 2x1) = 90
x1 + 150 - 3x1 = 90
-2x1 = -60  ⇒  x1 = 30
x2 = 100 - 2(30) = 40
  

Optimal solution:

• \(x_1 = 30\)
• \(x_2 = 40\)
• Profit: \[ Z = 40(30) + 30(40) = 1200 + 1200 = 2400 \]

5. Numerical example 3 – Multi‑period production with inventory

A firm plans production for two months. It can produce up to 80 units per month, demand is known, and inventory carries over with a holding cost.

5.1 Decision variables

P1 = units produced in Month 1
P2 = units produced in Month 2
I1 = ending inventory after Month 1
I2 = ending inventory after Month 2
  

5.2 Data layout in Excel

        B       C
3     Month1  Month2
4     P1      P2          (production)
5     I1      I2          (inventory)

7   Demand    60      70
8   Prod cap  80      80
9   Prod cost 10      11
10  Hold cost 2       2
  

5.3 Constraints

Inventory balance Month 1:

\[ 0 + P_1 – 60 = I_1 \]

Excel (I1 cell, B5): =P1 - 60 or =B4 - B7

Inventory balance Month 2:

\[ I_1 + P_2 – 70 = I_2 \]

Excel (I2 cell, C5): =B5 + C4 - C7

Capacity constraints:

\[ P_1 \le 80,\quad P_2 \le 80 \]

Nonnegativity: \(P_1, P_2, I_1, I_2 \ge 0\)

5.4 Objective function

Minimize total production + holding cost: \[ Z = 10P_1 + 11P_2 + 2I_1 + 2I_2 \]

Excel (objective cell, say B12): =10*B4 + 11*C4 + 2*B5 + 2*C5

5.5 Typical mistakes and fixes

• Mistake: Forgetting to include inventory in Month 2 balance. Fix: Use =B5 + C4 - C7 for I2.
• Mistake: Allowing negative inventory (no nonnegativity on I1, I2). Fix: Add constraints I1 ≥ 0, I2 ≥ 0.
• Mistake: Objective omits holding cost. Fix: Include I1 and I2 with their holding cost coefficients.

6. Checklist for fixing blending and production models in Solver

• Ensure decision variable cells are numbers, not formulas.
• Verify every constraint cell is a formula referencing the decision variables.
• Check all SUM and SUMPRODUCT ranges include every ingredient/product and no extra cells.
• Rewrite quality constraints by multiplying out denominators.
• Add nonnegativity (and integer/binary) constraints where appropriate.
• Use Simplex LP for linear blending and production models.