Revising Criteria weights using Excel Solver

Technological and other changes are forcing organizations to quickly modify their project design requirements. For example, the successful introduction of new technologies in the construction industry would require a fundamental change in which the preliminary cost and schedule estimates are made. Changes in the weights of various inputs that go into the preliminary estimate become an immediate requirement.

Design cycle improvements require training teams interactively and process inputs improvement. Project stakeholders demand improvements in the deliverables and justifications of enhancements in the first costs even if the life cycle costing of the habitat goes down.

Similarly, the technique illustrated in the solution may be used in changing indicator weights as needed. (The improvements and user demand would also push the green building norms and other sustainability indicators).

The following example suggests how the Excel solver tool may be utilised to revise the various criteria weights during such a revision process.

Problem statement: Version Revision of Green Building requires changes to be made in the criteria / credit weights.

Constraints: The revised weights should be integers and maximum points which can be earned should also remain as per the set benchmark. i.e. increase at one aspect is required to be compensated elsewhere. The solution requires to convert qualitative inputs of the subject matter experts into numbers/numerical weights.

The team responsible for proposing the changes gathers expert inputs through an analytical hierarchy proescs. This involves noting down the qualitative comments/ suggestions in the pre-designed/set format. Click the link to download the workbook.

Experts are requested to choose what qualitative changes they think should be made in the existing criterion/ credit weights. A structured list for creating a dropdown has been designed for illustration.

The AHP worksheet team/designer might have to experiment with and assign as to what kind of quantitative change is meant when the experts opine slight, moderate and much changes. Working on no change is straightforward and does not call for any initial change in weight. Options in the drop-down (For illustration) in the workbook are given below.

One such filled form is shown below. Note here that the total weights of the New weights do not match the baseline total weight.

Criteria	Existing_Points	Choose__Comment	New__Weights
Criteria_1	2	Should_Weigh_Much_More	3
Criteria_2	4	Should_Weigh__Slightly_More	5
Criteria_3	7	Should_Weigh_Slightly_less	6
Criteria_4	10	Should_Weigh__Slightly_More	12
Criteria_5	20	Should_Weigh__Slightly_More	23
Criteria_6	15	Should_Weigh_as_equal	15
Criteria_7	15	Should_Weigh_much_less	6
Criteria_8	10	Should_Weigh__Slightly_More	12
Criteria_9	8	Should_Weigh_Slightly_less	7
Criteria_10	4	Should_Weigh_Slightly_less	3
Criteria_11	5	Should_Weigh_Slightly_less	4
Criteria_12	4	Should_Weigh___More	5
	104		101

As noted these qualitative differentiations are for illustration, one should use jargon which is appropriate and understood/ understood in that particular expert community.

In the example one can change the inputs i.e. the quantum that excel would take for change calculations. The worksheet and solver window are self-explanatory, to those who have used solver before. However, the problem may not get solved with Simplex and GRG nonlinear or Simplex methods and Evolutionary solving method would be required.  It may take more than a few seconds in working out a solution even on a fast computing system.

The project team might require using Delphi technique for fine-tuning the weights. In any case, the Delphi technique is appropriate in case you want to derive such design input for the first time.