To make decisions easily, we use a weighted scoring model. We can create a weighted scoring model in Excel following some simple steps. In this article, we will show 4 suitable examples. These examples will make you understand the concept of the weighted scoring model. And you will be able to create your model following the examples. So, without further ado, let’s start the discussion.
Watch Video – Create a Weighted Scoring Model in Excel
Introduction to Weighted Scoring Model
A weighted scoring model is a process for choosing the best option based on multiple factors or criteria. Weighted scoring models are used to make the decision-making process easy. To complete different projects successfully, we need to make the right decisions. This decision-making process becomes when there are multiple criteria. In those cases, we use a weighted scoring model.
There are two types of scoring models. One is unweighted and another one is the weighted scoring model. In the unweighted scoring model, the weights of different criteria are the same. But in the weighted scoring model, the weights of different criteria are different.
Steps to Create a Weighted Scoring Model in Excel
To build a successful scoring model, we need to follow some basic steps. The steps are given below.
STEPS:
- First of all, specify the most important criteria related to the process.
- Secondly, assign a weight to each criterion. The summation of the weights should be 100%.
- Thirdly, assign scores to the options.
- Lastly, you need to find the weighted scores. To do so, multiply the weight for each criterion by its score and add them up.
You can easily create a weighted scoring model in Excel by following the above steps. In the following section, we will explain the steps with 4 different examples.
How to Create a Weighted Scoring Model in Excel: 4 Examples
1. Choose the Best Location by Creating a Weighted Scoring Model in Excel
In the first example, we will choose the best location for setting up a production house by creating a weighted scoring model in Excel. For that purpose, we have assigned the criteria and their weights in the dataset below. We have included the numerical scores between 1 to 5 for locations A, B & C. Here, we will calculate the weighted average score. For example, the most important thing to set up a production house is the rent. So, the weight of the rent is also the highest. Location B has the best score for this criterion. It means the rent of Location B is the lowest.
Let’s follow the steps to compute the weighted score.
STEPS:
- In the first place, select Cell D11 and type the formula:
=SUMPRODUCT(D6:D10,$C$6:$C$10)/SUM($C$6:$C$10)- Hit Enter to see the result.
Here, we have used the SUMPRODUCT function to find the sum of the products and divided it by the SUM function to calculate the average.
- After that, drag the Fill Handle to the right to see results for Locations B & C.
- In the end, we can say that Location A is the best option for setting up a production house as it scores the highest.
Note: To determine the weighted score without average, use the formula below:
=SUMPRODUCT(D6:D10,$C$6:$C$10)🔎 How Does the Formula Work?
- SUMPRODUCT(D6:D10,$C$6:$C$10)
This part of the formula calculates the product for each criterion and then adds them up. Generally, it represents the basic formula,
D6*C6+D7*C7+D8*C8+D9*C9+D10*C10
- SUM($C$6:$C$10)
Here, this part computes the sum of the range C6:C10.
Read More: How to Calculate Average Score in Excel
2. Design a Weighted Scoring Model to Rank the Employees
In this example, we will design a weighted scoring model to rank the employees of a company. To explain the process, we will use the dataset below. To rank the employees, we have assigned 4 criteria. The weights of these criteria sum up to 10. The scores of the employees are also distributed.
Let’s pay attention to the steps below to learn the technique.
STEPS:
- Firstly, we will calculate the total score out of 40. To do so, we will use the SUM function.
- Select Cell D11 and type the formula:
=SUM(D6:D9)- Press Enter to see the result.
- Now, drag the Fill Handle to the right.
- In the second step, we need to compute the weighted score.
- For that purpose, select Cell D12 and type the formula:
=SUMPRODUCT(D6:D9,$C$6:$C$9)- Hit Enter to see the result.
- Again, use the Fill Handle in Row 12 for the rest of the cells.
- In the following step, we will calculate the percentage of best. Here, we will use the MAX function.
- Select Cell D13 and type the formula:
=D12/MAX($D$12:$F$12)- After that, press Enter to see the result.
- One more time, drag the Fill Handle to the right.
- In the last calculation, we will rank the employees. To do so, we will use the RANK function.
- Select Cell D14 and type the formula:
=RANK(D13,$D$13:$F$13)- Then, hit Enter.
- Finally, use the Fill Handle to rank the employees. From the results, we can see Tom ranks first.
Read More: How to Calculate Total Score in Excel
3. Generate a Weighted Scoring Model in Excel and Determine the Highest Priority
Sometimes, we face different requirements to make a decision. Here, we will have 3 requirements and find which requirement should get the highest priority. The dataset is quite similar to Method-1. Like the previous datasets, it also contains some criteria, weights, and scores.
Let’s observe the steps below to find the weighted average score.
STEPS:
- In the beginning, select Cell D11 and type the formula:
=SUMPRODUCT(D6:D10,$C$6:$C$10)/SUM($C$6:$C$10)- After that, press Enter to see the result.
- Next, drag the Fill Handle to the right to copy the formula.
- In the end, you will get the results. And from the results, we can say Requirement Z should get the highest priority.
4. Find Weighted Average by Creating a Scoring Model in Excel
In the last example, we will find the weighted average marks of a student. For example, a student has attended some quizzes, exams, and assignments. At the end of the year, we need to calculate his weighted average marks. For that purpose, we will use the dataset below. We can see the weights of the exams and the scores in percentage. The quizzes, exams, assignments, and attendance are the criteria here.
Let’s follow the steps below to complete the calculation in Excel.
STEPS:
- Firstly, select Cell B13.
- After that, type the formula there:
=SUMPRODUCT(D5:D10,C5:C10)/SUM(C5:C10)- Finally, press Enter to see the weighted average marks.
Note: We didn’t use any absolute reference in this formula, because we applied it to a single cell.
Things to Remember
Though the process of creating a weighted scoring model is easy, still we need to remember some basic things.
- Don’t forget to use absolute references. Because we will copy the formula in other cells. If you don’t use absolute references, then, you will get incorrect results or errors.
- Assign the criteria carefully.
- The weights can be assigned in numerical values or percentages.
Download the practice book here.
Conclusion
In this article, we have demonstrated 4 easy examples to Create a Weighted Scoring Model in Excel. Here, we have discussed 4 suitable examples to explain the process. I hope these examples will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
