+ Reply to Thread
Results 1 to 6 of 6

What-If/Goal Seek

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 365
    Posts
    93

    What-If/Goal Seek

    In cells B6:E6, I have sales values for a sales person. In cell F6, I sum up the total. I need to know what would happen if the sales person increased their sales by 20%.
    My input box for the 20% is in cell H4.

    How can I calculate this using What-If?

    Your help is greatly appreciated.
    Karen

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: What-If/Goal Seek

    Quote Originally Posted by Karen615 View Post
    In cells B6:E6, I have sales values for a sales person. In cell F6, I sum up the total. I need to know what would happen if the sales person increased their sales by 20%. My input box for the 20% is in cell H4.
    Maybe one of the following, depending on your intent:

    F6: =ROUND(SUM(B6:E6)*(1+H4), 2)
    or
    G6: =ROUND(F6*(1+H4), 2)

  3. #3
    Registered User
    Join Date
    10-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 365
    Posts
    93

    Re: What-If/Goal Seek

    Thank you for your help, but this is a question for a school assignment.
    I have to use the “What-If” scenario using absolute referencing.
    Determine what would happen if total sales for each sales representative were to increase by 20%.
    Do I use Scenario Manager or Goal Seek? If so, how can I accomplish this?
    Thank you

  4. #4
    Registered User
    Join Date
    10-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 365
    Posts
    93

    Re: What-If/Goal Seek

    If it's Goal Seek, which is:
    Set Cell
    To Value
    By changing cell

  5. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: What-If/Goal Seek

    Quote Originally Posted by Karen615 View Post
    Thank you for your help, but this is a question for a school assignment. I have to use the "What-If" scenario using absolute referencing. Determine what would happen if total sales for each sales representative were to increase by 20%.
    Do I use Scenario Manager or Goal Seek?
    Since it is a school assignment, you should do it according to the instructor's direction.

    I'm not familiar with Scenario Manager. It might automate what I would do manually, to wit....

    Given B6:F6, I would put several "what-if" percentages into a column or row, and build a formula similar to the one I offered earlier in a parallel column or row. For example:


    A
    B
    C
    D
    E
    F
    5

    Qtr1
    Qtr2 Qtr3 Qtr4 Total
    6
    Last year
    $7,696 $8,206 $6,049 $5,378 $27,329
    7
    Est diff next year:





    8
    -10% $6,926 $7,385 $5,444 $4,840 $24,596
    9
    -5% $7,311 $7,796 $5,747 $5,109 $25,963
    10
    5% $8,081 $8,616 $6,351 $5,647 $28,695
    11
    10% $8,466 $9,027 $6,654 $5,916 $30,062
    12
    20% $9,235 $9,847 $7,259 $6,454 $32,795

    The formula in F6 is =SUM(B6:E6). Copy F6 into F8:F12.

    The formula in B8 is =B$6*(1+$A8). Copy B8 into B8:E12.

    Note the mixed absolute references B$6 and $A8. I suspect that is the point of the exercise ("using absolute references").

    But again, if the how-to details are specified by the instructor, you should do it his/her way.


    Quote Originally Posted by Karen615 View Post
    If it's Goal Seek, which is:
    Set Cell
    To Value
    By changing cell
    I cannot see any reason why you would use Goal Seek, if I understand the "what-if" model correctly above.

    You might use Goal Seek if you did not understand the arithmetic to derive one value from another, or if the arithmetic is not a "close formula" (requires iteration).

    But in this case (as I understand it), the arithmetic is simple.
    Last edited by joeu2004; 10-16-2016 at 07:34 PM. Reason: cosmetic

  6. #6
    Registered User
    Join Date
    10-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 365
    Posts
    93

    Smile Re: What-If/Goal Seek

    Thank you so much for all your time and help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Seeking Goal without using Goal Seek
    By Doc Science in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-07-2015, 02:24 PM
  2. Replies: 3
    Last Post: 07-27-2012, 01:44 PM
  3. Goal Seek VBA with relative 'Goal' parameter
    By alirulez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2012, 07:19 PM
  4. Goal Seek Formula But Not Using Goal Seek
    By cady923 in forum Excel General
    Replies: 1
    Last Post: 08-05-2011, 03:53 PM
  5. Goal Seek VBA Help
    By BFlick11 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-31-2010, 12:42 PM
  6. Goal Seek
    By groatog in forum Excel General
    Replies: 0
    Last Post: 05-17-2010, 10:34 PM
  7. Goal Seek?
    By Skibum in forum Excel General
    Replies: 1
    Last Post: 02-03-2009, 08:43 PM

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