Thursday, May 29, 2014

Minimizing Shipping Costs With Excel Solver

Minimizing the Total Cost

of Shipping From Multiple

Points To Multiple Points

With Excel Solver

This Solver problem minimizes the total cost of shipping the same product from multiple factories to multiple stores. Shipping costs are different from each factory to each store. Each store needs a different quantity of the product and each factory has a different amount of the product available for shipping. This problem assumes that the only cost differences are the per unit shipping costs between the factories and the stores.

The Problem

A company manufactures its own products at its 3 factories and then delivers these products at its own 3 stores. Each of the 3 stores orders a different amount of the product from the factories while each of the 3 factories has a different amount of the product available to ship to the stores. Shipping costs per unit of product are different between each factory and each store. Determine the optimal amount of product to ship from each factory to each store in order to minimize total shipping costs while fulfilling each store’s order.

 

Shown as follows are the costs of shipping a single unit of the product from each factory to each store :

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013(Click Image To See a Larger Version)

 

Excel Solver Problem Solving Steps

 

Excel Solver Step 1 – Determine the Objective

In this case, the objective is to minimize the total cost of shipping between all factories and all stores. The cell in which that total cost is calculated is the Objective Cell.

 

Excel Solver Step 2 – Determine the Decision Variables

We are trying to determine how many units of product to ship from each factory to each store in order to minimize total shipping cost while fulfilling all store orders. The Decision Variables are the numbers of units of the product to ship from each factory to each store.

 

Excel Solver Step 3 – Build the Excel Equations That Combine the Objective With All Decision Variables

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

The preceding image of the model shows the yellow Objective cell (D20), the green Decision Variables (C13 to C15, E13 to E15, G13 to G15), and the light blue Constraint cells (C17, E17, G17, J13 to J15).

Following on the next several pages is an expanded view of this Excel model for greater clarity.

The next image shows a close-up of the part of the model containing all the green Decision Variables and the light blue Constraint cells that Constraint the amount shipped to each store.

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

Following are the yellow Objective total cost of shipping and the light blue Constraint cells that ensure that each store will receive its required number of units of product.

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

Following are the light blue Constraint cells that ensure that each factory will not attempt to ship more units than that factory has available.

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

 

Excel Solver Step 4 – List all Constraints

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

The light blue Constraint cells shown here are the cells on the spreadsheet where the user inputs the number of units of product needed from each store and the number available from each factory.

 

Excel Solver Step 5 – Test the Excel Spreadsheet

Test the Excel spreadsheet completely before adding information to the Solver dialogue box. Make sure that any changes to Decision Variables produce the correct results in the Objective cell.

 

Excel Solver Step 6 – Insert All Data into the Solver Dialogue Box

Input the Objective cell, Decision Variable cell, and all Constraints into the Solver dialogue box as follows:

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

The Integer Constraints ensure that only whole number of units of product get shipped. The equality Constraints ensure that each store’s order will be exactly fulfilled. The inequality Constraints ensure that no factory attempts to ship more product than it has available.

We have to scroll down to view the remaining Constraints, as follows:

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

All equations on the Excel spreadsheet are linear (1st order) so we can use the Simplex LP (Linear Programming) Solver engine for this optimization problem.

Step 3 shows the completed problem with Decision Variables that have been optimized by the Solver to maximize the Objective while staying within the problem’s Constraints.

 

Excel Solver Answer Report

 

Part 1

Note:

- The Solver Result

- How long Solver took to solve the problem

- The Solver Engine that was used and the Solver Options settings

- Where the Objective Cell was labeled in the Excel model for its name to appear as it does in Part 1 of the Answer Report

-

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

 

Part 2

- Note that the Variable Cells contain the Decision Variables

- Note where the labels for each Decision Variable are placed in the Excel model so that the Decision Variable’s name will appear here in Part 2 of the Answer Report as it does

- Note the type of variable - Either Continuous or Integer (Integer, Binary, or Alldifferent)

- Note the Before and After values of each Decision Variable

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

 

Part 3

- Note how each Constraint is labeled in the Excel model in order for the Constraint’s name to appear here in Part 3 of the Answer Report as it does

- Note which Constraints are binding (had their limits hit) and which aren’t.

- Note how much slack is still available in any Constraint that has not had its limit hit.

- Note any Integer Constraints (Integer, Binary, Alldifferent)

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

solver, excel solver, optimization, minimize shipping costs, linear programming, statistics, excel, excel 2010, excel 2013 (Click Image To See a Larger Version)

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

No comments:

Post a Comment