Sensitivity Analysis - Microsoft Excel - YouTube

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