# Minimizing shipping costs in Excel using Solver

In this example, we’re going to find alternative options for shipping materials keeping total shipping costs at a minimum level. Say a company has warehouses in St. Louis, Los Angeles, and Boston. Six retail outlets are situated all over the United States. These retail outlets take orders from customers. The company then ships products from one of the warehouses. The company’s target is to meet the product needs of all six retail outlets from available inventory in the warehouses. While shipping products to outlets, the company wants to keep shipping charges as low as possible.

## Explaining the workbook components

The workbook [that you have downloaded] is a little bit complicated, so I have tried to explain workbook components individually:

• Shipping Costs Table: This table contains the cell range B2:E8. This is a matrix that holds per unit shipping costs from each warehouse to each retail outlet. For example, the cost to ship a unit of a product from Boston to Detroit is \$38.
• Product needs of each retail store: This information appears in the cell range C12:C17. For example, retail outlet Houston needs 225, Denver needs 150 units, Atlanta needs 100 units, and so on. C18 is a formula cell that calculates the total units needed from the outlets.
• No. to ship from…: Cell range D12:F17 holds the adjustable cells. These cell values will be varied by Solver. We have initialized these cells with a value of 25 to give Solver a starting value. Column G contains formulas. This column contains the sum of units the company needs to ship to each retail outlet from the warehouses. For example, G12 shows a value of 75. The company has to send 75 units of products to the Denver outlet from three warehouses.
• Warehouse inventory: Row 21 contains the amount of inventory at each warehouse. For example, Los Angeles warehouse has 400 units of inventories. Row 22 contains formulas that show the remaining inventory after shipping. For example, Los Angeles has shipped 150 (see, row 18) units of products, so it has the remaining 250 (400-150) units of inventory.
• Calculated shipping costs: Row 24 contains formulas that calculate the shipping costs. Cell D24 contains this formula: =SUMPRODUCT(C3:C8,D12:D17). Click to know SUMPRODUCT function. We’ve copied this formula for the cells E24 and F24 using relative cell references.

In this example, we’re going to determine the least expensive way to ship products from warehouses to retail outlets.

Cell G24 is the last cell used, the total shipping costs for all orders.

Read More: Excel Solver – Introducing you with a simple example

The solver will fill in the values in the cell range D12:F17 in such a way that will minimize the shipping costs from the warehouses to the outlets. In other words, the solution will minimize the value in cell G24 by adjusting the values of cell range D12:F17 fulfilling the following constraints:

• The number of units demanded by each retail outlet must equal the number shipped. In other words, all the orders will be filled. These constraints can be expressed by the following specifications:
C12=G12, C14=G14, C16=G16, C13=G13, C15=G15, and C17=G17
• The number of units remaining in each warehouse’s inventory must not be negative. In other words, a warehouse can’t ship more than its inventory. The following constraint shows this: D22>=0 E22>=0 F22>=0
• The adjustable cells can’t be negative because shipping a negative number of units makes no sense. The Solve Parameters dialog box has a handy option: Make Unconstrained Variables Non-Negative. Make sure this setting is enabled.

Setting up the problem is the difficult part. You have to know how to add constraints. See this link to know about adding constraints in the Solver Parameters dialog box. In this example, you have to enter nine constraints.

Read More: Resource Allocation Model in Excel Using Solver

## Steps to setup the Solver Parameters dialog box

Use the following steps to set up the Solver Parameters dialog box. This dialog box appears when you choose Data ➪ Analysis ➪ Solver.

### Step 1:

Fill Set Objective field with this value: \$G\$24.

### Step 2:

Select the radio button of the Min option in To control.

### Step 3:

Select cell \$D\$12 to \$F\$17 to fill the field By Changing Variable Cells. This field will show then \$D\$12:\$F\$17.

### Step 4:

Add constraints one by one. The constraints are: C12=G12, C14=G14, C16=G16, C13=G13, C15=G15, C17=G17, D22>=0, E22>=0, and F22>=0. These constraints will be shown in the Subject to the Constraints field. Click this article to know more about adding constraints.

### Step 5:

Select the Make Unconstrained Variables Non-Negative check box.

### Step 6:

Select Simplex LP from the Select a Solving Method drop-down list.

### Step 7:

Now click the Solve button. You will find the Solver Results dialog box shown in the following figure. Click OK, you are done with your result.

Solver displays the solution shown in the following figure.

This is the solution created by Solver.

The total shipping cost is \$55,515, and all the constraints are met. Notice that shipments to Miami come from both St. Louis and Boston.

Solver is a complex tool. This chapter just introduces the basics of the tool. If you’d like to learn more about Solver, I highly recommend the website for Frontline Systems. Frontline Systems company developed Solver for Excel. Its website has several tutorials and lots of helpful information. You can also download a detailed manual from their website about Solver. You can also find additional Solver products for Excel that can handle much more complex problems.

Master Data Analysis: Top Data Analysis Courses Online

Happy Excelling 🙂

Solver Excel – A Step by Step Complete How-to-use Guide

How to Assign Work Using Evolutionary Engine?

#### Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1. Cannot download example file. It keeps directing me to mailchimp and then I’m seeing error because I’m already subscribed.

2. Hi,
I hope you can help me in that using excel

The Kellogg’s Cornflake Company began in 1906 with the Kellogg brothers who originally ran a sanatorium in Michigan, USA. They experimented with different ways to cook cereals without losing the goodness. Their philosophy was ‘improved diet leads to improved health’. Between 1938 and the present day Kellogg’s opened manufacturing plants in the UK, Canada, Australia, Latin America and Asia. Kellogg’s is now the world’s leading breakfast cereal manufacturer. Its products are manufactured in 3 countries; Belgium (location 50,4) with total capacity 25000 tons and distribution costs per unit 6\$; China (location 39, 116) with total capacity 28000 tons and distribution costs per unit 9\$; and France (location 48, 2) with total capacity 37000 tons and distribution costs per unit 5\$, while sold in more than 3 countries; Greece (location 37, 23) with total demand 15000 tons and distribution costs per unit 9\$; Ireland (location 53, 6) with total demand 18000 tons and distribution costs per unit 4\$; and Luxembourg (location 49, 6) with total demand 28000 tons and distribution costs per unit 9\$. It produces a wide range of cereal products, including the well-known brands of Kellogg’s Corn Flakes, Rice Krispies, Special K, Fruit n’ Fibre, as well as the Nutri-Grain cereal bars. Kellogg’s business strategy is clear and focused: • to grow the cereal business – there are now 40 different cereals • to expand the snack business – by diversifying into convenience foods • to engage in specific growth opportunities.

Questions
1. In order to minimize total distribution costs, design a facility location model to determine the right location of establishing a distribution center.
2. Design a distribution network and generate an answer report to determine the flow of goods between suppliers and markets.
3. Is it feasible to shut down one supply markets to minimize total costs if you know that the fixed costs are as follows: Belgium (18000 \$), China (17000 \$), and France (19000\$)? Generate an answer report to shows your results.

• Hi Ahmed,
I am keeping a note of your problem, hope we shall be able to make a solution to this.
Thanks.