Sensitivity Analysis

[skydivetom]

Experts:

I need some assistance with "Sensitivity Analysis". First, allow me to say, this topic is relatively new to me. I've googled various information incl. the info in the URL (see below).
https://treeplan.com/wp-content/uplo...sing-excel.pdf

Overall, I was able to follow along with the provided example and replicated the data table in Figure 2.11.

//Break//

Now, while the example (URL) works fine when determining which scenario gives me the greatest Net Cash Flow, I'm trying to figure out how to apply sensitivity analysis to a completely different data set (see snapshot below).

Ultimately, in my data set, I have 3 factors (blue, red, green)... each of them have different weights (50%, 30%, and 20%, respectively). Based on the lookup values, a "Total Score" (column E) is derived. This sample data
provides 24 distinct value sets (i.e., 3 values for Factor 1 * 4 values for Factor 2 * 2 values for Factor 3 = 24). It must be noted though that my **actual** data set will not necessarily have only distinct combinations. In fact, my actual data set may have 100 items where, naturally, some combination are duplicated.

So, if you were given this sample data set, how would you approach the topic "sensitivity analysis"? Is this even possible based on what's included in this data set?

Thank you in advance for any recommendations/solutions!!

EEH

[AliGW]

I don't see anything like this in your workbook - is it the right workbook?

[skydivetom]

I tested posting the XLS and when I downloaded it, it was corrupted. Trying again. (Hardly doubt people will now respond to my question though... too much unrelated admin stuff).

[AliGW]

I've already removed the irrelevant stuff. Of course people will respond.

[skydivetom]

I didn't know... understood about the zip files.

2nd time around I could repair it and save it w/ different file name. Based on what you indicated, it doesn't mean the repair works for someone else. Anyhow, could you please remove the irrelevant stuff one more time.

Hopefully, someone will try the 2nd version of the XLS file. Thanks!

[AliGW]

The problem with forum attachments has been fixed. Please don't open further duplicates on this issue - the one you opened today has been closed. You must continue here. Thanks.

[MrShorty]

As explained in your linked tutorial, sensitivity analysis is a generic concept for a variety of strategies for exploring the impact of input variables on one or more output variables, with the goal of understanding which inputs have greater and/or lesser impact on the outputs. Their example uses continuous variables (price, cost, units sold) that can easily take any value. Your example, on the other hand, is taking discreet, category inputs where the inputs can take only certain values. Many of the strategies (like goal seek) will not be applicable to category data.

It looks like you started your analysis by generating a table with all of the possible input combinations and an associated total score value. Without knowing more about what your numbers and your analysis might mean, the only "sensitivity" analysis I can see doing is one where you compare pairs of rows where two of the three inputs stay the same, and only one input changes. For example, compare items 1 and 2, 3 and 4, ... 23 and 24 to see the impact of factor 3 switching from yes to no (the total score changes by a constant amount of 20 -- whatever that means to you). I could compare rows 1 and 3, 3 and 5, etc. to begin to see the impact of factor 2. Analyze the changes in total score when only one of the inputs changes (by comparing pairs of items) and see what patterns emerge. If this is a one time or rare task, I'd probably just do it manually, rather than try to program something into the spreadsheet.

[skydivetom]

MrShorty - thank you so very much for the additional feedback. It is tremendously helpful and makes sense.

Again, I appreciate the pointers.

Cheers,
Tom