Home // Video Tutorials // 6 - 10 (Advanced) // 7 // Solver Excel 2013

Solver Excel 2013

How to use the Solver Excel 2013 tool to optimise the best solution for a given spreadsheet model.

Solver Excel 2013

The solver add-in allows you to solve for a situation where you have some constraints. So whereas the goal-seek function in excel only allows a set input and output, solver allows multiple inputs and some constraints.so in this example I am selling coffee – I decide I want to sell regular, premium – two different premium types – but I have some constraints. So I have a constraint of the total number of cups I can do in a day and then the max of the premium and the mocha cups. Here’s my total number of cups and total revenue. We want to know what is the best combination, to get the highest total revenue. So solver, once you have loaded it via the add-ins, will be in the DATA tab – over here it is called SOLVER. And when I click on it it brings up this screen. So the first thing it wants to know, is what is the objective. So what I am going to say is Please go – that’s the cell I’m worried about and the moment I can choose the maximum, or the minimum or the value of a set number and I’m going to say just give me the maximum you can get. The next thing it wants to know is what cells is it allowed to change and in this case I’m working with a number of cups, so I’m going to click here and by holding the control key down, I can click on these different cells and you’ll see it is listed here.

We are now going to put the constraints in, so to do that, you’ll see this is where it goes and I’m going to say add one.
So what we need to do is to explain to Excel what the constraints are – so the first one is the total number of cups must
be less than 500, so I am going to tell it – look at this cell here – which is the sum of all the cups sold – I’m going to say it must be – you can see you have all the various options. I’m going to say it must be less than or equal to – and I can either put a number in, or in this case I’m going to link to that cell there. I’ve got two more constraints – I’m going to say add – the next one is the premium cups can only be 350, that’s over here – so I’m going to say that this cell must be again less than or equal to that cell there and add again this cell here must be less than or equal to that one there. When we are done, we can say OK. We are almost done.

If you have enough knowledge about this subject you can also choose the way the solving is going to be done – generally speaking we leave the grg non linear and now it is simply a case of clicking solve and letting Excel do the work. What you can see is Excel says it has found a solution. Looking at the solution, it seems that the best possible combination is we do the maximum number of premium cups at 350, we do the maximum number of mocha cups and the left over is the regular coffee. And this gives us the amount of $1000. Looking at this perhaps this solution is not ideal, because the chances are we are going to sell a lot more of these and probably a lot less of these. So what we can do then, is just run another analysis. And I’m going to put another constraint in and in this case I’m going to say that we need to sell at least as many regular coffees as perhaps these premium coffees. So I’m going to add a constraint and I’m going to say that this cell must be equal to or in this case actually I’m going to say bigger than or equal to that cell. Say OK, we now have this constraint and I’m going to ask for it to be solved. Again it seems to have found a solution, so let’s look at the solution. You’ll see it is a lot less – 890 – but now this seems a lot more reasonable, in my mind. In this way once you have built a model, you can use the solver to find what the best possible answers could be. It doesn’t mean they are realistic, you may find you keep adding various constraints to make them realistic but it is a very good way of assessing what the correct decision is in any financial model.