Everyone! We've moved to WordPress.

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.

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...

...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.


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.

Download the file
One Way Sensitivity Analysis.xlsx

Visualizing.org: Garbage In, Garbage Out

There are a lot of awful visualizations and info graphics floating around the internet. I've sent some of the worst I've found to my new internet friend, Kaiser Fung, at JunkCharts. His blog hosts a formidable, if unfortunate, collection of chartjunk and he attempts both to make sense of them as well as to provide suggestions on how to make them better. 

So where do I find these graphics?  I go to Visualizing.org, a site I am finding harder to respect as time goes on.  That might sound harsh, but if the aim of the site is to "make sense of complex issues," I think they often miss this point while giving praise and monetary rewards to data designs that, in fact, make issues harder to understand.  To demonstrate this point consider a recent winning visualization, E-CUBE-LIBRIUM, from a team at Columbia University.  According to the team: 
By configuring social, economic, and environmental data in terms of a "Rubik's Cube" analogy, the cube represents a country's growth with inversely proportional categories placed opposite each other. The 3D extrusions on each cube face is a sustainability indicator, showing volumes where data increases or decreases. From these models, we are able to quickly draw connections and visually identify how each factor affect the equilibrium of the entire system....For example, how do economic gains adversely affect social and environmental health? In each cube, we are searching for stable equilibrium and positive, balanced development that can be sustained over time. Unstable equilibrium may appear to be balanced, but it is not sustainable, due to an irresponsible levels of CO2 emissions, or alarming income and social disparity, for example.
Here's how the country of Chad is represented in the Rubik's cube analogy:

In theory, plotting a country's relevant indicators should give some visual cues and insights. However, their three-dimensional forms obscure much of this data. There is no scale and no way to tell while looking at each figure if a system is balanced, or even how much gain is too much gain. Moreover, so much is represented that we must keep deferring to the legend to figure out exactly what we're looking at.

But there are larger problems that seriously undermine the team's work.  First, this paragraph I excerpted from their write up:
By abstracting data into these virtual objects, they can then be tested for physical properties such as center of gravity, balance point, mass, inertia, aerodynamics, and any number of precise simulation results...The center of gravity of a cube gives us indications as to where a country's development is distorted and which direction the country needs to move in order to achieve balance. Inertia allows us to test for direction of past and future movement - or lack thereof. Aerodynamics would reveal holes and outliers in a country's cube, indicative of a crisis or a boom/bust capitalist cycle.
I'm not sure what to say here except...no, these things aren't possible.  There's a reason we don't test a bar chart for its weight in pounds - or how far we can toss a three-dimensional pie chart like a Frisbee.  Graphs are representations of abstract values in made-up space; they don't have inherent physical properties.    

Which brings me to the second major problem.  Because humans are pattern-oriented, ideas that manifest symmetry and balance can be appealing and convenient.  Consider American politics: you might sit on the left, the right, or smack dab in the middle.  It's an arguably useful simplification; but when you consider where you fall on the political spectrum, is the "ideal" policy truly at the center point?  Put another way, what reason exists to assert that if a country's indicators aren't in "balance", or tending toward some abstract and invented equilibrium, national turmoil will result?  Nothing, of course.

But a steady rise in CO2 emissions sounds like sustainable growth, right?  Maybe, but E-CUBE-LIBRIUM never speaks to this point visually - it's a point asserted in the team's write up.  In fact, nothing new is gained by the visual Rubik's cube.  The actual country indicators by themselves, as numbers, likely do a better job of telling this story, if it exists.

Which brings me to the biggest problem of all: the entire thing is rather heavy-handed.  I believe the designers knew going in which countries they wanted to portray as "unsustainable." Consider,
Unstable equilibrium may appear to be balanced, but it is not sustainable, due to an irresponsible levels of CO2 emissions, or alarming income and social disparity, for example.
Because there's nothing in this visualization that indicates system imbalance, or, for that matter, irresponsible CO2 emissions or alarming social disparity, I’m left wondering if these items aren't just the preconceived notions the designers imposed upon their visualization.  Indeed, the passage appears to argue that their design could give a misleading impression about a country’s stability, that knowledge of a country not presented here (such as social disparity) might mean the appearance of sustainability is incorrect.  Put another way, it doesn't matter what our visualization says, it can't be sustainable because we can't have a country that pollutes at an alarming rate violate our ridiculous analogy. Sounds like confirmation bias to me.  

Apparently, Visualizing.org, Columbia University, and the United Nations disagrees with me (and, to some extent, Newtonian Physics). This team's nonsense won their school $10,000 to continue visualization education.  Hopefully, Columbia will invest in some books on the subject

I'll leave you with this.

Find Me at an Upcoming Conference


Change the Font Size, Color, and Style of an Excel Form Control Label

Anyone who has used a Form Control Label likely knows its limitations: you can't increase the font-size, -color, or style.  Below, you can see that these formatting items have been "grayed out" in the Font group on the Excel Ribbon.

To be sure, the Label control has received a lot of flack for these deficiencies.  A look through some Excel forums shows suggestions to use a TextBox shape or an ActiveX Label instead of the hapless Label control.It's a tragedy since the other form controls are lightweight and easy to use. Some forum posters even said Labels are best used to cover cells you don't want the user to click. So sad.   

But things are looking up.  I've since discovered you can take a boring Label Control from this...

...to this!


As it turns out, Labels take on the text-font and -size features of a referenced cell.  So, to make my label look this this, I simply linked it to a pre-formatted cell, like G2, below.

In fact, this is the same mechanism to link a Textbox shape to a cell.  The difference here is that a Textbox can take an unformatted cell and apply new formatting on the front-end, when it's displayed to the user.  Above, we see that the cell must first be formatted, then linked.  In fact, whatever formatting exists in the cell when you first make the link, the label will maintain this format until a new link is created.  For example, if I were to change G2 to a black color and a smaller font, the label would not show these new changes (however, it would change its text if I changed the value in G2 to something else).  So to change the Label's formatting -- even when it's linked to the same cell -- you'll need to click the label, click the formula bar, and retype the cell link.  

Admittedly, everyone else might have already figured this one out.  However, I'm still very excited.  Don't get me wrong, Textbox Shapes are great, but having too many could become expensive on your spreadsheet, especially if you are constantly updating the screen (how many redraws can your computer handle before things start to slow?).  Labels, as Form Controls, inherently carry less bloat and overhead.  They're perfect for dynamic dashboards.