This article is part of my series: Excel Solver – A Step by Step Complete How-to-use Guide.
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.
Cell G24 is the last cell used, the total shipping costs for all orders.
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.
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.
Fill Set Objective field with this value: $G$24.
Select the radio button of the Min option in To control.
Select cell $D$12 to $F$17 to fill the field By Changing Variable Cells. This field will show then $D$12:$F$17.
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.
Select the Make Unconstrained Variables Non-Negative check box.
Select Simplex LP from the Select a Solving Method drop-down list.
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.
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.
Learning More about Solver
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.
Happy Excelling 🙂
Download Working File
Download the working file from the link below: