Example of how to build a sensitivity analysis table in Excel to evaluate how changing two inputs simultaneously will affect an output (profit). This is one of the functions on the “What-If-Analysis” section of 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 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
Transcribed from video
Sensitivity Analysis – Microsoft Excel By Eric Andrews