I followed along and completed the model. As a public Accountant, I found this very rewarding. Finally, a model that incorporates the balance sheet as well. Well done guys. I am enrolled with the CFI and I strongly recommend the institute to anybody who is serious about FP&A. Also, great for CPA's & CA's etc who want to be able to perform strategic planning and be able to impact the strategic decisions back to the P&L, Balance sheet and cash flow. Also, the skills gained from the CFI allow you to work independently from the constraints within off the shelf models. Keep up the good work. John Papazisis FIPA
This model is just the basic one with simple assumptions but the author had done it perfectly, easy to follow. In case we forecast company with high growth rate, debt ratios constrained, we will see how to deal with source of funding and cash surplus. Circular reference will be kicked in.
Although I don't work with financial forecasting, I found this video extremely interesting, objective and simplified. Great job. Thank you for sharing... I´m from Angola
Just completed a few courses from you and continuing. just loved your program. everything from teaching style, beautiful notes, well thought time duration and flexibility. I did all the courses honestly and thanks for that.
Though I am not new to the financial forecasting, I found this video extremely interesting as some of the long methods have been shortened and simplified. Great Work. Many thanks for sharing
1:35 iterative approach = circular fashion. 2:40 linked 3 financial statement model is the building block of all other financial analysis 2:55 you can do financial analysis, ratios, and test scenario, profitability. 3:20 metrics, accretion and dilution. 4:10 a traditional way of layout financial statement model to a single-tab model 4:50 instead of organizing everything horizontally, we organize vertically. 6:35 a lot of information on the approach: how one get to the other. 10:45 interest is dependent on the open debt balance. 15:20 corkscrew calculation: 17:00 then we have an assumption that the debt is either repaid, or additional borrowed again. 19:40 copy this formula over. 20:30 press Ctrl + R: to fill that right. 22:05 there are many nuance between current and future taxes. 24:05 how do we pull this equity capital forward? 25:10 assumption if the company need to pay dividends, then it needs to deduct. 26:45 non-cash items: D&A, stock-based compensation, unrealized gains or loss. But D&A is the main one 28:20 AP means account payable, we haven't paid. 29:00 investing cash flow could be complicated here, such as acquisition of other companies, disposition of assets.
Thanks CFI for making this! Question: At 24:12, H36 [Income Statement net earnings of 2015 is 27,313(Black=Formula)] and H62[Net Earnings of 2015 in Cash Flow Statement (Operating Cash flow, Blue=hard coded) is 26,713], How was this able to 'Balance' and make it past all the 'checks'? Thanks again!
Thank you so much. Literally relied solely on this for making the three fin statements and it balanced after a little tweak. This tweak was in my first year of the financial part of the cash flow. Instead of your method of subtracting the debt or equity from the previous year, I directly input the value of issuance on debt and equity from my assumptions table since I didnt have any historical data (my values were zero). It balanced after that and I'm still grinning over this. I dont usually write comments but this was really helpful. Thanks so much
@@CFI_Official Im having this same issue i think but i cant manage for the life of me on how to figure out how to fix my model any help is appreciated!
Thank you very much.... afther years i'have finally understood the cash flow and it's purpose. great easy example and explication. thank you. a day a will remember. bye. dario
We've made the template available for free download (due to so many requests!): corporatefinanceinstitute.com/resources/knowledge/modeling/link-the-3-financial-statements-cfi-webinar/
This is a great tutorial. It totally helped me understand how the 3 statements are linked. Questions: 1. I am a novice and wanted to understand that in real life, these steps apply to linking every single time; no exceptions? 2. What if the PP&E had a schedule and a salvage value? How different would the calculation be?
Question 1: Yes, these linking steps should work as long as the historical financial statement balances and there are no other manual plug-in numbers in the model. Question 2: If PP&E has a schedule and a salvage value, you will need to build a PP&E depreciation schedule and calculate a breakdown of the change in PP&E. We have an article on depreciation schedule which provides the steps to building a proper PP&E schedule: corporatefinanceinstitute.com/resources/knowledge/modeling/depreciation-schedule/ If you want an in-depth Excel demonstration, CFI's Startup / e Commerce Financial Model & Valuation Course covers the step-by-step approach to building the PP&E schedule and linking it to the financial statements: courses.corporatefinanceinstitute.com/courses/ecommerce-startup-financial-model-valuation
You talked about a monthly view at the end. I know how to build this but not always sure on what on the balance sheet will take the prior month for the year view and what will be a sum. Do you have a good resource discussing the monthly view?
Thanks a lots by the great corporate finance institute iam really inspire your works and such a great job doing by your organisation and i hope u could be make such as statements.And thank you so much for financial statement template to download the sheet .
Amazing content, thaks for that! One question, if i put a revenue by products, for exemple, in income statement of Apple, we can have the percentange which each product is responsible in the revenue, i mean, in this case, i could put the growth or something like this in the assumptions area?
and the 3 stateme model in one single sheet is what now i will allways do!!!! it's my way adopted in something i have never done... catch you ... i will do exactly so.
I still struggling with others different lines on the income statement and balance sheet. What should we do with Them . Assumptions? Or just try to add them somewhere else just to show them up?.,
Hi, are you referring to a single vertical tab Excel model? You can create sections of "income statement", "balance sheet" and "cash flow statement" using the Group function in Excel. The keyboard shortcut for the Group function is Shift + Alt + Right. Let me know if that helps!
Thanks for the great job. In calculating the grow rate for future period, I will just going to assume the %tange since we have known the growth rate for the historical or find out the industry growth rate
hi, i am about to follow the program and i saw this playlist so i want to see it before i register. may i know if there are an order to watches the videos or its already in order ? i hope to hear from you.... thanks
Hi Taleb, There is no specific order to watch the videos in this playlist. Feel free to explore them in any order you like! Hope you enjoy the content!
Thanks a lot for your lessons. They are really helpful. As a young man interested in finance, i know within me that i need a tailored guild to finance. I need a mentor. Please can you help out.
By doing that way the financial expense was treated as operating cash flow. Wouldn't it be better include the financial expense as an adjustment to the net income in order to get the operating cash flow and then add the financial expense to the financing cash flow session?
I can not for the life of me get the balance sheet to balance. My cashflow statement balances. Im tracking it back to the cash line item 41 which tracks back to line item 78 closing cash balance, which then tracks back to the sum of net increase (Dec) in cash and opening cash balance. The net increase (Dec) in cash tracks to cash from operations which looks correct. Cash from investing which is zero for me, and cash from financing which is way too high. So my major issue is how to calculate for issuance of debt and issuance of equity. Maybe im just mistaking what these line items are used for any help would be appreciated.
Those are assumptions about how the business will perform in the future. The forecast can be built by analyzing what happened in the past, combined with information about how the future looks.
hei, guyz, i am a bit under panik, i have to do a: valuation of the target incl. 3 statement model (BS, PL, CF). I assumed it's a dcf, but.. can anyone help me understand a bit?
What if I have a negative Net working capital number? ( i have just one negative and the rest are positive) I want to subtract it to get the cash from operations number, but I get the wrong answer bc its a double negative. How do I fix this without having to change the formula for any negative years?
Hey Jon K, I need to see your work before I comment on your question ! My best guess is as Negative WC(Working Capital) means that you are short on cash needed to finance your CFO (Cash flow from Operating Activities) which is needed to be financed from somewhere, In simple terms that's mean either Internally (Share holder's equity) or externally (Debt); and to do so you need you adjust your CFI (Cash flow from Investing Activities) or CFF (Cash flow from Financing Activities). Now! to know the amount you need use for example "IFSUM" function as the following =-SUMIF(Cell A1:A20"
Hi Marc, thanks for reaching out to us! We were closed over the weekend, but I've left you a voice message and sent an email :) Let me know how we can assist you.
I followed all the instructions but the cash in balance sheet and cash flow statement won't balance and I'm losing my mind. Please can someone help me?
I followed along and completed the model. As a public Accountant, I found this very rewarding. Finally, a model that incorporates the balance sheet as well. Well done guys. I am enrolled with the CFI and I strongly recommend the institute to anybody who is serious about FP&A. Also, great for CPA's & CA's etc who want to be able to perform strategic planning and be able to impact the strategic decisions back to the P&L, Balance sheet and cash flow. Also, the skills gained from the CFI allow you to work independently from the constraints within off the shelf models. Keep up the good work. John Papazisis FIPA
Hi John,
Thank you for taking the time to share your feedback! We are glad to hear that you found the courses and resources helpful!
@@CFI_Official hi it was so informative.. can i get copy of this excel for my reference?
I want to know how to down load your tutorial for trying to do myself again. Kindly o the needful
THis is real and amazing .Expeditetools,com helped me financially with a transfer of $30,000 yesterday .
@@diraviams9717 has has mi
This model is just the basic one with simple assumptions but the author had done it perfectly, easy to follow. In case we forecast company with high growth rate, debt ratios constrained, we will see how to deal with source of funding and cash surplus. Circular reference will be kicked in.
This is better than "Financial Accounting" from Haravard business school online... so practical and straight to the point!
Although I don't work with financial forecasting, I found this video extremely interesting, objective and simplified. Great job. Thank you for sharing... I´m from Angola
Another complex topic explained in the most lucid approach... Fantastic job done Tim...
Just completed a few courses from you and continuing. just loved your program. everything from teaching style, beautiful notes, well thought time duration and flexibility. I did all the courses honestly and thanks for that.
I didn't start to watch the video, but i want to thank you all for the effort and the help you give to all people around the world
great stuff, goldmine of information available for free.
Though I am not new to the financial forecasting, I found this video extremely interesting as some of the long methods have been shortened and simplified. Great Work. Many thanks for sharing
You're very welcome! We are glad to hear that you foudn the video helpful!
Thank you for the tutorial sir.I used to work in a securities company as a research analyst.I learned the 3 financial statement model
1:35 iterative approach = circular fashion.
2:40 linked 3 financial statement model is the building block of all other financial analysis
2:55 you can do financial analysis, ratios, and test scenario, profitability.
3:20 metrics, accretion and dilution.
4:10 a traditional way of layout financial statement model to a single-tab model
4:50 instead of organizing everything horizontally, we organize vertically.
6:35 a lot of information on the approach: how one get to the other.
10:45 interest is dependent on the open debt balance.
15:20 corkscrew calculation:
17:00 then we have an assumption that the debt is either repaid, or additional borrowed again.
19:40 copy this formula over.
20:30 press Ctrl + R: to fill that right.
22:05 there are many nuance between current and future taxes.
24:05 how do we pull this equity capital forward?
25:10 assumption if the company need to pay dividends, then it needs to deduct.
26:45 non-cash items: D&A, stock-based compensation, unrealized gains or loss. But D&A is the main one
28:20 AP means account payable, we haven't paid.
29:00 investing cash flow could be complicated here, such as acquisition of other companies, disposition of assets.
thanks brother your helping us even after 5 years hope your doing good thanks once again I have my exams tom pray for me
Speechless! Just subscribed after watching my first video! Thanks @Corporate Finance Institute
Thanks CFI for making this! Question: At 24:12, H36 [Income Statement net earnings of 2015 is 27,313(Black=Formula)] and H62[Net Earnings of 2015 in Cash Flow Statement (Operating Cash flow, Blue=hard coded) is 26,713], How was this able to 'Balance' and make it past all the 'checks'? Thanks again!
Thank you so much. Literally relied solely on this for making the three fin statements and it balanced after a little tweak. This tweak was in my first year of the financial part of the cash flow. Instead of your method of subtracting the debt or equity from the previous year, I directly input the value of issuance on debt and equity from my assumptions table since I didnt have any historical data (my values were zero). It balanced after that and I'm still grinning over this. I dont usually write comments but this was really helpful. Thanks so much
You're very welcome! We are glad to hear that you found the resources helpful!
Im having this same issue i think but i cant manage for the life of me on how to figure out how to fix my model any help is appreciated!
@@CFI_Official Im having this same issue i think but i cant manage for the life of me on how to figure out how to fix my model any help is appreciated!
Wonderful video. Thank you for sharing. Where would a startup place "preorders" on a balance sheet where a deposit was taken?
I just can't believe how simple you made that difficult topic .. Thanks
Hi Ahmed,
You're very welcome! We are glad to hear you found the video helpful!
Great video. I want to understand more on the assumptions. Revenue & COGS. What informed those? historical CAGR, arithmetic?
Thank you very much.... afther years i'have finally understood the cash flow and it's purpose. great easy example and explication. thank you. a day a will remember. bye. dario
he made it so simple, I think the key is the formulas in the spreadsheets
Thank you for building this great model.
We've made the template available for free download (due to so many requests!): corporatefinanceinstitute.com/resources/knowledge/modeling/link-the-3-financial-statements-cfi-webinar/
Awesome vid! How do we download the file? Clicking on the "Download 3 Statement Model Template" link does nothing. Thanks in advance.
Keep it up! Looking forward for more videos from you, don't stop!
this is incredibly helpful! thank you so much. May God bless you
This is so incredibly helpful! Thank you so much for posting this :)
Thank you, Sir. What is the common list of schedules used in month-end close activities?
This is a good go-to for a refresher.
Thank you so much, very simply explained 👌🏻⭐️
This is a great tutorial. It totally helped me understand how the 3 statements are linked.
Questions:
1. I am a novice and wanted to understand that in real life, these steps apply to linking every single time; no exceptions?
2. What if the PP&E had a schedule and a salvage value? How different would the calculation be?
Question 1: Yes, these linking steps should work as long as the historical financial statement balances and there are no other manual plug-in numbers in the model.
Question 2: If PP&E has a schedule and a salvage value, you will need to build a PP&E depreciation schedule and calculate a breakdown of the change in PP&E. We have an article on depreciation schedule which provides the steps to building a proper PP&E schedule: corporatefinanceinstitute.com/resources/knowledge/modeling/depreciation-schedule/
If you want an in-depth Excel demonstration, CFI's Startup / e Commerce Financial Model & Valuation Course covers the step-by-step approach to building the PP&E schedule and linking it to the financial statements: courses.corporatefinanceinstitute.com/courses/ecommerce-startup-financial-model-valuation
You talked about a monthly view at the end. I know how to build this but not always sure on what on the balance sheet will take the prior month for the year view and what will be a sum. Do you have a good resource discussing the monthly view?
Had to re-watch two times and redo my work but I finally got the spreadsheet balanced. Thank you for providing such informative content for free!
Thank you, very easy to follow and understand, thank you for teaching this
Love this form of financial statement integration here.
Thanks!
Very enlightening class. Well done
Hello Thank you for your video, I have leant lot. but i dint get from where you updated assumption parts will you please explain
Really helps me a lot in learning 3 statement model thank you CFI 🙏
Is there a video teaching the layout of the excel template above? Illustrating how we go about creating such a layout.
can you tell me how the forecast percent numbers are calculated ?
Thanks a lots by the great corporate finance institute iam really inspire your works and such a great job doing by your organisation and i hope u could be make such as statements.And thank you so much for financial statement template to download the sheet .
Hi Shaik,
You're very welcome! We are glad to hear that you're enjoying the resources.
Amazing content, thaks for that! One question, if i put a revenue by products, for exemple, in income statement of Apple, we can have the percentange which each product is responsible in the revenue, i mean, in this case, i could put the growth or something like this in the assumptions area?
and the 3 stateme model in one single sheet is what now i will allways do!!!! it's my way adopted in something i have never done... catch you ... i will do exactly so.
Thank you so much for doing this!
I still struggling with others different lines on the income statement and balance sheet. What should we do with Them . Assumptions? Or just try to add them somewhere else just to show them up?.,
Excellent webinar Tim, thanks!!!!
This is awesome! Thanks for the excellent video
This is really nice!!!! Thank you
May I know where is the quarterly and monthly videos? where can I watch it? Thank you.
Many thanks for sharing
Congrats for the tutorial!!!
Thank you for the tutorial. It is very valuable for my studying!
It is excellent, thanks for sharing
Excellent video. Cheers
Thanks for sharing, How did you come up with single tab presentation?
Hi, are you referring to a single vertical tab Excel model? You can create sections of "income statement", "balance sheet" and "cash flow statement" using the Group function in Excel. The keyboard shortcut for the Group function is Shift + Alt + Right. Let me know if that helps!
Fantastic connection in Excel, how can I get a copy of this excellent spreadsheet please?
Soooo much more clear than mergers and inquisiitions
Glad you've found it helpful!
This is absolutely fantastic, thanks for sharing.
Why net earnings in the income statement FYE 2015 & 2016 are different to the cash flow? Otherwise great video, thanks for sharing.
I thought it was weird too.
Should I edit the three financial statements? I have missing parts in this income statement template, the same for the balance sheet
Thanks for the great job. In calculating the grow rate for future period, I will just going to assume the %tange since we have known the growth rate for the historical or find out the industry growth rate
Please the above link does not show the Excel Template. Anybody with the template
Why have we not considered interest payment added back to CFO and subtracted in CFF?
This form of integration is simple and logical.
Sir,
How to prepare financial statements individually and link them for a perfect report.. Financila Statements
Very helpful, thanks for sharing.......
Excellent webinar. There's an error in cell H62 (Net Earning). It should be the same as in cell H36. Please check both numbers.
How to find future revenue growth assumptions on income statement using historical statements
great stuff, very straightforward, thank you
Thank you for your feedback! We're glad to hear you enjoyed the video.
@@CFI_Official Please let me know if you have a financial model to forecast commercial banks
i ask for fmva i have cfa investment foundation and i dont know anything about FMVA and how to start ?
hi, i am about to follow the program and i saw this playlist so i want to see it before i register. may i know if there are an order to watches the videos or its already in order ? i hope to hear from you.... thanks
Hi Taleb,
There is no specific order to watch the videos in this playlist. Feel free to explore them in any order you like! Hope you enjoy the content!
Great webinar. like the vertical tab method too. May I connect with you to get a copy of the template so I can build on that too?
You can download it for free here: corporatefinanceinstitute.com/resources/knowledge/modeling/link-the-3-financial-statements-cfi-webinar/
How can we forecast the Accounts Receivable, acc payable if we don't have any assumptions?
So if I purchase a vehicle and finance it I have both the loan and the asset. For some reason I can't get that to balance using this sheet.
Really interesting and well explained. I've been doing some financial modeling myself and this takes everything to a whole new level. Fascinating!
Thanks a lot for your lessons. They are really helpful. As a young man interested in finance, i know within me that i need a tailored guild to finance. I need a mentor. Please can you help out.
By doing that way the financial expense was treated as operating cash flow. Wouldn't it be better include the financial expense as an adjustment to the net income in order to get the operating cash flow and then add the financial expense to the financing cash flow session?
How did you know that the growth rate for 2017 was 5 at 9:17
Hi, the excel template used is no longer available on the site. Can I get it please?
Thank you for the tutorial . Can we get the excel file ?
Why is depn calculated on the Opening balance whereas addition to assets is ideally assumed to be done at beginning of year
I can not for the life of me get the balance sheet to balance. My cashflow statement balances. Im tracking it back to the cash line item 41 which tracks back to line item 78 closing cash balance, which then tracks back to the sum of net increase (Dec) in cash and opening cash balance. The net increase (Dec) in cash tracks to cash from operations which looks correct. Cash from investing which is zero for me, and cash from financing which is way too high. So my major issue is how to calculate for issuance of debt and issuance of equity. Maybe im just mistaking what these line items are used for any help would be appreciated.
how did you get the percent change of income statement and balance sheet in 2017 and so on?
Those are assumptions about how the business will perform in the future. The forecast can be built by analyzing what happened in the past, combined with information about how the future looks.
Very useful. Thanks for sharing :-)
thank you!
very useful one for the beginners...
thank you very helpful
Fantastic Job
Can anyone email the template for this ? I know it’s 7 years old but wouldn’t mind actually working through this.
What if Accounts Receivable were given as 50% of Total sales?
Thank you Sir
Why is the depreciation and interest is not calculated on the total sum for the year instead on the opening balance alone.
Hi,
You can email us at learning@corporatefinanceinstitute.com with any questions you might have and we would be happy to assist you!
How could I give reference to this particular video
You can use www.citationmachine.net/ to reference resources!
how is the growth rate forecasted?
فيه غلطة فى حساب AP حسبته على تكلفة البضاعة المباعة المقروض تحسبه على المشتريات
Thanks a lot!
hei, guyz, i am a bit under panik, i have to do a: valuation of the target incl. 3 statement model (BS, PL, CF). I assumed it's a dcf, but.. can anyone help me understand a bit?
Hi I am sorry to be obtuse, but does anyone have the file? thank you.
kindly would you send me the monthly model? it is urgent.. thank you so much!
too good !!
Please provide certification after course
Hi Pranesh. Yes, all of our free and premium courses come with certificates.
What if I have a negative Net working capital number? ( i have just one negative and the rest are positive) I want to subtract it to get the cash from operations number, but I get the wrong answer bc its a double negative. How do I fix this without having to change the formula for any negative years?
Hey Jon K,
I need to see your work before I comment on your question ! My best guess is as Negative WC(Working Capital) means that you are short on cash needed to finance your CFO (Cash flow from Operating Activities) which is needed to be financed from somewhere, In simple terms that's mean either Internally (Share holder's equity) or externally (Debt); and to do so you need you adjust your CFI (Cash flow from Investing Activities) or CFF (Cash flow from Financing Activities).
Now! to know the amount you need use for example "IFSUM" function as the following =-SUMIF(Cell A1:A20"
Called in to speak with a sales person about CFI consulting services and never got a call back.
Hi Marc, thanks for reaching out to us! We were closed over the weekend, but I've left you a voice message and sent an email :) Let me know how we can assist you.
I followed all the instructions but the cash in balance sheet and cash flow statement won't balance and I'm losing my mind. Please can someone help me?