Spreadsheet vs. Specialized Scorecard Software

When a company wants to build and automate a business scorecard, it decides between good old Excel and specialized software. Spreadsheets (like MS Excel or Google Sheets) don’t require any long training and are normally installed on many computers; at the same time, professional scorecard software provides some attractive functions, but implies additional cost.

The content of the article:

• Excel vs. Specialized Software: where Excel wins, risks, where specialized software wins
• Opinions: what experts say; what users say
• Practice: video tutorials on how to create scorecard in Excel
• Migrating from Excel: things to know before migrating from Excel-based scorecard to specialized software

The purpose of this article is to analyze the case where Excel wins and where the specialized software might be a better choice. If you have any additional ideas that will help business professionals to solve a dilemma, feel free to share it in the comments.

Where Excel Wins

A spreadsheet (like MS Excel) is an excellent business tool. We can find it on many computers and it is also available in the Cloud. When it comes to the scorecards/dashboards/KPIs, it has certain benefits and application areas.

1. No need for additional investments

If you don’t have a budget for any new software, then Excel is an excellent choice for an automation task. In a few clicks, one can build a basic scorecard with all the necessary details for the KPIs, including target values, initiatives, and business context. With some tricks a basic scorecard in Excel can be created even faster.

2. Single-user scorecard

If a person has a “black belt” in Excel and he/she will be the only user of the scorecard, then Excel is also a good choice. Use of the formulas, tabs, and cross-links will help to present all possible performance data and analyze it.

3. Creating a prototype

There is no “one size fits all” performance measurement and management solutions. Before investing in automation software, it’s a good idea to try various approaches and understand which one fits better to the needs of the organization. Spreadsheet software is a good test ground for such experiments. The prototype scorecard can later be moved to specialized tools like some of our users did.

Risks of Having Scorecards in Excel and How to Avoid Them

Now, let’s focus on some typical risks of having a scorecard in a spreadsheet. Some of them were mentioned by our users in the case studies.

1. The risk of spaghetti-style scorecard

While any specialized scorecard software dictates certain approaches to the scorecards, the spreadsheet gives us absolute freedom. In some cases, the result of this freedom is a spaghetti-style scorecard with many cross-links between hundreds of metrics and several tabs. It probably will work for a time, but the next actualization might become a nightmare. If you decide to use a spreadsheet, make sure you keep the structure of the KPIs simple.

2. Historical data is hard to manage

How does the customer retention change over time? Why are sales high this June? Do we grow faster than the market? Analyzing trends is a great source of business insights. The problem is that the spreadsheets were simply not designed to keep historical data.

Off course, there are some turnarounds like cloning the column before entering new data, keeping historical data on a separate tab, or in a separate spreadsheet. These tricks will work on a small scale, but for larger scorecards, the spreadsheet will soon become a huge monster that makes it hard to analyze performance over time. Think about the way historical data will be stored beforehand.

3. Strategy map is frozen

A good business scorecard should include a strategy map that explains the context behind the KPIs. Those who create scorecards in Excel can use build-in drawing functions or create a strategy map in the software for the presentations, like in Power Point.

The visual picture in this case might look very professional, but changing any detail will be a headache. That’s why those visually appealing strategy maps normally remain untouched until the next annual performance review. One way to win this game in Excel is to replace the visual map with a data table or text-based map, making it less attractive, but more editable.

4. Time consuming KPI normalization and weighting

In most cases the values of the indicators are measured on different scales, and for the purpose of further analysis, they need to be adjusted to the common scale. For example, if the “email response time” indicator is measured in hours, it needs to be compared with the “satisfaction rate” indicator measured in %, and then we need to normalize “email response time,” for example by presenting its value on the ¹ scale, and calculating its performance respectively.

Few weeks after the implementation the end users will probably actualize their requirements. The typical ones are:

• The measurement scale for the indicators can change over time
• Consider cases like “less value means better performance”
• Indicators might contribute to the overall performance with the different weights

It’s not a rocket science, and the math is simple, but it is a time-consuming task that can be easily automated.

5. The need to agree on the workflow

Most business professionals are familiar with Excel, so there is no need to train them for the basic operations. However, Excel is not a tool specialized in scorecards, so it doesn’t provide any fixed workflow for the scorecard creation and data entry. Organizations need to define and communicate to the teams how to use a scorecard-related workflow: how to add new KPIs, how to update and validate data, how to reflect changes on the strategy map, how to report on the performance, etc.

As the analysis of the history of software shows, the trend is towards specialized tools.

Where Specialized Scorecard Software Wins

With a specialized software users can expect automation of various aspects of the business scorecards. In the beginning any automation tool will provide a framework to build a scorecard, describe KPIs, and enter performance data.

1. Multi-level KPIs

Imagine a situation when you need to measure “Customer satisfaction, %” that correlates with the “Response time” and “Response quality,” and those two indicators need to be further drilled down. Such multi-level structure is possible in Excel, but maintaining it might be difficult. With the automation software, the multi-level scorecards are much more user-friendly.

2. Strategy map with cause-and-effect links

Unlike presentation software, most automation tools for the scorecards can build a live strategy map where the current performance data is visualized. Some visually appealing templates are already available in the tool, and even users without any design skills can create professional maps from scratch.

If you don’t need a strategy map, then probably, you are looking for a KPI software, but not for a Balanced Scorecard tool.

3. Team work and access management

Under the umbrella of “team work,” automation software allows you to maintain data in the Cloud, assign access rights to the scorecard and indicators, and send notifications to the team members.

All this is doable in Excel, for example, by placing a spreadsheet in the Cloud or by exchanging emails with attached files one can achieve the same result, but by spending much more time.

One thing that limits the possible application of Excel spreadsheets is how the historical data is treated. Anyone with a right to modify a document can not only introduce the new information but also change the historical data. In this way, the manager cannot be sure that the information stored in the spreadsheet was not manipulated. The spreadsheet software simply was not designed for this purpose.

Sign-up with a free plan at BSC Designer for immediate access to 31 scorecard and KPIs templates.

In a specialised software, like BSC Designer, you can limit the rights of users to modify historical or future data. Moreover, you can decide what data is considered as historical. For example, you can adjust the workflow to allow editing of data that is no older than 30 days, and modify values for the nearest future only, let’s say 3 days from today’s date.

4. Keeping scorecard up to date

Specialized automation tools will make data actualization easier, and in many cases, will help to automate the data entry by fetching required information directly from the database.

5. Cascading/alignment

Business scorecards are not for the top managers only. The important business goals need to be aligned with the goals of other business units, and respectively those business units need to be actively involved (in practice, there are different ways how organizations do this). The specialized scorecard software will provide their users with one or another way to do cascading.

Check out free examples of the Balanced Scorecards with KPIs, strategy maps, and dashboards created with BSC Designer software.

What Experts Say

We have interviewed some practitioners of the strategy scorecards, and among other questions, we asked their opinion about automation tools. Here you have some quotes:

From the interview with James Creelman:

Start with office tools, etc. After a year or so, migrate to a scorecard automation tool. Automation enables a company to get the best out of the scorecard – progress tracking and reporting, best practice sharing, etc.

From the interview with Jeroen De Flander:

I would suggest you either choose a standard software solution and change your process or postpone automation until you are 100 percent happy with the underlying process.

From the interview with Ignacio Castillo:

I must say with great pride that the only tool we work with is BSC Designer and we implement it with all our clients, and the version they prefer is in the Cloud.

From the interview with Dan Montgomery:

Good visualization is necessary to create a shared strategic perspective amongst your team…

You need a common database of valid information, with a customizable front end that makes it actually useful to an individual, and good presentation tools to support conversation and decision-making.

Spreadsheets, by design, don’t do a good job of this.

From the interview with Gavin Lawrie:

In our view, a well designed Balanced Scorecard comprises a selection of about 20 financial and non financial measures, reported quarterly.  The idea that you might need specialist software to support this activity for a single Balanced Scorecard is laughable.  Our view is that specialist software is not required (and is sometimes a distraction) for reporting a single Balanced Scorecard – the software is typically expensive compared to the alternatives (e.g. Excel) and usual not much more functional.

However if you have to report many Balanced Scorecards in a period (more than five is our current thinking), automated reporting solutions become more attractive.  It really depends on your organisation though, and there are no reliable hard-fast rules you can follow.  What is clear however, is that none of the (about 100) available software solutions will help you design a Balanced Scorecard – they are simply automata that make reporting a Balanced Scorecard design easier (regardless of what the vendor might say).

What Users Say

Some users of BSC Designer shared their experience related to moving from scorecards created in spreadsheet software to BSC Designer.

We find BSC Designer a very ergonomic and intuitive tool. It quickly adapts to your way to carry out the information analysis. It is simple, fast and effective. BSC Designer helps to avoid using and maintaining home-made Excel sheets and produces professional results. Read more…

Emmanuel Jaunart, founder of Mielabelo, Belgium

We have a very good experience with BSC Designer, as this instrument can facilitate the implementation of the Balanced Scorecard in our unit. We have been trying to design a MS Excel spreadsheet but this approach proved to be more difficult than anticipated. BSC Designer, therefore, is very welcome attempt at an electronic version of the Balanced Scorecard. Read more…

  Pierre de Montfort, SA Army ADA School, Kimberley, South Africa

I was searching for a tool, which would help me to build an overall index for Supply Chain department performance. BSC Designer seems to be the right tool – easy to use and also to share with others. So far, I was trying to prepare something similar in MS Excel, however it was too complicated to update with new data. I will see if your BSC Designer will satisfy all of my needs. Read more…

Jiri Kratochvil, Manager Supply Chain Projects, PMI, Czech Republic

How to Create Balanced Scorecard and KPIs in Excel

Video tutorials: Scorecard in Spreadsheet Software

How to Migrate From Excel-based Scorecard to Specialized Software

We discussed the PROs and CONs of having a strategy or KPI scorecard in Excel. Let’s discuss how to migrate from such scorecards to the scorecard maintained in a specialized software.

Absolute Weights vs. Relative Weight of Indicators

In many scorecards, the indicators are sorted according to their weight. A weight shows the impact of the indicator on the scorecard performance.

When moving from Excel to a specialized tool, make sure that the concept of weight used on your old scorecard matches with how it is calculated in the software.

There are two approaches to the calculation of weight:

• Using absolute weight
• Using relevant weight (recommended)

What’s the difference? Let’s say you have a scorecard with several levels like this:

• Goal 1
  • Goal 1.1.
    • Indicator 1.1.1
    • Indicator 1.1.2
  • Goal 1.2
    • Indicator 1.2.1
    • Indicator 1.2.2.
• Goal 2
  • Goal 2.1.
    • Indicator 2.1.1
    • Indicator 2.1.2.

With the “absolute weight” approach, you need to define the weight of all indicators. For example, you can say:

• The weight of indicator 1.1.1 is 10%
• The weight of indicator 1.1.2 is 15%
• The weight of indicator 1.2.1 is 5%
• The weight of indicator 1.2.2 is 20%
• …

The problem with this approach is that you basically need to be able to give a quantitative estimation of how all indicators on different levels are compared to each other. When you have just a few indicators, it’s possible, but when you have more than 12 indicators, those estimations won’t make any sense.

With the “relevant weight” approach, you need to decide on the importance of the item compared to the importance of another item on the same level.

In other words, you can say:

• “Indicator 1.2.1” has a weight of 30%, while indicator “1.2.2” has a weight of 70% – you compare indicators on the same level, not all the indicators on your scorecard

Then you continue with other groups and levels:

• What goal is more important? Goal 1.1 or Goal 1.2? To answer this question, you can define the weight for each of these goals.

Finally:

• What goal is more important? Goal 1 or Goal 2?

Knowing all that data, it’s possible to calculate the impact of each of the indicators on the overall performance of the scorecard (the absolute weight!).

If you are interested in the math that stands behind these calculations, check out the weight section of the scorecard calculation article.

As you can see, the “relevant weight” approach is recommended. This approach scales without problems, and the weight assignment is done in a natural way.

In BSC Designer, we follow the relevant weight approach for the indicators. You can specify the weight for each item on the “Context” tab.

Sign-up with a free plan at BSC Designer for immediate access to 31 scorecard and KPIs templates.

As for the absolute weight, it is useful when you need to have a global look at your scorecard. In BSC Designer, you can visualize absolute weight on a special “weight” chart.

Revise the Definitions of the Indicators

Look at your scorecard in a spreadsheet file or a text document.

Do you have indicators like “Increase customer satisfaction from 70% to 80% within the next quarter”?

Such indicators are actually a mix of:

• An indicator (“Customer satisfaction”)
• A current value (70%) and a target value for the indicator (80%)
• A time frame (1 quarter)
• Optimization direction (“increase”, not “decrease”)
• Measurement units (“%”)

While this S.M.A.R.T. approach works for scorecards managed manually, when moving to the automation software, we would need to organize things a little bit.

But that’s exactly what the goal of automation software is! Right? We talk about this in detail in the full guide to KPIs article.

Let’s define all these nuances in BSC Designer:

• On the “Data” tab, we can define the current value (“Value” field), the 80% target (“Target” field).
• On the “Performance” tab, we define that we want to increase the value of the indicator, not decrease it.
• On the “General” tab, we define the name of the indicator and its measurement units.

Sign-up with a free plan at BSC Designer for immediate access to 31 scorecard and KPIs templates.

Now, let’s click on the “Values editor” button to adjust the indicator even more:

• We can set the update interval to “Quarterly”, and
• Set target value for the next quarter to 80%

Sign-up with a free plan at BSC Designer for immediate access to 31 scorecard and KPIs templates.

If you plan to move your old scorecard to a specialized software, then the data import function will make the transition a lot easier.

Attributes of a Goal: Rationale, Initiatives, Commentaries, Budgets, Owners

Let’s talk about other attributes of a well-described strategy.

If your old scorecard was designed correctly, then besides the goals/KPIs, you will have some of these:

• Description – a more detailed explanation of what the goal is all about and how a KPI will be calculated.
• Supporting documentation – a more detailed explanation of the rationale – the reasons behind the goals, the choice of the indicators.
• Initiatives – specific action plans aligned with the goals and KPIs.
• Commentaries – some date-specific notes made by your team that would explain the fluctuations of the indicators and suggest corrective actions.
• Other attributes like budget, timeline, and owners. 

I personally find the commentaries to be the most valuable part of the strategy execution process.

That’s the way a team notes down some important thoughts that later could be a clue to the refactoring of the strategy.

In the specialized software. the goal attributes remain. For example, in BSC Designer:

• The name of the goal and its description is defined on the “General” tab.
• The initiatives help to capture all other details – the commentaries, the budget, the timeline, the owners, and the link to the supporting documentation.

Sign-up with a free plan at BSC Designer for immediate access to 31 scorecard and KPIs templates.

What’s the benefit of specialized tools, in this case? The data is stored in a more organized way. For example, we can easily align several different initiatives with a goal, decide the status of the initiatives, and automatically visualize them on the strategy map.

Similar to the KPIs data, if you already have your initiatives in a spreadsheet, you can import them using the data importing function.

Cascading: More People Can Be Involved

There will be changes on the organizational level as well. With the scorecard in a spreadsheet software, only few people could have effective access to the data. This normally results in a specific way to organize the cascading of the scorecard.

If you had a manually maintained scorecard, then, most likely, your cascading scheme was something like case 5 that we discussed among other cases of cascading.

With scorecards automated by specialized software, you can shift to a more adequate cascading approach. Something like it was discussed in case 11.

This is achieved by flexible access rights that can be specified in the software. For example, limiting access to the scorecards, indicators or historical data.

The benefit of this change is that more people could collaborate on the same strategy. I’m not just talking about updating KPIs with fresh data but about each department having their own detailed strategy that is connected to the top level strategy.

Backward Compatibility: Reporting of the Scorecard

With specialized scorecarding software, you can establish a new way of gathering and processing the data.

Still, some members of your team will prefer to see the reports in the old format. This requirement especially makes sense during the transition phase.

The solution, in this case, is to use the specialized software and prepare respective reports beforehand.

Sign-up with a free plan at BSC Designer for immediate access to 31 scorecard and KPIs templates.

For example, in BSC Designer, you can create different custom report profiles to fulfil this idea. The reports can be presented online or downloaded as PDF or Excel.

Expected Results

As a result of the transition from a manually maintained scorecard in spreadsheet software to a scorecard automated with a specialized tool, you can expect more transparency and organization in the scorecard.

For BSC Designer, we have a free plan for small projects that you can sign up with and see how it fits your workflow. As always, our team will be glad to assist you.

Conclusions

Spreadsheet software like MS Excel is a good choice for one-user business scorecards with few indicators. It helps to play with the data and sort processes out before starting to use specialized software. Maintaining and sharing large scale scorecard is time consuming with spreadsheet software.