I'm trying to do a sensitivity analysis of cashflow in a financial model by means of Data Tables. In the data table I run through different values of an input variable.
The model uses an iterative process (circular reference that is run through for a set number of steps or until steady state is reached) to calculate the interest cash flow. If I block the iterative process, the data tables work well, but obviously the interest rate is not calculated.
When the iterative process is activated, the data tables produce faulty results.
How can I do a sensitivity analysis of an iterative process to assess the influence of (over 200) input parameters ?
Is it possible to do this with an output format like data tables?
Best regards,
Jochem
ps. sorry if this is already covered in a thread (send me the url please?). can't seem to search the forum at the moment.
pps. is there any way to move this thread to the general questions area?
Last edited by jfloor; 09-22-2011 at 05:17 PM. Reason: edited for clarity
Welcome to the forum. If you set your calculations to automatic except data tables, that may help, depending on where they are in the calculation chain. Alternatively, you could set the calculation to manual, then develop a macro that will only calculate areas of the spreadsheet you specify, and only calculate the tables as needed. I'm not familiar with sensitivity analysis, so I can't comment on that, but thought I'd weigh in on your apparent calculation problem.
If your question has been satisfactorily addressed, please consider marking it solved. Click here to see how.
Also, you might want to add to the user's reputation by clicking the scales icon - it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
Thanks. I'm not familiar with macros (yes i should be..) and I don't think in terms of algorithms, unfortunately.
For clarity: my "sensitivity analysis" uses a "Data Table" to test the effect of changing one variable on the result of a calculation. An example is:
A B
1 10 11
2 8 20
3 11 3
Here, A1=10 is the input parameter that is being varied in column A (rows 2, 3)
B1 is the result that depends on A1 via some incredibly complicated formula and is the 'normal' result of the standard calculation. The "sensitivity analysis" involves changing A1 to see what happens to the result in B1. This is automated by means of the "Data table".
It seems that Data Tables use 'iterations' and therefore don't work for the result of a process that uses iteration itself... (i've also read something about macro's not being called during the calculation of a Data Table).
Both tools ("Data Table" and "iteration to calculate interest") work well separately, but not when both active. But I want to see the separate effect of changing a large number of input parameters on the interest rate ("ceteris paribus analysis").
Hope this clarifies my question. Or does it?
I'm not sure how you go about changing your calculation order, but I wonder if it's possible to rebuild what you have in an ordinary range and use a macro to make your iterative changes. I haven't done much work with data tables, and I'm wondering how married your process is to it. Can you post a sample workbook?
Click advanced, then click the paperclip icon to upload one.
If your question has been satisfactorily addressed, please consider marking it solved. Click here to see how.
Also, you might want to add to the user's reputation by clicking the scales icon - it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks