In today's video I'm
going to introduce you to Excel's Solver tool and
show you simple ways on how you can benefit from it. (upbeat music) I'm going to show you how
you can use Excel's Solver to solve for more complex problems that Goal Seek can't solve for. Why? Because Goal Seek is just
restricted to changing one single input variable. So this is the example that we looked at in the previous video. What we had were the units
sold, price per unit, we calculated the revenue,
then we had the cost per unit, the cost and then we
calculated the income, that's just revenue minus costs. We used Goal Seek to find out
how many units we need to sell to get a specific income. So if I go to Goal
Seek, that's under Data, What If Analysis. It's really, really simple to use. It allows you to set your calculated cell to a specific value without changing your formula by changing an input cell. So you can see here that
we're just restricted to selecting one single cell but what if you didn't want that? What if you wanted to change
two things or three things and what if you wanted to
add constraints to this? You wanted to say well, units sold, they can't be decimal places,
they have to be full numbers; price per unit can't go above four bucks and cost per unit must
be below this number. If you want to add any constraints to this and if you have more than one variable, that's when you need to use Solver. Solver is by default not activated when you install Excel. It's there, it's just not activated. Now you can see, that's my Solver here, I've activated it. If you don't see yours
here on the Data tab, it means that you need
to go and activate it. To do that, all you have
to do is go to File, Options, Add-Ins, and down here you go to
Manage Excel Add-Ins, just press Go and this is where
you need to put a tick mark. Right, so yours is not
going to have a tick mark, put a tick mark and press OK
and you get to see it here. Okay, so now let's take
a look at how this works. Solver is a very powerful tool. It can do complex statistical modeling, so calling it an advanced Goal Seek is not doing it much justice but the thing is that a
lot of people avoid Solver because they think it's very complex and it's meant for complex
problems, which it is, but it can also very quickly
solve your simpler problems. For our example here let's
say we want to set objectives, so that's our income, we want to set this to a value of 2000. Okay, now you can see you
have different options here, you could say "give me the maximum number "that this value could be,
that the income could be, "given these constraints
that you can put in here" or "give me the minimum
cost that I could have" given the constraints that you put here. But for our example, I'm going
to go with a value of 2000. Which cells do we want to change? Well I want to change these two because all you have to
do is highlight them. Now I also want to change
this one, so I'm going to put the Excel separator
and then click on this. So now Solver knows it
has to change these ones to get to this. If I don't give it any
constraints right now, let's see what Solver gives us. I'm going to leave everything
as is and press Solve. Okay, it says Solver found a solution and the solution, you can see right here. So it put this to 706, 4.26 and 1.43. Okay I say, well, no I want
to give it some constraints because I cannot sell at
a price that's above four. I'm going to do cancel
and go back to Solver. Okay, so everything else is still there. Now I'm going to add a constraint. My first constraint is units sold, I want them to be an integer, right, I don't want them to have any decimals. Okay, so that's one
thing I'm going to add. The next constraint that I want to add is that price per unit,
that must be less than or equal to four. Here I can either use
a cell reference for it or I can type the number in manually. In this case, I'm going
to add it in manually. I could add another
constraint for cost as well. I could also expand on
this same constraint and say it has to be less
than or equal to four but greater than and equal to three. I can add that by just
going here, saying add and putting this again, in here, and selecting the greater than
and equal to and three here. Okay, so we go back and let's take a look at our constraints. So the first one is an
integer, this one has to be between three and four. Let's solve for this and that's our result
here and we get to 2000. That's a very quick way of using Solver. You can add in more constraints and you can change your solving method but for these type of simpler problems, you really don't need to worry about this. This is basically the different methods that Solver can use to solve your problem. The first one is
Generalized Reduced Gradient and the last one, Evolutionary, they're both for non-linear cases. Simplex LP is for linear relationships, so if you're sure that
you have a linear one, you can use Simplex. If you don't, it's much
faster if you use the GRG. GRG tries to find a local optimal solution and Evolutionary, a
global optimal solution, so it takes a lot longer
to run than the first one and if you do use
Evolutionary, you do need to define the upper and lower
bounds of your variables here but in most cases you're just going to be fine with GRG non-linear. Once you press solve,
if you like the answer that Solver gives you, you can press OK and it takes it over. Obviously, it doesn't
touch any of your formulas, nothing has changed, the
only thing that's changed are your input values. You can see that's a full number and this is according to the
constraints that I defined. That's a very simple
example of using Solver but just to give you
another idea of Solver, let's take a quick look at this exercise. The aim of the exercise
is that we have a budget that's given to us for these projects and we want to allocate
it between all of these given special constraints
that are defined here. So let's say I originally
did the planning of this, I put in the production costs
associated with each project, the fixed costs, the extra
costs and it came to this number and I'm told "okay, you have
this budget, use this budget," but these are my constraints. The extra cost shouldn't exceed 1000, my fixed costs need to remain at 2000 and the total costs for each
project shouldn't exceed 9600. These are my constraints and given that, I need to change these numbers
so my outcome is this number. Let's quickly use Solver
to get the answer. The total cost here, that's this cell, it should be 65,000. Which cells can we change
to get to this result? So let's take a look at our constraints. Extra costs should not exceed, that's something that I can change. So I'm going to add that here. The other thing I want to
change is fixed costs need to remain at this but
they're already all 2000, so I don't need to add it here and total project costs
shouldn't exceed 9600. So total project costs are
these but these are calculated. I need to be able to change the input and the main input for them
is the production costs here. That's something I can change but the constraint is not on this, the constraint is going to
be on the total cost here. So let's add the constraints. First constraint is that
extra costs, they need to be less than or equal to 1000,
so now I'm going to do a cell reference here. Next one is that the total costs here, they need to be less than
or equal to this number. That's it, let's solve for this problem. Solver found a solution and
let's just go take a look at it. 65,000, this is nowhere close to 1000, that's fine and the total cost does not exceed 9600, so I'm happy with this, I'm going to accept the new input values. So this is how you can use Solver to solve for complex what if analysis that Goal Seek can't solve for. (upbeat music) Thank you for watching. If you liked this video and
you learnt something new, don't forget to give it a thumbs up and for more Excel videos like this one, why not subscribe to this channel so that you can get updates
when new videos come out.
