Parameters question

[Guest]

First of all, I don't think I am smart enough to go coding.
Being that, is there anyway to build calculation functions in Sheet2 and
list possible input in Sheet1 and automatically getting values under
different inputs?
For example, I put equity valuation calculations on Sheet2.
A1 of Sheet2 is the earning growth rate assumption, which drives all the
equity valuation calculations.
In sheet1, can I input 10 different expected growth rates in A1:A10 and get
equity values on B1:B10 (based on the calculations on Sheet2)?
I understand that creating a table can be helpful. But Table does not work
in separate sheets. Plus, if I want other parameters in the calculations in
addition to the equity value, I have to create other tables.

Appreciate your help!

Rt

 

[Guest]

The short answer to your question is yes, and what you want to do doesn't
necessarily require coding.

If you enter values in A1:A10 on sheet 1, you can get those values to appear
on B1:B10 on sheet 2 by creating a formula which links the two cells together.

I'm not sure what you mean when you say "Table does not work in separate
sheets." Tables can be created in any sheet you want, and linked to other
sheets via dozens of different functions.

Dave

 

[Guest]

Hi Dave,

I don't think I put the question clearly. My problem is when putting a new
growth rate, say 10%, in A2, the Sheet2 will calculate and return equity
price based on 10%. The originaly equity price (based on 5% growth rate) is
gone. How can I retain the old equity price?

As for the table, I was talking about Menu -->Data-->Table. It askes for row
input and column input, where both should be on the current sheet. To refer
parameters on other sheets, I have to link them back to the current sheet.

Hopefully I put it correct this time.

TR

 

[Guest]

OK, now I think I understand.

You could do a number of things to retain the original value, such as using
the Scenario tool to record various scenarios. You could also create a
drop-down list in A2 which list contains all the values you would like to
test. To do this, go to Data--Validation, select list, and either enter your
list or referene the range in which the list is typed.

As for the Data Table tool, that's a little misleading. That tool is meant
for scenario testing, as well, but not of the sort you're trying to do.
Here's some good information on the data table tool:
http://www.ce.cmu.edu/~hsm/bca2005/lnotes/XLDataTablesMonochrome2001.pdf

 

[Guest]

Hi Dave,

Thank you! I think your reply helps. I am trying to see if I can do what I
wanted to do by scenario tools.

For the data table tool, I believe that is what I wanted to use. It
considers different inputs and keeps results per different inputs. In this
case, one-variance data table is good enough. My concern of using data table
is the work to set up multiple data tables, which is time consuming and might
make the formatting awkward.

Thank you!

 

[Guest]

try this address---if you re not satisfied--please send me your file
-http://office.microsoft.com/en-us/assistance/HA100242441033.aspx