SOLVED

Sensitivity Analysis: Tornado Chart - Looking to vary the base case and updating the chart

%3CLINGO-SUB%20id%3D%22lingo-sub-1822105%22%20slang%3D%22en-US%22%3ESensitivity%20Analysis%3A%20Tornado%20Chart%20-%20Looking%20to%20vary%20the%20base%20case%20and%20updating%20the%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1822105%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20plotted%20the%20tornado%20chart%20as%20follows%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SustainableWombat_0-1603789835203.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F229488i1EF12C91BCC64B01%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22SustainableWombat_0-1603789835203.png%22%20alt%3D%22SustainableWombat_0-1603789835203.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CUL%3E%3CLI%3EBase%20Case%20Margin%20%3D%2075%25%3C%2FLI%3E%3CLI%3EThis%2075%25%20base%20case%20is%20plotted%20as%20the%20%22axis%20value%22%20-%20as%20circled%20in%20red%3C%2FLI%3E%3C%2FUL%3E%3CP%3EHowever%2C%20I%20am%20looking%20for%20a%20way%20to%20automatically%20update%20this%20%22axis%20value%22%20should%20my%20base%20case%20changes.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAre%20there%20any%20other%20methods%20to%20plot%20this%20tornado%20chart%20so%20that%20it%20automatically%20updates%20when%20the%20base%20case%20changes%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOther%20Information%3A%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EI%20understand%20another%20method%20to%20avoid%20this%20situation%20is%20to%20set%20the%20base%20case%20at%200%25%20and%20use%20the%20absolute%20deviation%20from%20base%20case%20-%20However%2C%20my%20presentation%20requires%20me%20to%20show%20the%20base%20case%20instead%20of%20the%20deviations.%3C%2FLI%3E%3CLI%3ENot%20Open%20to%20using%20macros%20as%20the%20file%20will%20be%20shared%20with%20various%20individuals%3C%2FLI%3E%3CLI%3EMy%20base%20case%20varies%20because%3A%3COL%3E%3CLI%3EAs%20we%20go%20along%20developing%20the%20model%2C%20the%20base%20case%20gains%20more%20clarity%20and%20accuracy%3C%2FLI%3E%3CLI%3EWe%20have%20many%20different%20active%20cases%20at%20the%20same%20time%2C%20which%20i%20use%20flags%20to%20toggle%3C%2FLI%3E%3C%2FOL%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1822105%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1822586%22%20slang%3D%22en-US%22%3ERe%3A%20Sensitivity%20Analysis%3A%20Tornado%20Chart%20-%20Looking%20to%20vary%20the%20base%20case%20and%20updating%20the%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1822586%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F845743%22%20target%3D%22_blank%22%3E%40SustainableWombat%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20think%20that%20is%20possible%20without%20using%20VBA%20code...%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1823618%22%20slang%3D%22en-US%22%3ERe%3A%20Sensitivity%20Analysis%3A%20Tornado%20Chart%20-%20Looking%20to%20vary%20the%20base%20case%20and%20updating%20the%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveSheet.ChartObjects(%22Chart%204%22).Activate%3CBR%20%2F%3EActiveChart.HasAxis(xlValue)%20%3D%20True%3CBR%20%2F%3EActiveSheet.ChartObjects(%22Chart%204%22).Activate%3CBR%20%2F%3EActiveChart.Axes(xlValue).Select%3CBR%20%2F%3EActiveChart.Axes(xlValue).CrossesAt%20%3D%200.75%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20quite%20stuck%20on%20how%20i%20can%20link%20the%20%22axis%20value%22%20to%20a%20particular%20cell%20(eg.%20Cell%20A1)%2C%20i%20currently%20type%20it%20as%200.75.%20How%20can%20i%20link%20it%20to%20a%20particular%20cell%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello All,

 

I have plotted the tornado chart as follows: 

SustainableWombat_0-1603789835203.png

  • Base Case Margin = 75%
  • This 75% base case is plotted as the "axis value" - as circled in red

However, I am looking for a way to automatically update this "axis value" should my base case changes. 

 

Are there any other methods to plot this tornado chart so that it automatically updates when the base case changes?

 

Other Information: 

  1. I understand another method to avoid this situation is to set the base case at 0% and use the absolute deviation from base case - However, my presentation requires me to show the base case instead of the deviations.
  2. Not Open to using macros as the file will be shared with various individuals
  3. My base case varies because:
    1. As we go along developing the model, the base case gains more clarity and accuracy
    2. We have many different active cases at the same time, which i use flags to toggle

 

Cheers!

5 Replies
Highlighted
Best Response confirmed by SustainableWombat (Occasional Contributor)
Solution

@SustainableWombat 

I don't think that is possible without using VBA code...

Highlighted

@Hans Vogelaar 

ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.HasAxis(xlValue) = True
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).CrossesAt = 0.75

 

I am quite stuck on how i can link the "axis value" to a particular cell (eg. Cell A1), i currently type it as 0.75. How can i link it to a particular cell?

Highlighted
Oyes, i decided to go with VBA and likely link it to a command button.
Highlighted

@SustainableWombat 

Like this:

 

ActiveChart.Axes(xlValue).CrossesAt = Range("A1").Value

Highlighted
works like a charm! thanks!
I shall invest some time on the side to learn vba too! cheers