Solver in Excel, Let Excel help you find the
solution
The Solver is the Excel tool that helps you find numerical solutions to
various problems you might have.
It is not loaded per default in Excel so you have to load it first. It is
a so called Add-in.
To load it, you must open the OPTION. These are in the FILE
menu under Option (you can also open it par pressing ALT F followed by T)
The following window will open for Excel but the solver
could be called Solver Add-in or complement Solver depending on which
version of Excel you are in.
Select the Add-in option and then press on the solver add-in
and then GO...
The following window will open.
Select the solver then OK.
This will create a new icon in the DATA Banner. It is
located at the far right of the banner.
Apple farm: it is sales time
We have harvested 13542 apples in 2014. Now we want to sell
them on various markets.
We have for this purpose prepared some nice baskets with
ribbon and flowers. These will give more value to our apples as they will
become Presents.
But of course making the baskets and adding the flowers and
ribbons cost money. So we calculated these costs and the profit we get for
each basket sold.
The profit is different for each basket and we only have a
limited budget of ribbon, baskets and flowers. We want to know what the
optimum quantity of each basket will be in order to make have the maximum
profit (because we worked very hard).
Look at the following table.
We have four (4) different sets or packs. You can see the
profit for each Set and underneath, you can see the costs of the basket,
flowers and ribbon as well as the number of apples in each basket.
The profit is using the SUMPRODUCTfunction. You can see on
top of the picture. It is the profit x the quantity for each Pack.
Similar is the cost of each items. The blue AREA, is also
=SUMPRODUCT(B11:E11,$B$7:$E$7)
Attention, dear readers, I am not a farmer just an excel
geek so I do not really know if 5 apples in a basket is something our real
farmers do...this is an example. But you can use the same scenario for a
hamburger shop where you have limited quantity of various ingredients, or a
manufacturing company with limited stock or space or head counts, etc....
So what is next:
Next you press on the SOLVER button which opens the
following window (just without the formulas)
First thing to do is set the objective:
What are you looking to maximize, minimize or find. In our
case it is the profit we want to maximize.
So we press on the right side of the SET OBJECTIVES window
and select the profit cell (orange cell).
Next we need to tell Excel which cells he can play with (or
lets say he should adjust) to change this profit.
These are the quantity cells (pink in the table).
And finally, we have to tell Excel what are the boundaries
or limits or constraints he has to play with.
If he had NO LIMITS, NO BOUNDARIES, NO CONSTRAINTS, then
Excel would just take the simplest answer which is the maximum profit Set In
our case it would be the PACK D.
But if you set limits in the price of baskets for example,
then obliges/forces Excel to use the less expensive sets too.
So here we are going to press the ADD button in order to
tell EXCEL that it should not go over the budget of basket, flowers and
ribbons
and most important it should use all the apples.
Now that you have set the boundaries, press solve and the
result appears after some short calculation by Excel.
Here we are, we will make a profit of $5'551 and use all the
baskets and ribbon money but we will use only $2'591 out of the $4'000
budget we had for flowers.
So that's it for the SOLVER
in Excel.
These were only some basics. Now you will learn by doing.