Implementing "Soft Constants"

This article is meant to help users with the implementation of "soft constants" within their model(s). A "soft constant" is a variable or formula result where you may want to reach a particular value, however you are unsure (or you do know for sure) if it is possible to reach the desired value and it is okay if the Solving process gets "as close as possible".

For example, you construct a distribution model that determines the minimum cost to ship items from three different warehouses that all have their own level of demand. Initially, you would want to guarantee all three warehouses have their demand completely met, and would add an equality constraint that "product provided" = "demand". Such constraints can not always be met in real world situations, in which case the "next best" would be to have your product provided be as close to the demand as possible.

To do this, you would include the "product provided" in your objective function. Note you want the "product provided" to have a positive affect on the objective - this means that if you're minimizing you will subtract your "product provided", if you're maximizing you will add your "product provided". This gives "weight" to your "product provided" in terms of the objective, and the "product provided" will in turn be optimized as well to find the best objective possible.

Following this you will want to adjust your equality constraint to a less than or equal to (<=) constraint, this allows solver to consider solutions that do not absolutely meet the previously implemented constraint. However, due to the adjusted objective function the value on the left hand side of the constraint will get "as close as possible" to the right hand side value.

To demonstrate this, please review the workbook available at the end of this article. This workbook is a modification of our "Supplier Choice" example.

On sheet Supplier Choice 1 - the model is not feasible, there are less contracts available than are required. On Supplier Choice Mod, we create a feasible model by changing the constraint "Total_awarded" >= "Contracts_required" to be "Total_awarded" <= "Contracts_required" and subtract (since we're minimizing cost) 100 times the SUM of the "Total_awarded" values from the objective. The extra multiplication is for "scaling", any one contract that is awarded will "add" more to the objective than is lost with just the SUM, so we "scale" the SUM such that when a contract is awarded, the Total Cost will become smaller instead of larger.

Since we have changed our original objective, but we still want to know what our actual cost is, the original objective function is now in Cell C25 so we can quickly see the cost of awarding the contracts. Note that cell C25 is not part of the model, it is just a "data cell" now telling us a useful piece of information that was "lost" due to our model modifications.