How to Build a Financial Model in Excel - Full Tutorial for Beginners
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- For the assumption sheet Excel/ PDF, visit : www.thewallstr...
Any Query Contact us on WhatsApp -
api.whatsapp.c...
Subscribe to our channel for regular tips on CFA, FRM, and Investment Banking.
Follow us on:
Linkedin: / the-wallstreet-school
Facebook: / thewallstreetschool
Instagram: / the.wallstreet.school
With markets tumbling, inflation soaring, the Fed imposing large interest-rate hike, while treasury yields are rising rapidly-which means more red ink for portfolios this quarter. How can I profit from the current volatile market, I'm still at a crossroads deciding if to liquidate my $125k bond/stock portfolio
infact, ever since Coronavirus, I've been in regular communication with financial examiners. Nowadays, buying moving stocks is quite easy; the trick is knowing when to buy and when to sell. The section and leave orders for my portfolio are made by my counsel. accumulated more than $550,000 from a $150,000 savings that was initially stale.
Very interesting, I don't have any knowledge on these please, I'm thinking of investing but i need a professional to guild me through on it so i will start earning profit
My advisor is YUVAL ERIC BROKMAN., a renowned figure in his line of work. I recommend researching his credentials further. he has many years of experience and is a valuable resource for anyone looking to navigate the financial market.
google his name
Yuval Eric Brokman
One of the best video that i have ever seen related to financial modeling, your way of teaching is estonishing without complexity well explained able to adapt quicky ,sign of an prolific teacher , spread your valuable knowledge like this, appreciate your hardwork.
Thankyou!
I appreciate your approach of teaching.. To my understanding this just proves how much we need an edge as investors because playing the market like everyone else just isn’t good enough, we just need to hold onto our hopes and wait to see how things turn out because market movements are almost always unpredictable. In my portfolio, I'm noticing more red than green. I'm 47, and earn nothing less $150k per year, but nothing to show for it yet
Very true, a huge part of my portfolio growth has come during this bear market. I've been able to scale from $180K to $572K in a short period of time. I basically was just following the steps and guideline from my financial advisor. as long as you've professional help, you're good to go
Mind if I ask you to recommend this particular coach you using their service? Seems you've figured it all out.
Well i won't say i have........His name is "Nolan Velden Brent" can't divulge much. Most likely, the internet should have his basic info, you can research if you like
he appears to be well-educated and well-read. I ran a Google search on his name and came across his website; thank you for sharing.
Sir, while calculating EBITDA in the Monthly P&L, you deducted E28 (payment settlement charges) instead of E29 (the total indirect expenses). As a result, EBITDA should come out to be negative for the initial years.
A very insightful video. I had a great learning experience. Thank you, sir.
Yes correct I have notice the said things while studding this course
Same thing happened to me as well
Its my question too. Clear. Because i used total indirect expense and the result is totaly different.
Yes, I had the same experience, despite having the same total revenue, direct expenses, and indirect expenses my EBDTA came negative for a few months initially...
yaa but You can Tackle it very easly if you Increase the revenue in Assumption Sheet and Decrease the Direct Expenses Because the %age of the Direct Matarial is more than the Revenue thats why the EBITDA is coming Negative
This is the best video on financial modeling to date. Thanks a lot!!
Heyy can you please reupload and share the drive link for the Assumptions Excel sheet...old link in the description is not working.
Great Video Sir, But there is one correction to be done. At 1.33.50 , While Calculating EBITDA you have mistakenly considered only one indirect expenses rather than taking sum total of indirect expenses. Due to that the next steps also gave wrong financial picture of the company..
Although balance sheet was matched but the figure was not correct..
My Balance sheet got tally at 211.1, 226, and 311.1 for the three respective years... Anyone who is reading this and have got same balance sheet amount kindly hit the like button so that i can understand the answer is correct.
However, it was really gem of the video. It has provided me a good foundation to get deeper into Financial Modelling world.. Thank You sir.
ABSOLUTLY,I ALSO FOUND THAT MISTAKE
I'm amazed that you remember shortcuts for everything in Excel
Thoroughly enjoyed making it. Just one rectification that EBIDTA wasn't correctly calculated (instead of Total Indirect exp, payment settlement charges were deducted) at 1:33:58.
Hi, Thanks for pointing out. We will add the rectification disclaimer in the caption
Yes I have seen the same mistake.
Yes even i identified this
Also because of this there will be no Tax in 1st Year as EBIDTA is coming negative
Ya
I watched the comments to see whether someone else has detected this mistake or not :D
Really knowledgeable video. I do understand how much efforts you would require to prepare this video and how many years of experience is really helping you to match the balance sheet in one go.
Thanks team. It take whole semester in MBA to complete this much of knowledge.
Super! It took me a week to learn it and apply it, but the journey was worthwhile.
No one has noticed that in calculation of alcoholic beverages in current assets of working capital, sir has chosen the sum range from direct expenditure alocholic beverages of monthly p&L
Thank you so much for such an amazing and knowledgeable video. I never expected to learn this much from a single video.
same
Thanks for sharing your knowledge ✨💫
So nice of you
One of the things that stands out is the clarity with which you explain. I'm not a beginner but it was a nice one to refresh anyway. You should do short project appraisal models using a discounted cash flow technique as well such as PV or discounted payback. It would help beginners as well.
Hey Chris, can we connect on LinkedIn? I am a beginner in Financial Modeling, and would love to have your guidance if you don't mind.
@@thekaizenist7375 send me an email or your LinkedIn profile
Hey did you receive my previous my mail?
@@thekaizenist7375 unfortunately not.
@@chrisalmighty Can you please share your LinkedIn id
Really, the entire video was so resourceful, got to learn a lot, and kudos to the teacher for teaching such a wide topic in a simple manner.
Do you have the excel sheet?
Very nice video and made me to understand clearly the content. Hats off to you!!!!!!
Sir, first of all thank you for such a helpful video, other than that i think some corrections are there :-
1) EBITDA one , which everyone pointed out
2) while calculating working capital , we have taken direct expenses as a base for Current Assets- inventory as well as for current Liability - creditor for raw material.
what i think is for current asset we have to take revenue as a base
3) March is a month having 31 day, while we divided everything by 30 while calculating working capital
thank you TWSS for download case study assumption file, very helpful for me
Amazing video and thank you for it is still relevant today. I followed all and reproduced identically. It is a great pratical piece.
Well Explained!
However, while calculating "EBITDA" in Monthly P&L Sheet, you have subtracted Row AM28, where you should be subtracting AM29 (Total Indirect Costs). EBITDA comes out negative in some cases.
Respected Sir Excellent Experience Thanks Take Care And Always Best Wishes
Thank you for making this video, the confidence built in making a financial model.
Thank you for making this video . . .the way you are elaborating every inch of details . . . people like you are path provider
Thoroughly enjoyed making it. Just one rectification that EBIDTA wasn't correctly calculated (instead of Total Indirect exp, payment settlement charges were deducted) at 1:33:58.
Yess got the same correction viewers kindly correct it
Hello Himanshu, Pleasantly surprised to see your videos. Congratulations! I used to work at McKc (hope you remember :)
Thank you sir, at least learnt the basics. Kudos to your efforts. Did modelling parallel to watching video.
Amazing video, learnt so much from this. It's a humble request to make a video on valuation too. And thank you for sharing knowledge.
Up next!
@@thewallstreetschool which MBA specialization is more useful to become invesment banker? MBA in finance or MBA banking and finance
@@abhilashchowdary4618 Interest is more useful ! Curiosity is Important ! it will save you the lumpsum you pay to the mba institutes
Thank you so much , i watched the entire video, it was really appreciating.
I 7up 777uuuuuuuuu7uuuuuuuuuuuuuuu7 77uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu uuuuuuuuuuuuu7uuu77uuuuu7 u7777u
Up I ui uuuuuuuuuuuuuuuuuuuuuu buy uuuuuuuuuuuuuuuuuuuuu uuuuuuuuuuu77u77u7u7uuuu7u7uuuuuuu777uuuuu7 uuuuuuuuuuuuuuuuuuuuu uuuuuuuuuuuu7u7u7uuuuuu77uuuu7uuuu7u😉😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅😅
Did they taught bull bear base live scenario in this video??
Total Revenue is 24.6 Lakhs Less Total Expenses are (Indirect) 17.9 Lakhs (Direct) 9.4 Total= 27.3 Lakhs, So in Your Monthly P&L Sheet Dated 31-Jul-20 EBITDA 6.5 in profit but actually, it is loss. Please Re-visit your model make some changes to fix it. Overall it's a very good video to learn Excel.
Yess i was following this and making my own model....i got the same changes as you mention...kindly correct it viewers
Very nicely explained, excellent.
I want to add one point just to update, in the monthly revenue sheet row no. 15 E have linked with weekdays instead of weekend, it seems
I learnt alot, and you managed all points very well
Thanks
Hatts off to the instructor he's so profound and knowledgeable. Kudos to him
Heyy can you please reupload and share the drive link for the Assumptions Excel sheet...old link in the description is not working.
@@karanhatkar6935 Naah bro I don't have it now
Great video! Thank you so much for sharing this knowledge.
I request the team to make a video on valuation modelling too. Thanks in advance.
Which specific Valuation model do you want?
@@thewallstreetschool startup valuation
@@thewallstreetschool yes please! Startup Valuation
I have watched the entire video of tutorial. It is really useful.
I bow down to ur teaching, knowledge and enthusiasm. Thnk u sir for investing ur time for this video.
Its awesome , Balance sheet should not have prepaid rent value constant for every year. that means Montly or annually rent expanses adjustments are not made properly. REST IS AWESOME. THANK YOU.
Very nicely explained. It would have been very helpful if you could have incorporated debt component in it instead of 100% equity. The video would have been longer but it would have been very helpful and complete in all respects.
Sir Financial planning and budgeting ki upar video banaiye
I completely understood the financial modeling but the thing is too much complexity in the excel formulas. As you already said, you are having an experience using Excel for the last 7-8 years but think about freshers who are new to Excel. At least you should have some simplicity in the formulas. The formula part was gone above my head.
Infact..I learnt excel shortcuts along with FM which is the core. Write down all shortcuts by inserting one formula page..
Thanks for explaining so good
Lawde ka Finaciial model
@@studyclub4854 kya dikt hai tumhe
Need to get a grip on the logic of formulas. It won't be a mess then.
@@shivanshshukla5883 bohot dikkat hai
Very knowledgeable and resourcefull video thanks a lot
Awesome, Sir the way you create this financial model like amazing 👍
Now i am very passionate to learn financial modelling.
Great video. Thoroughly enjoyed. EBITDA is being miscaclulated.
Thanks, Yogesh
Yes i was abt to say same
Exactly
Dear.
Please check working for EBITDA Calculation in Monthly P&L...Wrong reference given for Indirect Tax Expenses. thanks for Excellent Video got good Insight of Financial Modeling.
A list of shortcuts used would be helpful. Where is the first use and explanation of "den" in this video? Informative vid overall.
Jain Sir, for a learner like us it is really a great tool I get a very good understanding of all the accounting details and formatting. Sir in Monthly P&L mine and yours EBITDA findings are different. when gone in detail found that you did not reduced indirect expenses from revenue completely thats why there is a differrence. So please check the same and revert if possible.
Yeah same happend with me also EBT of annual P&L was in negative...I thought my model has some problem but it was geniune mistake and thats okay...The motive of this video to get basic understanding of modelling and how its get done to novice who wants to get into finance🔥 and they successfully able to execute the same 💯
Yes, your observation related to EBITDA calculation is correct
EBITDA comes out to be negative, does the balance sheet match?
Hi really brilliant FM video, just a single correct is needed in calculating net profit correctly in P&L
Your video is nice but if you can increase the working sheet width from 100% to 125% it’s easy to see clearly
Kindly make a video on Financial Planning and Analysis( FP&A) covering budgeting, forecasting, actual vs variance analysis. How a fresher can get a job in this domain and what skillsets are required and other relevant insights.
Thank you.
Pls make video on the same
Great model TWSS enjoyed it. Can you please make a Modeling tutorial on FMCG OR MERGER MODEL from scratch.
Sure, allow us some time.
@@thewallstreetschool yes sir please make it
Such video deserve mellions of subecribers..
Hello, thanks a lot, it is very useful, but please check the 1:34:01 EBITDA calculation, instead, E28 should be E29, please comment.
Time is more valuable than money. You can get more money, but you cannot get more time. Great video! This is truly similar things that I usually make on my page. Keep me posted if you choose to give a shot at my videos. Waiting for the next one!
Hi, the model is beautifully prepared and explained. Request you to take a more practical working example which consist of debt ( usually market works with 70:30 debt to equity ratio). This will help us to understand the various types of debt with debt scheduling. Thanks
Thanks for the feedback Rijul. We will take care in further videos :)
Bro i am confused with the calculation of Changes in working capital 2:01:45. Can you please explain it to me?
Your content is better understood than the pairs onces 👍
A very insightful video sir. Hope to see new types of Financial Modelling tutorial as well .
GREAT VEDIO, APPRECIATE YOUR EFFORT AND COMMITMENT , EXPECTING MORE VEDIOS IN THIS SERIES (FINANCIAL CONSULTANCY)
Sir while calculating EBITDA in Monthly P&L you deducted E28 (which is paymment settlement charges) intead of E29 (which is total of indirect expense)
That's correct. As a result EBITDA is negative
Great video and explained very well!
Thank you for this video
I just started watching the video and I am loving it.Can you please provide the excel file too
32:53- ALT + H O I
or
ALT+ OCA
33:50 - note
45:24- FREEZING COLUMN
Thanks for sharing this model with us. Model is very informative and I learnt so many things from this model.
Please make me correct if I am wrong, One thing notices EBITDA formula error in video you deduct only payment settlement charges instead Inderct expenses, and nop tax expenses in 1st year coz of negative EBT
Regards,
Rohit garg
I noted this too, you are right on the EBITDA calculation. it was an oversight by the poster.
It was a great video with lot of things to learn from it. But i have one correction when you were calculating total indirect expenses in Annual P&L it was E29 but you entered E28. Thanks for the video. learned a lot!!!!!!!!
I too have same query.
Hey do u have the excel sheet for my practice
great video but I want to address one error at 1:34:01 ,you have written E9-E16-E28 for EBITDA but it's E9-E16-E29. instead of E29 you selected E28. Again this was a very informative video.
You are observant. I noted that mistake as well. But overall the video is very well done and insightful.
Yeah... Was picking my brain because of that... My spreadsheet values didn't match it.... Thank you!
Hi, love your detail explanation on excel shortcut. Btw, your formula in monthly p&l (EBTIDA) sheet have minus wrongly in total indirect cost cell.....
Thank you Sir for sharing this knowledge..
I think I could not find any better video than this one, Could not follow the Balance sheet thing may be because of my own limitation. The formula creation is really appreciation worthy, One small glitch I would like to point out though, At 1:34:00, IN MONTHLY p&l when we are calculating EBITDA , BY MISTAKE DURING SUBTRACTION have chosen E28 instead of E29
Heyy can you please reupload and share the drive link for the Assumptions Excel sheet...old link in description is not working.
Thanks a lot for efforts you put. It was very helpful.
Hi. Amazing stuff! some flaws I request you to please check. Major one seems that in EBITDA Calculation, you have accidentally reduced only last item in Indirect Costs instead of reducing Total Indirect Costs. Ignore if irrelevant. Thanks. Look forward to many such training Videos. Thanks!!
Found same error.
Yes you're right ashish
Can someone please reupload and share the drive link for the Assumptions Excel sheet...old link in the description is not working.
You may please use the link in the description above for the assumption sheet
Thanks for such am amazing tutorial. But I'm currently having issues creating my own assumptions. I'm working on a gas station project. But i can't put together a clear assumption in terms of APC and also M-o-M & Y-o-Y growth. The product can only be paid for once a month so the revenue projection would be weekly, annually and monthly so please can you shed some light on this.
The best free resource :)
I would appreciate that every Concept is very well explained. Can you provide the link for solved excel sheet
In your monthly revenue sheet, you limited guests to 60, 80, 70, and 90, depending on the day and meal type. Should it actually be 2 x each of those numbers because there are two rounds? Your min for lunch was 10 x 2, so shouldn't your max be 60 x 2?
Asking the same question why he limited guests to maximum occupancy per round as it should be limited to per day. I think it should be 120,160,140,180
Much love from Florida.
You deserve subscribe ☺️
Amazing video!! I'm financial analyst in Brazil! Thanks for this tutorial!!
there is software that if anyone know well accounts they don't need complexity like this I did this easily in a different software
Which software are you talking about
Kindly share the financial model which you have just created. It would be really helpful for us when creating our model we can simply compare it to yours. Thankyou
Abig thumbs up nicely explained private equity
Please share the FOA Fundamentals of Accounting link that you mentioned in the video. Thanks. Thanks for the great content it is instrumental.
Good job, thanks for sharing your knowledge
Ωplus1
⑥③①⑨①⑥⑧⑨⓪⓪
WSpp
Thanks sir, for sharing your knowledge on this platform.
You influenced me from you work.
Greeaaattttt👍👍👍
In this Financial Modeling for Japanese Restaurant, for Franchsie, monthly P&L shown, calculation of EBITDA is wrong.. it cannot be in + in the starting months, you may like to check. Please correct it.
Nice video sir. I had seen video recently..I appericate you daily put those levels on telegram
Hi maybe good to have subtitles for the excel shortcuts or just textbox that shows excel shortcuts when it was used, great for our learning thank you so much...
In the formula of number of guests per day, sir has capped it to "Occupancy per round i.e 60", but it should be capped at "Occupancy per round * number of rounds i.e 120".... Formula should be this:
=IF(H6>0,(MIN(Assumptions!$B19*(1+Assumptions!$B20)^('Monthly Revenue'!H3-4),Assumptions!$B21))*Assumptions!$B18,0)
Really a good informative video....helpful for sure....thank you
There is a small mistake in calculating EBITDA at 1:33:54
Thank you for this video really interesting
Small ?
Heyy can you please reupload and share the drive link for the Assumptions Excel sheet...old link in the description is not working.
@@karanhatkar6935 I think you should put up a new comment instead of reply
@@shinepriyan6890 Yeah I did it already...but I was asking if u can send the file in case you have it now.
Very succinctly explained.
at 42:50 while calculating operating days mine says 30 and 31 under month 2 and 3 instead of zero whereas I'm using the same formula
same! were you able to find a solution for it?
Have you mentioned dollar sign?
To derive Closing balance has to substract purchases return as well
Very nice lecture
Thanks a lot🙏
I learned more excel than financial modeling😀
Very informative seesion, just one thing, in Monthly P&L tab the EBITDA calculation is wrong.
very useful video, thanks
Sir ,please check EBITDA calculation ,instead of Total indirect expenses you have taken payment settlement charges in calculation
Great... lot's of love 🎉🎉
Thanks, Pls upload the video with Debt project.
A really good informative session. Thank you for the session