Welcome to Highline BI348, Video No.
4.
Hey, if you want to download this workbook and follow along,
BI348, Chapter 7, click on the link below the video.
Hey, now, we've already worked in this workbook
and created our model on this sheet.
And we did Goal Seek on the sheet over here.
But we want to come back to Quad fixed cost, variable cost,
profit sheet, because I want to create a new math
formula and a new formula down in our model that actually
calculates break-even units.
Now, the last video we used Goal Seek and changed the quantity
and the price to figure out what the break-even point was.
That's the point at which we make exactly zero profit.
Now I want to come over here, and we're
going to define a new formula, unit break-even point.
That's the actual units that gives us
an exact profit of zero, equals UBE's, for units break-even,
equals.
And remember, there's two types of costs- fixed and variable.
So to get break-even units, we have
to figure out how we're going to pay off the fixed cost- FC.
And we're going to divide it by-- well, think
about this-- zero.
That means we had no variable cost.
As soon as we sell one unit, we've sold the revenue
and incurred one unit variable cost.
So we could take fixed costs and simply
divide it by the gross profit per unit,
which is revenue minus VC, which is our variable cost per unit.
Now, most of the time when you see this formula, this
is what you're given, the contribution margin
from each incremental sale of a boomerang, revenue
minus variable cost.
That will give you the amount left over
to cover your fixed cost.
So by taking total fixed cost by this contribution margin,
dividing it, it gives us the exact number of units.
Now remember, we have a twist.
We have 2% of the boomerangs cannot be sold.
But remember, that 2% does not affect the variable cost.
So we have to multiply only times the revenue.
So for every we're assuming $16.50,
we have to take away 2% of that.
So we say, times in parentheses, 100 minus the 2%,
which is our D, close parentheses.
And that's the formula that will give us our break-even units.
So if I hit Enter, I can come down here, equals.
Click on my Break Even Units Formula tab.
And now we can make our formula, equals fixed cost, divided by,
open parenthesis.
And again, most of the time your formula
is just per unit revenue minus per unit variable cost,
close parentheses.
But that doesn't work for us.
We have to take 2% from every single assumed revenue sale.
So times in parentheses, 1 minus D. Close parentheses.
And that will do it.
Now, when I Control/Enter, it's sucking our number formatting
here.
And I need to wipe this away, because this is not dollars.
This is units.
You can go to Home,
over to the Number group, the dropdown in General, or you
can use the keyboard Control/Shift/Gr
ave/Accent/Tilde.
By the way, that Grave/Accent/Tilde key
is to the left of the No.
1 and above Tab.
Now, these are units.
We do not want partial units.
And I'm going to round this.
And I always want to round up, because we're
going to have to sell to the next one to break even.
So I'm going to hit F2.
And I'm not using the normal Round function.
I'm going to use Round Up.
And the rounding convention for Round Up, comma,
for number of digits is the same for Round.
And actually we've seen this function in the Round function
a lot in our prereq classes.
We know that if we're rounding to the whole number or integer,
I'm typing a 0, close parentheses, Control/Enter.
So there it is.
2662 is our break-even in units.
And this is a dynamic formula.
If we change any of our inputs, everything updates.
Control Z, because I'm going to leave that 1650.
All right.
So in this video we just saw how to create our break-even point
in units formula.
Next video we're going to take Goal Seek to a new level
and do data tables.
All right.
We'll see you next video.