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

22 comments:

  1. We read your blog site, share most helpful details in blog. Shipping from China to Amazon FBA

    ReplyDelete
  2. Great Article. Thank you for providing such a unique and valuable information to your readers. I really appreciate your work. shipping from China to the UK

    ReplyDelete
  3. Found your post interesting to read. I can't wait to see your post soon. Good Luck for the upcoming update. This article is really very interesting and effective. Small Parcel Audit

    ReplyDelete
  4. After seeing the video I got some great information about Transport Services, which is really helpful for me, as it contains some different kind of content in it.
    shipping from China to USA

    ReplyDelete
  5. I'm genuinely impressed with your knowledge. You have such great knowledge of dressing. Please share more information like this. I really appreciate your blog. Thank you for sharing this.
    international freight shipping companies

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Great job for publishing such a nice article. Your article isn’t only useful but it is additionally really informative. Thank you because you have been willing to share information with us. Curbside Pickup Software in USA

    ReplyDelete
  8. There are several factors affecting shipping costs, shipping companies have made strenuous efforts to optimize costs and delivery time. As a fellow shipping company providing logistics management and dangerous goods transport services, I am glad to learn the insights you have shared on shipping costs. Thanks for posting it. Keep it up.

    ReplyDelete
  9. Thanks for giving such good knowledge. I got some helpful information from this post. Keep Posting such types of posts.Local Moving company in Hackensack NJ

    ReplyDelete
  10. chicago frieght Thanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info.

    ReplyDelete
  11. Ukrainian deck and engineering personnel are as desirable as or higher than any different, however the price in their hard work is a superb fee. The hard work for deck officer and engineering personnel are ruled with the aid of using worldwide agreements, such as STCW (Standards of Training, Certification and Watchkeeping for Seafarers).Shipping from china

    ReplyDelete
  12. I am in touch with the proprietors of the Orient Venus. I may want to likely get this stunningly lovely deliver for much less than $15,000,000 today, and get a few concessions and extras thrown in to boot.Shipping from china

    ReplyDelete
  13. Good information, You have provided important and valuable data for us. It is good and essential for everyone. Keep posting always. I am very thankful to you. office relocation company

    ReplyDelete
  14. In today's fast-changing environment, the whole
    Logistics Melbourne mechanism is customer-service driven. The timely, reliable and accurate delivery of products free from damage are thus the major elements of customer service. Competing in the global arena requires efficient supply chains where the cumulative efficiency of all the partners matter.

    ReplyDelete

  15. This is really a good source of information, I will often follow it to know more information and expand my knowledge, I think everyone should know it, thanks Best ssd vs hdd service provider.

    ReplyDelete
  16. The information in the post you posted here is useful because it contains some of the best information available. Thanks for sharing it. Keep up the good work usa to pakistan shipping rates.

    ReplyDelete
  17. You have done good work by publishing this article here.Trogir rent a boat I found this article too much informative, and also it is beneficial to enhance our knowledge. Grateful to you for sharing an article like this.

    ReplyDelete
  18. I will share it with my other friends as the information is really very useful. Read more info about india online shopping shipping to Germany. Keep sharing your excellent work.

    ReplyDelete
  19. Your article provided me with an array of data about this topic Dubai Courier, and I would appreciate you continuing to write.

    ReplyDelete
  20. This post provided me with a wealth of information. The post is both educational and helpful.Thank you for providing me with such valuable information about Premium Express Delivery in mauritius Keep up the good work.

    ReplyDelete
  21. It is what I was searching for is really informative. South Carolina Auto Transport Quotes It is a significant and useful article for us. Thankful to you for sharing an article like this.

    ReplyDelete
  22. I am attracted by the info which you have provided in the above post. It is genuinely good and beneficial info for us aboutMovers Packers in Jaipur Continue posting, Thank you.

    ReplyDelete