Thursday, November 4, 2010

Excel data tables to the rescue

I learned how to use Excel data tables over the summer, and it's really coming in handy now for FinD. In case, like me 5 months ago, you are oblivious to how awesome they are, here is a quick rundown.

Purpose: Scenario analysis. They provide a great way to try modifying 1 or 2 values/assumptions to test their impact on an equation.  This makes them useful for doing a sensitivity analysis.

Here is an example of doing a 2-variable data table.

1. Set up the proposed 2-variable data table as shown below. The equation you want to check with all of the different possible values goes in the top left (green cell), the row variable (Cash Received Annually) that you are going to test with different values is in red, and the column variable (Discount Rate) that you are going to test with different values is in blue. Highlight the cells and select the "Data Table..." option.

2. In the window that pops up, select the cells where you want to plug in all of the different values. Those cells should have an impact on the equation you are testing.

3. Voila! The value of the equation you are testing, in this case NPV, under each of the different scenarios is automagically filled into the table.

I know that my instructions are pretty bad, so here is the official Microsoft tutorial that does a much better job of explaining how to use the tables.

If you are going into consulting, file this away under "Probably going to use it in the future."


  1. This is secondary in nature to investment banking dude.

  2. I was hoping to get into Kellogg next year and I need a new computer now. Your thoughts on getting a macbook ?

  3. The Macbook isn't perfect, but I think it's a pretty good laptop, and I've never found myself unable to do something I needed to do in school on account of it.

    In terms of hardware, I haven't run into any other laptops that are comparable to the Macbook Pros. Easily the best overall laptop design I've ever encountered.

    The software is a mixed bag. There are certain things that annoy me, like how iPhoto manages pics, being unable to cut and paste files, and the buggy performance when exporting the display to an external monitor. I was also surprised to find that the OS crashes from time to time, a la Windows.

    On the other hand, I like using a Unix-based OS. The OS comes with pretty much all of the programs that you'll need right off the bat. And a lot of the inefficiencies of working in Mac are being hammered out quickly: Snow Leopard made using Microsoft Exchange easy, Office2011 for Mac means I no longer have to boot into a virtual image of Windows, and school does a lot of support for it.

    Bottom line, if you are interested in learning how to use the Mac, I don't think it's a bad move to make. But I also don't think it's the alpha and omega of computers.

  4. Sir I think u have not used the in build function NPV of Excel but u have write the formula of NPV and then use the data table on it.
    Can u tell me how to do the data table by using inbuild NPV formula of excel ( it need a range of value as an input) so how to do this in Data table ?


Note: Only a member of this blog may post a comment.