Break even analysis l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 21

Break Even Analysis PowerPoint PPT Presentation


  • 130 Views
  • Updated On :
  • Presentation posted in: General

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.

Download Presentation

Break Even Analysis

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Break even analysis l.jpg

Break Even Analysis

Lecture 5

This lecture is part of Chapter 2:Budgets, Running a Company


Today s lecture l.jpg

Today’s Lecture

  • Fixed versus Variable Costs

  • Break Even Analysis

  • Learn how to use the Solver in Excel


Break even analysis3 l.jpg

Break-Even Analysis

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.


Types of costs l.jpg

Types of costs

  • It is essential to realize that there are two basic types of costs a company incurs.

    • Variable Costs

    • Fixed Costs

  • Variable costs are roughly proportional to a company’s sales. I.e. the cost per unit remains roughly constant.

  • Fixed costs remain roughly the same regardless of sales. I.e. the cost per unit is inversely proportional to sales.


Types of costs5 l.jpg

Types of costs

Sales Value

Total Costs

Variable Costs

Fixed Costs

Sales

A simple graph but important to understand!


Break even analysis6 l.jpg

Break-Even Analysis

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.


Break even analysis7 l.jpg

Break-Even Analysis

  • Fixed Costs:

    • Rent: 5,000

    • Utilities: 300

    • Helper: 1,500

  • Variable Costs:

    • Flowers: 40% of selling price

  • So we know that:

    • Selling price – cost of flowers – rent – utilities – helper = 0

  • when she breaks even


Break even analysis8 l.jpg

Break Even Analysis

Let’s enter the last line into the spread sheet

=E6-E7-E8-E9-E10

But!!!?? I’m not a Mathematician!

How am I going to find the value for E6 so that E11 = 0?

=E6*$H$7

We learned something in the last lesson. It’s good to put the assumptions separately.


Break even analysis9 l.jpg

Break Even Analysis

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?


The solver l.jpg

The Solver

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 solver11 l.jpg

The Solver

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.


Break even analysis12 l.jpg

Break Even Analysis

Let try out the Solver!

The break even point is 11,333

Incredible!

Like this anyone can do Math!


An equation l.jpg

An Equation

  • The Solver is a fabulous tool, but really in business it is beneficial to at least be open-minded about equations.

  • Let’s give it a try! We know that:

    • E11=E6-E7-E8-E9-E10

    • (Income = Sales – Cost – Rent – Utilities – Helper)

  • At the break even point, Net Income = 0 and hence E11 = 0

  • Inserting this we obtain:

    • 0 = E6-E7-E8-E9-E10


An equation14 l.jpg

An Equation

  • We want to change E6 such that

    • 0 = E6-E7-E8-E9-E10

  • is true.

  • Ah, if we just set E6 to the other values together the above equation is true. Or,

    • E6 = E7+E8+E9+E10

  • is the solution to our problem.

  • Let’s try it and go back to the situation before we used the solver:


Break even analysis15 l.jpg

Break Even Analysis

Let’s fill the equation into cell E6

Oops!

=E7+E8+E9+E10

What went wrong?


An equation16 l.jpg

An Equation

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)


Break even analysis17 l.jpg

Break Even Analysis

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!


Break even analysis18 l.jpg

Break Even Analysis

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.


The other way around l.jpg

The other way around

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.


Break even analysis20 l.jpg

Break Even Analysis

  • With our nice equation, it’s fairly easy to accomplish this:

    • E6 * (1 - H7) = E8+E9+E10

    • (1 – H7) = (E8+E9+E10)/E6

    • H7 = 1 - (E8+E9+E10)/E6

Cool! And I thought that Financial Analysis is only for … others 


Key points of the day l.jpg

Key Points of the Day

  • The Solver is a great tool

  • But Math can be very useful as well

  • There are two types of costs:

    • Variable Costs

    • Fixed Costs

  • Break Even Analysis is a snap with Excel


  • Login