Question

I am working on a Supply Chain Network Design homework and having a terrible time trying to understand the MS Excel Solver. How to use it to resolve my two problems. I have to submit the answers using a single Excel file (with multiple worksheets and answer each question in a separate worksheet). Can you help?I am working on a Supply Chain Network Design homework and having a terrible time trying to understand the MS Excel Solver. How to use it to resolve my two problems. I have to submit the answers using a single Excel file (with multiple worksheets and answer each question in a separate worksheet). Can you help?

Answer 1

When you use excel solver, you need to convert every problem in linear programming model. Each of the problem must be modeled on a single sheet and then the solver can be used to optimize it.

Now, you need to convert each of the problems into a linear programming problem. This means that you will create an objective function, a set of constraints and solve for the decision variables. For example, if you are looking to solve a transportation problem then (most likely) your objective will be to minimize the transportation cost. Thus your objective function should the sumproduct of solution and the given costs. Then you need to make sure that the sum of supplies does not exceed capacity and the sum of transports match the demand. This is a simple example, but overall each problem needs to be modeled in LP and then solved on each sheet.

You will find the solver tool on the “Data” tab of excel. In case, it is not enabled then you may have to go to options->add ins and then enable excel solver.