Excel Solver Example problems with variables or constraints

Excel users have applied long and successfully this program to solve various types of tasks in different areas.

Excel is the most popular program in every office in the world. Its facilities allow you to quickly find effective solutions in a wide range of activities. The program is capable for solving various kinds of tasks: financial, economic, mathematical, logical, optimization and many others. For clarity we will see each of the described tasks with solving in Excel and consider it with an examples.



Solving tasks in data optimization problems

Optimization models are applied in the economic and technical sphere. Their goal is to choose a balanced solution and optimal in specific conditions (the amount of sales to receive a certain revenue, the best bill of fare, the quantity of flights, etc.).

The following commands are used to solve optimization problems in Excel:

1. The Script Manager (“DATA”- "What-if Analysis" - “Scenario Manager”) analyzes several variants of the original values, creates and evaluates sets of scenarios.

2. Selection of parameters (“DATA”- "What-if Analysis" - "Goal Seek") helps to find the values that will ensure the desired result.

3. Solution search (Microsoft Excel add-in, "DATA" - "Solver") calculates the optimal value taking into account variables and constraints. Go to the link and find out how to plug in the "Solver" setting.

The command "Parameter Selection" is using to solve the simplest tasks. You use "Scenario Manager" for the most complex tasks. Let’s consider an example of solving an optimization problem using the "Solver" add-on.

Condition. The company produces several brands of yogurt. Let’s call them "1", "2" and "3". The enterprise receives 200 rubles having sold 100 jars of yoghurt "1". Brand "2" brought 250 rubles. "3" - 300 rubles. The sales are well-established, but the amount of available raw materials is limited. You need to find what kind of yogurt and how much you need to do to get the maximum revenue from sales.

Known data (including the rate of consumption of raw materials) will be listed in the table:

We will compile a working table based on these data:

1. We still do not know the number of products. These are variables.
2. The following formulas are written in the column "Profit": C11:= 200*B11, C12:= 250*В12, C13:= 300*В13, C14: =SUM(C11:C13).
3. The consumption of raw materials is limited (these are restrictions). The following formulas are added to the cells:
   milk A18: kefir B18: egg C18: sugar D18: That is, we have multiplied the consumption rate by the quantity.
4. The goal is to find the maximum possible profit. This is C14 cell.

Activate the "Solver" command and enter the parameters.

The program issues its decision after clicking the "Solve" button.

The best option is to concentrate on the making yogurt "3" and "1". Kefir "2" is not worth producing.



Solving financial tasks in Excel

Most often financial functions are used for this purpose. Let's consider an example.

Condition. Let’s calculate how much to deposit, so that in four years 400,000 rubles will be formed. The interest rate is 20% per annum. Interest is accrued on a quarterly basis.

Let’s make a table with initial data:

Since the interest rate does not change during the whole period, we use the function of the =PV (Rate, Nper, Pmt, Fv, Type) to cell B7.

Filling in the arguments:

1. Rate – Interest rate is 20%/4 because Interest is accrued on a quarterly basis.
2. Nper – Total number of periods is 4*4 (total term of deposit*number of accruing periods per year).
3. Pmt – Recurring payments is 0. We do not write anything. The deposit will not be replenished.
4. Fv – Future Value is the amount of money we want to receive at the end of the deposit period.
5. Type is 0.

The investor needs to deposit this money so the result is a negative number.

We use the formula To verify the correctness of the solution: PV = Fv/(1+Rate)*Nper. We substitute the values: PV=400 000 / (1 + 0.05) 16 = 183245.

Solving the econometric tasks in Excel

Mathematical and statistical methods and models are used to establish quantitative and qualitative interrelations.

There are two ranges with values:

The values of X will play the role of a factor characteristic, Y is the resultant one. The task is to find the correlation coefficient.

The function CORREL (array 1, array 2) is provided to solve this task.

Solving the logic tasks in Excel

The table processor has built-in logic functions. Any of them must contain at least one comparison operator which will determine the relation between the elements (=,>, <,> =, <=). The result of the logical expression is a Boolean value of TRUE or a Boolean value FALSE.

Task for example. The students passed the test. Each of them received a grade. The test is handed over if there are more than 4 points. If less it’s not handed over.

1. Put the cursor in C1 cell. Press the function icon. Choose "IF".
2. Fill in the arguments. The logical expression is B1> = 4. This is the condition under which the logical value is TRUE.

3. If TRUE - "Passed the test". FALSE - "Did not pass the test".

Solving the mathematical challenges in Excel

Using the program arsenal, you can solve not only the simplest mathematical problems, but more complex (operations with functions, matrices, linear equations, etc.).

The condition for the training task. Find the inverse matrix B for the matrix A.

1. We make a table with the values of matrix A.

2. We highlight on the same sheet the area for the inverse matrix.
3. Go to "FORMULAS". Category - "Math and Trig". Type - "MINVERSE".

4. In the “Array” argument field enter the range of the matrix A.

5. Press Shift + Ctrl + Enter at the same time because this is an obligatorily for entering arrays.

Download of examples

The possibilities of Excel are not unlimited. But the program can handle with the most of tasks. Especially there is no description of capabilities that can be expanded with macros and user settings.