One of the most frequently used tools in the managerial sector is the scorecard model. If you own a company or run an organization, then it is a must to get an idea about the internal condition of the company and predict which aspects you should emphasize for its overall growth. And for performing all these tasks, a balanced scorecard model will ease your journey to achieve e your goal. If you want to create a balanced scorecard in Excel, then you have landed in the right place. I will show you some easy steps on how to create a balanced scorecard in Excel in this article.

**Watch Video â€“ Create a Balanced Scorecard in Excel**

## What Is a Balanced Scorecard?

The *Balanced Scorecard* system was first developed by Robert Kaplan and David Norton. This is actually a frequently used framework in the sector of organizational management. Every company must have to set a goal and plan successively in order to achieve the goal. So, managers need to track the important issues of the organization to develop strategic performance. The monetarization of strategic initiatives, actions, key performance, and time frames for implementation is highly recommended to achieve this goal.

The overall performance of the organization is found from the *Balanced Scorecard *model and the facts that should be emphasized for the internal growth of the company can be picturized.

## How to Create a Balanced Scorecard in Excel: with Easy Steps

This section will familiarize you with some easy steps to create a *Balanced Scorecard* in Excel.

Letâ€™s say, we are looking at a company that is trying to measure its operational performance. We are considering 4 categories:

- Transportation
- Warehousing
- Inventory Management
- Order Processing

And within each of these categories, we have some strategic goals that we should optimize for overall growth. So, for creating a balanced scorecard model, I will proceed with the following steps below. Letâ€™s check them right now!

### Step 1: Assign Factors for Scorecard Model

First of all, assign the parameters which will denote the performance of the company. I have set these parameters for the model:

- Strategic Measurement
- Weight
- Predetermined Score (50 and 100)
- Actual Condition
- Score
- Fulfillment
- Weighted Score

*Note**: You can add more parameters and proceed with your acquainted strategy based on your strategic plan.*

- Then, assign the strategic goals under each of the categories. I have used my factors like the screenshot below. Define which factors affect your performance and assign them under each category.

### Step 2: Set Weight to the Scorecard

Now, 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 must have to be 100%.*

- After that, assign the percentages of the strategic goals under each of the acting categories. Keep in mind that, just like the categories, the subtotal of each of the factors under the categories should be
**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 must be **25%+55%+20%=100%.**

The same goes for the other categories.

### Step 3: Calculate Score in Model

Now itâ€™s time to calculate the score based on the assigned inputs denoting the strategic goals.

- Here, we have two column headings (i.e.
**50**and**100**). These two actually denote scores for the corresponding inputs.

For example, in the case of the **Damaged Goods Delivered**, if the percentage of the delivered damaged products is **4%** then the score will be** 50 **and if the percentage is **2%**, then the score will be **100**. So, the score will be increased with the decrease of the damaged products.

- Hence, assign the values for the determined scores (
**50**and**100**) for each of the factors.

- Now, input the
**Actual**values of the corresponding factors under each category.

- Now, apply the following formula for one factor (i.e.
**Damaged Goods Delivered**).

`=G6*($F$4-$E$4)/(F6-E6)+($E$4*F6-$F$4*E6)/(F6-E6)`

Here,

**E4**= 50**F4**= 100**E6**= 4%**F6**= 2%

- Hence, apply a similar formula for the other factors to get the corresponding score.

### Step 4: Create the Balanced Scorecard

Now. itâ€™s time to complete the balanced scorecard model.

- Here, apply the following formula to the fulfillment column.

`=H6*D6`

Here,

**H6**= Actual Score**D6**= Percentage of Damaged Goods Delivered

- Now, use
**AutoFill**to drag the formula for the other factors.

- Then, we will use
**the SUM function**to apply the following formula to calculate the weighted score.

`=SUM(I6:I8)*D5`

Here,

**I6**= First cell of the Fulfillment column**I8**= last cell of the Fulfillment column**D5**= Weight percentage of Transportation

- Similarly, calculate the
**Weighted Score**for the other categories.

- Last of all, calculate the
**Total Weighed Score**considering each category by applying the following formula.

`=SUM(J6,J10,J13,J16)`

You can download the practice book below.

## Conclusion

In this article, I have tried to show you some methods of how to create a balanced scorecard in Excel. I hope this article has shed some light on your way of creating a balanced scorecard in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please donâ€™t forget to share them in the comment box.

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