Sensitivity Analysis – Microsoft Excel 2016

42
4



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.

RESOURCES & LINKS:
____________________________________________
► Download the practice file we use in the video:

My Courses:
► Become an Excel Power User in 2.5 Hours (81% discount):
► Master Excel Keyboard Shortcuts (85% discount):

Related Financial Modeling Videos:
► Using NPV & IRR to Analyze Investments:
► Build a 3 Statement Financial Model:
► Goal Seek in Excel:
► Build an Excel Combo Chart (Dual Axis):

► Subscribe for more finance videos:
____________________________________________

Sensitivity analysis in Excel is one of the skills that separates the common Excel user from the power user.

Often we try to analyze business problems my thinking about what will happen if certain things change. For instance, what happens if we change the price? Well, the revenue would go up if we just changed price for a business, but total customers stayed the same.

However, the reality is that in reality, you never have scenarios where one variable changes, but everything else stays the same. There are always repercussions. When you raise the price, usually some customers will stop buying, but some will continue.

So when trying to analyze situations, it is better to try to build models and analyses that reflect many different variables changing at the same time.

This is where sensitivity analysis comes in. Using this Excel feature, we are able to examine how multiple changing variables will affect different business metrics. To do this, we can build a “sensitivity data table” to examine the range of possible outcomes.

In this tutorial, we learn how to build a sensitivity analysis table in Excel 2016 to evaluate how changing two inputs simultaneously will affect an output (profit).

I hope you enjoyed this tutorial, please subscribe to my channel:

► Subscribe for more finance videos:

If you’d like to improve your skills and resume, I built a business-focused Excel course on Udemy that teaches the 20% of Excel used for 80% of business.

The course is 2.5 hours and includes things like:
-Completely abandoning the mouse to increase speed 3X
-Data manipulation & visualization
-Conditional statements & pivot tables

Thousands of people from over 80 countries have taken the course, with the skills helping them pass college exams, receive job offers, and even get raises. Take a look at the reviews!

Nguồn: https://vnecon.vn/

Xem thêm bài viết khác: https://vnecon.vn/tong-hop

42 COMMENTS

  1. Show how to configure the table on the left side first, before jumping to computing. That's half the work.

  2. Hey! Love your videos. Straight to the point and easy to pick up. However, Im having trouble trying to understand how does this table derive its numbers?

  3. Check out my brand new video on valuing companies using the Discounted Cash Flow Method ► https://youtu.be/Gm0Xn_MidZ8

  4. Hey Eric I have a similar kind of analysis but this involves a equation containing the involvement of parameters taken in the row and columns, can you help me on that. I will share more details if you are interested

  5. Actually, I have a question. I just read through your course description on Udemy. I’d be interested in enrolling but I have a Mac and was wondering how dramatic the drawbacks are since you recommend having windows. What I’m wondering is whether there will be merely slight differences in keyboard shortcuts or more substantial disadvantages which will not allow me to properly follow the course and execute all functions you teach. Thank you for your answer in advance. All best, Albert

  6. What if analysis and sensitivity analysis are not the same thing. This video is useless for my sensitivity analysis problem.

  7. I built a tool that lets you run a sensitivity analysis by simply uploading your Excel file: https://causal.app/sensitivity

  8. I built an Excel sensitivity analysis tool – https://causal.app/sensitivity. It figures out which variables in your model are the most important, and shows you what happens if you vary each variable one by one.

  9. The video is a very good example. this type of analysis will need some marketing facts as well in order to find the best price strategy.

  10. Eric, I set up the table just like you do in the video and followed the steps to the letter, but after I input the variables into the Data Table and click OK the OP remains the same at $4000 under each assumption.. I am using Excel 2016 as a part of the Microsoft 365 suite.
    What's wrong?

  11. hi, when i do the same method, I obtain the same numbers in all the boxes (the one we put in the top left corner). Does someone know where the error might come from ?
    Thanks

  12. I've never seen a YouTube video that is so precise, concise and straight to the point before. Thank you, Eric

  13. Thanks Eric! Very clear explanation. I have some questions regarding your keyboard shortcut operations. I saw you quickly removed two decimal places, but with alt key, and H and 9, you can only remove 1 decimal places. How did you manage to remove two decimals in only one or two press on keyboard? Thanks for your help!

  14. As a Chinese, his English still make sense to me to understand how to use Excel Table function to solve practical problems/assignments. Many thanks!

  15. how come the column and row cell selections gets mixed up when data is analysed – how can your prevent this ??

LEAVE A REPLY

Please enter your comment!
Please enter your name here