Photo by Dan Lohmar on Unsplash.com

One of our clients said

“I think working with this type of complex methodology with so much data cannot work in spreadsheet. That increases the risks of error”

We have seen this with every team we have onboard to Probench who used to calculate the score using spreadsheets. See if you feel the same pain while using the following steps:

1. You pick one researcher who has worked little in excel or spreadsheet. You ask her or him to create the scorecard and create the complex formula and statistical formula.
2. The person sometimes out of no choice or inclination to help the team takes the job. They often don’t like it and find it too much of work.
3. She/he creates a Big Sheet which contains all the required calculation and formula to formulate the score card.
4. Someone from the team copies all the data from the spreadsheet to the big sheet or calculation sheet. Each row or column at a time and makes sure the data is not offset by one (1) to the label against which it is to be mapped.
5. The formula that was created in step 3 will execute and bring the result sets.
6. Then it will be manually copied to another spreadsheet to create the scorecard or manually copied to a word document to format a scorecard.
7. When you discover there is a score mistake in methodology, or you have learned something new which changes the weightage. You have to recalculate the entire calculation for all participants.

Few of the steps above would have been automated using macros in a few instances. However, overall this will be a painstaking task.

Solution

Software which automates the scoring algorithm. Once configured correctly, it should work for all. We at 73bit handle this task at 2 levels.

Default Scoring

Surveys can be structured in the form of tree with nodes in it. Each “node” in this survey can be assigned a score. By varying these values, questions can be weighted in relation to each other. Based on the score value of each node and the responses of the users the score is calculated each time a user saves the responses. Due to the tree structure, the scores of the child nodes are rolled up to the parent. So we suppose that based on the user’s response, if a child node scores 50%, then the score of the parent will automatically reduce by 50% depending on the weightage of the child question.

The scores can also be updated long after the surveys are complete. This is often useful, as new interpretations may arise during the auditing process. When the scores are updated after the submission of the survey, then we have a way where we can recalculate scores for each of the participants on just one button click.

This saves the manager from a lot of rework and makes the process error-free saving a lot of time providing confidence on the outcome.

Custom Scoring

Sometimes due to the complex methodology, the scoring is not as straightforward as mentioned above. There may be many complex calculations involved like in the case Access to Nutrition Foundation where their methodology involved something called Healthy Multiplier and Global Multipliers. These multipliers are basically there to incentivise the companies based on their policies and add these multipliers to their scores.

So to handle this type of complex scoring, we have datasets where we use the default scores generated by Probench and apply various mathematical formulas to get the desired output. This only has to set once, and scores can be calculated for all the rest of the participants again on just a click of a button.

This way, the complex requirement of scoring gets resolved the way you may have resolved it using Excel sheet but here it only has to be done once, and the rest will be automatically taken care of by the system.

You can download the ebook from here to read the rest of the chapters.