1 Attachment(s)

Formulating goal seeking tool - a challenging problem

Suppose we know that when price is 10, earnings (per share) is 2, then P/E is 5; and thus when P/E reaches 20 under the same earning, the theoretic price will be 40, as P/E*E=P

Now given the price and dividend, a function, D/(P+lnP), is 9.27%. We want to know what the price will be when D/(P+lnP) is X%, like 5%, as seen on the attachment file.

In this case, there's no mathematical way to re-formulate D/(P+lnP)=constant in terms of P. Given a simple excel, we have to use the "goal seeking" tool.

Goal cell $C$2

Goal value 0.05

Variable cell A2

But we couldn't keep using the goal seeking one by one if we have thousands of calculations.

Now what should be done to achieve the goal of calculation in Excel?

Any help would be much appreciated.

Re: Formulating goal seeking tool - a challenging problem

**Administrative note**

Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile says Windows10, which AFAIK is not an Excel version

Re: Formulating goal seeking tool - a challenging problem

You might find this tutorial of mine interesting: https://www.excelforum.com/tips-and-...ind-roots.html In it, I show hot to set up the spreadsheet to use NR and secant algorithms (and circular references) to solve these kinds of problems. Adapting my polynomial examples to your function should be straightforward.

Re: Formulating goal seeking tool - a challenging problem

Re: Formulating goal seeking tool - a challenging problem

Good to see there is mathematical solution to this kind of problem. Since I have only limited literacy in the mathematical world, I am still puzzled about what exactly have to be done. Do I need to develop a spreadsheet for root testing or is there a possible excel equation that can be developed? Would the process easier or complicated than manual goal-seeking? Thanks in advance.

Re: Formulating goal seeking tool - a challenging problem

Quote:

Since I have only limited literacy in the mathematical world

I find this unfortunate, because I find that I must first understand the math behind a problem before I can reliably program the solution. Have you had any success understanding how Newton's method works?

Quote:

Would the process easier or complicated than manual goal-seeking?

It appears that you already know how to manually use Goal-seek to solve this problem, so I would say that it will be easier -- though quite tedious -- to do it manually rather than try to program a more automated solution. I find that this usually feeds into some kind of "cost-benefit" analysis. Is the effort/cost required for programming a less tedious solution offset by the benefit of less tedium. Only you can decide for yourself.

Quote:

is there a possible excel equation that can be developed?

You noted in your OP, and I would concur, that this function does not have an easy inverse function. Excel formulas are not magic -- if the formula cannot be inverted, there will not be a simple Excel formula for the inverse, either. As the final example (post #9) in my tutorial shows, one could perform the root finding algorithm in a VBA UDF, which will simplify things in the spreadsheet. It's the same algorithm, just moved into a different programming language.

Quote:

Do I need to develop a spreadsheet for root testing

Ultimately, this is a root finding problem, so, yes, you need to develop a spreadsheet you can work with for finding roots. If you are willing to go through the tedium of calling goal seek manually, your current spreadsheet will work for this. If you want to try to build off of one of my sheets, where you have limited mathematical literacy, the secant version (post #7) might be the easiest to adapt. You should only need to replace my composite polynomial function in B8:D8 and F8:H8 with your function. You could maybe:

1) Because this function cannot handle negative numbers, let's work in row 12, which defaults to a positive initial guess for x.

2) Enter 5 (your given value for D) in B12.

3) Enter 0.05 (your target percentage) in C12.

4) Enter your formula in D12 =$B12/(A12+ln(A12))-$C12 -- note the mix of relative and absolute references.

5) Select D12 and copy into H12.

6) Spreadsheet should iterate and find the root. Check the solution and see if it is correct.

7) "Copy" (however that works best for you) row 12 into your spreadsheet and make however many copies you need.

That's more "cookbook" programming that programming from a solid understanding of the algorithm, but maybe that is good enough for now. If you ever need to edit or adapt to changing circumstances, those edits may be difficult unless you spend some time now understanding exactly how the algorithm works.

If you really don't like any of those options, we also have many examples of using VBA to call Goal Seek and/or Solver in a loop to solve these kinds of problems:

Using goal seek: https://www.excelforum.com/excel-pro...-function.html

Using Solver: https://www.excelforum.com/excel-pro...lver-loop.html

I don't usually find this approach easier to program, but some users do find it easier.

At this point, you need to decide which approach is going to best use your time and effort.

1 Attachment(s)

Re: Formulating goal seeking tool - a challenging problem

Thanks a lot on the detailed explanation, I think I'm having some progress on excel literacy if not mathematical literacy.

I did as you recommended but couldn't find the desired root yet. Where should I find it? Is there any laughable mistake I made?

Thanks again.

Re: Formulating goal seeking tool - a challenging problem

It looks like you got the same root I did when I tested this -- 95.44149 in A12. This looks like the root (or at least a root), because the value for y in D12 is 0. Are you expecting a result other than 95.4?

2 Attachment(s)

Re: Formulating goal seeking tool - a challenging problem

Do we see different values in the same worksheet? Mine is 0.998048

Please check my screenshot. Is there any further setting needed to be adjusted?

Re: Formulating goal seeking tool - a challenging problem

I'm not sure where the .998 number is coming from (other than it looks like the same value as is in A11). When I open both of your files, A12 immediately resolves to 95.4... I cannot see why it resolves to 0.998 on your computer.

What happens if you "reset" (enter TRUE in B5) the algorithm, then enter FALSE in B5?

2 Attachment(s)

Re: Formulating goal seeking tool - a challenging problem

After I re-downloaded and reset B5 to TRUE then FALSE again, strange things happened: The worksheet highlighted row 12 as self-referencing...

Re: Formulating goal seeking tool - a challenging problem

That's what Excel does with circular references when you have iteration disabled. I guess I was not clear in my tutorial, but any iterative process like this that uses circular references is only going to work if you enable iteration (Excel options -> calcultion options -> check the box/radio button that enables iteration).

Re: Formulating goal seeking tool - a challenging problem

Amazing, it's working now. I'll try to copy and paste and edit to different situations. Thanks very much!