Transcript A Finance Survival Kit

GEM2508
A Finance Survival Kit
GEM2508
Frederick H. Willeboordse
[email protected]
GEM2508
Lecture 6
Break Even Analysis
GEM2508
Today’s Lecture
Fixed versus Variable Costs
Break Even Analysis
GEM2508
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.
GEM2508
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.
GEM2508
Types of Costs
Sales Value
Total Costs
Variable Costs
Fixed Costs
Sales
A simple graph but important to understand!
GEM2508
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.
GEM2508
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
GEM2508
Break-Even Analysis
Let’s enter the last line into the spread sheet
A
2
3
4
5
6
7
8
9
10
11
12
B
C
D
E
F
G
H
I
Auntie Petunia Flowers - Break even Analysis
Sales
Cost of Sales
Rent
Utilities
Helper
Net Income
0
5000
300
1500
-6800
Cost as percentage of
sales
40%
=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
It’s good to put the
assumptions separately.
GEM2508
Break-Even Analysis
Piece of cake! Trial and error!
A
2
3
4
5
6
7
8
9
10
11
12
B
C
D
E
F
G
H
I
Auntie Petunia Flowers - Break even Analysis
Sales
Cost of Sales
Rent
Utilities
Helper
Net Income
12600
5040
5000
300
1500
760
Oops! Not as easy as I thought.
Especially if H7 is not a nice
number like 40%.
Cost as percentage of
sales
40%
Is there a better way?
GEM2508
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 *** we can use the previously
discussed solver.
That was close ….
GEM2508
Break-Even Analysis
Lets use the Solver!
A
2
3
4
5
6
7
8
9
10
11
12
B
C
D
E
F
G
H
I
Auntie Petunia Flowers - Break even Analysis
Sales
Cost of Sales
Rent
Utilities
Helper
Net Income
11,333
4,533
5,000
300
1,500
0
Incredible!
Like this anyone can do Math!
Cost as percentage of
sales
40%
The break even
point is 11,333
GEM2508
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
GEM2508
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:
GEM2508
Break-Even Analysis
Oops!
Let’s fill the equation into cell E6
A
2
3
4
5
6
7
8
9
10
11
12
B
C
D
E
F
G
H
I
Auntie Petunia Flowers - Break even Analysis
Sales
Cost of Sales
Rent
Utilities
Helper
Net Income
0
0
5000
300
1500
-6800
Cost as percentage of
sales
40%
=E7+E8+E9+E10
What went wrong?
GEM2508
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)
GEM2508
Break-Even Analysis
Yatta! It works
Yosh! Let’s try that again.
A
2
3
4
5
6
7
8
9
10
11
12
B
C
D
E
F
G
H
I
Auntie Petunia Flowers - Break even Analysis
Sales
Cost of Sales
Rent
Utilities
Helper
Net Income
11,333
4,533
5,000
300
1,500
0
Cost as percentage of
sales
40%
(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!
GEM2508
Break-Even Analysis
If Aunt Petunia raises the price so that the cost
is only 30% of sales ….
A
2
3
4
5
6
7
8
9
10
11
12
B
C
D
E
F
G
H
I
Auntie Petunia Flowers - Break even Analysis
Sales
Cost of Sales
Rent
Utilities
Helper
Net Income
9,714
2,914
5,000
300
1,500
0
Cost as percentage of
sales
30%
… she can break even already at 9714. Of
course now she may be so expensive that no
one wants to buy her flowers anymore.
GEM2508
The Other Way Around
There may be times when it is better
to turn things around.
Just now, Aunt Petunia found how much she needs to sell
in order to break even given certain 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.
GEM2508
Break-Even Analysis
With our nice equation, it’s fairly easy to
accomplish this:
Cool! And I
thought that
Financial
Analysis is only
for … others 
E6 * (1 - H7) = E8+E9+E10
(1 – H7) = (E8+E9+E10)/E6
H7 = 1 - (E8+E9+E10)/E6
A
2
3
4
5
6
7
8
9
10
11
12
B
C
D
E
F
G
H
I
Auntie Petunia Flowers - Break even Analysis
Sales
Cost of Sales
Rent
Utilities
Helper
Net Income
12,000
5,200
5,000
300
1,500
0
Cost as percentage of
sales
43%
GEM2508
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