To produce fertilizers of types 1 and 2, chemical substances A, B, C, and D are used, with the requirements shown in Table 1.
Table 1. Requirements for chemical content in fertilizers
| Fertilizer type | Requirements for chemical content |
| 1 | No more than 50% of substance A No more than 60% of substance B |
| 2 | From 40% to 70% of substance B At least 20% of substance C No more than 80% of substance D |
The characteristics and reserves of the minerals used to produce the chemical substances A, B, C, and D are shown in Table.
Table 2. Characteristics and reserves of minerals
| Mineral | Maximum stock, t | Composition, % (A, B, C, D) | Price, u.c./t |
| 1 | 1200 | 30, 20, 15, 35 | 40 |
| 2 | 2500 | 20, 30, 10, 40 | 50 |
| 3 | 3100 | 15, 15, 40, 30 | 60 |
The selling price of 1 ton of fertilizer type 1 is 320 u.c., and of fertilizer type 2 is 350 u.c.
It is required to maximize the profit from the sale of fertilizers of both types.
Step 1. Mathematical Model
Let:
amounts of chemical substances A, B, C, D used to produce fertilizer type 1.
amounts of chemical substances A, B, C, D used to produce fertilizer type 2.
= amounts of the i-th mineral used.
The model is: maximize
subject to the following constraints:
- Composition of fertilizers (Table 1):
- Mineral characteristics (Table 2):
- Variable bounds:
Step 2. Preparing the Excel Worksheet
Data for solving the problem are placed on the worksheet.
Table. Calculation formulas used in Excel for the fertilizer problem
| Description | Cell | Formula |
| Objective function | D11 | =320*SUM(C5:C8)+350*SUM(D5:D8)-40*F5-50*F6-60*F7 |
| Constraint 1 | B14 | =C5-0.5*SUM(C5:C8) |
| Constraint 2 | B15 | =C6-0.6*SUM(C5:C8) |
| Constraint 3 | B16 | =D6-0.7*SUM(D5:D8) |
| Constraint 4 | B17 | =0.4*SUM(D5:D8)-D6 |
| Constraint 5 | B18 | =0.2*SUM(D5:D8)-D7 |
| Constraint 6 | B19 | =D8-0.8*SUM(D5:D8) |
| Constraint 7 | B20 | =SUM(C5:D5)-0.3*$F$5-0.2*$F$6-0.15*$F$7 |
| Constraint 8 | B21 | =SUM(C6:D6)-0.2*$F$5-0.3*$F$6-0.15*$F$7 |
| Constraint 9 | B22 | =SUM(C7:D7)-0.15*$F$5-0.1*$F$6-0.4*$F$7 |
| Constraint 10 | B23 | =SUM(C8:D8)-0.35*$F$5-0.4*$F$6-0.3*$F$7 |
Step 3. Entering Data in the Solver Parameters Window
Fill in the Solver Parameters window as shown:
Do not forget to adjust the required Solver Options.
Step 4. Solver Results
The solution to the fertilizer composition problem is shown:
Solver produces:
- Optimal quantities of fertilizers 1 and 2.
- Mineral usage within the stock limits.
- Reports (Answer, Sensitivity, Limits) that describe the structure and robustness of the solution.