Problem with warehouses location and distances

Hello from Italy to everyone!

I'm new into linear and non linear regression models in Excel and I came across this forum, which I expect to learn a lot from!

I am kind of stuck with one problem that I am trying to solve now! I would really appreciate your help!

I need to find out the minimum number of warehouses that a company must place in order to serve 25 cities within a country. A city is considered to be served if its distance to the closest supply center is lower or equal than 250 Km. I have a table that shows me the distance at which each of this 25 cities is from the others.

I thought I can use the excel IF function (1,0) in order to see if a city is located at a distance lower than 250 km. But this just take into account pairs of cities, not all together. I guess I can do this exercise with the SOLVER and using some constraints, (maybe with binary decision variables?) but i really cannot figure out how!

I would really appreciate your help!

Thanks! :D

Hi,

Welcome to the Forum. As you problem obviously is homework and forum rules stats that

In this forum we cannot allow direct help with homework or assignment questions, if we tell you how to do it you will never learn how to do it! We can answer specific questions and point you in the right direction, if you really don't understand your work/assignment

forum members will try to give you some help but a better understanding of your problem would make it easier to do so could you please upload the exercise instruction.

Alf

Ohh ok sorry, did not know that!

No problem. Just regard my comments as an information of what kind of help you can expect in this forum.

Having had a look at your uploaded file I'm at the moment unsure of how to set up solver but I've not given up yet and as there are 3 other forum members that have downloaded this file I'm sure that somebody will be able to help you.

Alf

Well you are right in assuming that a combination of solver and binaries will give you a solution to your problem.

In the uploaded file you have two tables one showing distances and one showing which cities have a distance equal to or less than 250 km by using an IF formula. As you now know the cities that fits "the bill" how about substituting this formula with a formula that is only a cell value (set as binary value) times 1 for all the cities that is 250 km or less apart?

Alf

Originally Posted by Alf

Well you are right in assuming that a combination of solver and binaries will give you a solution to your problem.

In the uploaded file you have two tables one showing distances and one showing which cities have a distance equal to or less than 250 km by using an IF formula. As you now know the cities that fits "the bill" how about substituting this formula with a formula that is only a cell value (set as binary value) times 1 for all the cities that is 250 km or less apart?

Alf

Thank you so much for your help!

I think I can figure out how to solve it now. I will try tomorrow and see what happens. I will keep you updated about the result hehe.

Thanks again!

As you have solved your problem I’ll upload my solver models.

Reading the instruction to the problem I was surprised by the comment “The company only cares to know if a city is served or not” as the transportation cost should be a deciding factor as well as the building costs at each site where the warehouses are to be built.

As you can see from the two models they both give the same number of warehouses to build in order to serve all cities but transportation cost do vary quite a bit depending on where you build.

Using solver in modelling there could be a number of possible solutions so in order to find the optimal solution to one’s problem one must add the “right” constraints as well as using the "right" objective.

Alf

Ps Your profile says "Excel 2003" but your uploaded file is an ".xlsx" file i.e. "Excel 2007" or "Excel 2010". As the help you will be given in this forum depends a bit on you Excel version you better update your Forum profile.