Proportion of raw materials
Table 1
active pharmaceutical ingredient (API)
Methyl ethyl propyl butyl
A 1 1 1 1
B 5 4 6 5
C 2 1 1 2
A pharmaceutical factory produces three kinds of drugs, namely, A, B and C. There are four raw materials to choose from, and the costs are 5 yuan, 6 yuan, 7 yuan and 8 yuan per kilogram. Each kilogram of different raw materials can provide a variety of drugs, as shown in table 1. Pharmaceutical factory requires to produce exactly100g of drug A, at least 530g of drug B and no more than160g of drug C every day. It is required to select the quantity of various raw materials to meet the needs of production and minimize the total cost.
Solution:
(1) Establish a simple mathematical model. According to the meaning of the question, let X 1, X2, X3 and X4 represent the dosage of raw materials A, B, C and D respectively, and the following linear programming can be easily obtained:
Objective function: minimum z = 5x 1+6x2+7x3+8x4.
Constraint: X 1+X2+X3+X4= 100.
5X 1+4X2+5X3+6X4≥530
2X 1+X2+X3+2X4≤ 160
X 1≥0，X2≥0，X3≥0
(2) Enter the mathematical model of linear programming problem into Excel according to the style in Table 2. In Table 2, the formulas contained in the relevant cells are as follows:
Cell formula
C5 =D3*D5+E3*E5+F3*F5+G3*G5
C6 =D3*D6+E3*E6+F3*F6+G3*G6
C7 =D3*D7+E3*E7+F3*F7+G3*G7
C8 =D2*D3+E2*E3+F2*F3+G2*G3
(3) Select the Add-in option in the Tools menu and load the Solver at the installation prompt (pay attention to insert the installation disk). You can also copy the Solver folder under "Pfiles\Office\Library" in the installation disk and the Solver. xla and Solvr32.dll in the directory to the Office installation directory "Office\Library" and then load it.
(4) Select "Solver" in the tools menu, and then click the C8 cell in the pop-up "Solver Parameters" dialog box to make the absolute quotation of $C$8 appear in the "Target Cell", and select "Minimum" in the following small box according to the meaning of the topic. In Variable Cells, select D3:G3 from the table to display $D$3:$G$3 in the text box. Click Add in the constraint, then click C5 cell in the cell reference position in the Add Constraint dialog box to make it display as $C$5, select = in the box behind and edit the constraint value as $B$5. Similarly, the second, third and fourth constraints are edited as "$ c $6 ≥ $ b $6", "$ c $7 ≤ $ b $7" and "$ d $3: $ g $3 ≥ 0" respectively. Press OK to exit.
(5) Press "Solve" button, and in the pop-up "Planning Solution Results" dialog box, you can generate calculation results, sensitivity analysis and report with limited scope as required, and then press "OK" to solve the model.
(6) If the numerical solution is found to be a decimal, it can be expressed as an integer as required, and the method is as follows:
① Hold down the Ctrl key and select the cells D3, E3, F3, G3 and C8 that need to be expressed by integers.
② Choose format, cells, numbers and scientific counting.
③ Select "0" format in "Decimal". Press "OK" to exit.
(7) According to the above steps, the calculation results of the model shown in Table 3 can be obtained. As can be seen from Table 3, Party A is 30 >, Party C is 40kg, and Party D is 30 >: When kg and B are 0, the cost reaches the minimum, and the minimum cost is 670 yuan.
Table 2 A B C D E F G
1 MEBO
Two numbers
Quantity 5 6 7 8
3 orders
Price1111
4 About
restrain
deprive
Sharp mouth
appropriate
knot
fruit
5 a 100 1 1 1 1
6 b 530 5 4 5 6
7 c 160 2 1 1 2
8 Total cost
Table 3
english word
Methyl ethyl propyl butyl
Two numbers
Quantity 5 6 7 8
3 orders
Price1111
4 About
restrain
deprive
Sharp mouth
appropriate
knot
fruit
5 a 100 100 1 1 1 1
6 b 530 530 5 4 5 6
7 c 160 160 2 1 1 2
8 Total cost 670
Solving linear programming problems with Excel is simple and easy to master. Its rules and skills can be summarized as follows: in the actual solution process, we only need to determine the cell position of the objective function and the "variable cell" area, and then input the constraint conditions correctly to determine whether the objective is the maximum value or the minimum value, and we can get the correct result.
Many problems in operational research can be solved by programming methods provided by Excel, such as linear planning, assignment, transportation, machine allocation and personnel arrangement. As long as the production, manufacturing, investment, finance, engineering and other issues. Both are profit maximization and cost minimization, and the answer can be obtained quickly by programming.