Models and technologies for solving optimization problems...

Models and technologies for solving optimization problems

The result of the activity of any economic system (firm, enterprise, industry) is determined by the effectiveness of the use of resources. This entails the need to find the best option for using resources to find the maximum values, for example, for profits, or the minimum values ​​for losses. Such problems are called optimization problems. Optimization tasks are widely used in the economy, for example, the transport task, the optimization of the product range, the optimization of advertising costs, the determination of the optimal number of newspapers or magazines that will be published, which will ensure maximum revenue from sales, etc.

By solving optimization problems, we mean the process of selecting the values ​​of the variables x, that provide the optimal value of some function f ( x ) within a certain range of its existence. The optimum value of a function is its extremum, i.e. its maximum or minimum value.

In MS Excel 2010, optimization tasks are resolved using the add-in Solution search.

Example 9.27. The trading enterprise produces three types of products from raw materials of three types. Production figures are shown in Fig. 9.51.

Production figures for Example 9.27

Fig. 9.51. Production metrics for Example 9.27

It is required to determine the volume of output of each product with given raw material reserves.


As the elements of the matrix are the numerical values ​​- Consumption of raw materials by product, weight. units/ed. ", and as a vector of free terms -" Stock of raw materials, weight. units .

The solution of this economic planning problem is to solve the systems of linear equations with the tool "Solution search" (Figure 9.52).

Solution of economic planning problems under given conditions

Fig. 9.52. Solving economic planning problems under given conditions

Example 9.28. The firm produces two models A and In bookshelves. Their production is limited by the availability of raw materials and the time of machine processing. For each product of the A model, 3 m2 of boards are required, and for the B-A model product, m2. The firm receives from its suppliers up to 1700 m2 of boards per week. For each product of the A model, it takes 12 minutes of machine time, and for the B product, it takes 30 minutes. You can use 160 hours of machine time per week. How many items of each model should a firm be released per week in order to maximize profits if each product of the A model brings in $ 2 of profit, and each In - $ 4, profit?


The number of shelves of the model A will be denoted by X1, and the models In - X2. Next, in the figure (Figure 9.53), a computer model is described taking into account the conditions of the problem. The objective function in cell 13: = A3 * 2 + B3 * 4 is specified and constraints in cells E3, H3, A3, B3 are prescribed in accordance with the formulas given in cells E4, H4, C6, D6.

Refer to the Find solution tool. on the Data tab (Figure 9.54) and fill in the appropriate parameters.

We get the result. The maximum profit will be obtained if the shelves of the model A are released in quantity of 300 pieces, and the shelves of the model B - in the amount of 200 pcs. (Figure 9.55).

Solution Model of Example 9.28

Fig. 9.53. The solution model of Example 9.28

Solution search options

Fig. 9.54. Solution search options

The result of the solution of Example 9.28

Fig. 9.55. The result of the solution of Example 9.28

Example 9.29. The firm needs to select business partners (from among five firms) to enter into contracts for the supply of goods for up to 2 million rubles, by determining the amount of the deal with each partner and ensuring the maximum profit taking into account that the expected amount of risks from transactions ns will exceed the amount of expected profit. Known parameters that characterize the values ​​of profit, risk and the maximum amount of the transaction with each of the partners (taking into account the further introduced symbols) are shown in Fig. 9.56.

Source data for Example 9.29

Fig. 9.56. Baseline data for Example 9.29


We introduce the following conventions:

• the total amount of the transaction, RUB. - P;

• The total number of i -x business partners - n;

• The maximum possible transaction amount with i -th partner, rub. - k i ;

• The amount of the transaction with i -th partner, rub. - x i;

• profit from the deal with i -th partner, % - ci •;

• The risk of a deal with a i partner,% - h i .

Then the objective function can be written as follows:

i.e. as a search for the maximum value of the sum of the products of transactions per interest earned for each i partner, provided that the following restrictions exist:

Let's create in the tabular form the model of the solved problem according to Fig. 9.57, in which cells B4: F4 and B5: F5 are entered data from the table in Fig. 9.56. The values ​​of the cells B3: F3 can, in principle, be filled arbitrarily. Calculated column formulas Result: H3 = CUMM (B3: G3); H4 = B3 * B4 + C3 * C4 + + D3 * D4 + E3 * E4 + F3 * F4; H5 = B3 * B5 + C3 * C5 + D3 * D5 + + E3 * E5 + F3 * F5.

Computer model for Example 9.29

Fig. 9.57. The computer model for example 9.29

Set the cursor in cell G4 and turn to the Find solution tool. (the Data tab) (Figure 9.58). Set the optimization function to the maximum for the objective function.

Solution search options for the partner search task

Fig. 9.58. Search options for the solution for the Partner Search task

Set the variable cells of variables B3: F3, then we introduce restrictions for this task:

• the maximum possible amount of the transaction $ B $ 3 <= 250 000; $ C $ 3 & lt; = 000 000; $ D $ 3 & lt; = 140,000; $ E $ 3 & lt; = 500,000; $ F $ 3 & lt; = 1 200 000;

• the possible total profit should exceed the possible total loss: $ G $ 5 <= $ G $ 4;

• the total possible transaction is 2,000,000 rubles: $ G $ 3 = 2,000,000;

• the values ​​of possible trades must be positive: B3: F3 & gt; = 0.

After clicking on the Find Solution button and after the successful completion of the search (Figure 9.59), we get the result (Figure 9.60).

Solution Solution Search Window

Fig. 9.59. The Solution Solution Search Window window

The result of the solution of Example 9.29

Fig. 9.50. The result of the solution of Example 9.29

thematic pictures

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)