# calculating break-even point for budgets

1. ## 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  Register To Reply

2. ## 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.  Register To Reply

3. ## 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.  Register To Reply

4. ## 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  Register To Reply

5. ## Re: calculating break-even point for budgets

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

6. ## 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.  Register To Reply

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