# How to Create a Balanced Scorecard in Excel (with Detailed Steps)

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:

1. DamagedÂ  Goods Delivered (25%)
2. On-Time Delivery (55%)
3. 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)`

You can download the practice book below.

<< Go Back to Scoring | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF