Learn FINANCIAL MODELLING in EXCEL - STEP BY STEP - Session 3
ฝัง
- เผยแพร่เมื่อ 7 มิ.ย. 2024
- 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
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.
Thank you buddy😊
thank you very much this is really helpful
#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
while starting protect cells how did sir showed all the formule in the cell using goto like he said??
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.
Thanku so much sr from bottom of hard
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
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
Can’t thank you enough for all your videos. You are a gem of a teacher. Blessed to have found you.
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!!
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.
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.
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.
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!
Best content available for financial modelling! I am glad I found this.
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. :)
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.
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!!
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.
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.
I love it - thank you so much!!. Much valuable content providing to us for free..
I m feeling that I'm learning.. This cource in heaven .. Thankyou so much sir.. For making our life even more easier..
you're a blessing for so many ambitious students
God bless you Sir 🙏
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.
Thank you so much sir! You have been of great help.
A goldmine for finance enthusiasts
Thank you so much sir🥺
We need teachers like you.
Great excel function video for modeling. Thank you!
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.
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.
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
Great Sir! Again thank you so much!
Love the way he uses sarcasm...Teacher ho to aese ho...Finance ka rencho...👏
using youtube since 10 years, but commenting for second time. THE BEST❤
Excellent stuff. Respect from Pakistan.
you are remarkable thanks for sharing useful knowledge
Sir, thank you so much for sharing this insightful video with us
You are simply amazing.. Please make more videos on Core finance concepts and current finance topics & launch your own course !
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
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.
Really helpful sessions Sir. You are an inspiration for me really !
Thank you so much for such valuable session 👏👍
thanks for this amazing video,it helps a lot...
Thankyou sir for all the valuable efforts of yours
Thanku so much bro the content you have provided is pure class
Best training sessions.
Whattttt is thisssss? I mean , you are really creating wonders. Thankyou so much for this playlist.
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.
really enjoyed this session sir !!! God bless you
Thank you so much sir for your efforts, much obliged!
Most welcome! Keep Learning!
55 mins of complete knowledge 🔥
Speechless..Hats off to you sir...😇
Thank you sir!!!
17:09
Hey " if " function Best!!!! thank you sir
Thanks a lot sir . These videos are really helpful
You are great sir🙏❤
Loved the video!!!!
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
I could not perform the data table & Scenario Analysis exercise using Alt, A, W, and T. The amounts were not coming the way they are coming in the video. I tried doing this exercise 5 times but still was not able to perform it. then I got the answer. Just letting my fellow viewers know that we have turned the settings to "Automatic except for data tables" in Formula settings, hence we have to press the F9 key for calculating the table. Hope this helps!!
Sir, you are doing a great job 😍
how do we need to use f9
same thing is happening with me
@@ektabhatt13watch the first video and change your settings, then you don't need to use F9
Thank you so so much Manav! Really helped
This helped. Even I was stuck here
Thanks a lot.
Thank you so much sir God bless you 🙏
Loved the session Sir. Thanks alot❤
thank you sir for great efforts
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.
it's just a mindbloing session
Very Very insightful
Amazing Session!
Best session out of the world 🎉🎉❤❤
Great sir, please keep going. It's very helpful. Thank you so much.
Glad you find it useful. Keep learning!
u re such a geniuos sir
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
Thanks for the session
wow absolutely awesome brother
Thank you ❤ Parth Bhai for making this video
Thanks you so much sir
You are the best Sir
Thank you so much buddy. Love from pune. ❤
Mind blowing session
Superb ... class
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. :)
THANK YOU
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
Love u sir..I am an ACCA from BD
This is Best !
GREAT JOB
Sir Thanku so much 🙏🙏🙏🙏💐💐💐
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.
Thank you So much sir🥺🥺🥺🥺🥺🥺🥺🥺
Tysm sir 🙏
Sir , your next sandip masheswasri... u r doing it this without any selling purpose ... man god blessed u
You are a gem
SIR AAP KITNA ACHHE SE SAMJAATE HO LITTERALY....!!!!😇💛💫
Awesome sir
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.
All the best
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.
Glad it was helpful!
thank you sir
thank yuuuu😍🤩
sir you spend all night to teach us and we will also spend all night to learn it.
Maza hi aagyaa
DATA TABLE isn't working even in scenario analysis. All the formulas are correct, hardcoding is correct, Value 9570 is linked to a cell and also included while using the function, Still results are all 0s. Does this have anything to do with excel settings? What could possibly be my mistake. Spent an hour on this. Any help would be appreciated.
Great content. Do we have 3 statements model linked in future videos??
Great work Sir, I like your style when you said jin ko pata hy vlookup ap agy barh ja e a 18:52 🤣
Can we use financial modelling for analyzing stocks?
struggling with the language ,wish it was delivered fully in english....good course like this should have been in a common language for international students too..anyways thanks soo much sir
Parth Sir, Data Table and Scenario Analysis functionalities are not giving expected results in the excel sheet, do I need to change any settings. Please help me. Thanks🙂
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.