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 closeup 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 (1^{st} 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

(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
 Overview of the Normal Distribution
 Normal Distribution’s PDF (Probability Density Function) in Excel 2010 and Excel 2013
 Normal Distribution’s CDF (Cumulative Distribution Function) in Excel 2010 and Excel 2013
 Solving Normal Distribution Problems in Excel 2010 and Excel 2013
 Overview of the Standard Normal Distribution in Excel 2010 and Excel 2013
 An Important Difference Between the t and Normal Distribution Graphs
 The Empirical Rule and Chebyshev’s Theorem in Excel – Calculating How Much Data Is a Certain Distance From the Mean
 Demonstrating the Central Limit Theorem In Excel 2010 and Excel 2013 In An EasyToUnderstand Way
 tDistribution in Excel
 Binomial Distribution in Excel
 zTests in Excel
 Overview of Hypothesis Tests Using the Normal Distribution in Excel 2010 and Excel 2013
 OneSample zTest in 4 Steps in Excel 2010 and Excel 2013
 2Sample Unpooled zTest in 4 Steps in Excel 2010 and Excel 2013
 Overview of the Paired (TwoDependentSample) zTest in 4 Steps in Excel 2010 and Excel 2013
 tTests in Excel
 Overview of tTests: Hypothesis Tests that Use the tDistribution
 1Sample tTests in Excel
 1Sample tTest in 4 Steps in Excel 2010 and Excel 2013
 Excel Normality Testing For the 1Sample tTest in Excel 2010 and Excel 2013
 1Sample tTest – Effect Size in Excel 2010 and Excel 2013
 1Sample tTest Power With G*Power Utility
 Wilcoxon SignedRank Test in 8 Steps As a 1Sample tTest Alternative in Excel 2010 and Excel 2013
 Sign Test As a 1Sample tTest Alternative in Excel 2010 and Excel 2013
 2IndependentSample Pooled tTests in Excel
 2IndependentSample Pooled tTest in 4 Steps in Excel 2010 and Excel 2013
 Excel Variance Tests: Levene’s, BrownForsythe, and F Test For 2Sample Pooled tTest in Excel 2010 and Excel 2013
 Excel Normality Tests KolmogorovSmirnov, AndersonDarling, and Shapiro Wilk Tests For TwoSample Pooled tTest
 TwoIndependentSample Pooled tTest  All Excel Calculations
 2 Sample Pooled tTest – Effect Size in Excel 2010 and Excel 2013
 2Sample Pooled tTest Power With G*Power Utility
 MannWhitney U Test in 12 Steps in Excel as 2Sample Pooled tTest Nonparametric Alternative in Excel 2010 and Excel 2013
 2 Sample Pooled tTest = SingleFactor ANOVA With 2 Sample Groups
 2IndependentSample Unpooled tTests in Excel
 2IndependentSample Unpooled tTest in 4 Steps in Excel 2010 and Excel 2013
 Variance Tests: Levene’s Test, BrownForsythe Test, and FTest in Excel For 2Sample Unpooled tTest
 Excel Normality Tests KolmogorovSmirnov, AndersonDarling, and ShapiroWilk For 2Sample Unpooled tTest
 2Sample Unpooled tTest Excel Calculations, Formulas, and Tools
 Effect Size for a 2IndependentSample Unpooled tTest in Excel 2010 and Excel 2013
 Test Power of a 2Independent Sample Unpooled tTest With GPower Utility
 Paired (2Sample Dependent) tTests in Excel
 Paired tTest in 4 Steps in Excel 2010 and Excel 2013
 Excel Normality Testing of Paired tTest Data
 Paired tTest Excel Calculations, Formulas, and Tools
 Paired tTest – Effect Size in Excel 2010, and Excel 2013
 Paired tTest – Test Power With GPower Utility
 Wilcoxon SignedRank Test in 8 Steps As a Paired tTest Alternative
 Sign Test in Excel As A Paired tTest Alternative
 Hypothesis Tests of Proportion in Excel
 Hypothesis Tests of Proportion Overview (Hypothesis Testing On Binomial Data)
 1Sample Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013
 2Sample Pooled Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013
 How To Build a Much More Useful SplitTester in Excel Than Google's Website Optimizer
 ChiSquare Independence Tests in Excel
 ChiSquare GoodnessOfFit Tests in Excel
 F Tests in Excel
 Correlation in Excel
 Pearson Correlation in Excel
 Spearman Correlation in Excel
 Confidence Intervals in Excel
 zBased Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013
 tBased Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013
 Minimum Sample Size to Limit the Size of a Confidence interval of a Population Mean
 Confidence Interval of Population Proportion in 2 Steps in Excel 2010 and Excel 2013
 Min Sample Size of Confidence Interval of Proportion in Excel 2010 and Excel 2013
 Simple Linear Regression in Excel
 Overview of Simple Linear Regression in Excel 2010 and Excel 2013
 Complete Simple Linear Regression Example in 7 Steps in Excel 2010 and Excel 2013
 Residual Evaluation For Simple Regression in 8 Steps in Excel 2010 and Excel 2013
 Residual Normality Tests in Excel – KolmogorovSmirnov Test, AndersonDarling Test, and ShapiroWilk Test For Simple Linear Regression
 Evaluation of Simple Regression Output For Excel 2010 and Excel 2013
 All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013
 Prediction Interval of Simple Regression in Excel 2010 and Excel 2013
 Multiple Linear Regression in Excel
 Basics of Multiple Regression in Excel 2010 and Excel 2013
 Complete Multiple Linear Regression Example in 6 Steps in Excel 2010 and Excel 2013
 Multiple Linear Regression’s Required Residual Assumptions
 Normality Testing of Residuals in Excel 2010 and Excel 2013
 Evaluating the Excel Output of Multiple Regression
 Estimating the Prediction Interval of Multiple Regression in Excel
 Regression  How To Do Conjoint Analysis Using Dummy Variable Regression in Excel
 Logistic Regression in Excel
 Logistic Regression Overview
 Logistic Regression in 6 Steps in Excel 2010 and Excel 2013
 R Square For Logistic Regression Overview
 Excel R Square Tests: Nagelkerke, Cox and Snell, and LogLinear Ratio in Excel 2010 and Excel 2013
 Likelihood Ratio Is Better Than Wald Statistic To Determine if the Variable Coefficients Are Significant For Excel 2010 and Excel 2013
 Excel Classification Table: Logistic Regression’s Percentage Correct of Predicted Results in Excel 2010 and Excel 2013
 Hosmer Lemeshow Test in Excel – Logistic Regression GoodnessofFit Test in Excel 2010 and Excel 2013
 SingleFactor ANOVA in Excel
 Overview of SingleFactor ANOVA
 SingleFactor ANOVA in 5 Steps in Excel 2010 and Excel 2013
 ShapiroWilk Normality Test in Excel For Each SingleFactor ANOVA Sample Group
 KruskalWallis Test Alternative For Single Factor ANOVA in 7 Steps in Excel 2010 and Excel 2013
 Levene’s and BrownForsythe Tests in Excel For SingleFactor ANOVA Sample Group Variance Comparison
 SingleFactor ANOVA  All Excel Calculations
 Overview of PostHoc Testing For SingleFactor ANOVA
 TukeyKramer PostHoc Test in Excel For SingleFactor ANOVA
 GamesHowell PostHoc Test in Excel For SingleFactor ANOVA
 Overview of Effect Size For SingleFactor ANOVA
 ANOVA Effect Size Calculation Eta Squared in Excel 2010 and Excel 2013
 ANOVA Effect Size Calculation Psi – RMSSE – in Excel 2010 and Excel 2013
 ANOVA Effect Size Calculation Omega Squared in Excel 2010 and Excel 2013
 Power of SingleFactor ANOVA Test Using Free Utility G*Power
 Welch’s ANOVA Test in 8 Steps in Excel Substitute For SingleFactor ANOVA When Sample Variances Are Not Similar
 BrownForsythe FTest in 4 Steps in Excel Substitute For SingleFactor ANOVA When Sample Variances Are Not Similar
 TwoFactor ANOVA With Replication in Excel
 TwoFactor ANOVA With Replication in 5 Steps in Excel 2010 and Excel 2013
 Variance Tests: Levene’s and BrownForsythe For 2Factor ANOVA in Excel 2010 and Excel 2013
 ShapiroWilk Normality Test in Excel For 2Factor ANOVA With Replication
 2Factor ANOVA With Replication Effect Size in Excel 2010 and Excel 2013
 Excel Post Hoc Tukey’s HSD Test For 2Factor ANOVA With Replication
 2Factor ANOVA With Replication – Test Power With GPower Utility
 ScheirerRayHare Test Alternative For 2Factor ANOVA With Replication
 TwoFactor ANOVA Without Replication in Excel
 Randomized Block Design ANOVA in Excel
 RepeatedMeasures ANOVA in Excel
 SingleFactor RepeatedMeasures ANOVA in 4 Steps in Excel 2010 and Excel 2013
 Sphericity Testing in 9 Steps For Repeated Measures ANOVA in Excel 2010 and Excel 2013
 Effect Size For RepeatedMeasures ANOVA in Excel 2010 and Excel 2013
 Friedman Test in 3 Steps For RepeatedMeasures ANOVA in Excel 2010 and Excel 2013
 ANCOVA in Excel
 Normality Testing in Excel
 Creating a Box Plot in 8 Steps in Excel
 Creating a Normal Probability Plot With Adjustable Confidence Interval Bands in 9 Steps in Excel With Formulas and a Bar Chart
 ChiSquare GoodnessofFit Test For Normality in 9 Steps in Excel
 KolmogorovSmirnov, AndersonDarling, and ShapiroWilk Normality Tests in Excel
 Nonparametric Testing in Excel
 MannWhitney U Test in 12 Steps in Excel
 Wilcoxon SignedRank Test in 8 Steps in Excel
 Sign Test in Excel
 Friedman Test in 3 Steps in Excel
 ScheirerRayHope Test in Excel
 Welch's ANOVA Test in 8 Steps Test in Excel
 BrownForsythe F Test in 4 Steps Test in Excel
 Levene's Test and BrownForsythe Variance Tests in Excel
 ChiSquare Independence Test in 7 Steps in Excel
 ChiSquare GoodnessofFit Tests in Excel
 ChiSquare Population Variance Test in Excel
 Post Hoc Testing in Excel
 Creating Interactive Graphs of Statistical Distributions in Excel
 Interactive Statistical Distribution Graph in Excel 2010 and Excel 2013
 Interactive Graph of the Normal Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the ChiSquare Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the tDistribution in Excel 2010 and Excel 2013
 Interactive Graph of the tDistribution’s PDF in Excel 2010 and Excel 2013
 Interactive Graph of the tDistribution’s CDF in Excel 2010 and Excel 2013
 Interactive Graph of the Binomial Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Exponential Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Beta Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Gamma Distribution in Excel 2010 and Excel 2013
 Interactive Graph of the Poisson Distribution in Excel 2010 and Excel 2013
 Solving Problems With Other Distributions in Excel
 Solving Uniform Distribution Problems in Excel 2010 and Excel 2013
 Solving Multinomial Distribution Problems in Excel 2010 and Excel 2013
 Solving Exponential Distribution Problems in Excel 2010 and Excel 2013
 Solving Beta Distribution Problems in Excel 2010 and Excel 2013
 Solving Gamma Distribution Problems in Excel 2010 and Excel 2013
 Solving Poisson Distribution Problems in Excel 2010 and Excel 2013
 Optimization With Excel Solver
 Maximizing Lead Generation With Excel Solver
 Minimizing Cutting Stock Waste With Excel Solver
 Optimal Investment Selection With Excel Solver
 Minimizing the Total Cost of Shipping From Multiple Points To Multiple Points With Excel Solver
 Knapsack Loading Problem in Excel Solver – Optimizing the Loading of a Limited Compartment
 Optimizing a Bond Portfolio With Excel Solver
 Travelling Salesman Problem in Excel Solver – Finding the Shortest Path To Reach All Customers
 ChiSquare Population Variance Test in Excel
 Analyzing Data With Pivot Tables
 SEO Functions in Excel
 Time Series Analysis in Excel
 VLOOKUP
No comments:
Post a Comment