Break Even Analysis. Lecture 5 This lecture is part of Chapter 2: Budgets, Running a Company. Today’s Lecture. Fixed versus Variable Costs Break Even Analysis Learn how to use the Solver in Excel. Break-Even Analysis.
This lecture is part of Chapter 2: Budgets, Running a Company
An important part of running a company is the determination of how the company should be financed and how the prices of the products the company sells should be set.
Clearly, in order to do this properly a sound financial analysis is necessary.
A simple graph but important to understand!
Commonly, the Break-even point is defined to be the level of sales where:
Revenues = Expenses
Let us have a look at a simple example.
Aunt Petunia opens a flower shop.
Let’s enter the last line into the spread sheet
But!!!?? I’m not a Mathematician!
How am I going to find the value for E6 so that E11 = 0?
We learned something in the last lesson. It’s good to put the assumptions separately.
Piece of cake! Trial and error!
Oops! Not as easy as I thought.
Especially if H7 is not a nice number like 40%.
Is there a better way?
We could cave and write down the equation. That would, in fact not be such a bad idea since equations are really not as difficult to understand when one knows what they are supposed to mean.
But *** RELIEF *** Excel has an incredibly useful function called the solver. It will solve equations without giving it the equations!
That was close ….
The Solver can be found in the Tools menu of Excel. If it’s not there, it can be installed by going to the “Add-ins” menu item and selecting the Solver Add-in.
This is really quite self-explanatory!
Enter the appropriate values for “Set target cell”, “Equal to” and “By changing cells”, and click Solve.
Let try out the Solver!
The break even point is 11,333
Like this anyone can do Math!
Let’s fill the equation into cell E6
What went wrong?
May be Math isn’t as easy as I thought…
But wait, doesn’t E7 depend on E6? Yes indeed, we have defined E7 = E6 * H7 (cost of product).
Let’s put this into our equation:
E6 = E7+E8+E9+E10 => E6 = E6*H7+E8+E9+E10
Or: E6 - E6*H7 = E8+E9+E10
E6 * (1 - H7) = E8+E9+E10
E6 = (E8+E9+E10) / (1 - H7)
Yosh! Let’s try that again.
Yatta! It works
(E8+E9+E10) / (1 - H7)
After all, this was quite easy wasn’t it?
And now we can change H7 to play out scenarios. Very useful indeed!
If Aunt Petunia raises the price so that the cost is only 30% of sales ….
… she can break even already at 9714. Of course now she may be so expensive that no one wants to buy her flowers anymore.
There may be times when it is betterto turn things around.
Just now, Aunt Petunia found how much she needs to sell in order to break even givencertain cost as percentage of sales. This is in general a good idea since for many products the profits are more or less set.
In order to see how feasible a business is one could also enter an expected sales amount and then see how much the cost of the product can be in order to break even.
Cool! And I thought that Financial Analysis is only for … others