From the course: Excel: Using Solver for Decision Analysis

Introduce the problem - Microsoft Excel Tutorial

From the course: Excel: Using Solver for Decision Analysis

Start my 1-month free trial Buy for my team

Introduce the problem

“

- [Instructor] Businesses that deliver physical goods must decide where to place their warehouses. It's a difficult and computationally intensive problem, but we need to remember that delivery based costs are based mostly on distance. So our goal in this chapter is to locate warehouses to minimize distance traveled to the stores that we serve. We can represent locations of our stores and also warehouses using an XY scatter plot. So here's what that might look like. In this case, we have 25 stores which are represented in blue, and we also have three depots or warehouses that are represented in orange. And you can see at the bottom looks like 28 and 10 that we have an orange dot right next to a blue dot. So that means they might even share the same facility. We're going to solve this problem to calculate the best mix of depot positions to minimize total distance. But even the small problem is too complex for linear and nonlinear solving engines. That means we need to use the evolutionary engine which is the third of the three available to us. One thing to remember about the evolutionary engine is that it doesn't guarantee that you'll find an optimal solution. In fact, it doesn't guarantee that you'll find any solution or the same solution twice if you run the model more than one time. Another disadvantage of the evolutionary engine is that it does take a while to run. So while it can solve problems that the other solving engines can't, it means that it's more computationally intensive and you will need to be patient.

Contents