Revising Criteria weights using Excel Solver


Technological and other changes are forcing the organizations to quickly modify their project design requirements. For example, 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 in the preliminary estimate becomes 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 for as needed. (The improvements and user demand would also push the green building norms and other sustainability indicators).

The following example suggests how the solver tool of the Excel may be utilized to revise the various criteria weights during such 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 analytical hierarchy process. 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 as to what qualitative changes they think should be made in the existing criterion/ credit weights. A structured list for creating a dropdown has been created 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 do 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. 


Comments

Popular posts from this blog

Using Excel Solver Feature to analyze and optimize the Steel Truss Design.

Using Excel conditional formatting for comparative statements

Excel Solver Primer for Construction Stakeholders