Help Manual


Sigma Magic Help Version 15

Balanced Scorecard


A Balanced Scorecard is used to monitor the performance of Key Performance Indicators (KPI) for any process. The objective of monitoring these metrics is to provide an early warning system to management in case these metrics are going off track. The metrics are balanced if we look at a holistic view in developing the metrics - for example, there are metrics defined from the point of view of the end customers, financial performance, internal business processes, and learning and development. The metrics have to be tracked at a frequency such that there is time to implement corrective actions in case the metrics are going off track. The color-coding of the metrics ensures that management can focus on those metrics that are off-track rather than focusing on all the metrics.

This tool can be added to your active workbook by clicking on Project and then selecting Balanced Scorecard.


Click on Analysis Setup to open the menu options for this tool.


A sample screenshot of the setup menu is shown below.
Scorcard Input Dialog Box
Num Metrics: Specify the total number of metrics you are dealing with for your process or department. You can specify up to 20 metrics on one tool. If you have more metrics, you need to pick the most important ones to track here.
Update Period: Specify the frequency of measurement of the metrics. Currently, the frequency that can be specified is Weekly, Biweekly (once in two weeks), Monthly, and Quarterly. Ensure that the frequency of measurement is sufficient so that corrective action can be taken if the metrics are off-track. Note that only one frequency can be specified for the entire worksheet. If one metric is measured quarterly while another metric is measured monthly, then you need to use the most frequently measured metric as the frequency for the entire worksheet. The quarterly metric values will not change every month but will be fixed for each quarter.
Metric: For each metric, you need to specify the type of metric that is measured. There are four types of metrics: Largest, Nominal, Smallest, and Text. The largest type of metrics is those metrics for which the largest value is the best. An example of this type of metric is "Revenue". Larger revenue is better for the organization. The next type of metric is "Nominal". Nominal metrics are those metrics for which an optimal value exists. Any values that are too large or too small away from the nominal value are not good for the organization. An example of a nominal metric is temperature. For example, the ideal temperature may be between 18 degrees Centigrade to 24 degrees Centigrade. If the temperature is too high or too low, then it is not optimal for the occupants. The third type of metric is the "Smallest". For this type of metric, the smallest possible value is the best for the organization. An example of the smallest metric is the defect rate. Smaller defect rates are better for the organization. Finally, the fourth type of metric is called "Text". This is a discrete type metric that can only take a limited set of discrete values. An example of the discrete metric is project phase - say "Plan", "Do", "Check", "Act".

Thresholds: After specifying the type of metric, you now need to specify the transition points for each metric that will change the color of the actual values. For example, if you specify a largest type metric, the lowest set point is automatically set at -infinity and the highest set point is automatically set at +infinity. The second transition point defines the transition from red to yellow color and the third transition point defines the transition from yellow to green color. For example, if the transition points for the largest type metric are -inf, +5, +10, +inf. Then any actual values less than +5 will have a red color, any values between 5 and 10 will have a yellow color, and any values greater than 10 will be shown in green color. If the transition points are defined as -inf, +50%, +100%, +inf. Then the transition values for the colors depend on the plan values defined on the worksheet. If the plan value defined on the worksheet is +200. Then the transition points are -inf, 100 (which is 50% of 200), 200 (which is 100% of 200), and +inf. So, any actual values less than 100 are colored in red, all values between 100 and 200 are colored in yellow, and all values greater than 200 are colored in green.
View Example: Click on this button to open the example file. You can view the example to get an idea of how to fill out this tool or you can use the example as a starting point and modify it to meet your project needs.
Help Button: Click on this button to view the help file for this topic.
Cancel Button: Click on this button to cancel any changes and exit the dialog box.
OK Button: Save any changes you have made in the dialog box and compute the analysis outputs.
The following table shows how the transition points are defined for each metric type, where the four thresholds that are input for each metric type in the dialog box are t1, t2, t3, t4. If the transition values are defined as a percentage then we take the percentage of the plan value as the transition point. For example, if the input is 95%, then the transition point is 95% * P. Let the plan value defined on the worksheet be P and the actual value on the worksheet be A.
TypeRed ColorYellow ColorGreen Color
Largest A < t2 t2 ≤ A < t3 A ≥ t3
Smallest A > t3 t2 < A ≤ t3 A ≤ t2
Nominal A > t4 or A < t1 t1 ≤ A < t2 or t3 < A ≤ t4 t2 ≤ A ≤ t3
Text A = t3 A = t2 A = t1


On the worksheet, for each metric, define the name of the metric along with the units, the owner who is accountable for this metric, and the plan values for each measurement period, and the actual values for that measurement period. The plan values are typically set at the beginning of the year and the actual values are updated over time to track the performance of the metric. Corrective actions are required if the metrics are going off-track. The conditional formatting is automatically set based on the input worksheet.


If you click on the Checklist button, you will see the following dialog box. Checklist The checklist contains a few pointers on things you need to pay particular attention to while performing this analysis. The checklist has been derived from experience working on past projects. Not all the checklist items need to apply to you. However, you should carefully review the checklist and apply any recommendations that make sense for your project. Make sure to read each checklist item and mark it as completed after you have read and taken actions as appropriate for your project.


Click on Compute Outputs to update the worksheet format. Note that this button has no specific action for this tool since defining the input dialog box also formats the worksheet. This can be used in case you have inadvertently deleted some of the conditional formattings on the worksheet. A sample screenshot of the worksheet is shown below. Balanced Scorecard Example The scorecard is the main output of the analysis. You need to focus on those metrics that are in red and use problem-solving to identify the main causes and define actions to bring the metric back into the green.


Here are a few pointers regarding this analysis:
  • The software only allows you to define 20 metrics. We strongly recommend you limit the number of metrics you should track to less than 20. However, if you have more than 20 metrics, you will have to manually define the conditional formatting of your worksheet.
  • You can always verify if the conditional formatting is correct by clicking on the cells within Excel and verifying the formulae. Note that there are two different types of inputs for conditional formatting. If a percentage value is specified in the input dialog box, the values used for conditional formatting are a percentage of the plan values. If you specify absolute numbers in the input dialog box these are used for the conditional formatting and not the plan values defined on the worksheet.