What does this function do? {=table(D19,D21)}

[Ridgeline.capital]

I'm reviewing someone else's excel model and I've run across something
I hadn't seen before. This is a repeated function in what appears to
be a 5x5 table. Obviously, D19 and D21 are referring to specific
cells. When I change those reference cells, the table changes.
However, I can't figure out where the results within this table are
coming from? For finance types, the results within the table are
IRRs, and the table is a sensitivity analysis (based on the D19 and
D21 reference cells) but I can't see where it's drawing from.

Any help would be appreciated.

Thanks.

 

[Roger Govier]

see reply to earlier message

 

[Guest]

Sounds like a VBA code issue
there is a table function in VBA which might simlar
I am not familiar with 2007, if this is what you are using, it may also be a
fuction available there

 

[Ridgeline.capital]

I can't figure out where the formula is that the table refers to.
That's the problem. It's like the formula is hidden.

 

[Guest]

Table function is u der the menu Dta --> table and is used mostly for
scenario analysis ..basically what the outcome would be if one of the
variable were changed...In your particular instance the variables are in D19
thru D21 and the resuilts re coming from the cell right above the table
perhaps E18

 

[Guest]

select cell d19
<tools><formula auditing><trace dependents>
a blue arrow show show from D19 to the cell with the formula used in the table
your table should start right and down one cell
the values in the table will change by sustituting the values to the right
of the arrow into the equation at the arrow in the place of what the eqyation
shows as D20 and D19 will be replaced by the values below the equation.


This is from interpreting items in Help. with which I sometimes (ok most of
the time) have problems.

 

[Guest]

Back in April I added some comments to a (somewhat) similar post. Let’s say
that you are looking at NPV and IRR (a financial example). You can place the
NPR and IRR on the horizontal axis (x axis). Let’s say these go in C1414.
Make sure these are references to the cells where you actually calculated the
NPV and IRR, based on the cash flows generated by the project that you are
analyzing! Hard coding these values will not work in your data
table/sensitivity analysis. Then, you can place numbers corresponding to a
growth rate on the vertical axis (y axis). Let’s say these go in B15:B20.
Starting at 0% and going to 25%, in increments of 5%, may be a good idea.
Then click on B14 (the empty cell between your x & y vales). Move down and
right so that B1420 are selected. Then click, Data, Table, Column input
cell (this should be your growth rate, whatever cell it is on your
spreadsheet), Ok. Your data table should populate with all relevant
information. Write back if you get stuck somewhere.

Check out this link for a much more comprehensive explanation of data tables
and sensitivity analysis:
http://www.ce.cmu.edu/~hsm/bca2005/lnotes/XLDataTablesMonochrome2001.pdf