Tag: Solver

Guest Post: Optimal Location vs. Center of Gravity

 

Retired Temple U. Prof. Howard Weiss created the Excel OM and POM software that we provide free with our text.

Chapter 8 of your Heizer/Render/Munson text introduces the Center-of- Gravity (COG)
method which has a goal of finding a location that minimizes the total cost or weighted distance of shipping to multiple locations. The textbook notes that the COG may not optimize the total cost but that the method to optimize the cost is more complex than simply finding the weighted average coordinates. In this blog I show how to let Excel’s Solver do the complex work to find the coordinates that actually minimize the total weighted distance.

Consider the Quain’s Discount Department Stores example. The spreadsheet below displays the Excel model for this example. Column F contains the weighted distance from the center of gravity. For example, cell F5 contains:
B5*SQRT((C5-C$12)^2+(D5-D$12)^2)

Cell F9 contains the weighted total from the COG while cell G9 contains the weighted distance from Solver’s changing variable cells in row 14. The figure shows the optimal solution in row 14 but the starting values in row 14 can be set to any two numbers. Solver’s objective is to minimize the sum of the weighted distances shown in cell G9. There are no constraints.

For this particular example, we know that the coordinates will be non-negative so we leave the “Make Unconstrained” checkbox at its default checked position. The method is set to GRG Nonlinear.

Solver yields optimal coordinates of x = 63.86 and y = 97.27, with a minimum total weighted distance of 299,234. The COG in your text of x=66.67, y = 93.33 is very close to the optimal coordinates and leads to an extra cost (cells G10 and G11) that is less than 1% above the optimal cost. This agrees with the textbook’s note that the extra cost using the COG is less than 2% above the optimal cost.

Guest Post: Using Solver’s Nonlinear Programming Procedure for Operations Models

Prof. Howard Weiss shares his insights on the power of Excel’s Solver.

I previously have posted for The OM Blog that in the operations course it is important to help students develop their Excel skills. Today I will introduce students to nonlinear programming in Excel’s Solver for Trend Analysis models, a topic in Chapter 4 of your Heizer/Render/Munson text. It highlights to the students exactly what is being optimized – sum of squared errors.

Trend Analysis
Example 8 from Chapter 4 illustrates the Solver process. The initial intercept of 10 and slope of 10 yield the forecasts in column D. Errors and squared errors follow from the forecasts and demands with the sum of squared errors shown in cell F12. This is Solver’s objective. The changing cells are the intercept and slope, there are no constraints, and the method in Solver is GRG Nonlinear. In addition, for least squares the “Make Unconstrained Variables Non-Negative” needs to be unchecked since slopes/trends can be negative in forecasting– although not in this example.

After solving, the solution, not displayed here, appears as Intercept 56.71, slope=10.54 (as shown in the text) and the minimum sum of squares, not shown in the text or figure above, is 773.