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

Posted by at

22 comments:

  1. China FreightSeptember 18, 2019 at 11:34 PM

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

    ReplyDelete
  2. William JessieOctober 13, 2020 at 5:08 AM

    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. legacymarbleandgraniteDecember 10, 2020 at 3:32 AM

    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. chinafreightDecember 15, 2020 at 10:48 AM

    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. SharonmartinsJanuary 21, 2021 at 10:26 PM

    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. CurbsidedeliverylmtFebruary 19, 2021 at 10:16 AM

    This comment has been removed by the author.

    ReplyDelete
  7. CurbsidedeliverylmtFebruary 19, 2021 at 10:22 AM

    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. Freight Assist AustraliaMay 24, 2021 at 12:07 PM

    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. Rent A Helper MovingJune 11, 2021 at 3:16 AM

    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. kinzayShaikhJune 16, 2021 at 11:17 AM

    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. Harry jackSeptember 5, 2021 at 3:21 AM

    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. Harry jackSeptember 5, 2021 at 3:37 AM

    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. MTC Office Relocations LondonSeptember 24, 2021 at 11:39 AM

    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. dawnknightOctober 12, 2021 at 4:20 PM

    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. Arabian ExpertDecember 1, 2021 at 9:26 PM


    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. dstcourierJanuary 24, 2022 at 3:37 PM

    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. aaronnssdJanuary 24, 2022 at 4:20 PM

    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. DwayneMarch 14, 2022 at 4:31 PM

    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. lorideliveriesApril 13, 2022 at 12:09 AM

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

    ReplyDelete
  20. John ClarkJune 9, 2022 at 9:35 PM

    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. USA Auto ShippingSeptember 24, 2022 at 10:11 AM

    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. Packers Movers JaipurFebruary 7, 2024 at 2:43 AM

    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

Subscribe to: Post Comments (Atom)