Excel Solver for Optimal Decision-Making and Profit Maximization

Solver overview. Solver is a tool that's available right next to the data analysis tool pack. This is a feature that you'll have to add to your data tab the same way you added the data analysis tool pack.

In this spreadsheet you have the instructions you need to follow to add this as well as instructions for how to do this on the Mac. On the PC you'll go to file options. When you go to options you want to go to trust actually add-ins and then you look for manage excel add-ins down below.

When you click go you'll see a check box for solver add-in. If it's already checked you have it. If it's not checked that's what you'll want to check and then click okay.

Then when you go over to the data tab you should see it on the analyze in the analyze group. Understanding solver. When using solver three questions need to be answered.

What is the decision? What are the decisions to be made? What cells need to change? What are the constraints on those decisions? Are there any limitations on other values that will be affected when you make changes to the changing cells? And finally what is the overall measure of performance for these decisions? The target cells. Do you want to make the target cells reflect the maximum amount, the minimum amount or some other variation? You have three solving methods with solver. Simplex which solves linear optimization problems.

GRG non-linear which solves optimization problems where the target cells and some of the constraints are non-linear. And evolutionary involves optimization, solves optimization problems where the target cells and the constraints contain non-smooth functions. We'll take a look at some examples.

When you are choosing certain types of solving methods there may be additional information you want to review and we list that down at the bottom. For instance for evolutionary these are additional values that you can enter in when it comes to GRG non-linear. Here are some other additional values that you could take into consideration.

For our purposes most of what we're going to do is going to be pretty simple and I'll walk you through it. Let's actually do the first exercise. It's available on the next sheet.

So solver simplex LP. Now this is a very interesting situation. We want to determine the most efficient use of resources for linear problems and we have a linear problem that requires the simplex method.

This solves linear optimization problems in form AX and BY. The constraints are represented by a system of non-linear inequalities. When graphed a feasible region can be identified.

Within the feasible region each vertex is evaluated to determine maximum and minimum. So what is it in the real-world? What is it in the exercise we're working on here? This is something you should understand. Using linear programming to calculate the maximum profit in the scenario below.

That's what we want to do. We want to use linear programming to calculate the maximum profit in the scenario below. A company produces two types of desks.

Desk A and desk B. Now each desk must first be assembled and then painted. Desk A requires four hours to assemble and four hours to paint. Desk B requires eight hours to assemble and 12 hours to paint.

Now when it comes to the constraints the company can provide at most 160 labor hours for assembly and also at most 180 labor hours for painting. The profit on desk A is $40. The profit on desk B is $65.

The question that's being put to you is how many of each desk should the company produce to maximize profit? We do have numbers to start with. So X and Y, those are the variables. Four hours for assembly, four hours for painting, eight hours for assembly, 12 hours for painting.

We have the list of profits here. We have a graph that is generated from this table right here. We have the mathematical calculations that we'll need to take into account to try to figure this out without using Solver.

But our best option is to actually use Solver to figure this out. But let's do some manual adjusting of numbers to see what we can come up with. If you scroll down to where the cells are yellow, the only cells you need to touch are those in row 54.

This is where you'll enter how many of each desk should be made or ordered. And so desk A, you'll enter an amount here. Desk B, you'll enter an amount here.

That's all you have to enter. And let's see if we can come up with the highest maximum profit. I tell you to try 10 and 10.

So I'll go here and type 10 and then I'll go here and type 10. All right. It looks like a maximum profit of $1,050.

I was within the threshold of 160 for assembly and 180 for painting. I only have 120 and 160. All right.

Well, let's see if I can increase desk A by five more. Okay. Now I've maxed out my painting time, but I'm still under when it comes to the assembly.

I'm at 140 and my limit is 160. Now, I don't think I can do a number, I can enter a number that's any higher. I'll probably go over.

So let me try 18 and nine, 18. I'll go here and I'll type nine. Okay.

I'm back at 180, the max, but I went up to 144. That's a little closer to 160. My maximum profit at the moment is 1,305.

So $1,300 and $305. I may say, you know what, that's the best that I could do. I would like to see what Solver can come up with as a solution.

So I'm going to demonstrate how Solver can come up with a solution that you may not expect. And it's actually going to be the solution that maximizes profit higher than what I have here now. So on the Data tab, you want to head over to the right and click on Solver.

When you click on Solver, because this sheet has already completed this exercise, the values that I need to select are already selected. So I'll just review them with you. I want to set the objective.

The objective is F56. I want this to return a maximum profit. So I select that cell and here I choose max.

Now what's going to have to change in order for me to get the maximum profit? Well, that is C54 and D54. These two values here that I was fluctuating and playing around with. So let me make sure not to click in the wrong place.

So that's what Solver is going to work with. Now, are there any constraints? Yes. So F51 has to be equal to G51.

So that's the assembly. Whatever the assembly is, it has to be less than or equal to 160. Whatever the painting is has to be less than and equal to 180.

And those are all the criteria that I need to feed into Solver. Now I'll click Solve and I'll see how Solver does. It may take a while.

And Solver did come up with a solution. The results window looks like this. And so I'm going to keep my solution.

I'll click OK. Solver determined that to maximize profit, I need to only make desk A and ignore desk B. I will get to 160 for assembly and 160 for painting. But I will make $1,600 profit.

And as you can see, that's definitely more than what I came up with when I was entering the numbers by hand. So that's an example of Solver. It is able to take into account constraints and the ability to maximize profit by making logical choices.

We're going to take a look at another way you could use Solver in the next exercise. But for now, this is it for this section. Thank you for watching.