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.
