Excel data table for sensitivity analysis

I've used Microsoft Excel for so long that it has become kind of like another appendage at work. It is one of the most powerful tools you can learn to use to increase your productivity in an office. I played a lot of video games growing up, and in video game terms increasing your Excel skill is kind of like adding experience points and making your character more powerful. The better you know how to use excel, the more you can do in less time, especially if you're in finance/accounting/marketing/other numbers-oriented fields (and these days with rise of big data, almost every field is now numbers oriented). Every day I learn a new function, re-learn how to use an old one that I haven't tried in a while, or discover a new way to do something quicker.

One powerful tool I use often enough to have memorized how to use it is the data table function. Let me put its usefulness in context...

So you've created a working DCF model that values a company and you've assumed a discount rate and a revenue growth rate as part of this. Your boss/portfolio manager/wife/self looks at it and says "ok, but a DCF is just a tool to estimate value. You're giving me an precise number. How sensitive is that number to the discount rate you chose? By the way I don't believe the revenue growth rate you chose- what would the value look like if you dropped it by 5%?"

You go back to your spreadsheet and build a table. You spend the next hour methodically changing the discount rate input, and the earnings rate input, and noting the results. You come up with a whole range of values. You format them nicely, print it out and show your boss/portfolio manager/wife/self. He/she says "great, this is what I was looking for. I'm not confident the revenue growth will be as high as it needs to be to make a good return buying the stock at its current price so I'm not sure what to do here. How about you change the margin assumption in your model and show me what it does to this sensitivity table?"

You sigh. It's six o'clock. It will take another hour to do this, but you go back and make the change to your margin assumption. You then slog through, methodically changing the discount rate and the revenue growth rate and noting the results. You think about that beer waiting for you at home. Your mind wanders and you wonder if Excel has a way to automatically create a data table that will be sitting there, ready to update at a second's notice if you change any part of your model. One that will vary the assumptions for you behind the scenes and print out the results for you... 

You fire up the google. Your hard drive and RAM are jolted with electrical impulses. The google algorithm does your bidding. It sorts, it seeks. Your ISP brings you data at your maximum download speeds, your monitor renders bits and bytes into pleasing words and pictures.... and one second later you get search results. Pages upon pages of search results, all of them pointing you to the data table function. You fire up youtube, watch a tutorial, and seconds later you have a dynamically linked sensitivity table ready to do your bidding at a moment's notice. It takes a bit of time to get it right- some videos only use a single variable analysis, you keep getting the row and column inputs confused.... but it is time well spent. Learning how to use a data table just shaved an hour off of every future DCF you do. It made you more efficient. It made you better at your job. 

Anyway, if you haven't learned this yet, I strongly suggest you check out how to create a data table in Excel. Yes it is a bit complicated at first, but it gets much, much easier. 

As a side note, I've found the best way to learn how to do things in excel is with actual live problems. Something that you need to deliver and that would be mind-numbingly tedious without an excel trick. 

I suck at using blogger for anything but text, so as of now I'm not able to give you an effective tutorial on how to use data analysis (apologies to anyone who came here looking for this). What I can say is that there are hundreds of demos just waiting for you on google. I tend to prefer a youtube demo because you can see how people do things live, which helps you understand what you may be doing wrong when you mess up. 

I just did a quick search and thought this demo was pretty good.

Have fun.