• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple outputs in two-variable data tables

I

ikirilov

New Member
Hello, all!

(my 1st post ever)


I refer to the following (incredibly useful to me) Chandoo post (by Hui) discussing one-variable and two-variable data tables:

http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/


My question is: Is there a way to have multiple outputs in a two-variable data table?


The background is: I have a large Excel file which takes a long time to re-calculate. I am calculating IRR and NPV (as well as other outputs) for a variety of scenarios (i.e. inputs). I am using a two-variable data table which allows only one output (e.g. IRR). However, I'd also like to calculate the NPV (as well as other outputs) for the same two inputs.


I can certainly do one two-variable table (with the same inputs) for each output - but that means that the calculation time is doubled / tripled, etc. which is unmanageable (one table takes nearly 20 mins to calculate!!). To be clear, all the data tables would have the same inputs in row/column - it's just the output which would be different (e.g. IRR, NPV, etc.).


Note that I cannot use one table to calculate one output (e.g. IRR) and then link all other outputs formulaically to that table - i.e. each output varies with the inputs. Hence - unless there's a clever way to avoid that - I think I really need one table for each output.


One workaround I can think of is to combine the outputs into a text string e.g. "14.2% / $23.6" via TEXT() and "&" - and then parse back the output from the table into two (or more, depending on how many variables I combine in the string) separate tables. But it would be nice to have a "normal" solution ;-)


I don't use VBA, but if a macro is the only solution, then perhaps I need to learn how to do that!


Many thanks to all who take the time to respond,

Ivo
 
Ivo


Firstly, Welcome to the Chandoo.org forums.


The section in that Post on Data Tables you should read is called MULTIWAY DATA TABLES

That enables you to adjust any number of input/output variables


If you have any troubles post a sample file and I'll assist

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hui:


Many thanks for responding.


In other words, what you mean is that the 2x2 matrix which would normally be my data table is converted into a 1x"many" scenario table, where "many" is effectively the number of cells in the 2x2 matrix? Then all the outputs I need for each of the scenarios are linked to the right of the scenario column (as well as probably adding some of the inputs as columns to make clear what's the scenario)?


In fact, as you say, I could effectively set up the equivalent of almost any-dimensional matrix number of scenarios and display any number of input/output variables to the right?


I think that's right and I have to say I missed it completely (despite it being obvious!), so I do feel like a dummy :)


Cheers,

Ivo
 
Ivo


Correct


You can feed multiple inputs in by using 1 variable to control the inputs and at the same time monitor multiple outputs


It is worth noting that data Tables cannot feed off other Data Tables, they don't get re-calculated iteratively for each consecutive data table. I'm not sure if that is by design or not, but it doesn't work. I have been able to re-structure worksheet where people required this functionality in the past but it may not always be possible.
 
Hui: May I request your help? I need to calculate a 2 dimension NPV data table, using a range of discount rates and cash flow growth percentages. Can I post my spreadsheet somewhere for your help? Or you could instruct me. I have not been able to understand how to allow for the revenue growth figures in the NPV formula linked to the data table. The NPV formula is =NPV(rate,values...)


Suppose, I have my NPV output in A1, and to the right of it, I put all different revenue growth rates (so that would be B1:E1 for instance), and below it, put all varying discount rates (so A2:A8 for example). then select the whole array, being the whole box A1:E8, then click "what if analysis" then insert data table. For the row variable, I can select the cell where I have put the discount rate assumption, but for the column variable I need to put the revenue growth rates but the NPV formula is different, it doesn't account for revenue growth rates.
 
Hi gauravpurohit,


You can start a new thread for the question. Plz don't hijack someone else's thread. It is prohibited in this forum.


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top