Solver Analysis in Excel

In this article, we will solve the typical supply chain problem with the help of excel. Consider yourself in place of a supplier whose main motive is to deliver the goods on time, fulfill the quantity requested and keep the costs MINIMUM.

Now, there can be various perspectives or angles on how can one minimize the costs. In this article, we would discuss the Logistics part of that problem.

Let us understand with the help of an example:

Consider you have 2 warehouses from where you send the goods to 3 customers. Based on the past data, you know the cost per unit to send products to those customers.

Now each of the warehouse has its total capacity it can fulfill whereas each customer has the specific demand. The main question here is how much to send to each customer from each warehouse, in order to keep the costs MINIMUM.

You can solve it using hit & trial method but your boss won't wait that long! So, let's try to solve it using Excel - Solver Add-in under Data tab.

If you are not able to locate the add-in, don't worry. Go to File -> Options -> Add-ins -> in the Manage tab (at the bottom), select Excel Add-ins - > Go and there you will find that.

Coming back to our million dollar question!

Open Solver and there you will find some fields to fill up. Let's check it one by one.

Set Objective: In our case, it is the Total Cost, which we want to keep minimum. Hence, I am selecting cell B18. Now, you can either select Max or Min or Value of (fixed value).

We can also make scenarios- to see what would be the minimum cost or the maximum cost.

Subject to the constraints: Here, we need to setup the constraints which we cannot avoid. e.g. Each WH has its capacity and it can not deliver over the capacity. So that is the constraint. Similarly, each customer has the specific demand. So, the supplier can not under-deliver not overdeliver, hence that is another constraint. Plus, each quantity should be an integer value, so that is one more constraint.

In the above picture, you can find these constraints listed.

Select a Solving Method: There are 3 types of solving method given in this analysis tool

a. GRG Non-Linear : It is used for non-linear set of equations

b. Simplex LP : It is used when you have a linear equation to solve

c. Evolutionary: This is also used for non-linear set of equations, but it is more complex than GRG Non-Linear method and tries to give more accurate results.

In our case, we can use Simplex LP method as we have simple linear equations. Click on Solve, and you got your solution.

So, in out case, we have to spend minimum Rs. 731000 in order to fulfill the demand of the customers.

Note: Sometimes, the solver couldn't find a solution even after multiple iterations. In that case, try changing the solving method and you will get an optimum solution.

Now, this was a simple example but the intention of writing this article is to understand the approach. We can make this example complex by increasing the customers and warehouses and adding other fixed and variable costs. But the aim would always be to MINIMIZE THE COST.

Thanks! Happy Learning!

P.S. Like, Comment & Share, if you find this article useful.