so today we're gonna look at sensitivity analysis in Microsoft Excel this is Microsoft Excel 2016
in a business scenario where we want to look at different combinations so sensitivity analysis
is basically a type of analysis where you look at different combinations of assumptions and you
examine the result so here we're going to look at different number a different number of units sold
and different price points to look at the profit at these different combinations so I set up a
very simple financial model we have assumptions let's assume this is a chair store we are selling
a thousand chairs a year one hundred and fifty dollars a chair and our cost per chair is is fifty
dollars and our fixed costs which are our store rent and our payroll are right here and so if you
look at our income statement or profit and loss we have our revenue our cost our cost of sales we
have our gross profit fixed cost and our operating profit so I want to analyze our operating profit
and look at different number of units sold so what I'm thinking about is okay so I want to change
the price okay so I'm gonna change the price but I know if I raise the price I'll probably sell fewer
chairs and if I lower the price I'll probably sell more chairs so what really is the best combination
for my operating profit and so let's you know to answer this question very quickly we're gonna need
to build a sensitivity analysis table so here's how you construct the table the first piece of
this is basic formatting so I like to put a line along each edge of the table so it's very clear
sort of what we're doing and so we start here and along the row axes I want to do chair sold
so here's where we're going to look at different numbers of units and along the column we're
gonna do price of the chair so chairs sold in the midpoint let's do a thousand and on the midpoint
here let's do 150 because that's what our current model has for the assumptions but I want to look
at sort of the tail end up and down so let's look at 750 five hundred 1250 and then 1500 so we know
at a higher price point we'll probably sell fewer chairs so maybe this is 175 okay this is 200 and
you know as we have lower price points you'll see that we'll sell probably more chairs so now that
we've set up the basic table there's there's one last step and you need to link in the result that
you want the table to populate which is operating profit so you link it in here in the corner of the
table so at this point you highlight the entire table including that corner you go to the data
tab and then what-if analysis select data table and for your row input we have chairs sold and
you need to link in the actual assumption so we've linked in our row input and then column input we
have price so we link in price and if we click OK we will populate the table with operating profit
so at a hundred at a hundred dollars and 1500 chairs we'll actually make fifteen thousand of
operating profit which is less than our current model and so what we see here is at some of these
higher price points you know at five hundred chairs and two hundred dollars we're making only
fifteen thousand dollars compared to the current model here so these types of tables will will
allow us to price products and and and and just look at the results as things change and they're
very helpful in business so I actually have a course focused on Excel for for business analysts
and I have it on udemy I'll include a link to that in the details thank you for listening