What-if Analysis – Data Table

One reader asked me how to create sensitivity table as following:

sensitivity-table

Before I start the tutorial, do one thing first.

  1. Download and open the latest excel.
  2. Go to “HAIO Value” worksheet.
  3. Navigate to cell C277 and change font color to red (or any color you like)
    sensitivity-table-formula
  4. Note that 0.209 is linked to L57.
  5. Highlight D278 to L286 and press Del button. No worry, you can always download my excel again if you screw up something.

Here is the step by step. This feature is available in most of the version of Office. Here, I use Office 365.

  1. When you create your own sensitivity table, make sure you construct the table by following format:
    Sensitivity Table - Structure.PNG
  2. Highlight C277 to L286.
  3. Navigate to Data, then select What-if Analysis.
  4. Click Data Table. You will see a small window (a lousy designed window).
    data-table-window
  5. You have to select cells that define the FY2018 MLM Growth Rate and FY2017 MLM Growth Rate. Here you are:
    data-window-cell-selection
  6. Click OK.
  7. Press F9 on your keyboard. This is important!
  8. Scoll down and see the results.

Alright! Usually, cool stuffs come with payoff (like exploded Note 7 and overpriced iPhone 7).

  1. If you have many data table built in your excel, calculation of your excel will be slower. If the calculation is complicated and you keep adding new data table, this will make situation even worse.
  2. By default, to reduce calculation overhead, Excel already enabled “Automatic Except for Data Tables”.
    Default Calculation Mode.png
  3. This means Excel won’t recalculate your data tables by default.
  4. You have to press F9 to calculate the active worksheet.

You can try to change the option to “Automatic” if you only have very few data tables and simple calculation. I tried before where everytime I change a value, Excel will be unresponsive for 10-20 seconds to recalculate 20++ data tables at one time. Think twice before you change the option.

To learn how to format cells colors, please Google “Conditional Formatting”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s