Learn FINANCIAL MODELLING in EXCEL - STEP BY STEP - Session 3

In this session of FULL COURSE on FINANCIAL MODELLING in EXCEL; we have covered everything you need to know in FINANCIAL MODELLING as BEGINNER. It covers ways and TRICKS through which you can SIMPLIFY your FINANCIAL MODEL using various EXCEL functions such as NAME MANAGER, DATA VALIDATION, VLOOKUP, GOAL SEEK, DATA TABLE, SCENARIO ANALYSIS, SCENARIO MANAGER, etc. we have also touched upon, HOW TO PROTECT your FINANCIAL MODEL IN EXCEL using CELL LOCKING and WORKSHEET PROTECTION.
Like our previous session in FULL COURSE on FINANCIAL MODELLING for BEGINNERS, it also breakdown everything in STEP BY STEP manner using LIVE PROJECT or EXERCISE file so you can PRACTICE it while watching this session (link below).
FINANCIAL MODELLING in EXCEL is a crucial SKILL one must learn in order to make a CAREER in INVESTMENT BANKING, EQUITY RESEARCH, VALUATION, and other CORE FINANCE ROLES.
By the end of this course, you will have a STRONG FOUNDATION in FINANCIAL MODELLING and be able to confidently CREATE FINANCIAL MODELS IN EXCEL to support decision-making in your professional life.
Start your journey towards becoming a FINANCIAL MODELLING EXPERT now!
You can connect with Parth Sir on Linkedin -
📥 DOWNLOAD the EXERCISE FILES - drive.google.com/drive/folder...
📛 If you want to join our next LIVE masterclass on Zoom = Join our Whatsapp community (TVS Mentoring) - chat.whatsapp.com/KS2CNcmqgXf...
--------------------------
👉JOIN:
🔥 NEXT LINKEDIN COHORT - rzp.io/l/uHTWYlSH
🔥 NEXT EQUITY RESEARCH COHORT - pages.razorpay.com/ERCohort4
--------------------------
✅ You can connect with Parth Sir on Linkedin -
✅Follow The Valuation School page on Linkedin - / thevaluationschool
✅Join our Instagram handle - / thevaluationschool
✅Join our Whatsapp channel - whatsapp.com/channel/0029VaLf...
Time Stamps
0:00 - Introduction
1:14 - Mentoring Session Details
1:35 - Why Simplify the model?
2:33 - Name Management
15:10- Using VLOOKUP for Simplification
23:58 - GOAL SEEK in Modelling
28:26 - Using DATA TABLE
33:34 - SCENARIO ANALYSIS
39:58 - SCENARIO MANAGER
44:25 - DATA VALIDATION
50:45 - PROTECTING the MODEL

[@user-jm5yf8gu3k]

Basic Conventions: 1. Reduce Column Width of Column A to 1.15 and leave it blank. 2. Write the currency at top before anything else. 3. Plot years through formulas. 4. Distinguishing between Actual & Estimate figure years. Select the cell with Actual Year. Ctrl+1 (Format Cells window) Number  Custom  Choose “0”  Type:  0“A” (Sample will show “2020A” - For Actual). Write “F/E” for Forecasted/Estimated. 5. Make the heading background dark and use White as font colour. 6. Freeze the heading. 7. Use Paste Special for Copy - Paste. Ctrl + Alt + V for Paste Special. 8. Consistency is must. Numbers at 1 decimal. Percentages at 2 decimals. Comma at the distance of 3. How to do that? a. Ctrl+1 (Format Cells window) Number  Custom  Choose 5th option from top (#,##0;-#,##0)  Type: #,##0.0;(#,##0.0) 9. Hard-coded numbers’ font colour should be blue. Font Colour  More Colours  2nd row and 3rd from last colour (blue). For ease, use F4 (Fn+F4) after selecting hard-coded numbers to copy the format. (Works only if you have not used some other formatting) 10. Use formula wherever possible. 11. The “Particulars” column should be left-aligned. And others should be right-aligned. 12. Use Alt+H+0 for increasing decimals. 13. Use Ctrl+Alt+V (Paste Special) for copying the formats. 14. Use Shift+Alt+Right Arrow for grouping. Use Shift+Alt+Left Arrow for ungrouping. 15. Bold the top and bottom line. Add borders to Immediate attention rows/columns like Gross Profit, EBITDA, EBIT etc. 16. Checking/Auditing the model: a. Ctrl+G  Special (OR Alt+S)  Select Constants  It will show all the hard-coded entries. b. Ctrl+G  Special (OR Alt+S)  Select Formulas  It will show all the formulas. 17. Shift+ Space Bar  Row Selection Ctrl + Space Bar  Column Selection 18. Try not to hardcode data into formulas as well. Go To wouldn’t be able to catch it as well.

[@ayushjain2871]

Thank you buddy😊

[@pranjaltupe6251]

thank you very much this is really helpful

[@neharikapattnaik65]

#Dynamic Headings: ="Heading"&" - "&Cell of hardcoded company name #Conditional Format: (Alt+H+L)then highlight cell rules #Functions: Time periods : 1.Function for finding end of month -Monthly Data =EOMONTH(start date,select cell of hardcoded zero) press F4 for fixing start date 2.Function for finding end of year-Annual Data =DATE(YEAR(start date)+select hardcoded zero,12,31) press F4 for fixing start date 3.Function for finding Gap betwen Start date and end date in terms of years (note: end date can be Monthly or Yearly data) =YEARFRAC(start date,end date) press F4 to fix start date #Formulas of Averages: Average =AVERAGE(select all cost of a column) Weighted AVG =SUMPRODUCT(select all weights,select all cost in a column) press F4 to fix Weights Median =MEDIAN(select all cost in a column) Minimum =MIN(select cost column) Maximum =MAX(selct cost column) Small =SMALL(select cost column,select hardcoded no.3) press F4 to fix Hardcoded no.3 Large =LARGE(select cost column,Select harcoded no.3) press F4 to fix Hardcoded no.3 #IF Statement: =(logical test,value if true,value if false) Use of Margin Space - put # along the Headings for Easy Navigation

[@MsMayank70]

while starting protect cells how did sir showed all the formule in the cell using goto like he said??

[@maheshshettigar5205]

I want to hug you buddy. There are very few people in this world who can teach students the way you teach. You're not forcing anything, nor selling anything, you are soo good at it that learners will appreciate your work in coming years. I hope your channel grows exponential. Thank you once again.

[@laxmirawat6183]

Thanku so much sr from bottom of hard

[@ishu9359]

Sir a true teacher and a true Heart ❤️ ❤ I have no words to express but I feel from the last 20 days ki like yes I can do the best financial model and which is always in my hands and you always mentioned that your model should be in your control that's I feel today

[@shashankkumar9153]

These videos will make history may be these are on slower side in terms of views and traction but mark my words people who are genuinely interested will visit again and again just to learn and clear their concept, please keep up the good work as these videos will act as a pioneer in the field of financial modeling and valuation God bless you brother

[@trishnachoudhary2968]

He knows he doesn't need to "Sell" his course, cuz its already the best and that we would support him!! This is my third session with you, I'm already so overwhelmed by how amazing excel is, excited to learn much more about it. Thank you!!

[@user-vz3bs9fu6p]

i love it when you invoke the IF and VLOOKUP formulae. It actually helps understand the formula better. In college, I had literally rote learnt adding 2 to the column index input. It worked because of the model but the logic was flawed. Thank you so much!

[@MilesToGo_Som-deepa]

Can’t thank you enough for all your videos. You are a gem of a teacher. Blessed to have found you here in the right juncture of my life.

[@ruchidattachhatre6314]

The Protect Sheet explanation was really important. Most of the FM workshops don't teach this. Honestly this is explicit content. Thank you so much.

[@wonderwall4470]

Can’t thank you enough for all your videos. You are a gem of a teacher. Blessed to have found you.

[@James-ms9lh]

Thank you for this session sir. This session not only opened the doors to learning Financial Modelling for me but also gave me a new perspective on using Excel. This session has made me realise how much my excel skills were lacking.

[@prabhjotkaur7243]

Awesome and simplified. I have done a course on Financial Analyst, but I rarely remember anything from that. And these videos will stay for lifetime in my head. Very nicely explained with simplified examples. Thank you so much Sir for these videos. Turned on All the Notifications from the channel, only and only this channel.

[@tanayaggarwal126]

Best content available for financial modelling! I am glad I found this.

[@ananyabhatnagar6243]

Sir, You're truly great. I have been seeing your videos since past 3 days and there's not a single thing which have gone above my head reason? you simplify everything and breakdown to understand it in easier way. I will call you god of Valuations. :)

[@stoicaovidiu6595]

I just want to really thank you from the bottom of my heart^^You giving so much value for free...But I think you're doing it for free because it gives you, emotionally a feeling that money cannot buy.Bless your heart.

[@aseemgoyal101]

This video covers advanced excel topics such as; Name Manager from series 5:20 Data Table and Scenario Analysis 39:00 Circle Invalid data in data validation at 49:35 Protect sheet and calculated cells 53:00

[@ayushluhadiya3357]

Thank you, sir, for the incredible sessions! 🙌 I want to express my heartfelt appreciation for the exceptional teaching style you bring to every session. Your expertise and passion shine through, making the learning experience truly enjoyable. Despite already being familiar with the subject matter, I find myself captivated by your approach and gaining valuable insights with each session. Your dedication to teaching and your ability to deliver information smoothly have undoubtedly elevated my understanding of the topic. Learning from you has been a remarkable journey, and I am grateful for the knowledge you have shared. I am honored to have your support and guidance throughout this learning process. Your encouragement and expertise have made a significant impact on my educational journey, and I can't thank you enough for that. Once again, thank you so much for your unwavering support and for making each session an enriching experience. Your commitment to excellence is truly inspiring, and I look forward to continuing this remarkable learning journey with you.

[@ABHINAV1696]

Very well explained all the formulas with at most simplicity. Sir blessed you as a mentor / teacher which teach students with full of passion and enthusiasm!!

[@akhilgontela2142]

I love it - thank you so much!!. Much valuable content providing to us for free..

[@diprosombrahmachary4741]

Best thing about sir; firstly he will be telling that; I am not going to make you understood the basic stuff then also describes each and every stuff from the very basics. thanks a lot sir.

[@muralidandu8993]

I m feeling that I'm learning.. This cource in heaven .. Thankyou so much sir.. For making our life even more easier..

[@pokm824]

Hey just a excel tip @ 20:43 instead of using vlookup , excel has a function specifically designed for these type of problems , the function is lookup , =lookup( Lookup value , lookupvector, result vector) Lookup value is the value u want to look for , in this it is 25% , lookvector is nothing but from where u want to lookit up from that is the De ratio table , and the result vector is wt u want as a result , in this case it is the coupon rate table Tq so much sir for these amazing lectures really soooo helpful

[@SportSphere360_]

Thanks Sir Boss Guru Maharaaaaaaaaj, GOD Bless you, really enjoying the lectures. Recently started following your channel and these video are full of knowledge and clarity. I hope to continue watching these videos completely.

[@vijayjoshi-mw8cr]

Thank you sir for providing the financial modeling playlist for free.. In this tutorial you taught very well. & it is my request that please make playlist on Fixed Income Securities certification exam preparation for NISM.. Thank you once again for excellent teaching :)

[@user-su1rc3pk9o]

Thank you so much sir! You have been of great help.

[@kunaljadhav7344]

Very insightful and detailed to the basic fundamentals before moving ahead to the ultimate aspects of the topic speaks of your experience in the field. Genuine appreciation towards teaching with a noble intention of free but quality learning. Loved your explanation member. Highly recommended.

[@anshikasingh5822]

Great excel function video for modeling. Thank you!

[@Dhruv_Malhotra11]

A goldmine for finance enthusiasts

[@simran7761]

you're a blessing for so many ambitious students God bless you Sir 🙏

[@sagarkharat]

Amazing videos... Thank you for your efforts.. also request everyone to share these videos to your connections... really disheartening to see that the number of comments and likes are very much low for such an amazing content.. where stupid ticktock or Insta people get famous doing nothing good for the society.. this channel deserves much appreciation that its getting now

[@yashgotal7402]

using youtube since 10 years, but commenting for second time. THE BEST❤

[@yashrajput7212]

Thank you so much sir🥺 We need teachers like you.

[@hussy3754]

I was thinking I m pro in excel but after watching the last 2 videos I realized that how rookie I am thank you very much sir.

[@badrishpai9141]

Thank you Parth Verma Sir for this valuable course. Looking forward for more videos like this. Please keep bringing up value adding courses like this.

[@chiragdabhi3854]

Love the way he uses sarcasm...Teacher ho to aese ho...Finance ka rencho...👏

[@sradhanjali5241]

Thank you very very very much sir 🙏 , excellent teacher i have ever seen

[@harsh_206]

55 mins of complete knowledge 🔥

[@parulsethi4421]

You are simply amazing.. Please make more videos on Core finance concepts and current finance topics & launch your own course !

[@chandanshukla4783]

Great Sir! Again thank you so much!

[@richasiwas1950]

Whattttt is thisssss? I mean , you are really creating wonders. Thankyou so much for this playlist.

[@ahsanali50]

Thanks Sir Parth, Day 2: Session 3 done; Today session was quite advance level but you explained really well but need to revise it tomorrow before moving it to Session 4. Stay Blessed. :)

[@beinghuman4388]

If anyone has problem related to data table calculation where all values are coming as 9570, you need to press F9 after the data table output. This happens because in workbook calculation setting we set as "Automatic except for data tables", so we need to manually calculate it by pressing F9.

[@DOBUDOBU-wi1bx]

Nahi hora vhai merato 2010 mein vhi chalayatha laga isme nahi huwato 2016 me dekha phir vhi nahi huwa 😭😭sab try karliya help me

[@meltinmathew2]

Loved the video!!!!

[@waqasiqbal1652]

Excellent stuff. Respect from Pakistan.

[@NaveedKhanCA]

Fantastic work.... for borders .. like for all border Alt, h, b, A for bottom Alt,h,b o for top Alt,h,b,p and so on

[@daveblue1234]

17:09 Hey " if " function Best!!!! thank you sir

[@ValueAnalystX]

Sir, thank you so much for sharing this insightful video with us

[@meghamohnot150]

Best training sessions.

[@MuslimIslam01]

you are remarkable thanks for sharing useful knowledge

[@kartiksahani6109]

thanks for this amazing video,it helps a lot...

[@RahulVerma-tq8yo]

Thank you so much for such valuable session 👏👍

[@adityagaur6763]

Really helpful sessions Sir. You are an inspiration for me really !

[@patelgaming7358]

Speechless..Hats off to you sir...😇

[@jaskiratsingh5649]

Boss u are taking an excellent initiative, I mean even those who give some knowledge don't share such deep things which u have shared in this video.

[@harshjain2468]

Amazing Session!

[@akshitrana9735]

Thanku so much bro the content you have provided is pure class

[@antaskhan6032]

u re such a geniuos sir

[@vedikakhamayacha7659]

Very Very insightful

[@user-dc8if6nl4t]

it's just a mindbloing session

[@nikhilgidwani1819]

Thankyou sir for all the valuable efforts of yours

[@viralvideosandfunny5259k]

wow absolutely awesome brother

[@rishabh3952]

Best session out of the world 🎉🎉❤❤

[@prathameshshenoy07]

really enjoyed this session sir !!! God bless you

[@think-king4030]

Pehle video like kar deta hu, kyuki pata hai video toh achha hi hona hai, 2nd lecture me 6 ghante lagaya par pura samajh ke liye, really found a gem

[@muskanagarwal5808]

Thanks a lot sir . These videos are really helpful

[@mayurahuja7955]

Loved the session Sir. Thanks alot❤

[@CASAURABHBEDMUTHA]

This is Best !

[@divbrnwl]

Great sir, this is pure gold..wanted to learn financial modelling since a long time...but unable to do so...but this time no excuses. Thank you so much for your effort.

[@thevaluationschool]

All the best

[@Vinayworks666]

Thanks for the session

[@abhishekaryan6022]

Mind blowing session

[@dilipsahu434]

Thank you ❤ Parth Bhai for making this video

[@jatinmaini3982]

Thank you so much sir for your efforts, much obliged!

[@thevaluationschool]

Most welcome! Keep Learning!

[@user-kg7ce7ch3t]

DHANYAAVAD SIR

[@parshvsutaria4589]

Thank you so much sir!!!

[@jatinraghuwanshi3774]

thank you sir for great efforts

[@VikramadityaVK]

You are the best Sir

[@priyankakadam3525]

Thank you so much sir God bless you 🙏

[@shakirchoolakal5436]

Superb ... class

[@abhishektyagi4955]

Great sir, please keep going. It's very helpful. Thank you so much.

[@thevaluationschool]

Glad you find it useful. Keep learning!

[@kunalpashankar2941]

Thank you so much buddy. Love from pune. ❤

[@arifmahar722]

thank you

[@tusharsrivastava220]

You are great sir🙏❤

[@kanisharaid7783]

GREAT JOB

[@oyenaru]

Trust me I have learnt all this functions but never thought that these functions will be used in this way and to be honest these functions used by you is more easy to learn.Thank you sir for this one.

[@thevaluationschool]

Glad it was helpful!

[@vikashmaurya5185]

Thank you very much sir

[@saifilicious1749]

You are a gem

[@sayanguhaa]

Sir , your next sandip masheswasri... u r doing it this without any selling purpose ... man god blessed u

[@ayush2000super]

Shortcut for borders- Instead of using ctrl+1, we can use the following as well- a. alt h b r/l- right/ left border b. alt h b p/o- top and bottom border alt h b will activate the border shortcut, and there are other border formulas (eg. oustide borders, bottom double, thick outside borders etc.) as well which we can use

[@butterflyvolger3966]

Love u sir..I am an ACCA from BD

[@aminliaqat4513]

Awesome sir

[@vkarhe1]

sir you spend all night to teach us and we will also spend all night to learn it.

[@raunaksaw124]

Time stamp 4:59 While calculating WACC , you have used C12 instead of C13 for multiplying value of debt to total capital Although the WACC is correct because both equity value and debt value is same 👍 You are doing a pretty good job, i am learning a lot from your videos Thanks for the help ❤️

[@allabouthuskies.9213]

Tysm sir 🙏

[@syedaliraza7194]

Great content. Do we have 3 statements model linked in future videos??

[@llinone8366]

Sir Thanku so much 🙏🙏🙏🙏💐💐💐

[@Nexus26688]

THANK YOU

[@nidaparveen1557]

I have been confused as to how to lear financial modelling, bought expensive course however couldn't complete it. Didn't like the way of teaching. This one is free and fun. Hope to complete this one.

[@inmypoint]

thank you sir

[@rashidbhatti8704]

Great work Sir, I like your style when you said jin ko pata hy vlookup ap agy barh ja e a 18:52 🤣

[@laksha8888]

SIR AAP KITNA ACHHE SE SAMJAATE HO LITTERALY....!!!!😇💛💫

[@togetherwithme2780]

Maza hi aagyaa

[@CsfromScratch]

Thank you So much sir🥺🥺🥺🥺🥺🥺🥺🥺