Solve linear programming problem using Excel - Analytics Tuts - Free Printable

Problem Description

The problem involves a farmer who wants to customize a fertilizer mix using two types of food: Food A and Food B. The goal is to meet the required amounts of phosphoric acid, nitrogen, and potash at minimal cost. Here are the details:

- Food A:
- Contains 20 pounds of phosphoric acid, 30 pounds of nitrogen, and 5 pounds of potash per cubic yard.
- Costs $30 per cubic yard.

- Food B:
- Contains 10 pounds of phosphoric acid, 30 pounds of nitrogen, and 10 pounds of potash per cubic yard.
- Costs $35 per cubic yard.

- Requirements:
- At least 460 pounds of phosphoric acid.
- At least 960 pounds of nitrogen.
- At least 220 pounds of potash.

The task is to determine how many cubic yards of each food (Food A and Food B) should be used to meet these requirements at the minimal cost.

---

Solution Approach

This is a linear programming problem where we need to minimize the total cost while satisfying the given constraints. We will use Excel's Solver tool to solve it.

#### Step 1: Define Variables
Let:
- \( x \): Number of cubic yards of Food A.
- \( y \): Number of cubic yards of Food B.

#### Step 2: Objective Function
The total cost to minimize is:
\[
\text{Total Cost} = 30x + 35y
\]

#### Step 3: Constraints
The constraints based on the requirements are:
1. Phosphoric acid constraint:
\[
20x + 10y \geq 460
\]
2. Nitrogen constraint:
\[
30x + 30y \geq 960
\]
3. Potash constraint:
\[
5x + 10y \geq 220
\]
4. Non-negativity constraints:
\[
x \geq 0, \quad y \geq 0
\]

#### Step 4: Set Up the Problem in Excel
From the image, the setup in Excel is as follows:
- Cell `D4` contains the value of \( x \).
- Cell `E4` contains the value of \( y \).
- Cell `F4` calculates the total cost using the formula:
\[
=D4*30 + E4*35
\]
- Cells `D6`, `D7`, and `D8` represent the contributions of Food A to phosphoric acid, nitrogen, and potash, respectively.
- Cells `E6`, `E7`, and `E8` represent the contributions of Food B to phosphoric acid, nitrogen, and potash, respectively.
- Cells `G6`, `G7`, and `G8` calculate the total contributions:
\[
G6 = D6*D4 + E6*E4 \quad (\text{Phosphoric acid})
\]
\[
G7 = D7*D4 + E7*E4 \quad (\text{Nitrogen})
\]
\[
G8 = D8*D4 + E8*E4 \quad (\text{Potash})
\]
- Cells `H6`, `H7`, and `H8` contain the minimum requirements:
\[
H6 = 460, \quad H7 = 960, \quad H8 = 220
\]

#### Step 5: Use Excel Solver
The Solver parameters are set as follows:
- Set Objective: Minimize the total cost (`$F$4`).
- By Changing Variable Cells: `x` and `y` (`$D$4:$E$4`).
- Subject to the Constraints:
- \( 20x + 10y \geq 460 \)
- \( 30x + 30y \geq 960 \)
- \( 5x + 10y \geq 220 \)
- \( x \geq 0 \)
- \( y \geq 0 \)

#### Step 6: Solve Using Solver
After setting up the problem in Excel and configuring Solver, run the Solver to find the optimal values of \( x \) and \( y \).

---

Solution Output

From the image, the Solver has already been run, and the results are:
- \( x = 30 \) (cubic yards of Food A)
- \( y = 35 \) (cubic yards of Food B)
- Total cost = \( 30 \times 30 + 35 \times 35 = 900 + 1225 = 2125 \)

#### Verification of Constraints
1. Phosphoric acid:
\[
20(30) + 10(35) = 600 + 350 = 950 \geq 460 \quad \text{(Satisfied)}
\]
2. Nitrogen:
\[
30(30) + 30(35) = 900 + 1050 = 1950 \geq 960 \quad \text{(Satisfied)}
\]
3. Potash:
\[
5(30) + 10(35) = 150 + 350 = 500 \geq 220 \quad \text{(Satisfied)}
\]

All constraints are satisfied, and the solution is optimal.

---

Final Answer

\[
\boxed{30 \text{ cubic yards of Food A and } 35 \text{ cubic yards of Food B}}
\]