+ Reply to Thread
Results 1 to 2 of 2

Goal seek as a report without interrupting the flow of the calculations?

  1. #1
    Registered User
    Join Date
    09-30-2015
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    1

    Goal seek as a report without interrupting the flow of the calculations?

    Imagine the following scenario:

    Margin from unit sales: X in year 1, Y in year 2, Z in year 3 [imagine this is linked to table of sales units by year and changing margins over time]
    Overheads: A in year 1, B in year 2, C in year 3
    Therefore profit of D in year 1, E in year 2, F in year 3

    I want to add a row summarising how many units I would need to sell in each year in order to break even (i.e. how many sales units at given assumptions to make profit = 0). This would look like: "Break even sales units: G in year 1, H in year 2, I in year 3"

    I can use Goal Seek to figure this number out for a single year by setting D to 0 and then solving for the sales units number in the subtable.

    BUT I want the sales units numbers in the model to be my planned sales, not the break even sales. I don't want to interrupt the main calculations, I just want a row that reports how many sales units I would need under those assumptions to break even. So that it dynamically updates when I change assumptions, which the above Goal Seek approach doesn't do.

    Any ideas? A single variable data table? What if analysis? Something completely different?

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

    Re: Goal seek as a report without interrupting the flow of the calculations?

    I am not knowledgeable in financial calculations, so I don't know all of the details. My limited experience around the forum is that profit calculations like this are usually relatively simple algebraic functions. In these cases, my suggestion is to get pencil and paper, write out the algebraic expression for profit, then solve the equation for sales units assuming profit is 0. Once you have the equation solved on paper, then it should be straightforward to program that expression into Excel where you want to compute "break even sales". Since this cell is a regular formula, it should seamlessly update with each change in the spreadsheet.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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
    By mangesh in forum Excel General
    Replies: 0
    Last Post: 04-26-2007, 06:12 AM
  6. Goal Seek
    By Mike in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2006, 12:45 AM
  7. [SOLVED] Goal Seek
    By Jake in forum Excel General
    Replies: 1
    Last Post: 06-12-2005, 12:55 AM

Tags for this Thread

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