How to Build a Financial Model in Excel - Full Tutorial for Beginners
HTML-код
- Опубликовано: 7 сен 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
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.
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!
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.
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
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
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.
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
I'm amazed that you remember shortcuts for everything in Excel
Super! It took me a week to learn it and apply it, but the journey was worthwhile.
Thanks team. It take whole semester in MBA to complete this much of knowledge.
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
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
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?
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
Very nice video and made me to understand clearly the content. Hats off to you!!!!!!
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.
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
Thank you for making this video . . .the way you are elaborating every inch of details . . . people like you are path provider
thank you TWSS for download case study assumption file, very helpful for me
Thank you for making this video, the confidence built in making a financial model.
Amazing video and thank you for it is still relevant today. I followed all and reproduced identically. It is a great pratical piece.
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
Hello Himanshu, Pleasantly surprised to see your videos. Congratulations! I used to work at McKc (hope you remember :)
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
Thank you sir, at least learnt the basics. Kudos to your efforts. Did modelling parallel to watching video.
Respected Sir Excellent Experience Thanks Take Care And Always Best Wishes
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??
Thanks for sharing your knowledge ✨💫
So nice of you
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
I bow down to ur teaching, knowledge and enthusiasm. Thnk u sir for investing ur time for this video.
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.
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.
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
Awesome, Sir the way you create this financial model like amazing 👍
Now i am very passionate to learn financial modelling.
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
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.
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, 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?
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
Your video is nice but if you can increase the working sheet width from 100% to 125% it’s easy to see clearly
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!
A very insightful video sir. Hope to see new types of Financial Modelling tutorial as well .
Hello, thanks a lot, it is very useful, but please check the 1:34:01 EBITDA calculation, instead, E28 should be E29, please comment.
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.
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.
Great video. Thoroughly enjoyed. EBITDA is being miscaclulated.
Thanks, Yogesh
Yes i was abt to say same
Exactly
32:53- ALT + H O I
or
ALT+ OCA
33:50 - note
45:24- FREEZING COLUMN
Hi really brilliant FM video, just a single correct is needed in calculating net profit correctly in P&L
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!
Your content is better understood than the pairs onces 👍
GREAT VEDIO, APPRECIATE YOUR EFFORT AND COMMITMENT , EXPECTING MORE VEDIOS IN THIS SERIES (FINANCIAL CONSULTANCY)
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
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
Such video deserve mellions of subecribers..
Very knowledgeable and resourcefull video thanks a lot
I just started watching the video and I am loving it.Can you please provide the excel file too
At 49.00 , using max was not needed because, 2℅ growth will be always the no which would pop up and there is no need of deciding whether the higher no is original customers or previous months customer +2%
You can just-
1. Link 'no of customers' from Assumptions sheet for the month of April. (Which is 20).(Only in Month 1)
2. Then use, min('No of customers, 60)
Sir Financial planning and budgeting ki upar video banaiye
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.....
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
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.
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.
Thank you for this video
Great video and explained very well!
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)
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
The best free resource :)
Amazing video!! I'm financial analyst in Brazil! Thanks for this tutorial!!
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.
You deserve subscribe ☺️
Thanks a lot for efforts you put. It was very helpful.
I would appreciate that every Concept is very well explained. Can you provide the link for solved excel sheet
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
Much love from Florida.
Thank you Sir for sharing this knowledge..
Thanks sir, for sharing your knowledge on this platform.
You influenced me from you work.
Greeaaattttt👍👍👍
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?
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...
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
Please share the FOA Fundamentals of Accounting link that you mentioned in the video. Thanks. Thanks for the great content it is instrumental.
To derive Closing balance has to substract purchases return as well
I have completed my graduation, and post graduation, but i have interest in stock market, and i am experienced. And i want to learn more, Suggest me any finance related course?
Sir u have made a mistake while calculating ebita. U subtracted payment settlement charges instead of total indirect exp. I was following along with u n I literally pulled my hair out trying to find where I went wrong😄 plz correct it.
Great video btw👍
Very informative seesion, just one thing, in Monthly P&L tab the EBITDA calculation is wrong.
Very succinctly explained.
I learned more excel than financial modeling😀
This is a really informative video but sir while the calculation of EBITDA you have used the formula of E9-E16-E28 whereas it should be E9-E16-E29... seems like a small error otherwise very useful video and thanks for this!!
Thanks for pointing that out. I was stuck thinking why my EBITDA differs from his :D
I am getting decimal values for Weekends Lunch and Dinner . Let me how to approximate them
Can use Alt H 9, or simply click on the icon on number tabs on the home page
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.
Sir ,please check EBITDA calculation ,instead of Total indirect expenses you have taken payment settlement charges in calculation
Amazing tutorial enjoyed the complete session but i have few doubts my balance sheet isn't matching in year 2 & 3
Nice video. I learned a lot. But I would like to point out 1 mistake. Your EBITDA calculation is incorrect (at 1:33:59). It should be Total Revenue-Direct Expense-Indirect Expense. You took Total Revenue-Direct Expense-Payment settlement charges by mistake. Took one row above. That gives the wrong calculation.
Really a good informative video....helpful for sure....thank you
Hi - Please check and correct EBITDA formula in Monthly P&L