Watch Video – Create a Balanced Scorecard in Excel
Step 1 – Assign Factors for the Scorecard Model
- Assign the parameters which will determine the performance of the company. These are the parameters for this example:
- Strategic Measurement
- Weight
- Predetermined Score (50 and 100)
- Actual Condition
- Score
- Fulfillment
- Weighted Score
- To assign strategic goals under each of the categories, first define which factors affect performance for each category.
- Add them to the scorecard.
Step 2 – Set Weight to the Scorecard
- Set the percentages of the categories (i.e. Weight) that affect the performance of the organization.
Note: Make sure that the total weight of the categories equals 100%.
- Assign percentages to the strategic goals under each of the categories. Just like the categories, the subtotal of each of these should equal 100%.
For example, Transportation has 3 factors:
- Damaged Goods Delivered (25%)
- On-Time Delivery (55%)
- Average Costs Per Order (20%)
The total of these factors is: 25%+55%+20%=100%.
Step 3 – Calculate Score in Model
- The next two column headings (E4 and F4) denote scores for the corresponding inputs. In this example, the score will increase with the decrease of the damaged products.
- Assign values for the determined scores (50 and 100) for each of the factors.
- Input the Actual values under each category.
- Choose the first Actual value (G6) and apply the following formula:
=G6*($F$4-$E$4)/(F6-E6)+($E$4*F6-$F$4*E6)/(F6-E6)
- E4= 50
- F4= 100
- E6= 4%
- F6= 2%
- Use the Autofill Tool to copy the formula to the remaining cells.
Step 4 – Create the Balanced Scorecard
- Apply the following formula to the fulfillment column (I6):
=H6*D6
- H6= Actual Score
- D6= Percentage of Damaged Goods Delivered
- Use the Autofill Tool to copy the formula to the remaining cells.
- Use the SUM function and the following formula to calculate the weighted score:
=SUM(I6:I8)*D5
- I6= First cell of the Fulfillment column
- I8= last cell of the Fulfillment column
- D5= Weight percentage of Transportation
- Use the formula to calculate the Weighted Score for all the other categories.
- Calculate the Total Weighed Score with the following formula:
=SUM(J6,J10,J13,J16)
Download Practice Workbook
You can download the practice book below.
<< Go Back to Scoring | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi
It is quite amazing to know about balanced score card.
i am a bit confuse about 50 100 Actual , The next two column headings (E4 and F4) denote scores for the corresponding inputs.
If you can kindly elaborate a bit more.
thanks
Hello Adnan,
You are most welcome. Thanks for your appreciation. The “50” and “100” columns represent performance benchmarks. The “50” column typically indicates a baseline or minimum acceptable performance, while the “100” column represents the ideal or target performance level. The “Actual” column shows the real performance values. The “Score” column reflects how close the actual performance is to these benchmarks, and it is multiplied by the corresponding weight to calculate the “Weighted Score.” The total weighted scores are summed to determine overall performance (82 in this case).
Regards
ExcelDemy