# Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 1)

This article describes what I like to call “Easy One Way Sensitivity Analysis” in Excel.  I’ve used it before in several dashboards.  If you want to try it out for yourself before reading ahead, both Healthcare dashboards on the Fun Downloads page implement my Easy One Way Sensitivity Analysis method.  Here’s a pic of one of those dashboards:

Above, you can use the scroll bars to change the weights for each given metric.  As you change one weight, the others change proportionally, just as you would expect with one-way sensitivity analysis. In Part 1, I discuss how to build the structure that drives this functionality.  In Part 2, I’ll discuss how to put it on your dashboard.

Below I begin with a review of sensitivity analysis and weighted-sum models.  If you’d like, you can skip the review and go right to the instructions, here.

Review
The title of this article may seem like a mouthful, but don’t let the technical words fool you.  It’s likely you’ve seen this stuff before.

Imagine, for a moment, you are evaluating the healthcare systems of different nations.  After considerable research and many hours with healthcare experts, you decide there are five key criteria you will use to evaluate each nation’s system.  They are:

Health level – a measure on the overall health of a country

Health distribution – a measure of how equally healthcare resources (above) are distributed across the country

Responsiveness – a measure of the speed of health services, doctor’s choice, amenities, etc.

Responsiveness distribution – a measure of how equally healthcare responsiveness (above) is allocated across the country

Financial fairness – a measure of how citizens in each country “fairly” distribute the financial burden of the healthcare system

Not every metric is equal, however.  And, after some careful thought, you decide that each criterion should be weighted by a proportion of its importance to the overall performance of each system.  The weights are as follows:

This model may not look familiar, but, in fact, it’s the very same criteria and weights used in the landmark World Health Organization study, World Health Report 2000: Health Systems, Improving Performance, which assigned a rank to 191 different countries based on the performance of their healthcare systems.   Indeed, this model is used in many published rankings. It works like this: you define the key metrics about the subject you want to investigate and assign each a weight.  You then collect data about each metric resulting in an overall score for that metric. For example, you could collect surveys from healthcare professionals ranking their healthcare networks on a scale from 1 to 10 and then average the results (I’m simplifying a bit). Finally, you would multiply each metric by its associated weight then sum those products together to come up with an overall score. If you understand me so far, then you get the weighted-sum model.

In the published rankings above, the weighted sum model is used to evaluate many different countries. Broadly, you’re simply investigating a resultant list of alternatives whose scores follow directly from the importance of each input defined in the first step. As such, you may want to investigate how changing the importance of inputs impacts the overall score. This is called sensitivity analysis.

One-Way Sensitivity Analysis

One simple, if powerful, sensitivity analysis method is to vary only one weight at a time while maintaining the proportional importance of the other weights. This is called one-way sensitivity analysis and it works like this:

Say I want to test what happens if I increase Health Level by 4%.  First, lets divide the weights into two theoretical groups,

The rule here is that each group must always sum to 100%. So, if we add 4% to Health Level, we have to subtract it from the other group.

Now that the overall sum of the “other group” has changed, the weights that make up that group are adjusted while maintaining the same proportion to the group’s sum as they did before.  First we find the proportion…

Then multiply by the new group weight…

Finally,

…our weights again add up to 100.0%

Easy One-Way Sensitivity Analysis in Excel
The above method may appear daunting, but we can make Excel do most of the work for us. I call my “easy” because it’s both straight-forward and requires no VBA to implement.  However, it has some limitations that will become clear by the end.

Step 1 – Set up your calculations table

This is how I set up my calculations tab tab:

Note that I have my weights listed on the left side.  I’m given some space between my weights and my score table.  Note, too, that I have all the multiplication set up.  You’ll likely want a similar layout. Of course, to save some time, you can download the workbook above with the layout already set up (use the Example tab).

Step 2 – Insert Form Control Scroll Bars, One for Each Weight

Go to the Developer tab.  Select Insert > Form Control Scroll bar.  (If you don’t see the Developer tab on your Excel screen, you’ll need to enable it.)

Insert five different scroll bars below the weights table.  Or, better yet, insert one, then copy and paste.
Step 3 – Link each Scroll Bar

Insert a new column between columns B and C.  Put a new heading in the new column titled “Linked Weight.”  We want to link each scroll bar to a metric so that we can use the scroll bars to change the weights.  To link a scroll bar to a value on a sheet, right click on the left-most scroll bar and select Format Control.  In the Cell link field, select the cell that will hold the current value of the scroll bar. You will also need to set Minimum Value to 1.

From the left-most scroll bar to the right, you’ll want to link them to cells starting C3 all the way down to C7. Once the cells have been linked, go ahead and click the down arrows on each scroll bar. You should see the values change as you scroll down. This will also allow you to double-check that each scroll bar is associated with the correct cell.

Step 4 – Adjust Each Value

We have a small problem: the scroll bars increase their cell’s values when scrolling down. While there’s nothing wrong with that per se, it’s counter intuitive to how we think. For our purposes, we’d like the action of scrolling up to actually increase the resulting value and scrolling down to decrease. So we need to adjust the values on the spreadsheet to reflect this preference.
Insert another column between C and D.  Name it “Adjusted Weights.”  In each cell next to the Linked Weights, you will take the cell to its left and subtract 100 (the max value of the scroll bar).  You can do this with a formula like the one shown below.

Play around with the scroll bars. Note that the adjusted weights now increase and decrease intuitively.

Step 5 – Sum and Define Proportions

At the bottom of the Adjusted Weight column, add a SUM cell.

Now you will overwrite the values in your Final Weights column.  For each cell in Final Weight, you will divide the cell to its left (in the Adjusted Weight column) by the total sum you’ve just calculated.  Use the formula below as a guide.

At this point you’re finished with Part 1. If you play around with the scroll bars, you will see that you can change the weight of one criteria at a time, while maintaining the proportionality of the others. The final scores update automatically! The scroll bars also adjust the location of the selector (that little gray bar) accordingly.

Limitations

As you may have already noticed, the scroll bars appear to have a limit. (Scroll one of them to its maximum value to see what I mean.) For some applications, this limitation isn’t a real problem; but you may create a model where the “easy” method is not useful. Additionally, because we use formulas to derive the final weights, you can’t type a desired weight into the Final Weights column without breaking part of the scroll bar mechanism. (But you can fix this problem with some VBA.)
In Part 2, we’ll talk about putting this structure into a dashboard layout.

One Way Sensitivity Analysis.xlsx

# Numbering Grouped Data for Pivot Tables

We’ve all had some experience with Excel’s automatic numbering.  For example, if you have a simple list and you would like to add consecutive numbers to a column next to your data, you’d simply select the cells containing your first two numbers and then drag accordingly.  Take a look at the sheet below to see an example.

But real-world lists aren’t always so simple.  And for certain data, a simple consecutive list just won’t do.  Sometimes we receive spreadsheets that have some type of intrinsic grouping but no unique group identifier. The challenge is that we would really like to apply Pivot Tables to this data, but we can’t do anything until there exists some type of group identifier.

Problem #1: Items grouped without collation
Take a look at the datasheet below from my nonexistent accounting information system.

Because I’m smart(ish), I know that a new group begins every third row of data.  The first three rows of data (Excel rows 2,3, and 4) represent store 1, the next three store 2, and so forth.  I would like to add an additional group_id column such that each grouping is numbered consecutively.  Something like this:

How?  I use this formula,
=INT((ROW()-2)/3) + 1
then fill downward.  This works by applying integer division in the amount of each item in a group to the current row. So, for row 3, we have

=INT((ROW()-2)/3) -> =INT((3-2)/3) – > =INT(1/3) -> = INT(0.33) = 0.

(If it’s been a while since your last algebra class, just think of it as dividing and then “rounding down” the result.)

I add one (“+ 1”) at the end of the formula so that my grouping doesn’t start at zero.  That’s optional.

Notice though, that I subtract 2 because my data starts on row 2.  If I had started on row 1, I would likewise subtract 1; for row 3, subtract 3.  Here’s a cheat sheet:

For Items Grouped Without Collation

=INT((ROW()-First row of data on spreadsheet) / # Items per Group)[+1] optional

Problem #2: Items grouped with collation

In the following spreadsheet, we’ve culled information from many different spreadsheets maintained by many different people.

The problem is that each spreadsheet administrator used a different naming convention for the same account (see the highlighted accounts).  And take a look at the final Retained Earnings and note that it is labeled ”  RE.”  Those extra spaces can creep into the spreadsheet and easily go unnoticed.  What a nightmare.

But wait, our list has some semblance of order: accounts can be grouped every sixth row. We just need to group each item, one through five, until the end of the list.  Like so,

How?  With this formula,

=MOD(ROW() – 2,5) + 1

This should look familiar to the one above.  But instead of using integer division, we’re now using modulo division; that is, we’re interested in the remainder.  Take row 3

=MOD(ROW()-2, 5)+1 ->  =MOD(3-2, 5)+1  – >  =MOD(1, 5)+1  -> = 1+1 =2.

Here’s the cheat sheet:

For Items Grouped With Collation

=MOD((ROW()-First row of data on spreadsheet,# Items per Group)[+1] optional

Final Thoughts

1.  Once you’ve created your new group_id column, it’s a good idea to select your new work, copy, and then paste as values.  If the groupings aren’t going to change later, there’s no reason to keep it as a formula.  Remember, fewer formulas means better Excel performance — especially if you plan to use a Pivot Table later.

2.  If you’re not into the numbering scheme, create a lookup table that maps the numbers to a proper name.  Create another column at the front of your data and use a lookup method, like Index, to map the correct names.  Then do a copy/paste values.

3. If you have a list scheme you use quite often, you can actually save it as a custom fill series and then use it later.

Remember:

For Items Grouped Without Collation

=INT((ROW()-First row of data on spreadsheet) / # Items per Group)[+1] optional

For Items Grouped With Collation

=MOD((ROW()-First row of data on spreadsheet,# Items per Group)[+1] optional

# A High Ranking Function

Excel really doesn’t have an explicit “between function” to allow users, given a set of ranges, to find in what range a selected number falls. The obvious and messy workaround is to use a bunch of nested IFs, which is tedious and error-prone. That’s where the RANK function comes in.

Here’s a breakdown.

`RANK(number,ref,[order])number - The number you want to findref - The reference to spreadsheet rangeorder - optional; excel defaults to descending order`

The RANK function returns the location of a specified number in a given range. So let’s say you have a given set, 1,2,3,4,5. Obviously, testing for the number three will return a location of 3. For our range test, we combine a number outside the set and see where it falls.

Check out the image below to see it in action:

Excel returns a rank of 4, since 3.2 will appear in the fourth location of the ranked set, 1,2,3,
3.2,4,5.
At this point, there are two important items to point out.

• How excel sorts the data for ranking is important. Above we use an ascending order (we pass a 1 to the last argument) and below we’ll use a descending order (we’ll pass a 0 to the last argument). You’ll have to decide which best fits your analysis.
• Because you combine two ranges into one, you must use parenthesis. If you miss this step, the RANK function will interpret B2 as the next parameter and return an error.

Here’s a Weather Application

This example tests for a temperature’s range and then displays how the day will feel corresponding to that range.

You could also use this trick when you must evaluate a value on a complex function. For example, you might have a graph with several curves that represents some utility for a given value, x. Thus, if you know the bounds of your curves, you can test in what range your x falls and apply its corresponding curve-function.

Finally, you might have noticed that this trick restricts you to inequalities with an inclusive lower bound and exclusive upper bound. There’s ways to fix this and I plan to talk more about them in future posts.