Build a Dynamic 3 Statement Financial Model From Scratch
ฝัง
- เผยแพร่เมื่อ 31 พ.ค. 2024
- Create a three statement model linking the income statement, balance sheet, and cash flow statement into a dynamic financial model used for valuation (discounted cash flows, M&A models, LBOs etc.) as well as financial analysis (scenario analysis, sensitivity analysis etc.)
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
Find the FREE downloadable template of the 3 statement model below:
👉 DOWNLOAD the Excel file for this video: view.flodesk.com/pages/62061d...
This type of financial model is often used as the foundation for more complex models like a discounted cash flow (DCF). Here's the key steps in the process:
1. Firstly, we’ll organize and build our income statement using a set of assumptions
2. Next, we’ll create schedules to model out more complex line items like CapEx and Depreciation)
3. Following that, we’ll build out our balance sheet
4. Lastly build up the cash flow statement and link the 3 statements together
Along the way I'll explain the 3 financial statements and their key line items. I'll also explain the types of assumptions we'll be making to forecast figures for future years. Lastly, we'll learn some financial modelling best practices such as font colors, formulas, and custom formatting.
LEARN:
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
📊 Get 25% OFF Financial Edge Using Code KENJI25: bit.ly/3Ds47vS
SOCIALS:
📸 Instagram - / kenji_explains
🤳 TikTok - www.tiktok.com/@kenjiexplains...
📹 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
1:00 - Formatting the Income Statement
3:01 - Building the Income Statement
11:06 - Fixed Assets Schedule
15:42 - Formatting the Balance Sheet
16:49 - Building the Balance Sheet
27:05 - Building the Cash Flow Statement
31:10 - Linking the 3 Statement Model
Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
![เกิดใหม่ทั้งทีก็เป็นสไลม์ไปซะแล้ว ซีซั่น 3 - ตอนที่ 57 [ซับไทย]](http://i.ytimg.com/vi/pNhft3YZFRE/mqdefault.jpg)
📝 Our Complete Finance & Valuation Course: www.careerprinciples.com/courses/finance-valuation-course
Very informative but I was lost by your voice and face. Nice voice and handsome.
A TH-cam channel for the FMVA certificate. An exam will be uploaded for each module, in addition to 50 general exams for the certificate to train for the exam.
th-cam.com/channels/NXXbI0wyijpmkr7PIoAFtg.html
Certificate Explanation Channel
th-cam.com/play/PLVU8ZFD--E7taLJe0PoEE9PGpmcPDDiW7.html
@@drinksdessertsdiaries4613 m
how to download sir, i need your simple file excel , thank you sir
Big lair
Very informative and great video. Three comments:
1. the CFO is wrong; you should add the net income to it,
2. and the cash balance should never be minus! In this case, more short-term borrowing should be modeled.
3. the fully depreciated assets should be removed from the fixed assets, and accumulated depreciation balances
You saved me a 2 am “pls fix“
Now I have more knowledge about personal finance. I just subscribed to your channel. Big ups to everyone working effortlessly trying to earn a living while building wealth in this recession. I’m 45 and my husband is 51, we are both retired, no debts. We are planning on relocating to Thailand. We are currently living a frugal and financially savvy lifestyle and generating passive income even during this recessionary period. Our commitment to saving and investing in the financial market has enabled us to maintain this lifestyle and continue earning monthly passively through investment!"
Congratulations on your early retirement, Interesting indeed! Currently, I am in dire need of investment advice or tips. Last year, I hesitated and failed to take any action until the year concluded. However, this year, I am determined to try something new, as I am very receptive to various investment ideas. I want to be retired in my forties or fifties.
No problem at all! If you're seeking to earn substantial profits from your investment, I would suggest determining your investment horizon and implementing a long-term plan. I worked with Nolan Velden Brent to create a long-term investment strategy, and he assisted us in managing our investments while we focused on my jobs without any concerns.
Thank you for your advice. It's challenging to find a reliable investment advisor here, and I appreciate your input. Seeing the successes you've achieved through investing, I would love to have access to your investment advisor's information if you wouldn't mind sharing it.
I work with **Nolan Velden Brent** ,who is based in the United States. If you would like more information about him, you can conduct a search online. He even got featured on CNN recently.
Thailand is really a nice comfy plan to retire. Working with a skilled financial planner can be compared to having a mentor in the field of finance. I used to struggle to invest on my own and ended up losing money, but things changed once I started working with *Nolan Velden Brent* . He played a pivotal role in helping me improve my financial situation. Previously, I relied solely on my job and salary for income, but now I have found ways to generate additional income with ease, which has allowed me to leave traditional employment. Nowadays, I believe that investing is not a choice, but a necessity for anyone who desires financial independence and a good quality of life.
This was really well done. My first attempt at connecting all three statements. Great teacher!
Hi Kenji
Great video! I love how the simplicity you use to explain this! Very easy to learn!
Hi Kenji,
Just wanted to say a big thank you for your fantastic videos. I have gained so much knowledge from them. As a holder of an MBA in Finance from a private school, I can vouch that your videos are of the highest quality for educational purposes. TH-cam is a great platform to learn new things with amazing educators like you. I have been refreshing my skills and knowledge with your videos, which are truly top-notch. As a Financial & Cost Analyst for a public S&P company, I can attest that one never stops learning. I'm now a new subscriber and will definitely recommend you to all my colleagues.
Keep up the great work! Cheers!
I LOVE THIS REVIEW
Thanks a million Kenji, I really appreciate this. These videos have really helped a ton. Keep up the great work!!!
I have learnt a lot from you. The model is simple and elaborate and resourceful for projections. Your work is great
Clear and crisp information kenji no one were explained like you. Continue these type videos on excel.
"sharing is caring"
thanks will do!
THANK YOU! So much for these series!! It's just amazing Kenji
Managing money is different from accumulating wealth, and the lack of investment education in schools may explain why people struggle to maintain their financial gains. The examples you provided are relevant, and I personally benefited from the market crisis, as I embrace challenging times while others tend to avoid them. Well, at least my advisor does too, jokingly
Through closely monitoring the performance of my portfolio, I have witnessed a remarkable growth of $508k in just the past two quarters. This experience has shed light on why experienced traders are able to generate substantial returns even in lesser-known markets. It is safe to say that this bold decision has been one of the most impactful choi
that's impressive!, I could really use the expertise of this advisors , my portfolio has been down bad....who’s your financial advisor or coach, do you mind hooking me up?
The adviser I'm in touch with is *CAROLINA MELINA PHERSON* she works with Merrill, Pierce, Smith incorporated and interviewed on CNBC Television. You can use something else, for me she strategy works hence my result. She provides entry and exit point for the securities I focus on.
She appears to be well-educated and well-read. I ran a Google search on her name and came across her website; thank you for sharing.
I am helping my mom with an extremely small pickle business, just started a few months ago.
Thank you so much for this video! This will help us massively.
Still in business?
Dear Kenji, thanks for your explanation. Amazing! 👍👍
This was extremely helpful Kenji, it was a great refressher. Thank You
Pretty cool, easy, and intuitive stuff that I feared until I saw this video and implemented simultaneously. Thanks, Kenji!
One of the best instructors. Great work!
Dear Kenji.Thank you so much for the great lesson.
For the first time in my life, I could match a Balance Sheet. I feel, I have conquered the Mount Everest. God Bless You, Brother!
Hi Kenji, any video with details on how to forecast items like revenue, capex, COGS etc.? Also i loved this video and made the financial model too
Thank you very much Kenji! Much love and respect from Sweden
the best, short and compact,thanks Kenji the great
A very good video. I am ex multinational companies and what ur doing is simple and hence professional. Good job man
Hi, Kenji! Thank you for a great content. It helps me A LOT. It's very hard for me to understand accounting kind of things, I got C in my class 🥲 But I can tell your content is very well-explained and easy to understand.
Great video! Really helped me preparing for my recruitment process.
I must say, this video really makes my work so easy now that i saw it. thanks a lot, you really add some in me from an accounting point of view.
Hi Kenji, thank you so much for walking us through your 3 way model. You template and explanation was so very helpful. I have a quick question regarding key drivers, how do you integrate these into the model? or do you normally leave this in another tab? Thank you!
Hi Kenji
Am impressed by the very good presentation. It has helped me in a lot of my financial models in Excel.
Hey Kenji,
Thanks a lot for explaining the video in detail. It really helped me a lot ! Love from India
Best I’ve seen. Can you make one where you just build it from absolute scratch; like what you’d expect in an interview
Quick fix to this model in 2022 he estimates negative cash balance, obviously that cant happen or the company would go bankrupt, so an easy fix is to have a increase of capital (increasing common stock and cash by the same amount) so you don't go bankrupt. In the cash flow it would be a new row for capital raise or something along those lines. Great model!
Best tutorials out there! Keep it up Kenji
THANKYOU SO MUCH FOR THE GOOD QUALITY OF VIDEO AND EXPLANATION !!!
simple and easy to follow. Thank you for putting this video.
Kenji Thanks really a crisp and precise explanation God bless you thanks again
Very few videos amazed me. This is undoubtedly one of them.🌟🌟🌟🌟🌟🌟🌟
Very helpful lesson. People who don't pay for this kind of help should be embarrassed. Thanks Kenji. But ... @29:20 ... but I think OCF should be NI + (Operating Activites) ... not just OA? No? What you have summed is just the adjustments to reconcile net income to net cash. And finally, I am not sure a negative cash balance @31:35 in 2022 is right.
Thank you Kenji, you always deliver great content
I appreciate that!
Thank you! Kenji, it took me several videos to understand, but your clip was sufficient. Can you pls share the excel working file or perhaps completed file from this video. I downloaded the base file w/o data in it.
Thank you indeed kenji ! it really contribute a lot for me.
Kenji, amazing work, thank you
Amazing video. Just one observation:
(23:42) - To calculate the fixed assets value for 2022 you should have added ending value of 2021 that is 8000 & capex of 14000, because ending value of net fixed assets(2021) would serve as beginning value for year 2022. Please let me know if i am not correct. Thanks.
And negative cash usually means an increase in overdrafts. To which an interest rate needs to be added, so impact on net income so on equity. This would have complicated a bit the story, but Kenji just wanted to keep things simple to explain the basic principles, without going to resort to a goal seek to solve the balance sheet equilibrium.
Really thorough video, thanks for the great content 👍
Really good explanation without unnecessary info and complexity. I think you should include the net income when you calculate the Cashflow from Operations as it is obviously an "operational factor". Just my 2c. Keep up the good work!
Thank you! And yes I agree not sure why I had it as a separate item there…luckily doesn’t affect the overall calculation
For the assumptions, when do you use Revenue versus Net Revenue? You used both throughout this example.
Very good explanation. Kindly make more such videos.
Thank you for the work file sir. It was very useful..
Great work Kenji👍
Fantastic work on this guys!
Great work on the video. How do you anticipate if a cell changes e.g life value change if you lock different cells?
Purchased career principles. Thank you for the course. It is very clearly explained step by step easy to follow. with start and end excel files. It's better than big name ones. customer.
Thank you for this comment :)
Thank you, Kenji for the great content that you provide us every week! Can you please make a video next time about assessment tests for job interviews and give us some tips? Thanks
Thank you, I'll put the suggestion on my list!
I like your models and really like your explanations. Great video Kenji!
Glad you like them, thanks for watching Roland!
Hello Kendji ! Very useful video. I've an issue about the computation of "Operating Cash Flow". Why did you not added the Net Income ?
This video is quite helpful for new starters. Thank you for sharing it, one comment: Cash balance shouls always be above zero. There might be one more control for it, negative cash balance shall be cured i) capital increase ii) borrowing or iii) asset sale iv) or NWC management.
Yess another Excel video, thank you Kenji! :) these help a lot!
Happy to hear that!
This Guy is pure Gold...
Loved this video, thank you.
I am really luckiest person to subscribe your channel Kenji....❤ from India🙏
Awesome thank you for this, now i can solve my base case for tomorrow´s interview :B
I enjoyed every bit of the video. Thank you
Thank you for the download excel sheets and the video
Thanks Kenji for the explanation
This was an easy to follow video. Thanks
hello kenji, your video is exactly what I'm looking for, but I'm a beginner and you speak faster...
Dear @KenjiExplains,
Thanks for sharing this nice video on how to connect the three financial statements, it was nicely done!
Concerning the Cash Flow Statement: shouldn’t the Operating Cash Flow (OCF) be = Net Income +/- all the adjustments (i.e., Operating Activities)? Why do you let Net Income outside the formula (29:22)? I would add Net Income in the OCF calculation and not in the final calculation to obtain the Net Cash Flow. The final value of Net Cash Flow would be the same in both cases. Nevertheless, by not adding Net Income to the OCF it gives the impression that OCF is smaller than it should be.
Thank you very much Kenji. You're awesome! But can you please explain how possibly you can have a negative cash balance as of year end 2023 ? Just because it is an estimate or forecast?
He added the cash for those years in the end because he needed the cash flow statement to determine the amount of cash.
Your videos have been helping me a lot with my undergrad classes. Thank you so much!!
Question though: Is there a specific reason you use ctrl c and ctrl v for dragging your formulas instead of highlighting and using ctrl r or ctrl d?
Thank you! Yeah the shortcuts you mention don’t work on my computer haha that’s why im sticking to the ctrl c v 😂
@@KenjiExplains ok Thanks!!
Hi Kenji thanks for the detailed guide. I have a question: how do you go about determining how much a company reinvest its earning from the previous year into this year invested capital? Would you use use last year’s net income? NOPAT? Or net income - dividend paid? And how can you tell how much % of that does the company keep to reinvest in the next year’s balance sheet?
I hope the question is clear.
I think this is a subjective call and depends on the life cycle stage at which the company is at.
This will be decided by management or boards.....
great course, Kenji. regarding current asset, I notice you dont have any inventory there? assuming Just In Time, I suppose ?
how do you account and calculate for inventory item if we are not using JIT?
Hi Kenji, Thanks for sharing this video about your " THREE STATEMENT MODEL" awesome report. I want to learn more about your model report and valuation. How could have an access to those other report related to Finance & Accounting model. Thank you!
Thank you very much.
Quick question, as regards the assumption, can I link it to macro economic numbers that correlate with historic numbers?
Depends on what line item but it is common to use macroeconomic number like the industry growth or country GDP growth
Thanks for your video. I have a question, is the money item less than 0 unreasonable?
Great, thank you Kenji
Hi Kenji, would you tech us how to prepare a by cost center PL from a PL statmenet :)
damn man, this is amazing, thank you. subscribed
Kenji is an amazing financial specialist
very informative!! just have a question: when i use the shortcut 'alt + H + 6' nothing happens. do you know how to fix that?
Thanks Kenji, your videos are very helpful and informative. I have noticed that the 3 way is based on annual figures, can this be broken down to monthly figures or is there a reason it’s for 12 months? Thank you
This is a very simple model to introduce beginners. in fact you can break it down into months. you can also mix it with actuals and compare it to the business plan etc. you can prepare a really complex model in many ways such as production planning etc. Btw great beginners guide!
this is great kenji cant thank you enough
Thanks for watching Will!
Awesome Thanks Kenji
Great Video! Thank you :)
Quite educative. Thank you
Thanks for the informative video
Thanks for you video, you are a genius
Dang, Financial Analyst job looks interesting!
thanks a lot super helpful!
for the interest payment, should we use PY debt closing balance instead of current year closing balance
You are awesome Kenji
Thank you very much!🙏🏿
Amazing...! big thanks @Kenji
Thanks Kenji!
Kenji - great videos. I really appreciate all the content you share. In minute 14:36, shouldn't the depreciation of the new lemon crusher only be accounted in the 1st year post acquisition? From what I understood you are calculating the depreciation at the moment you acquire the asset, and I think it should start depreciating in Y1, not Y0. Could you clarify this? Thank you again for your work!
I can chip in here for you. Depreciation is used as a shield against paying taxes, but unfortunately you cannot defer it to start at a later period. Just as how you start paying back a loan when you recieve it you start to write off the cost of acquiring the lemon crusher on the same month/quarter/year as you have bought it.
Really enjoy watching your youtube videos. Thank you!
Thanks for watching!
Amazing video, very useful content. Liked + subscribed.
Great info!
Luv your videos so much! appreciated your work!
Glad you like them, thanks for your nice comment sir Chen!!
kenji you are the best my bro
Thanks for the shoutout. Glad we’re able to help folks with these excel tutorials!
Thanks again!
Thank you for the tempalate
Thank you for this