Excel solver

In this opportunity we will show you how to use Solver in Excel in a practical way and we will also see in which scenarios or cases we can apply the Solver.

Before going into the details and moving on to the practical example, let's first see what the Solver is and what it is for. AS:

What is Excel Solver and what is it for?

The Solver is a tool or Excel add-in that allows us to perform calculations to achieve a result. In other words, with Solver we can find optimized targets for linear and nonlinear models.

In particular the Solver helps us to do analysis such as determining the maximum or minimum value of a cellmaking variables or changes in other cells and allowing us to find the most optimal solution to a problem

And it is a tool that we can take great advantage of in environments or scenarios such as logistics or manufacturing.

How to use Solver in Excel

To enable us to have a better understanding of how to use solver in excelwe will do the following exercise and solve it by applying Excel Solver tool.

“A blacksmith with 80Kg of steel and 120Kg of aluminum wants to make touring bikes and mountain bikes to sell respectively for 20,000 and 15,000 pesos each to get the maximum benefit. For the race he will use 1kg of steel and 3kg of aluminium, and for the mountain 2kg of both materials. How many touring and mountain bikes should it produce to maximize profits?

So, keep in mind that a linear programming model is always raised under two assumptions. One is to establish the objective function and two to account for the constraints of the problem.

The first thing we need to do is organize the problem information in an orderly and formulated way.to prepare it when we run Excel Solver.

As we can see in the previous image, we have two variables X and Y, one is a mountain bike and the other is a road bike and we have the data or materials ordered and corresponding to each and we also have the sale price.

You may also be interested in: Creating charts in Excel with scrollbar

Why do we need to use Solver to answer this exercise?

Anyone could say that if we sell the mountain bike at a higher value, whatever material we have, we will use it to make mountain bikes. But what we are looking for is an objective function that maximizes profit.

Why? Because if for the mountain bike I need 1 kg of steel and 3 kg of aluminum and we spend the 80 kg of steel to produce mountain bikes, we can make 80 bikes. But it turns out that for each bicycle we will use 3 kg of aluminum and we have 120 kg of it, which would only give us enough to produce 40 bicycles.

In other words, we would be left with material if we decided to produce only steel or aluminum. Then, we would have a material that we could use in manufacturing road bikes.

The idea is to find a model that can satisfy the production of the two bicycle models, maximizing revenues and minimizing idle resources, ie we don't have half the aluminum or half the steel left over.

And this is where the Solver comes into action. The first thing is to set up the exercise in Excel and now we will formulate the problem somehow.

Approach to exercise and objective function

The Production section, are the variables that we do not know and that we want to find, to generate the production of the product. Now we need to know the total materials. And what is the total of materials equal to? Well, basically the multiplication of what steel is used versus what is produced, for this we use the +SUMPRODUCTS function

And below in yellow we have the restrictions, which are given to us by the problem, which says that the blacksmith has 80 kg of steel and 120 kg of aluminum, i.e. we cannot exceed that amount of material, we can use less but not more .

Finally, the most important thing is to consider the Objective Function (FO). And what is the objective function of this exercise? If we do it mathematically, the exercise function would be (20,000 X + 15,000 Y) i.e. the selling price for those who produce mountain bikes and for those who produce road bikes

We want to find the maximum value that this can generate for us, i.e. the X and Y variables that produce the greatest benefits, taking into account the constraints.

The objective function is the sales price matrix multiplied by the production matrix, which is our independent and unknown variable that we currently have.

You may also be interested in: How to Create a Speedometer Chart in Excel

Activate the Solver and use it to solve the exercise

We will solve the exercise with the Solver. The first thing we have to do is activate the solverremember that Solver is a complement and we need to activate it in Excel.

To activate the Solver we have to go to File - Options - Add-ins - Excel Add-ins - Solver. We mark the completion and activate. Now let's go to the data tab and click on Solver

Let's establish the objective, remembering that it is the Objective Function that we called FO in the exercise file; then we select that cell.

Maximize or minimize goals?

The objective function can be to maximize, minimize or arrive at a value X. Normally the objective functions of maximize can be applied to options such as utility income, which generate value and to minimize are applicable to operating costs.

For the exercise, let's leave Max selected and move on to the next option by editing the variable cells. Where the cells that will make us move the entire model and that will give us a value in the objective function are the unknowns, i.e. what we have to produce from X and Y, i.e. from mountain bikes and road bikes.

In summary, what we will put are the variables that we do not know and that will help us calculate the Solver to know what is the maximum income we can get from the year.

Now we will add the restrictions, where we have that in steel there are 80 kg and in aluminum 120 kg

If you want to know more details on how to add restrictions, enter the video where we explain in a practical way how to apply Solver Excel for this exercise.

Now we have the whole model ready, with our target, which is the Objective Function (FO) cell, the cells that need to be changed, which would basically be the variables we don't know.

Since we have already formulated our exercise, when the solver calculates the optimal production variables, the entire model will calculate FO based on the calculation of production and sales price.

You may also be interested in: How to Create Barcodes in Excel for Free

resolution method

After doing the whole process we go to the part that says solving method and we indicate in the solver which one we want to calculate the problem. There are three methods: nonlinear models, the simple method and the evolutionary one.

Normally for linear programming models that also work with integer variables, we will always solve it with the simple method.

Now click on solve and accept. Here we already have the solver resolution, which tells us that we need to produce 20 mountain bikes and 30 road bikes and that the maximum and optimal income is 850,000 based on the restriction we have.

Our conclusion and recommendations for using Excel Solver

This is how to use the Solver, there will be more or less long exercises, but it is the structure itself. We remember that the first thing we did was plan the exercise well, formulate the objective function, formulate the constraints and always take them into account in the realization of the model.

Although it is a very simple exercise, it will allow you to know the correct way to use the Solver and to be able to go deeper or practice much more, we hope it will be useful for you. Don't forget to leave your comments, doubts or concerns and See you in an upcoming installment in Excel for Everyone!