Let us consider an example related to the production of materials. Suppose a company produces two types of construction materials: A and B. Both products are sold on the market.
To manufacture these materials, two raw products are used: I and II. The maximum daily available stock of these raw products is 7 and 9 tons respectively. The consumption of raw products I and II per 1 ton of each type of material is shown in Table.
Table. Consumption of raw products
| Raw product | Consumption per 1 ton of materials, t | Maximum available stock, t |
| Material A | Material B | |
| I | 3 | 2 |
| II | 2 | 3 |
Market research showed that the daily demand for material B never exceeds the demand for material A by more than 1 ton. In addition, the demand for material A never exceeds 3 tons per day. The wholesale prices per ton are: 4000 u.c. for material B and 3000 u.c. for material A.
Question: What quantity of each material should the factory produce to maximize revenue?
Step 1. Formulating the Mathematical Model
- Decision variables:
- x₁ – daily production of material A (tons)
- x₂ – daily production of material B (tons)
- Objective function (optimization criterion):
The total daily profit from producing x₁ tons of A and x₂ tons of B is:
F=4000x2+3000x1
The goal is to maximize this function:
Maximize F=4000x2+3000×1
- Constraints:
- Non-negativity:
x1≥0,x2≥0
- Raw material usage:
3x1+2x2≤7
- Market demand:
x2−x1≤1,x1≤3
Thus, the full mathematical model is:
Maximize F=4000x2+3000×1
subject to:
2x2+3x1≤1
3x2+2x1≤1
Step 2. Preparing the Excel Worksheet
On the worksheet, enter the required text, data, and formulas as shown in Fig. 9.6.
- The variables x₁ and x₂ are located in cells C3 and C4 respectively.
- The objective function is in cell C6, with the formula:
=4000*C4+3000*C3
- Constraints for the problem are entered in cells C9:D12.
Step 3. Working with the Solver Add-in
Using the Solver command on the Data tab (in the Analysis group), enter the required data for this problem.
- To add constraints, in the Solver Parameters window, under Subject to the Constraints, click Add.
- In the Add Constraint window , enter the first group of constraints:
- In the Cell Reference field, input the left-hand side (C3:C4).
- In the Constraint field, input the right-hand side (0).
- From the dropdown list, select the relation “>=”.
- This defines the non-negativity requirement for the variables.
Click Add again to enter the second group of constraints: C9:C12 <= D9:D12.
Press OK to complete entry. The Solver Parameters window now displays all the constraints.
- Click Solve to run Solver.
- The Solver Results window will display the solution and provide the option to generate a report.
Step 4. Reports
- Answer Report:
- Displays the target cell (objective function), the decision variables (x₁, x₂), and the constraints.
- In the Formula field – the dependencies used in Solver.
- In the Allowable field – the quantities of raw materials used. If the material is fully consumed, the status is shown as binding; otherwise, not binding.
- For boundary conditions, the difference between the optimal solution and the set boundary is shown.
- Sensitivity Report:
- Displays variable values in the solution and the ranges of changes to constraints that preserve the optimal solution.
- Limits Report:
- Shows the ranges within which the quantities of materials in the optimal solution can vary while keeping the solution structure unchanged.
- Displays lower and upper bounds of the variables and the corresponding objective function values.
