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

# Sensitivity Analysis in Finance | Calculating Break-Even Point

1 year ago
No Comments