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