Excel Financial Modeling | Sensitivity & Scenario Analysis

Use sensitivity analysis, scenario analysis, and goal seek/solver to evaluate different scenarios and forecast how a business is going to perform.
📝 Download the start file here: view.flodesk.com/pages/620fd889d328ec5152cf0a8f
👉 Excel for Business & Finance Course: www.careerprinciples.com/courses/excel-for-business-finance
1. Goal Seek / Solver: using goal seek, we can find an input to reach our desired output. For example, how many sales do we need to generate to make a million dollars. Excel Solver is the advanced version of this, where we can choose multiple inputs and set constraints to reach our desired output.
2. Sensitivity Analysis: using a sensitivity analysis table we can evaluate how changing a set of inputs will affect our profitability. The inputs we change are the price range and the quantity sold range. This is one of the functions on the What-If-Analysis section under the data tab of Excel.
3. Scenario Analysis: using a scenario analysis, we can see how different revenue and cost forecast will affect our profitability. For this we create a base, best, and worst case scenario, and use the choose formula to create a dynamic model.
LEARN:
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/courses/finance-valuation-course
👉 Excel for Business & Finance Course: www.careerprinciples.com/courses/excel-for-business-finance
📊 Get 25% OFF Financial Edge Using Code KENJI25: bit.ly/3Ds47vS
SOCIALS:
📸 Instagram - kenji_explains
🤳 TikTok - www.tiktok.com/@kenjiexplains?lang=en
📹 My FREE videos with MoneyUnder30:
- Index Funds Explained: bit.ly/IndexFundsMU30
- How to Invest: bit.ly/HowToInvestMU30
- Stock Market Investing: bit.ly/MU30Stockmarketinvesting
- How Zero Commission Brokers make money: bit.ly/CommissionFreeTradeMU30
- 50/30/20 Budgetting Rule: bit.ly/503020Method
GEAR:
📹 My Favorite Books & Gear: kit.co/kenjiexplains
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00​ -​ Intro
0:27 - Goal Seek & Solver
6:25​ - Sensitivity Analysis
9:22​ - Scenario Analysis
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

[@shrutigupta250]

I used to refer to this channel for learning basics of finance and ended up doing a degree in it. Now, I'm here to revise for am exam. Thanks a lot!

[@i123456987654]

Wow, you did a very good job with explaining the steps to goal seek and constraints...I love how you put on the screen the keystrokes...super helpful, thanks!

[@KenjiExplains]

Glad it was helpful!

[@sammy88l99]

Thank you so much Kenji for making these videos. Thank you for making education free 🙏

[@KenjiExplains]

Glad you like them!

[@alexrosen8762]

Your tutorials are just amazing. Very comprehensive with that extra touch. Thanks so much 🙏

[@malikkngn]

Thank you a lot for this amazing step by step content!

[@Sj-vs6mn]

You're videos are amazing! Very straight forward and easy to digest. I am learning so much, thank you!

[@ukemeisaac]

Great content! Great teaching. Thank you.

[@rohanagnelo7074]

Love this guys tutorials !!!

[@loremipsum3625]

Thank you very much for your knowledge, Sir. 🙏

[@rupomExcel]

I loved the way you presented.

[@derp.2898]

Instant subscriber, great job. I'll be bingeing your videos buddy thank you very much.

[@KenjiExplains]

awesome to hear!

[@mhknowledgeacademy6420]

Mr. Kenji. I love your videos. You explain very nicely and make it very easy to understand. Please make video on WACC calculation and modeling in excel.

[@duggie88]

Very nice video. Learned some good excel shortcuts and ways to do analysis

[@prathmeshsharma4258]

Thankyou so much Kenji💪

[@aliikhlaq]

Best video of the Excel tutorial

[@qww760]

The sensitivity analysis is great!

[@KenjiExplains]

Great to hear!

[@kingwei1838]

Awesome video, thank you!

[@asmaaljneibi881]

You are amazing! Thank you!

[@jayxpea]

This was great. Simple, to the point and very practical for every day business use.

[@KenjiExplains]

Great to hear thanks!

[@gvinodnair]

Simply brilliant and adds value to a subscriber. Keep it up Kenji!

[@KenjiExplains]

Much appreciated!

[@nywzhizhang]

Yeay finally you make this video. Thanks for considering your subscriber's request. Tons of thank you, Kenji. Keep it up!

[@KenjiExplains]

Thanks for the idea!

[@Fant0me972]

I really like this modelling series more DCF's please

[@brocklee3768]

Better than all of my professors combined.

[@giorgilobjanidze8297]

Very useful. Many thanks for your videos. Greate job

[@camlex6310]

Really helpful!! Thanks Kenji

[@KenjiExplains]

Glad it was helpful!

[@MuslimIslam01]

You are an amazing and thanks for sharing useful knowledge

[@erickportuguez3906]

wonderful and brilliant! the best

[@shreyansh1246]

you are simply the best ❤

[@tushargarg9118]

Your videos helps Please make a video on how to take assumptions to forcast financial statements like BS cashflow P&L

[@angelclarke1942]

Thanks very much for this explanation, the instructions were clear and I was able to build a scenario analysis

[@josephdelgado3619]

your videos are invaluable to those entering or looking to enter jobs in finance, thanks

[@neilr8738]

Thanks for putting out in depth content like this.

[@paulcompetente4329]

You're a legend bro! ✊ Amazing content! Keep it up! 🍻

[@KenjiExplains]

Thank you 🙌

[@richfrill]

thank you i learned a lot

[@Anon19146]

Great content! Keep it up

[@hariadiksa531]

Hi Kenji, thanks a lot for this lesson. How about forecast with pandemic issue ?

[@melvinsimbar7060]

You are actually amazing

[@ariesinlalaland9959]

Very useful, thanks for sharing!

[@KenjiExplains]

Glad it was helpful!

[@SOCI4BLE]

Absolutely clear, no BS, straight to the point and you make the content very easy to understand. Much love Kenji.

[@jp_skywalker]

Thank you so much. Now I have a better understanding on how to create sensitivity analysis. Do have a video or article about how to calculate company valuation or discounted cash flow?

[@hanniehyunnie1043]

wow you make everything easier

[@alm9707]

just amazing! This video is so well done!

[@KenjiExplains]

Glad you enjoyed it!

[@m.ssharma535]

Sensitivity & Scenario Analysis super useful

[@michaelkuhn275]

Great video! Thank u for including the short cut keys as well.

[@nikospaparis5500]

Great instructions

[@balaramsprasad]

Super video brother. Thanks for taking the time and making useful content for fin enthusiasts. Expecting more of these. Thanks

[@michaelquach7046]

Another banger video, keep up the good work!

[@KenjiExplains]

Thanks! Will do!

[@anthonyvazquez8742]

Excellent video. Love the examples directly in excel. It really helps give us the viewers a look at the practical applications of the theoritical concepts we are taught in school. This help bridge the gap. Thank you

[@KenjiExplains]

Great to hear! thanks for the awesome comment :)

[@zzaryee3572]

Thanks Teachers

[@ahnafjawadkhan2269]

Hey kinji thanks for the videos. It helps a lot. Can you make a video on FCFE and FCFF models

[@gurharkrishanbureau187]

Amazing. Pls post for trading and transactions comps

[@samanthaaaz22]

Very helpful!

[@exodus3243]

Really good content. Amazing, thanks for charing this knowledge :)

[@KenjiExplains]

Thanks for the nice comment! :)

[@Ockin4982]

Hello Kenji! Thank you for the quick tips and lessons, you really helped me in my career. If I may request a topic, can you bring up sensitivity analysis in the perspective of risk management? As in trying to find out from a financial model which change in an independent variable contributes the most to the change of the output. So that we can find out which independent variable to keep an eye on and divide our risk management resources accordingly.

[@adilsiddique980]

Well explained 👍

[@ankitaagrawal8273]

Thanks for the great content. You explain all the concepts in simple and effective way..

[@normang663]

Thanks Kenji, something about your videos that make excel really exciting to learn.

[@abhishekthakur110]

Thanks a lot

[@imirosmanov2745]

Dear Kenji, could you please explain how to carry out sensitivity analysis for operational risk stress testing?

[@jaimemoran5428]

insane, keep this way!

[@KenjiExplains]

thank you Jaime!

[@user-cf5yp1dd7x]

Your tutorials are great! Thanks Curious, how would you connect the 3 dynamic financial statements to different scenarios analysis with an existing small business and 3 years of historical data? Would you be able to do a tutorial on 3 connected dynamic financial statements for an existing small business (less than $1M revenue) with multiple debt assumptions?

[@masterexcel5762]

Very useful

[@imrandaud478]

God Bless you

[@emadr5780]

thank you very much

[@KenjiExplains]

Glad you liked it!!

[@Investment_Tradingstrategies]

wow ...Great wor k thanks for such nice video

[@jamessibanda2849]

very simplified explanation and easy to understand. i just have one question though, when doing sensitivity analysis, you used price as a variable in the column section instead of units but when i look in the table its indicating the opposite. why did you do like that. Also when i selected units as variables for column section and price as variable for row section, the results are different, do you know the reason for this?

[@Ari-lu5ve]

Great video. I loved how you included the keyboard shortcuts. It would be cool if we could look at more interesting/real world application data instead of sticking with the lemonade stand example. Also, I thought you moved a little fast but I was able to pause the video to catch up so its all good. Thanks again.

[@KenjiExplains]

Noted!

[@tryme113]

Great!

[@kemalserkanozturk9060]

you are the best

[@qww760]

I have used solver before, and it’s pretty awesome!

[@paradigm3174]

thanks

[@tristanchiu6944]

wow very thorough

[@KenjiExplains]

thank you! waiting for your next vid!

[@pulnisav.4956]

Thank you for your knowledge sharing. I really love your channel. It's very useful tips and inspire me to do better.

[@beckysu9728]

very good video

[@mostxtremenoob6384]

Kenji, could you do a video of monte carlo simulations for financial modeling?

[@jasminq2995]

Hi, how did you select the cells above for SUM function? Did you use keyboard shortcut? Thanks

[@tinanguyen8425]

thank you so much, you are the best excel for business and finance tutor, your practical examples made me addicted to excel

[@ai_dividend_daily_1]

nice video i liked it

[@tillysimmons3859]

Can you please make a video on spreading comps? thank you

[@nicogerst6007]

Just FYI for the 2nd example (data table) the values in the video of the table are different than if you were to input product price manually; even though it says integers of 6-10 in the column, the actual value is the goal seek approximation of 8.2134.... ±1. Thus, the values in the table differ if you input price manually or copy/paste the values

[@pinareruelkeroglu4832]

First of all, I have watched many Finance videos and paid a fortune on Udemy. But you are outstanding! Well done and please keep going. Now, a serious question, I am actually doing exactly what you are doing. However, I get completely different values when I run the sensitivity calculation in the matrix. How is this possible? (*confused smile).

[@user-qo8mz4cm7g]

First thank you for your well-designed education program. When I use SOLVER, solving your SOLVER example, I encountered different solution pair sets like (22,626 #units; 8,79 usd/units), (18,865; 9,99), (24,026; 8,43), (24,394; 8,35) and so forth other than your base pair set (25,000; 8,21). I guess they are endless. My question is, is that correct? And if so whether we can make an automated table like "DATA TABLE"?

[@maxmood19]

Hi Kenji or Everyone else that can help. Thanks for this insightful video. I am trying to build a model with 3 interactive scenario (base, best and worst) that I want to show and interact on each tab of the worksheet. By interactive, I mean every scenario change on one tab affects other tabs of the model also. Is that even possible? if yes, please help.

[@willzinner8813]

great vid again sir

[@KenjiExplains]

Glad you enjoyed it

[@qikong7269]

Thank you for sharing your video Kenji! I really like your tutorials - they helped me a lot during my interviews in PE shops.

[@KenjiExplains]

That’s awesome to hear thank you for your comment 🚀

[@solomonbhandari-young4154]

The best

[@Believeit_]

Keep it up bro.. Love from India🇮🇳❤

[@KenjiExplains]

thank you Sachin!

[@i123456987654]

Ah, goal seek, takes me back to my business analytics class

[@KenjiExplains]

haha the good stuff

[@mrCetus]

Hi bro can you explain DCF and football field please? With a real case? Maybe one company? And can you please please explain wacc? And the inflation

[@xWarPlays]

I have a question, I get a numbers report every week and it’s a pain to keep copying and pasting the numbers in a new table. Is there a way to have specific parts of the numbers Im sent to be automatically transferred to the new table?

[@LalitPatilcasper]

Great video. One quick question - In sensitivity analysis how did the model recognize 10K SGA expenses? We only gave input for SPPU & CPU?

[@wicem4]

same question here if you have found the answer after so long..

[@angelmaravilla4708]

Amazing Video!, Just one doubt: Why do you choose the "GRG Nonlinear" algorithm instead of tge "Evolutionary" Algorith??, what's the statistical reason???? (I used the second approach, and it provided other series of values for "price" and "quantity")

[@moonshinemagic1771]

Excellent - how does this video differ to the sensitivity/scenario analysis in your course? Do you have an practical (real life) examples for IB/compote finance in terms of sensitivity/scenario analysis in your course?

[@KenjiExplains]

Yes it’s based on real companies like apple and adobe in the course so we analyze their real financial statements. Check out the landing page for more details! www.careerprinciples.com/courses/finance-valuation-course

[@arindambhattacharya7848]

I agree we can do goal seek and solver to get the value of the equation we want..but does it work when I need to share the file to an audience and the audience be given the choice of choosing/changing variables

[@luckylucky7916]

About price and quantity sensitive, do you have any advice for Co., with multi products? G&A cost not easy to separate for each product, will we use average value of G&A?

[@Falkland82]

Can i kindly have guidance to down load the spread sheet used in the example.

[@nelsonlim8437]

If I were to have more than one type of units and price points. Eg. 20. Do I have the create this type of assumption 20 times or are there better options? Thanks

[@jarisstarling6696]

A must Subscribe Channel !!! Thank you for your videos. I am currently working for Vanguard, which Series Licenses do you suggest offer the highest pay ? thanks for the insight

[@francisxl368]

How do you the control page down for the scenario & sensitivity analysis at 6:30 in the video?

[@arslanthemughals]

Hello Sir In order to be a financial analyst, do you need to have a deep understanding of GL accounting processes and procedures, I mean practical exposure?