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

test attribution text

Add Comment