+ Reply to Thread
Results 1 to 6 of 6

calculating break-even point for budgets

  1. #1
    Registered User
    Join Date
    05-05-2020
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2019 Professional Plus
    Posts
    9

    calculating break-even point for budgets

    Hi,
    i have a question relating to a budget i'm making. The budget has rows consisting of various posts of revenue and costs, and the columns are the months, e.g. 24 months in 2 years. My short-costs and revenues vary a lot, but my long-term costs are stable and i can vary my revenue based on 1 factor.
    At the bottom of the budget i have a budget line for liquidity. What i want is to calculate the abovementioned factor for which the liquidity in the long term does not change, so when income and costs are even.
    I cannot calculate this in the Excel ways that i know of. What i do now is that i vary the factor manually, until income and costs are even in month 24 (there is 1 break-even point, because there is only 1 factor that influences revenue, and the long-term costs are fixed).
    Do you know of any way to automate what i do now manually?
    best,
    Rik

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,159

    Re: calculating break-even point for budgets

    Hello Rik1234 and Welcome to Excel Forum.
    Please utilize the instructions in the banner at the top of the page to upload a sample of your budget. Please manually include the liquidity based on the sample and, unless it is blatantly obvious, the method by which you manually made the calculation so that we will have something with which to compare the output of any proposed formulas/code.
    Based on you description of the spreadsheet, would you be open to suggestions on changing the layout? It is often easier to make a calculation when using row over row data entry rather than matrix style e.g. 24 columns of months.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

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

    Re: calculating break-even point for budgets

    What you do now manually sounds like something that Goal Seek or Solver could replicate.
    https://www.excel-easy.com/examples/goal-seek.html
    https://www.excel-easy.com/data-analysis/solver.html

    You have not described your budget in any detail, but, my best guess is that this factor could be solved for algebraically -- if you were allowed to step away from Excel and look at the problem algebraically. If this is something that you need to do frequently, then I would step away from the spreadsheet and look at the problem that way.
    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
    05-05-2020
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2019 Professional Plus
    Posts
    9

    Re: calculating break-even point for budgets

    wow this is amazing, i'm glad i asked!! Goal seek did the trick for me.
    Thanks,
    Rik

  5. #5
    Registered User
    Join Date
    05-05-2020
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2019 Professional Plus
    Posts
    9

    Re: calculating break-even point for budgets

    ps what do you mean by stepping away from Excel and looking at the problem algebraically?

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

    Re: calculating break-even point for budgets

    That means exploring the algebra behind the problem to see if I can "solve" for this factor directly. I often see problems on the forum that reduce to something like y=x+0.1*x +k, and the user wants to solve for x using Goal Seek. Of course, in a simple case like this, it is relatively easy to solve for x directly and avoid Goal Seek. My suggestion to step away from Excel is just that, step away from Excel, write out the problem algebraically, and see if it is possible to solve for your "factor" without using Goal Seek. What you describe looks something like y=sum(factor*revenues)-sum(costs). If it is that simple, factor could be easily solved for directly.

+ 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. Move Page Break 1 cell up if bullet-point is in on the page break.
    By saudi_red_neck in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-06-2019, 10:24 AM
  2. [SOLVED] Break-Even Point in Cashflow
    By ima_ms in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-12-2018, 11:47 AM
  3. Replies: 0
    Last Post: 08-09-2017, 11:38 PM
  4. Break even point/Line in excel
    By vikram20.bhardwaj in forum Excel General
    Replies: 2
    Last Post: 05-09-2015, 06:21 AM
  5. Approximate break even point with LOOKUP
    By Reinier in forum Excel General
    Replies: 1
    Last Post: 10-13-2011, 10:46 AM
  6. sum function with a break point
    By jspinx in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2009, 07:31 PM
  7. sum function with a break point
    By jspinx in forum Excel General
    Replies: 5
    Last Post: 01-02-2009, 07:25 PM

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