# Basic Excel Business Analytics #04: Excel Formula for Units Break Even Point and a Unit Defect Rate

On October 8, 2019 by Raul Dinwiddie

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.

It is great to see how this is building up! Thank you again!

Office 2016 is here, will be great to know your take on the new features.

Your videos are awesome. You are awesome because you share your knowledge and help people to improve themselves.

hi, mike! every video of your is amazing. i have decided to watch each one of them

cool i love how organized you are awesome

Mike, you have this amazing talent of explaining things so effectively, thank you!

Hello team , Could you please assist to provide video for Break Even Point (( QTY and Value )) by DAX and Power BI . Pleaseeeeeeeeeeeeee thanks for your usual support