Sensitivity Analysis - Microsoft Excel

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

Channel: Eric Andrews

Views: 555,179

Rating: 4.8675251 out of 5

Keywords: Sensitivity Analysis, Sensivity Table Excel, Sensitivity Analysis in Excel, Sensitivity Analysis Problem, Sensitivity Analysis Accounting Formula, Sensitivity Analysis Chart, Sensitivity Analysis Chart Excel, sensitivity analysis excel, sensitivity analysis explained, sensitivity analysis in excel, sensitivity analysis example, sensitivity analysis finance, sensitivity analysis template

Id: N924D6tGOG8

Length: 4min 23sec (263 seconds)

Published: Tue Aug 02 2016