Using Excel to take better decisions

In this example, I will show you how I used Excel to decide which car to buy.

Step 1: you need to identify a list of cars to choose from (based on price budget, features, etc...)

Step 2: you need to identify the criteria that matters when choosing a car. The criteria I put here are: Price, Options, Space, Safety, Service, Spares, Performance and Resale. Feel free to add/remove other criteria of your interest.

Step 3: you need to give a weight to each criteria so that the higher the weight, the more important this criteria is to you (Check Row 2 in the picture below). For example you may be concerned with the price more than the performance, so price should get a higher weight. Please note that the total sum of all criteria should not exceed 100%.

Step 4: Data collection. This is the most daunting yet most important part of the process. You will need to gather data about the cars you are choosing from. The data should be qualitative and quantitative information about the criteria you are using. You may use Google or other car forum websites to get information about the performance, safety and other features of the models you are choosing from. Of course prices should be equivalent to those of your local market and not just a random search over the internet.

Step 5: After collecting the data, try to give a score from 1 to 5 for each car relative to how it scored against each criteria, where 1 is lowest and 5 is highest (cells D3:K8 in the picture below). For example a car with a high price would get a relatively low score, while a car with better safety or bigger space would get a high score.

Step 6: This is where you need to flex your Excel functions muscles. You need to use "Sumproduct" function to multiply each score to it's corresponding weight and return the sum of the results. This helps you calculate a score (called weighted average) to each car based on it's individual score in each criteria and the importance of this specific criteria to you. The car with a closer weighted average to 5 is the winner.

You may download the Excel template used in this example from this link: 
https://drive.google.com/open…

Now how can you use this technique in other decision making stories?