# Sensitivity Analysis in Finance | Calculating Break-Even Point

hey everyone eric here in this video i'm
going to teach you how to use sensitivity   analysis in microsoft excel to help calculate
the break-even point of a business so first   off what is sensitivity analysis it's here in
the data tab and it's under what-if analysis   what-if analysis functions help us to analyze
situations where we change variables inputs   so in our case those would be the assumptions
of the underlying business like the price   like the customer return rate things like this to
try to look at how those inputs affect outputs so   in a financial model the outputs are any of the
metrics in the model the revenue and the output   that we're analyzing in terms of breakeven is the
operating profit so with sensitivity analysis you   look at different combinations of inputs and
how those combinations create different outputs   so first off let's take a look at the model
and then i'll dive in to show you exactly how   to perform this type of analysis it's a very very
powerful type of analysis that i use all the time   okay first off so we have a basic
income statement we have revenue   you know cost of goods sold gross profit then
typical operating expenses personnel advertising   you know other opex which is like fixed expenses
and then your profit so what are the inputs   going into this model so first off in terms of
the revenue we need to figure out how many new   customers we have coming in the business so this
is very simplified but the structure of this is   actually how you build a much more complicated
model so you can learn something from this   so we have the advertising budget then we have
something called the customer acquisition cost   the customer acquisition cost if you don't know
what it is and i'll include a link to another   video where i explain it in depth below but is
the total marketing dollars you spend to get   one new customer to buy at your company so for
instance let's say that you spent a 100 dollars on   facebook advertising and through those ads two
new customers purchased your product the customer   acquisition cost would be the 100 divided by 2
so it would be 50 dollars in marketing for each   new customer who comes to the business so in our
case we're saying that the customer acquisition   cost is 75 dollars so to calculate the total new
customers all you do is you divide the advertising   budget by the customer acquisition cost and that
gets you the total new customers in addition   let's say year one is like our launch year but
in year two we have a lot of customers from year   one who come back and they return and they buy
again in year two so we have an annual return   rate of fifty percent and we calculate returning
customers as the total customers from year one   and fifty percent of them come back and they buy
in year two and then again in year three we have   the total customers from year two fifty percent
of them come back and buy in year three and then   we have our total customers so all we need to do
is take this and multiply it by the average order   value so that's the average revenue for one order
and we get the revenue so we just multiply those   two things together customers times average
order value we get the revenue similarly the   cost of goods sold is just the total customers
times the average cost of goods sold per order   finally and this is the easy part personnel is
just a number that we got basically straight from   our assumptions let's assume you know the team is
a couple people and it's not going to be growing   the advertising spend we just pulled straight from
here and finally other opex we pulled straight   from here and then we have our operating profit
so our company has investors they want us to try   to break even in year two or three so first they
want to know hey can you break even in year two   and so there's so many combinations of
variables that could create a break-even   situation but how powerful are these different
combinations of each variable so question one   they say okay what return rate and advertising
spend could get you profitable in year two   so we're going to look at that so let's set
up a sensitivity table so sensitivity table   rule number one the rows and columns these
two sections right here must be hard-coded   no linking no formulas you got to just type the
information in so let's start so we know that on   one hand we're going to look at the return
rate right and on the other hand they want   us to look at advertising spend so let's just
say ad spend and again this is all in year two   here let's make it a little shorter year two okay
so the second rule and if you break these rules   you're going to be leaving me tons and tons of
comments and saying that my video was wrong blah   blah if you follow these rules everything is going
to go great so rule number two you must link in   the metric that you are analyzing in the top left
hand corner so that is operating profit in year   two this is what we want to understand so return
rate so our base return rate in the model here   is 50 percent um okay so let's put in
some other return rates so 30 40 50 60 70   80 percent and let's say 90 percent probably
anything higher than that is is ridiculous   and then second we have advertising so our base
advertising in year two is four hundred thousand   um and now let's basically show like a range of
situations because they say at what return rate   and advertising spend could you become profitable
in year two so we don't know if that means hey   what if we spent less could we be profitable
what if we spent more could we be profitable   so here you can see that i'm just showing sort of
a a range from one hundred thousand all the way to   seven hundred thousand okay now highlight the
entire table including the rows the columns and   your linked cell go to the data tab click
what-if analysis and then click data table   now it says row input cell this is the assumption
that we are modeling in the row so that's the   return rate in year two column input cell this is
the advertising spend in year two so now click ok   okay so what exactly are we seeing here so these
are all of the different operating profits that we   would have at these different combinations of
variables so here at an 80 percent return rate   and 500 000 in advertising spend we
would have 159 000 in operating profit   and at a forty percent return rate and
two hundred thousand in advertising spend   we would have uh negative two hundred
and thirteen thousand in operating profit   so what is this basically telling us well i think
the main thing it's telling us is that uh we're   not going to be breaking even in year two so
we would have to go back to our investors and   say look we've done the analysis but i don't
think this business can break even in year two   maybe it can break even in year three we should
look at that so that leads to the next question   question two so they say okay fine year three
okay what if you lowered your cost of goods sold   and you also did a lot more advertising in
year two do you think by year three you could   get profitable if you spent a lot more in ads
in year two and you got a lot of new customers   into the business in the second year which then
became returning customers in the third year okay   so this is a little complicated but it's a
perfect situation for a sensitivity table   so i'm just going to copy down my
format because i can reuse some of this   i'm going to delete the inside of the table and
okay so they said how much you know what if we   really bumped up our advertising spend in year
two um would that make us profitable in year three   another thing they wanted us to look at is
not return rate but it's cost of goods sold   and so assuming our cost of goods sold is the
same every year um we'll be modeling this in year   in year three okay so cost of goods sold so
our cost of goods sold we're saying it's it's   37 dollars but let's put 37 dollars as the highest
because they say hey you know what can you do to   to lower this number so let's say you know we can
negotiate with some vendors we can change you know   the way we're doing something and we think we
can get that number a little lower so finally   our output in this case this was the ebit in year
two but now we're looking at ebit in year three year three okay so here we link in our
operating profit and again just remember   year three operating profit link it in okay table
is set up will this thing break even in year three   click what if analysis data table
woops sorry i i highlighted too much so highlight the table click
what-if analysis data table   so the row is your cost of goods sold
in year three and you can see that here   and your column is your advertising in year two so
can you acquire so many customers in year two that   you break the break even in year three so click ok
and the answer to this question is actually yes so   you see these are all the situations where we
spend seven hundred thousand in advertising in   year two and in the model we had four hundred
thousand so you can see that actually in any   of these scenarios even if our cost of goods sold
is still at 37 we'll actually break even in year   three so this is where you would go to you know
the board of directors and say look we need to   invest a lot more money in year two in advertising
because we'll actually have a break even business   in year three and so uh we're going to turn the
loss in year two but it's going to come back and   help us in year three so this is this is the type
of situation you're always trying to figure out   when you're working with businesses so again this
shows why this type of analysis is so powerful   so finally question three is can you just spend
your way to profitability at a flat customer   acquisition cost so meaning your marketing
doesn't get any more efficient it costs   75 to get a customer period no matter what and
let's say they're talking about um year three so   spend your way to profitability
in year three at a flat cac   okay so we're going to be changing ad spend in
year 3 and cac is not going to be changing so   how do we set this table up because you know
one variable's not changing and the other is   well this one's pretty easy so what we'll
do is we'll just copy a part of the table so   here you'll have cac and you'll
just put in your normal cac number   delete out everything inside the table and then
ad spend so they're saying you know can you just   spend millions and millions of dollars and you'll
be profitable so let's see okay so let's start at   five hundred thousand dollars in spending and
i think i just put in five million dollars   um and let's just add a million dollars a year
to it uh sorry a million dollars in each in each   cell so i think i need to
expand the column a little okay cool great and so now this is ad spend in year three
and this is cac in um in year three and this is 75   so let's just label that so
that we don't get confused   and here we link in operating profit which is ebit
earnings before interest and taxes in year three   link it in now highlight the entire table
make a guess do you think this is going to   get us profitable so the row input here
customer acquisition cost in year three   column input is advertising spend in year three
so what we see here is actually the more we spend   the bigger of a loss we turn assuming that
we're spending within the same year so we saw in   this table that if we spend a lot in advertising
in year two we actually end up profitable in year   three so in this situation we're saying if we
spend a lot of advertising in year three well   we won't be profitable in year three probably
would be closer to profitability in year four   but in this situation we'd go back to investors
and say we cannot spend our way to profitability   um assuming we ramp it up in year three
that's just not possible mathematically so i just showed you three sensitivity
analysis breakeven point tables so i hope   you feel really confident performing those
and doing that analysis yourself by the way   in the description below you can download
this excel file completely for free   i've also linked to some of my other startup
and finance focus videos as well and if you   found this content valuable please subscribe and
click the alerts notification like this video and   leave me a comment below and also check out my
online courses if you need to improve your excel   and finance skills that's all for today thanks
for watching and i'll see you in the next video 