Help Manual


Sigma Magic Help Version 17

Balanced Scorecard


A Balanced Scorecard is used to monitor the performance of Key Performance Indicators (KPIs) 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 them from a holistic point of view when developing the metrics. For example, metrics are defined from the end customers' point of view, 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 that are off-track rather than 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.
Scorecard Input Dialog Box
Num Metrics: Specify the number of metrics you deal with for your process or department. You can specify up to 20 metrics on one tool. If you have more metrics, you must 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 every two weeks), Monthly, and Quarterly. Ensure that the measurement frequency is sufficient to take corrective action 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 calculated 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 monthly but will be fixed for each quarter.
Metric: For each metric, you need to specify the measured metric type. There are four types of metrics: Largest, Nominal, Smallest, and Text. The largest type of metrics is those 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 and 24 degrees Centigrade. If the temperature is too high or too low, it is not optimal for the occupants. The third metric type is the "Smallest." For this metric type, 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 discrete-type metric can only take a limited set of discrete values. An example of the discrete metric is the project phase - say "Plan," "Do," "Check," or "Act."

Thresholds: After specifying the metric type, you need to select the transition points for each metric that will change the color of the actual values. For example, if you specify the most significant 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, and the third transition defines the transition from yellow to green. For example, if the transition points for the most significant 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 ten will be shown in green color if the transition points are defined as -inf, +50%, +100%, +inf. Then, the color transition values depend on the plan values specified on the worksheet. Suppose the plan value defined on the worksheet is +200. Then the transition points are -inf, 100 (50% of 200), 200 (100% of 200), and +inf. So, any 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.
Create Design: If this is your first time using this template, click this button to format the worksheet template. You can also update the worksheet format any time, but remember that you may lose any data entered on this worksheet. Once you are happy with the worksheet template layout, you must enter any required data on the worksheet. When the data entered into the worksheet is complete, you can click on Analysis Setup and then Compute Outputs to generate analysis results.
Analyze Design: Click on this button to save all changes and compute the outputs for this analysis. Review the results of your analysis and make changes to your inputs if required to update analysis results.
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, and 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%, 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


You will see the following dialog box if you click the Checklist button. Checklist The checklist contains a few pointers on things you must 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.
Item: The items on the checklist are listed on the left
Status: The status of whether the checklist has been answered is listed on the right.
Ideally, all items on the checklist should be marked as reviewed/completed.


On the worksheet, for each metric, define the metric's name along with the units, the owner who is accountable for this metric, 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 exact values are updated over time to track the metric's performance. 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 the Verify button, the software will perform some checks on the data you entered. A sample screenshot of the dialog box is shown in the figure below. Verify The software checks if you have correctly specified the input options and entered the required data on the worksheet. The results of the analysis checks are listed on the right. If the checks are passed, they are shown as green-colored checkmarks. If the verification checks fail, they are shown as a red-colored cross. If the verification checks result in a warning, they are shown in the orange exclamation mark, and finally, any checks that are required to be performed by the user are shown as blue info icons.
Item: The left-hand side shows the major tabs and the items checked within each section
Status: The right-hand side shows the status of the checks.
Overall Status: The overall status of all the checks for the given analysis is shown here. The overall status check shows a green thumps-up sign if everything is okay and a red thumps-down sign if any checks have not passed. Note that you cannot proceed with generating analysis results for some analyses if the overall status is not okay.


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 formatting on the worksheet. A sample screenshot of the worksheet is shown below. Balanced Scorecard Example A sample output message that is displayed on successful completion of the analysis is shown below. Project Outputs The scorecard is the main output of the analysis. It would be best to focus on those metrics in red, use problem-solving to identify the leading 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 limiting the number of metrics you should track to less than 20. However, if you have more than 20 metrics, you will have to define the conditional formatting of your worksheet manually.
  • 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 conditional formatting and not for the plan values defined on the worksheet.

© Rapid Sigma Solutions LLP. All rights reserved.