Decision Aiding Tool – Do a Trade Study

Choose the Best out of a set of attractive Alternatives



Buying a house, car, pet or PC? Choosing among alternative courses of action at work? Do you and your spouse or your colleagues differ on which choice to make? Here is how to get everyone involved to “put their cards on the table” and make a rational choice that will best serve your needs.

A relatively simple Excel-based tool helps you make the right decision, including decisions in the face of uncertainty! It is available for FREE.


Emotion vs Reason vs a Reasonable Balance

All too often our normal human emotionality drives our choices. We choose a house to impress our friends over one that will serve our needs better at a lower cost and less stress on our credit. We pick the car -or beer- associated with flashy commercials although we know -in our heart of hearts- those choices will do nothing to attract shapely women (or handsome men :^). We buy the “sizzle” rather than the steak!
Your husband wants house A and you want house B and you go round and round speaking past each other without really understanding why you disagree and what the real motivations are – including your own! You want your company to buy the latest and greatest technology but management wants to minimize risk. Are you simply motivated by the bragging rights of cutting edge novelty? Do they have luddite sensibilities when it comes to computers? 
A reasonable balance between reason and emotion may be achieved by doing what is called a “Trade Study” among alternatives using a Decision tool. Here is one that is available free and runs on your Excel spreadsheet. Download it at:

Rational Decision Making

Rational decision making will reduce the level of emotionality -including your own- and it consists of three easy steps and one hard one: 1) identifying a range of attractive Alternatives, 2) researching each Alternative and gathering factual data, 3) picking a number of Evaluation Factors, and, 4) agreeing with your spouse or colleages on the relative importance (“weighting”) of the Evaluation Factors.
One advantage of a rational Trade Study process is that you can think about each item in relative isolation. For best results you should resist the temptation to slant the evaluation criteria or weighting to favor the alternative you love. When you choose a parameter, assume “all else is equal” (even though you know it is not). These are the steps to follow in the Trade Study process:

1) Identifying a range of attractive Alternatives

All too often we may “fall in love” with one Alternative to the point we ignore the rest. We dismiss any suggested alternative by over-emphasizing the advantages of the one we love. This is a natural human emotion but it is toxic when our spouse or business associates happen to love a different Alternative.
Therefore, the first step is to make a list of attractive Alternatives. If the one you love is so great you have nothing to fear from the others, right? RIGHT!
It is a good idea to have five to nine Alternatives. If you have fewer than four you probably have not considered the issue enough. If you have more than ten you need to group them by category and first do a Trade Study to pick the category that is best. Then do a second Trade Study on the Alternatives that are in the winning category.

 2) Researching each Alternative and gathering factual data

Rational decisions are made on the basis of facts. You need to know the cost and other parameters of each Alternative. In some cases you will be able to gather specific values from a catalog or web page or other source. However, some parameters will be clouded by a degree of uncertainty. For example the asking price for a house is likely to be higher than what you can get it for. The EPA miles per gallon for a given car is a specific number, but “your mileage may vary”.
You and your spouse or colleagues need to agree on the factual data. Without that data you are not ready to do a Trade Study. Try to get the best information available, but don’t worry too much about uncertainty at this point in the Trade Study. The Decision Tool can handle it.

3) Picking a number of Evaluation Factors

Cost may not be everything, but it is way ahead of whatever is second! In almost every Trade Study, Cost is the first (and often also the second) Evaluation Factor.

The other Evaluation Factors depend on the particular Trade Study item. Evaluation Factors are quite varied but may be grouped into: a) Performance, b) Risk, c) Schedule, and d) Other.
It is a good idea to have five to nine Evaluation Factors. If you have fewer than four you probably have not considered the issue enough. If you have more than ten you need to group them by category. 

Cost Factors – Non-Recurring 

There are at least two kinds of cost, The first is called “non-recurring” because it is the initial acquisition cost – what you have to pay upfront for the item, or the investment your company will have to make to design and build the first prototype and set up the production line, etc. That is a one-time cost, but you will have to come up with the money right away so it could be a “show stopper”. For example, you have to make a downpayment on a house and if you do not have the cash on hand you cannot buy the house. In a business situation, your company will have to pay designers and engineers and computer specialists and so on to develop a particular product or service, During that time the money will be going out and nothing will be coming in.
Generally, if you are the purchaser, lower Non-Recurring Cost is the best.

Cost Factors – Recurring

The second kind of cost is called “recurring” because it must be paid on a monthly or yearly basis for the life of the item. In the case of a house it is the taxes, fuel, maintenance, and mortgage. In the case of a business venture it is the rent, raw materials, and payroll, etc. This is a cost that will be paid over time and it may be easier to pay in the future. For example, your income is likely to increase over time. In the case of a business investment, it is likely to pay off over time.
Generally, if you are the purchaser, lower Recurring Cost is the best.

Performance Factors

Performance Factors include size, weight, speed, variety, comfort, style, etc. Many Performance Factors are measured numerically: number of square feet of living space and number of bathrooms in a house; number of passengers, luggage capacity, and miles per gallon of a car; size, weight, screen dimensions, and processing speed of a laptop computer; number of channels available from a cable or satellite service, etc. Other Performance Factors such as comfort and style may be rated on a 1..10 scale.
Some Performance Factors are best when larger, some are best when smaller, and some are best when at a specific intermediate value. Remember, in our Trade Study we consider each variable individually, assuming “all else is equal”. At this point do not worry about the fact that an eight-passenger automobile will get poorer gas mileage than a four-passenger, or that a house with ten rooms will cost more than one with only five. The Decision Tool will take care of that. For now, just consider each Performance Factor on its own. Would you rather have an eight-passenger car and a ten-room house if all else was equal. You bet you would!
Generally, size, speed and capacity are better if they are larger. All else being equal, you would rather have a laptop with a larger screen, an HDTV that is as big as possible, and a car that can get 60 miles per gallon.
On the other hand you would like that laptop to be as light as possible and fit into your briefcase, which will limit screen size. You would like your cellphone and PDA to have maximum storage capacity and speed, but also be small enough to fit in your pocket.
An intermediate value is best for things like the thickness of a credit card – too thick would use up too much space in your wallet, too thin would make it more likely to bend and crumple. Similarly, an adult dog’s height and weight should be neither too high nor too low, etc. 
Some Performance Factors cannot be quantified in inches or pounds or any other standard measure. Use a numbering system of 1..10 for things like comfort, ease of use, etc., with the higher numbers signifying better performance.

Risk Factors

How well do you know the manufacturer or developer of the product or service you are considering? Vendor reputation is critical. If you are making a business decision, some Alternatives may have high potential payoffs but also a higher probability of failure.
Risk is best when it is low. You may be able to quantify risk in dollars as the probability of occurance multiplied by the cost if it does occur, which is called the “expected value” of a given risk. Risks with low probability of occurance, and low cost if they do occur, may usually be ignored. However, risks with a high probability of occurance must be consider even if their cost impact would be low, and, risks with high potential costs must be considered even if they have a low probability of happening.  
If you cannot quantify risk in terms of dollars, you may rate vendor reputation on a 1..10 scale, with the higher numbers signifying better reputations and therefore lower risk.

Schedule Factors

Is a given Alternative available immediately? If you order it now will it be delivered when you need it? Sometimes it is better to settle for a “spec” house that exists than one that will take six months to build. In a business situation, sometimes it is better to settle for a solution that will be in place and earning profits in six months rather than wait for a better solution that will take two years to implement.
In general, the shortest schedule is best. On the other hand, if you know you will not need the item until a particular future date, an intermediate value -the need date with a bit of margin- is best. Schedule is quantified by the number of days, months, or years it will take for the item to become available.

Other Factors

If you are familiar with the decision domain for the Trade Study you are conducting, you should be able to come up with some Evaluation Criteria specific to that domain. If you do not know what the Evaluation Criteria are, you are not ready to do a Trade Study. Make sure you agree on how you will quantify each Evaluation Criteria and whether lowest, highest, or some intermediate value is best.

4) Agreeing on the relative importance of the Evaluation Factors

Here is where the Trade Study process may get sticky!
You may think the size and resolution of the HDTV set you are eyeing is 70% of the issue. Your spouse may think furniture style, or price, is more important. In a business situation, you may want to revamp the whole process and totally automate and computerize everything. Your boss may have been the originator of the current manual process and be suspicions of anyone who wants to throw out something he loves. He or she may be frightened of computers, and for good reason based on bad experiences with drastic changes that cost way more than expected and did not work as advertised.
Agreeing on the relative weights of each Evaluation Factor will force you and the others to “put your cards on the table”. A good way to start is to have each person allocate percentage points to each Evaluation Factor such that they sum to 100. If your percentages are close on some Evaluation Factors, come to agreement and fix those items! Then take the remaining percentages and re-allocate them and continue the process.
If you cannot agree on the relative percentages for some Evaluation Factors, that is OK. At least agree on a range for them. You can use the Decision Tool multiple times with different sets of weighting factors and you may be pleased (or not) if the results are robust (or not) to reasonable changes in weighting.

An Example is Worth 1000 Words

Here is a simple example to demonstrate how easy the Decision Tool is to use.
Say I want to buy a pet dog. I have had a Golden Retiever in the past but I want to consider three other breeds my neighbors suggested. I am most familiar with Golden Retrievers and have had good luck with a local breeder, so information on that breed has the highest level of certainty. I have checked the other breeds out on the internet and with my neighbors who have recommended them, so I feel I have enough information to do a preliminary Trade Study, but there is a considerable level of uncertainty in that data. Fortunately the Decision Tool can handle uncertainty!

Fill in the SETUP Sheet

The first Excel sheet is for SETUP. Part of that sheet is shown below. [Double-click figure for larger view.]

Note that I entered the name of the Trade Study “Buy a Pet Dog” along with my name and date in the spaces provided at the top of the sheet. I then entered the names of the Evaluation Factors, indicating units that will be used to measure them (we don’t want to use pounds for the Golden and kilograms for the German Shepherd!)
The next column is the relative Importance Percentage for each Evaluation Factor. The Decision Tool will add the numbers in this column and tell you they add up to 100 and are OK, or if they have to be adjusted.
The next column informs the Decision Tool what is “Best”. Just type “L” if lower is Best, “H” if higher is Best, and “I” if an intermediate value is Best. If you type “I” for a given Evaluation Factor, the Decision Tool will prompt you to enter that Best value where indicated.
The right-hand part of the SETUP sheet is shown below.
Enter the names of the Alternatives in the indicated column. Then, type “Y” in the clear column if you want to include that Alernative in the given Trade Study. If you decide to run the Trade Study multiple times, you may wish to exclude some of the Alternatives on some of the runs. To exclude an Alternative, simply type “N” in the clear column.

Fill in the DATA Sheet

The second sheet of the Excel workbook is where you enter that data you have been collecting about all the Alternatives. [Double click figure for larger view.]
The Decision Tool will have copied the name of your Trade Study and the names of the Alternatives and Evaluation Factors. All you need to do is type your data (or estimates if you do not have exact data) into the clear cells.
When you enter a value into the “Lo” column, it will automatically be copied for you into the “Hi” column. That will save you the trouble of retyping data that has no uncertainty. If the data is uncertain, just type the lower number into the”Lo” column and the higher number into the “Hi” column.
As you enter the data, the Decision Tool will keep track of the highest data number and lowest data number for each Evaluation factor and those values will appear in the far left columns. Depending upon whether you said highest, lowest or intermediate was Best (on the SETUP sheet), the Decision Tool will display the Best value.
TECHNICAL NOTE (Read only if you really want to know how sausages are made :^): The Decision Tool uses a linear interpolation to score the data you type into the cells. For example, if you said highest was best, the Decision Tool will assign a value of 1.0 to the highest number of any of the Alternatives for that Evaluation Factor, and a value of 0.0 to the lowest. Any number between highest and lowest will get a value between 1.0 and 0.0, according to how close it is to the Best. If you said lowest was best, the Decision Tool will assign a value of 1.0 to the lowest number of any of the Alternatives for that Evaluation Factor, and a value of 0.0 to the highest. If you said intermediate was Best, the Decision Tool will assign a value of 1.0 to any of the Alternatives that have that exact intermediate number you specified for that Evaluation Factor. It will assign a value of 0.0 to the lowest and highest numbers. Any number between highest and Best or between lowest and Best will get a value between 0.0 and 1.0, according to how close it is to the Best. If you wish to force the range to be other than the highest and lowest numbers in the entered data, just make a dummy Alternative (say Alternative J) and make the “Lo” and “Hi” values for the desired Evaluation Factors for that Alternative the lowest and highest for the range you want to force.
The Decision Tool interpolates between the Best and the worst for a given Evaluation Factor to determine the value for a given Alternative. It then multiplies that value by the Importance Percentage for that Evaluation Factor and computes a composite score for both the “Lo” and “Hi” data estimates for that Alternative.

Look at the RESULTS Sheet 

Part of the third sheet is shown below. It contains a graphic representation of the Results!
The horizontal white bar shows the average score for the given Alternative and the vertical black line indicates the limits of uncertainty for that score. Note that the Golden Retriever has scored best of the four Alternatives, as expected. It has a relatively small amount of uncertainty. The German Shepherd is fairly close behind. Perhaps I should do some more research and get the best data possible on that breed. The Standard Poodle is somewhat lower on the graph and has quite a bit of uncertainty. The Border Collie is still lower. 

How Robust are the Results?

The Decision Tool is only as good as the SETUP information and the DATA entered into it. Let us say your spouse was in favor of the German Shepherd due to increasing crime in the vicinity. The Trade Study gave equal weight to “Temperament” and “Protection”. (Temperament is a measure of how friendly and interactive the dog will be around your children and grands and your neighbors. Protection is a measure of how aggressive the dog will be to an intruder.) Given increasing crime, perhaps Protection deserves a higher weight and “Temperament” lower? Well, if you boost Protection up from 15% to 25% and Temperament down from 15% to 5%, sure enough, the German Shepherd pulls ahead! See the new Results below:
The modified results show that the Gernam Shepherd is almost certainly the winner if Protection is very important. On the other hand, you might argue that an upgraded lock and alarm system would do the Protection job better than any dog. Another argument is the considerable liability if the German Shepherd took a bite out of a neighbor’s child – or one of your own.


The real value of a Trade Study and use of the Decision Tool is to reduce the emotional content of decision making and replace it with a data-driven decision. There is no guarantee that a Trade Study will satisfy all stakeholders. Indeed, others may modify your Trade Study as in the example above to change the winner.
The benefit is that any changes will have to be justified by some reasonable explanation, and that may be countered by a different reason-based argument. Any of the above dog breeds could be justified depending upon different circumstances of the prospective owners, which is why different people buy different breeds of dog and different brands of automobile and so on. A Trade Study Decision Tool is just that -a tool- and not the ultimate solution to all your relationship  problems!
Please feel free to download the FREE Decision Tool available at: