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 or mathematical function you might want to solve.

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.

solver option window excel

Select the Add-in option and then press on the solver add-in and then GO...

The following window will open.

 

addins

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.

solver icon data 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.

solver problem example

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)

solver window excel

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).

profit solver

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

solver constraints

and most important it should use all the apples.

solver

Now that you have set the boundaries, press solve and the result appears after some short calculation by Excel.

Solver

 

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.