Tornado chart sensitivity analysis excel

Ultimate Writer

Most of what you’re asked to do in this group assignment was covered in Individual Assignment 8 – you just need to use your own (properly cited) data, instead.

To start, pick what you’ll be investigating the sensitivity OF. This should be one of the calculations in your previous group assignments. Maybe you’ll be looking at your cost calculation, or at one of the benefits. Recall from a recent announcement that the working version of the tornado graph Excel tutorial is at:

https://web.archive.org/web/20170825054320/http://mbacharts.com/2015/12/23/tornado-chart/

Your report should include the following:

1. (5%) Your group’s name, member names and student numbers.

2. (10%) A brief description of what calculation you will be investigating the sensitivity of, including any relevant equations (e.g. NPV = -INITIAL_COST + (P/A,R,N) x ANNUAL_BENEFITS)

3. (5%) A brief description and definition of the variables you will be checking in your sensitivity analysis. You need at least 3. (In the example above, the variables could include INITIAL_COST, R, N and ANNUAL_BENEFITS. A description of INITIAL_COST might be “The market price, in 2017 Canadian dollars, of an ink-jet printer suitable for student use.”)

4. (10%) A table of minimum, maximum and baseline values for the variables listed in 3. All values should be cited using APA format in such a way that I can follow your references and find the source of your values. If you used assumptions instead of published values, explain why you made the assumptions you made, and provide references where appropriate. (For example, if you assume that the shadow cost of travel time is between 10% and 40% of the market wage, with a baseline of 30%, you should cite the papers you obtained the appropriate percentages from or that gave you the idea that these were reasonable percentages.) Except for references, your table could look like the one below.

MIN

BASELINE

MAX

INITIAL_COST

$350

$1,000

$2,000

R

$0

6%

$0

ANNUAL_BENEFITS

$100

$2,500

$3,500

N

1

1

1

5. (15%) A table with the best-case, worst-case and baseline values of the variables and the final value you are calculating. This is NOT the same as the table in Part 4. For example, the worst-case scenario for the NPV equation above would use the MAX of INITIAL_COST, but the MIN of ANNUAL_BENEFITS (since the worst that can happen would be maximum costs and minimum benefits). The table could look something like the one below:

WORST CASE

BASELINE

BEST CASE

INITIAL_COST

$2,000

$1,000

$350

R

10%

6%

1%

ANNUAL_BENEFITS

$100

$2,500

$3,500

NPV (N=1 in all cases)

-$1,909.09

$1,358.49

$3,115.35

6. (20%) A tornado diagram for your situation. It should be properly labeled, and have the widest bars at the top, and the smallest bars at the bottom. It’s fine to do this by hand, but if you want to use Excel, go for it. Show your work (calculations) as an appendix at the end of the paper (this lets me give you part marks if your diagram is wrong).

7. (20%) A spider graph for your situation. It should be properly labeled, and each plot should extend ONLY from variable’s minimum deviation from baseline to its maximum deviation from baseline. It’s fine to do this by hand, but if you want to use Excel, go for it. Show your work (calculations) as an appendix at the end of the paper (this lets me give you part marks if your diagram is wrong).

8. (15%) A short discussion where you answer the question, ‘which variable is our value most sensitive to?’. You should write the answer based on your tables and graphs. This doesn’t need to be more than a paragraph, but feel free to write more if you wish.