# 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 :

(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

(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.

(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.

(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.

(Click Image To See a Larger Version)

### Excel Solver Step 4 – List all Constraints

(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:

(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:

(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.

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

-

(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

(Click Image To See a Larger Version)

(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)

(Click Image To See a Larger Version)

(Click Image To See a Larger Version)

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

• Histograms in Excel
• Bar Chart in Excel
• Combinations & Permutations in Excel
• Normal Distribution in Excel
• t-Distribution in Excel
• Binomial Distribution in Excel
• z-Tests in Excel
• t-Tests in Excel
• Hypothesis Tests of Proportion in Excel
• Chi-Square Independence Tests in Excel
• Chi-Square Goodness-Of-Fit Tests in Excel
• F Tests in Excel
• Correlation in Excel
• Pearson Correlation in Excel
• Spearman Correlation in Excel
• Confidence Intervals in Excel
• Simple Linear Regression in Excel
• Multiple Linear Regression in Excel
• Logistic Regression in Excel
• Single-Factor ANOVA in Excel
• Two-Factor ANOVA With Replication in Excel
• Two-Factor ANOVA Without Replication in Excel
• Randomized Block Design ANOVA in Excel
• Repeated-Measures ANOVA in Excel
• ANCOVA in Excel
• Normality Testing in Excel
• Nonparametric Testing in Excel
• Post Hoc Testing in Excel
• Creating Interactive Graphs of Statistical Distributions in Excel
• Solving Problems With Other Distributions in Excel
• Optimization With Excel Solver
• Chi-Square Population Variance Test in Excel
• Analyzing Data With Pivot Tables
• SEO Functions in Excel
• Time Series Analysis in Excel
• VLOOKUP

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

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

3. I found your this post while searching for some related information on blog search...Its a good post..keep posting and update the information. Europa-Road nemzetkÃ¶zi fuvarozÃ¡s

5. 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

6. Thanks for sharing such great information. It's really helpful for me. I always search to read the quality content and finally I found this in your post. keep it up. Logistics Companies in miami

7. 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

8. This comment has been removed by the author.

9. 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

10. I found decent information in your article. I am impressed with how nicely you described this subject, It is a gainful article for us. Thanks for share it. Shipping from China to Mexico

11. I want to thank you for your work. you write helpful and amazing article. I'm hope similar best work from you later on also. Freight Consultants

12. 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.

13. 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

14. 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.

15. Very good information, You have provided excellent information for us. It is valuable and informative for everyone. Keep posting always. I am very thankful to you. Hand Carry Couriers

16. 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

17. 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

18. 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

19. 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.

20. 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.

21. 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.

22. 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.

23. 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.

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

25. This takes some pressure off of him, and will allow him to concentrate on driving instead of the clock. In the end, happy customers and consignee's mean repeat business. And safe drivers are an asset to all of our industries. dispatch companies

26. I liked your work and, as a result, the manner you presented this content about Ecommerce Delivery Mauritius.It is a valuable paper for us. Thank you for sharing this blog with us.