+ Reply to Thread
Results 1 to 13 of 13

Formulating goal seeking tool - a challenging problem

  1. #1
    Registered User
    Join Date
    12-19-2018
    Location
    Hong Kong
    MS-Off Ver
    Excel 2016
    Posts
    42

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    05-14-2009
    Location
    RDC
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    6,917

    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
    Last edited by Pepe Le Mokko; 05-14-2019 at 02:38 AM.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,762

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    12-19-2018
    Location
    Hong Kong
    MS-Off Ver
    Excel 2016
    Posts
    42

    Re: Formulating goal seeking tool - a challenging problem

    Thank you, updated now.

  5. #5
    Registered User
    Join Date
    12-19-2018
    Location
    Hong Kong
    MS-Off Ver
    Excel 2016
    Posts
    42

    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.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,762

    Re: Formulating goal seeking tool - a challenging problem

    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?

    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.

    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.

    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.

  7. #7
    Registered User
    Join Date
    12-19-2018
    Location
    Hong Kong
    MS-Off Ver
    Excel 2016
    Posts
    42

    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.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,762

    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?

  9. #9
    Registered User
    Join Date
    12-19-2018
    Location
    Hong Kong
    MS-Off Ver
    Excel 2016
    Posts
    42

    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?
    Attached Images Attached Images
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,762

    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?

  11. #11
    Registered User
    Join Date
    12-19-2018
    Location
    Hong Kong
    MS-Off Ver
    Excel 2016
    Posts
    42

    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...
    Attached Images Attached Images
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,762

    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).

  13. #13
    Registered User
    Join Date
    12-19-2018
    Location
    Hong Kong
    MS-Off Ver
    Excel 2016
    Posts
    42

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1