Sensitivity Analysis - Microsoft Excel

[@eric_andrews]

I made this video in 5 minutes at my kitchen table one afternoon and forgot about it for literally 4 years. Still can't believe how many people watched it. Thank you 🙏

[@sudeepm8096]

Old is Gold!!! 🪙

[@eric_andrews]

@@sudeepm8096hahaha

[@geno8079]

fr

[@JeanPaulUS]

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

[@eric_andrews]

Very, very happy to hear it. Cheers!

[@shinlanten]

No matter how much experience you have using Excel, there's always something new to learn 👍

[@pennyatglitter]

If you are getting all the same figures. Highlight the table and click F9.

[@anthonyinyanya213]

Mine is still not changing

[@snigdhabhatnagar8001]

You explained everything so clearly and didn't waste any time! Everything so precise.

[@eric_andrews]

I'm so happy to hear that you were able to learn some new concepts quickly. Cheers Snigdha!

[@AbelSimbel]

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?

[@Daschickenify]

@I'mBatman You just helped me out, I had the same problem! The table has to be fed a function for that top left cell, not straight data. You can't just type it in, gotta build the formulas starting from your assumptions and feed the table with that.

[@gmjarabejoii9675]

Thank you for the help batman

[@eric_andrews]

Hi Clarence - I see you all helped each other in the comments, nice work! Yes you need to enter the formulas for the revenue, cost, and profit part. Also, you'll see that I included a link to download the file in the description. So if you have any other questions you can just download it and take a look. Cheers.

[@firasezraa]

gg bang erik kamu sangat membantu tugas akhir aku bang semangat ngontennya !

[@JayKayHP27]

The part where the data table self populated blew my mind. Thank you Eric

[@dimasnaufal2080]

GG Bang

[@swiadiapkhazava1119]

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

[@eric_andrews]

Revenue = chairs sold * price COGS = chairs sold * cost per chair Gross profit = revenue - COGS Operating profit = gross profit - SG&A This is the normal format for an income statement! That help?

[@geraldmaina7709]

Simple and direct.. Thanks Eric

[@eric_andrews]

My pleasure!

[@XJIcequeen]

What if you were on a Google Sheet?

[@eric_andrews]

Check out my brand new video on valuing companies using the Discounted Cash Flow Method ► kzbin.info/www/bejne/fZ6TiaGVgs6XkJo

[@isabelsuareztorres9510]

A qui empezó la leyenda

[@imirosmanov2745]

Dear Eric, could you please explain how to use sensitivity analysis for stress testing of operational risks?

[@eric_andrews]

Yes here is another video I have on sensitivity analysis for finance: kzbin.info/www/bejne/hYace4h4l86cjcksi=-h3dialK-8jf8s1_ Stress testing operational risks is basically just playing around with the assumptions in your model (higher sales, increased costs, etc) to see where the business is sensitive and at risk of large declines in profits. I show that in the video I linked to.

[@norbertvillas]

What about on service business industry, with different subscription service like netflix?

[@JumpRopeVeteran]

Great video! This is a really cool way to use What If analysis. I just subscribed. Thank you.

[@eric_andrews]

Really glad to hear it ! Thanks for the sub 🙏

[@mazaheerrajwani]

Thank you Eric, that was useful and easy to follow

[@nickhastings6705]

Great video! However when I follow the exact steps using my figures, I just end up with the same number across the table? Any tips? Thanks

[@eric_andrews]

Most likely you are not writing the formulas correctly in the model. Make sure that everything is formulas, EXCEPT for your assumptions, those are hard-coded. Feel free to download the practice file for free in the description to cross reference! Cheers

[@MichaelAwoleye]

This is what happened to me as well, I followed the example and did mine but the excel generated the same figure all through the cells.

[@mjime128]

@@eric_andrews I have the same problem as Nick. I downloaded your practice file and I am able to generate the what-if analysis. I notice your file only has the operating profit as a formula and that is exactly what I have on my file. Any other suggestions?

[@yohancerodriguez6600]

@@eric_andrews Oh, I see now (0:55 to 1:08). The Profit and Loss parameters all tie back to the assumptions through the background formulas. Pardon the ignorance, I am less than a novice, is there any way to derive CIs from this SA?

[@spongebozz8704]

FUCK MY LIFE ARRRGHHHH --- WHY DO I NEED AN EDUCATION??!!

[@maytalksmack]

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

[@eric_andrews]

Hi Brent - if you have any questions, you can download the actual file I used in the description. My guess is that you are hardcoding something that needs to be linked!

[@ummrahaf6914]

Very useful. How come it is all 4,000 as an answer for me?

[@javeliaallen7224]

I tried this and all my figures in the body of the email came out to be the same as the operating profit...HELP!!!!!!!!!!!!!!!!!!

[@eric_andrews]

Hi Jav - I recommend you download the file in the description to troubleshoot and cross reference. You must have something hard-coded that should be formula. Hope it helps!

[@ahsanbaig2959]

I have Excel 2016 I created a table just like that and did sensitivity analysis like shown in this video but I'm getting the same number for my values. All my numbers are hard coded, I also deleted the values (not assumptions) then I get 0 for all my values. Not sure what's going on!!

[@eric_andrews]

Hi Ahsan - here is a link to download the file used in the video. excelpoweruser.weebly.com/blog/youtube-file-download The issue is that in the financial model, you need to make sure you are using formulas. The revenue needs to be Units * Price, the costs need to be Units * Unit Cost and the whole model needs to be linked to itself as well as the assumptions. Take a look at the practice file I think that will clear everything up for you!

[@sandunidilrukshika8188]

Thank you very much. This was helpful to me.. Thanks again

[@angelicafrandsen5771]

Studying my MBA, other than 'sensitivity analysis' does anyone know what this method is called ?

[@eric_andrews]

It is a type of "what if analysis" which is a type of scenario analysis

[@souravde8276]

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

[@zhuoqinghan6272]

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!

[@eric_andrews]

That's great to hear it was easy to understand. Thanks for the comment 😁

[@MsLaurenfish]

Super helpful! Thank you!

[@albertrzeszutek2645]

Great Video. Clearly explained! Thank you !

[@usamaalhasni9300]

I have an issue with this. when i do what if the the only numbers i got is 40000 so what is the problem with this please could you response??????

[@eric_andrews]

Have you downloaded the practice file? I would recommend you start there to compare your results.

[@chrisyip54]

Sorry, can I set different variable of chairs sold and price, just like up&down 500 or the variable was fixed in 250? Thanks

[@millycentaoko6669]

This is one of the most precise and succinct Excel KZbin tutorial that I have watched, thank you

[@eric_andrews]

Really appreciate the comment cheers!

[@TheLennard62]

How can I run the analysis with percentages (in my example the occupancy rate of hotel rooms) instead of (expected) sales? So the occupancy rate will be the row input and the price of the room the column input. Because now when I try I keep on getting the same answer (the net income) in every cell

[@angies451]

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!

[@eric_andrews]

Sure! Just do that same sequence a second time, or, you can tap F4 which will repeat your previous sequence - a nice trick!!

[@nurulathirahzulkifli282]

I followed the instructions, but somehow the result didnt change, i got the same result across all rows n columns. Any idea where I may make a mistake?

[@jurgvogt4366]

Thanks - very helpful and straightforward!

[@JavierBonillaC]

This is an excellent example for my son. I wanted a simple examp,e to introduce the concept to him.

[@stanislavandreev3853]

I think I am going to buy a chair :)

[@eric_andrews]

Haha - well now you have the analysis to back it up!

[@stanislavandreev3853]

Eric Andrews cool job. Kudos!

[@sirwankakai4393]

Why do you put (Price) in Column and not Row, and Chair sold in row?

[@eric_andrews]

Makes no difference, you can put any assumption you want in row, and any assumption you want in column! Works perfectly fine the opposite way and will give the same numbers. Cheers

[@lukaskobis7904]

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

[@anthonyinyanya213]

I'm getting the same answer for all parts. What should I do?

[@eric_andrews]

Make sure the column & row assumptions are referencing hard coded numbers (no formulas). Make sure the numbers along the borders of your data table are also hard coded (no formulas), and the top left corner is linked to a cell with a formula (the metric you are sensitizing). Finally, delete everything in the table, re-highlight everything, and run the analysis again. That should work.

[@marsmcfly]

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

[@micahsnow346]

Hey there, what if you're trying to do a sensitivity table that has only one input?

[@im4485]

Thanks you this is awesome. I hope you have a wonderful life.

[@hollybazley5149]

I tried this but all I got was 0s for my table I have no idea why excel hates me

[@eric_andrews]

Did you download the file? Free in the description. I would start there. Cheers

[@hikikomorihachiman7491]

Thank you very much

[@datalearningsihan]

Well, these types of table, you will never use in a real life scenarios.

[@eric_andrews]

We use them all the time in finance, banking, investing, business, marketing, etc

[@TimFlaherty]

Thanks for responding to my email on a Sunday within 2 hours! You really helped me out today and I look forward to accessing your coursework in the future.

[@eric_andrews]

Hi Tim - no problem! Just let me know if you have any more questions.

[@kevinlor5299]

Home come when I link my OPerating profits and do the what if analysis, putting in my sold and price. All my numbers in tommy data table are all the same as my profit? I must be doing something wrong

[@eric_andrews]

Hey Kevin - here's what to do. Delete all of the data inside the table (not the assumptions). Then re-highlight the entire table and re-apply the what-if-analysis. Also, make sure 100% that your assumptions in the table are all "hard-coded" (just typed in) data. Try that again and let me know if it works! Sometimes when you mess with the table a little it stops working and you have to start over.

[@MAWallDahl]

Hi Eric, I'm having the same problem as Kevin. I did as you suggested but it is not fixing the problem. My prices range from 1.70 - 0.90 and volumes from 200,000 - 1,000,000. Is it a discrepancy in scale? Also I have 9 prices and 9 units sold. Is there a limit on the size of the matrix? Thanks.

[@eric_andrews]

Hey morgan - hard to say exactly what the problem is without seeing your work. Can you send me the file? I'll take a look and send it back. My email is [e-mail]

[@MAWallDahl]

Huh, I was recreating my file because I had deleted the one with problems. I copied and pasted the link to my net profit in the upper left corner of the data table, and in doing this instead of entering "=F15", it seems to have solved the problem. I came across that method, the Paste Link, randomly about 5 minutes ago, but it seems critical to this.

[@MAWallDahl]

Eric, I looked into this further b/c it was really bothering me. It looks like the "Calculation Options" on the Formulas Tab there are three options: Automatic, Automatic Except for Data Tables, and Manual. I don't fully understand what these options do, but the Data Table works much more smoothly if that option is set to Automatic.

[@shereengobran2726]

Hello Eric! I tried to make the sensitivity analysis, but it takes the last cell in the row of the table and make all columns the same figure of that cell, so please I need your help!

[@eric_andrews]

You are either hard coding something that needs to be formula, or doing formula for something that needs to be hard coded. Download the template to double check!

[@shereengobran2726]

@@eric_andrews Thanks for your prompt reply! I downloaded the template!

[@oxygensilver4563]

Helpful thank you!

[@7stringsbd368]

Thanks Eric. It was effective

[@feba78]

I follow, the others, Thank you, fine training!

[@kennyphan9602]

makasi bang erik aku sangat terbantu dengan penjelasan abang 😀

[@anujkumarshukla3881]

Short easy clear and crisp

[@eric_andrews]

Awesome - glad you found it helpful.

[@muhammadyaqoob9293]

Attach excel file, much better for user benifit.

[@eric_andrews]

It is right there in the description dude

[@MrWILDDER]

Love your work

[@Sankalp_359]

What is SG&A?

[@albertrzeszutek2645]

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

[@eric_andrews]

Hi Albert, in general, the course is much better on a windows OS. It's not terrible on a Mac, but the keyboard shortcuts will be much different. You could however give it a try and if it doesn't work out, just get a refund. There's a 30 day money back guarantee. Thanks and good luck!

[@mridila12]

Thank You!

[@Amanda-iq5jd]

Mine keeps filling all the boxes with either all 0 or all the original net income. What am i doing wrong. Please help me

[@eric_andrews]

Hey Amanda - I suggest you download the practice file (link in description) and cross reference. I imagine something is either formulas that should be hard coded, or vice versa. Cheers!

[@Amanda-iq5jd]

@@eric_andrews thank you! I ended up doing the calculations myself because I couldnt figure it out in time, but your video was still very helpful.

[@cjgustafson2953]

thanks I was looking for this explanation online for an hour

[@lukas4132]

Great video, short and simple, cool :)

[@sheenabian9368]

so cute lol great video

[@mrCetus]

great video! Love kind of short lessons. Thank you and waiting more ones like that!

[@eric_andrews]

🙏

[@aksy970]

ive seen this video like 15 times :D (lifesaver each time)

[@kyiwintun4253]

Hi Eric, thank you very much for the video. I kept getting 40,000 in all of my What if table. Is there sth I did wrong in terms of cell selection? or Is my Mac Office outdated?

[@eric_andrews]

Download the template in the description

[@zaheerulislam6736]

please do scenario analysis for the same variables used above

[@willowrose7791]

Can someone tell me why once I link in both the row input and column input it just puts the operating profit in all the cells? It’s the same number in all the cells. Please help me out!

[@eric_andrews]

(1) make sure the row and inputs of the sensitivity table are hard coded (typed in, no formulas), and (2) make sure you delete all the inside part of the sensitivity table and then re-run the what if analysis again. One of those should solve it.

[@willowrose7791]

@@eric_andrews Thank you so much for your reply! I have manually typed everything & deleted everything inside the table. Every-time I change the figure of the operating profit that’s linked in at the top left of the table, it automatically changes all the cells to the figure of said operating profit. I’m super new to excel so I’m probably not doing something correctly but I have followed your great tutorial to a T! Let me know if you have any other suggestions.

[@eric_andrews]

@@willowrose7791 no problem! Ok, I think I know what the issue is then. My guess is that your "operating profit" cell you're linking to (in the income statement) is hard coded? Make sure the operating profit cell is a formula, not hard coded.... Have you downloaded the practice file to double check? That would probably answer it for you. Cheers

[@bradcameron9798]

so helpful for my finance Assignment

[@swapnilgupta9644]

Can you please make a video on MOTAD

[@Wacc1611]

Dude I love you 💕.... You explained the complex topic with lots of ease....

[@eric_andrews]

Happy to help 😀 thanks Danish!

[@rajupakalapati]

you are making my life easier Thanks man :)

[@jamaldeenmahdi379]

I tried doing the same and excel is telling me input is invalid, what do I do?

[@eric_andrews]

Download my template in the description

[@jamaldeenmahdi379]

@@eric_andrews It works now. Thanks.

[@bobbobbgarte922]

Does not help en.m.wikipedia.org/wiki/Sensitivity_analysis

[@selimdu]

Thanks. Easily Understood.

[@devarajugowda0279]

How to find LDR and HDR in sensitivity analysis

[@kakacricket]

Hey ur a legend

[@franciscovilela7697]

My guy! Thanks a lot Eric

[@k8k8k8k]

is it possible to have a sensitivity analysis for service companies?

[@eric_andrews]

Of course. You can sensitize anything!

[@k8k8k8k]

@@eric_andrews how?

[@suzy5166]

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.

[@eric_andrews]

Those are exactly the type of situations you should use this analysis for. Great point suzy!

[@matanwax]

thank you!

[@imanetourabi7156]

This was very helpful Thank you

[@nicolascook3701]

Now how would you best chart this?

[@eric_andrews]

Displaying this in a table is the best way to tell this complicated story! I wouldn't chart it! In general I wouldn't recommend visualizing anything where the underlying data can be understood easily - then most people just ask for the underlying data anyway. If you wanted to try, you could probably chart one row at a time though, that could be interesting.

[@ummrahaf6914]

No change after pressing F9

[@eric_andrews]

Download the file. Build the whole thing from scratch exactly how I do in the video, delete all data out of the sensitivity, re-highlight and re-calculate the whole thing. You got this

[@dekass7965]

I got a job in investment banking because of you! Thank you u a legend!!!

[@eric_andrews]

Wow really happy to hear it. Which videos helped you most in the interviews?

[@hossamsobhey5056]

Thank you so much for such a great video.

[@williamrivera162]

Very instructive video, thanks you so much for teaching.

[@eric_andrews]

You are very welcome William!

[@sabyasachikabiraj]

I have multivariable, not single in that case how to do this? can you explain it

[@eric_andrews]

I usually run a bunch of different sensitivity table analyses off the model so that I can understand more about how the different variables interact with each other. You would basically just repeat the same process but testing the sensitivity of various variables, I have a new video on it, check it out if you're interested! kzbin.info/www/bejne/hYace4h4l86cjck

[@endangeredspecies5005]

didnt understand at 2:49. can you explain it in slow, so we can learn. thank you.

[@eric_andrews]

Sure. So for the sensitivity table, just type in the assumptions (you want to test) on the left side and top of the table. But in order for the table to work, you must write a formula in the top left cell of the table (cell E5 in the video), which is = and then the metric you want to analyze. So in this case, it is =A17 (because A17 has the profit, which we want to re-calculate with different assumptions). Does that help?

[@onomeogethakpo7988]

Great video, thank you.

[@novafarasadi8017]

thankyou. this is more simple than the other i've been watch...

[@eric_andrews]

You're welcome!

[@Dingkz7777]

Brilliant

[@dealiq]

Awesome

[@chandrabhatt]

precise

[@rekianour]

E-mail please i Want To ask u about sensitivity analysis

[@TelecomInsights]

very concise and precise. thanks

[@yurypopov6149]

What if there are 5 more competitors and cross elasticity of demand?

[@eric_andrews]

The hardest part would be building a model for that. Once you have a model you could sensitize the assumptions no problem. Maybe there is some % revenue change driven by competitors % price changes...not sure exactly but it's for sure possible

[@gayathrim3194]

Thanks, this is so simply and nicely explained. How do i do the same modeling for 3 variables-Price, cost and vol all within the same table?

[@eric_andrews]

Not possible unfortunately. You'll need to build a few different sensitivity tables looking at 2 variables at a time. Hope that helps

[@noreennkuraija7544]

Wow! Thank you so much.....easy and precise!

[@eric_andrews]

Glad it was helpful Noreen!