Question

Given the excel spreadsheet pictured Maximize project sdvf which is cell F8 ""select"" C2:C7 are the changed variable cells Constraints are: $17000 budget cell E9 Cells C2:C7 are binary and using the option bath for discussion purposes. If Solver returns ""total remodel"" in the optimization is clear that I would not want to ""paint walls"" or ""paint walls and replace fixtures"" as well. So how do I structure the spreadsheet or enter constraints to preclude duplication.

IF ""paint walls"" THEN do not ""paint walls and replace fixtures"" nor do ""total renovation""
IF ""paint walls and replace fixtures"" THEN do not ""paint"" nor do ""total renovation""
IF ""total renovation"" THEN do not ""paint walls"" nor ""paint walls and replace fixtures""

Answer 1

The question involves creating Excel Solver constraints to prevent duplicate project options. Additional constraints based on binary variables are needed to prevent the selection of redundant remodeling options. For example, if "paint walls" is chosen, constraints should ensure that neither "paint walls and replace fixtures" nor "total renovation" can be selected.

Step-by-step explanation:

The student is dealing with an optimization problem in Excel, specifically trying to apply constraints within Solver to avoid duplicating options in a project selection model. Binary variables are used in cells C2:C7 to represent the selection of different remodeling options, subject to a budget constraint in cell E9. The challenge is to structure these binary variables and constraints in such a way that selecting one option precludes the selection of other specific options that would be redundant, such as painting walls and total renovation simultaneously.

To address the issue, additional constraints need to be entered into the Solver for the binary variables representing the project options. For instance, if "paint walls" is selected (binary variable in cell C2 is 1), then both "paint walls and replace fixtures" (binary variable in cell C3) and "total renovation" (binary variable in cell C4) should be 0. This can be achieved by adding constraints such as C2 + C3 <= 1 and C2 + C4 <= 1, ensuring that if "paint walls" is chosen, the other two cannot be.