How to Link the 3 Financial Statements in Excel

Поділитися
Вставка
  • Опубліковано 22 гру 2024

КОМЕНТАРІ • 154

  • @johnpapazisis1997
    @johnpapazisis1997 6 років тому +94

    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

    • @CFI_Official
      @CFI_Official  6 років тому +2

      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!

    • @myjourney6262
      @myjourney6262 4 роки тому +1

      @@CFI_Official hi it was so informative.. can i get copy of this excel for my reference?

    • @diraviams9717
      @diraviams9717 4 роки тому

      I want to know how to down load your tutorial for trying to do myself again. Kindly o the needful

    • @fredgorman1575
      @fredgorman1575 4 роки тому

      THis is real and amazing .Expeditetools,com helped me financially with a transfer of $30,000 yesterday .

    • @sunwoonyuen7769
      @sunwoonyuen7769 3 роки тому

      @@diraviams9717 has has mi

  • @woodypham6474
    @woodypham6474 3 роки тому +8

    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.

  • @Ali_S245
    @Ali_S245 Рік тому +1

    This is better than "Financial Accounting" from Haravard business school online... so practical and straight to the point!

  • @Cassoma54
    @Cassoma54 Рік тому +2

    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

  • @yourstruly5DA
    @yourstruly5DA 7 років тому +13

    Another complex topic explained in the most lucid approach... Fantastic job done Tim...

  • @shrikantbadge3978
    @shrikantbadge3978 Рік тому +1

    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.

  • @asimhabbani2426
    @asimhabbani2426 4 місяці тому

    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

  • @amogh1685
    @amogh1685 2 роки тому +2

    great stuff, goldmine of information available for free.

  • @abdumail
    @abdumail 6 років тому +6

    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

    • @CFI_Official
      @CFI_Official  6 років тому

      You're very welcome! We are glad to hear that you foudn the video helpful!

  • @rizalukman7982
    @rizalukman7982 4 місяці тому

    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

  • @judyl.7811
    @judyl.7811 Рік тому +2

    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.

  • @ajk548
    @ajk548 2 роки тому

    thanks brother your helping us even after 5 years hope your doing good thanks once again I have my exams tom pray for me

  • @ibrahimgamer5177
    @ibrahimgamer5177 6 років тому +3

    Speechless! Just subscribed after watching my first video! Thanks @Corporate Finance Institute

  • @khakifit
    @khakifit 5 років тому +2

    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!

  • @Cameruption
    @Cameruption 6 років тому +4

    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
      @CFI_Official  6 років тому +1

      You're very welcome! We are glad to hear that you found the resources helpful!

    • @chrisgarrido8299
      @chrisgarrido8299 3 роки тому

      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!

    • @chrisgarrido8299
      @chrisgarrido8299 3 роки тому +1

      @@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!

  • @DonMaloy
    @DonMaloy 4 місяці тому

    Wonderful video. Thank you for sharing. Where would a startup place "preorders" on a balance sheet where a deposit was taken?

  • @aaboualy03
    @aaboualy03 5 років тому +2

    I just can't believe how simple you made that difficult topic .. Thanks

    • @CFI_Official
      @CFI_Official  5 років тому

      Hi Ahmed,
      You're very welcome! We are glad to hear you found the video helpful!

  • @ThikushoMichael
    @ThikushoMichael Рік тому

    Great video. I want to understand more on the assumptions. Revenue & COGS. What informed those? historical CAGR, arithmetic?

  • @darioguarracino8961
    @darioguarracino8961 3 роки тому

    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

  • @lizharaldmarketing
    @lizharaldmarketing 3 роки тому +1

    he made it so simple, I think the key is the formulas in the spreadsheets

  • @farhadahmad2373
    @farhadahmad2373 3 роки тому +1

    Thank you for building this great model.

  • @CFI_Official
    @CFI_Official  7 років тому +12

    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/

    • @mike12nv
      @mike12nv 2 роки тому

      Awesome vid! How do we download the file? Clicking on the "Download 3 Statement Model Template" link does nothing. Thanks in advance.

  • @Tracks777
    @Tracks777 7 років тому +6

    Keep it up! Looking forward for more videos from you, don't stop!

  • @storybegins8483
    @storybegins8483 Рік тому

    this is incredibly helpful! thank you so much. May God bless you

  • @KaranJanii
    @KaranJanii 3 роки тому +5

    This is so incredibly helpful! Thank you so much for posting this :)

  • @RA-rh5lb
    @RA-rh5lb Рік тому

    Thank you, Sir. What is the common list of schedules used in month-end close activities?

  • @TSquared2001
    @TSquared2001 4 роки тому +1

    This is a good go-to for a refresher.

  • @Denise.Conway
    @Denise.Conway Рік тому

    Thank you so much, very simply explained 👌🏻⭐️

  • @siddavid9355
    @siddavid9355 6 років тому +3

    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?

    • @CFI_Official
      @CFI_Official  6 років тому

      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

  • @6darthwader
    @6darthwader 11 місяців тому

    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?

  • @Foxygrandpa2131
    @Foxygrandpa2131 3 роки тому +2

    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!

  • @rhodahmatrobah7437
    @rhodahmatrobah7437 2 роки тому

    Thank you, very easy to follow and understand, thank you for teaching this

  • @TSquared2001
    @TSquared2001 6 років тому +6

    Love this form of financial statement integration here.

  • @loposantos
    @loposantos Рік тому

    Very enlightening class. Well done

  • @deepakbuva6621
    @deepakbuva6621 Рік тому

    Hello Thank you for your video, I have leant lot. but i dint get from where you updated assumption parts will you please explain

  • @trisviel7777
    @trisviel7777 3 роки тому +1

    Really helps me a lot in learning 3 statement model thank you CFI 🙏

  • @alecosavvas3361
    @alecosavvas3361 2 роки тому

    Is there a video teaching the layout of the excel template above? Illustrating how we go about creating such a layout.

  • @gigigogua9326
    @gigigogua9326 5 років тому +3

    can you tell me how the forecast percent numbers are calculated ?

  • @sam_official1710
    @sam_official1710 6 років тому +2

    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 .

    • @CFI_Official
      @CFI_Official  6 років тому

      Hi Shaik,
      You're very welcome! We are glad to hear that you're enjoying the resources.

  • @GabrielRebello-v5n
    @GabrielRebello-v5n Місяць тому

    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?

  • @darioguarracino8961
    @darioguarracino8961 3 роки тому

    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.

  • @daniilostroumov2906
    @daniilostroumov2906 2 роки тому

    Thank you so much for doing this!

  • @rikiboy212
    @rikiboy212 3 роки тому +1

    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?.,

  • @ricardolivos
    @ricardolivos 5 років тому +2

    Excellent webinar Tim, thanks!!!!

  • @thefutureinvestor
    @thefutureinvestor 2 роки тому

    This is awesome! Thanks for the excellent video

  • @joshuaofomaja506
    @joshuaofomaja506 3 роки тому +1

    This is really nice!!!! Thank you

  • @pl4195
    @pl4195 7 місяців тому

    May I know where is the quarterly and monthly videos? where can I watch it? Thank you.

  • @shalafang7099
    @shalafang7099 3 роки тому +1

    Many thanks for sharing

  • @mjcamposyt
    @mjcamposyt 4 роки тому +1

    Congrats for the tutorial!!!

  • @MPOON100
    @MPOON100 5 років тому +2

    Thank you for the tutorial. It is very valuable for my studying!

  • @sreenadhb
    @sreenadhb 3 роки тому +1

    It is excellent, thanks for sharing

  • @georgeaharris14
    @georgeaharris14 2 роки тому

    Excellent video. Cheers

  • @tinashemasango1114
    @tinashemasango1114 6 років тому +2

    Thanks for sharing, How did you come up with single tab presentation?

    • @CFI_Official
      @CFI_Official  6 років тому +2

      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!

  • @paulocampbell7638
    @paulocampbell7638 Рік тому

    Fantastic connection in Excel, how can I get a copy of this excellent spreadsheet please?

  • @jacobstromburg5803
    @jacobstromburg5803 6 років тому +1

    Soooo much more clear than mergers and inquisiitions

  • @androidtempaccand2315
    @androidtempaccand2315 7 років тому +3

    This is absolutely fantastic, thanks for sharing.

  • @slee4966
    @slee4966 5 років тому +1

    Why net earnings in the income statement FYE 2015 & 2016 are different to the cash flow? Otherwise great video, thanks for sharing.

    • @bcca2284
      @bcca2284 6 місяців тому

      I thought it was weird too.

  • @MrGlobalxD
    @MrGlobalxD 3 роки тому

    Should I edit the three financial statements? I have missing parts in this income statement template, the same for the balance sheet

  • @abideenadeniyi3971
    @abideenadeniyi3971 5 років тому

    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

  • @Adeymolahubs
    @Adeymolahubs 2 роки тому +1

    Please the above link does not show the Excel Template. Anybody with the template

  • @sahilrege7203
    @sahilrege7203 3 роки тому

    Why have we not considered interest payment added back to CFO and subtracted in CFF?

  • @isaacadelugba7444
    @isaacadelugba7444 4 місяці тому

    This form of integration is simple and logical.

  • @Mad1080
    @Mad1080 2 роки тому

    Sir,
    How to prepare financial statements individually and link them for a perfect report.. Financila Statements

  • @lijujose424
    @lijujose424 6 років тому +2

    Very helpful, thanks for sharing.......

  • @wilsonmoscoso4620
    @wilsonmoscoso4620 4 роки тому

    Excellent webinar. There's an error in cell H62 (Net Earning). It should be the same as in cell H36. Please check both numbers.

  • @soundaryasuresh1845
    @soundaryasuresh1845 5 місяців тому

    How to find future revenue growth assumptions on income statement using historical statements

  • @geludan1
    @geludan1 6 років тому +2

    great stuff, very straightforward, thank you

    • @CFI_Official
      @CFI_Official  6 років тому

      Thank you for your feedback! We're glad to hear you enjoyed the video.

    • @pedromendez3300
      @pedromendez3300 4 роки тому

      ​@@CFI_Official Please let me know if you have a financial model to forecast commercial banks

  • @youssefmagd7501
    @youssefmagd7501 3 роки тому +1

    i ask for fmva i have cfa investment foundation and i dont know anything about FMVA and how to start ?

  • @talebbagazi1435
    @talebbagazi1435 5 років тому

    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

    • @CFI_Official
      @CFI_Official  5 років тому

      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!

  • @jayelliott1
    @jayelliott1 7 років тому +2

    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?

    • @CFI_Official
      @CFI_Official  7 років тому +1

      You can download it for free here: corporatefinanceinstitute.com/resources/knowledge/modeling/link-the-3-financial-statements-cfi-webinar/

  • @MarinaM-w6t
    @MarinaM-w6t 6 років тому

    How can we forecast the Accounts Receivable, acc payable if we don't have any assumptions?

  • @michaelarndt2713
    @michaelarndt2713 3 роки тому

    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.

  • @richardgordon
    @richardgordon 2 роки тому

    Really interesting and well explained. I've been doing some financial modeling myself and this takes everything to a whole new level. Fascinating!

  • @joshuakumah5678
    @joshuakumah5678 4 роки тому

    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.

  • @marcosvinicius6733
    @marcosvinicius6733 4 роки тому

    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?

  • @JinaCollins-l1w
    @JinaCollins-l1w 29 днів тому

    How did you know that the growth rate for 2017 was 5 at 9:17

  • @comedypawn
    @comedypawn Рік тому

    Hi, the excel template used is no longer available on the site. Can I get it please?

  • @monafakhoury15
    @monafakhoury15 4 роки тому

    Thank you for the tutorial . Can we get the excel file ?

  • @Jasssi2505
    @Jasssi2505 2 роки тому

    Why is depn calculated on the Opening balance whereas addition to assets is ideally assumed to be done at beginning of year

  • @chrisgarrido8299
    @chrisgarrido8299 3 роки тому

    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.

  • @erickasalvador3792
    @erickasalvador3792 7 років тому +1

    how did you get the percent change of income statement and balance sheet in 2017 and so on?

    • @CFI_Official
      @CFI_Official  7 років тому

      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.

  • @alexrosen8762
    @alexrosen8762 7 років тому +5

    Very useful. Thanks for sharing :-)

  • @HariHaran-fn8lv
    @HariHaran-fn8lv 7 років тому +1

    very useful one for the beginners...

  • @David-yn3kq
    @David-yn3kq 4 роки тому

    thank you very helpful

  • @internalaudit5649
    @internalaudit5649 6 років тому

    Fantastic Job

  • @jamesfields7036
    @jamesfields7036 5 місяців тому

    Can anyone email the template for this ? I know it’s 7 years old but wouldn’t mind actually working through this.

  • @simfinso858
    @simfinso858 2 роки тому

    What if Accounts Receivable were given as 50% of Total sales?

  • @jigneshsolanki9737
    @jigneshsolanki9737 3 роки тому

    Thank you Sir

  • @delmon8256
    @delmon8256 6 років тому +1

    Why is the depreciation and interest is not calculated on the total sum for the year instead on the opening balance alone.

    • @CFI_Official
      @CFI_Official  6 років тому

      Hi,
      You can email us at learning@corporatefinanceinstitute.com with any questions you might have and we would be happy to assist you!

    • @delmon8256
      @delmon8256 6 років тому

      How could I give reference to this particular video

    • @CFI_Official
      @CFI_Official  6 років тому

      You can use www.citationmachine.net/ to reference resources!

  • @leenalata2076
    @leenalata2076 5 років тому

    how is the growth rate forecasted?

  • @mahmoud-ragab2020
    @mahmoud-ragab2020 9 місяців тому

    فيه غلطة فى حساب AP حسبته على تكلفة البضاعة المباعة المقروض تحسبه على المشتريات

  • @_gamtam
    @_gamtam 4 роки тому

    Thanks a lot!

  • @White9Zombie
    @White9Zombie 4 роки тому

    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?

  • @ferriskf
    @ferriskf 8 місяців тому

    Hi I am sorry to be obtuse, but does anyone have the file? thank you.

  • @MsMelodyca
    @MsMelodyca 4 роки тому

    kindly would you send me the monthly model? it is urgent.. thank you so much!

  • @omkartawade
    @omkartawade 3 роки тому

    too good !!

  • @praneshjawake8246
    @praneshjawake8246 5 років тому

    Please provide certification after course

    • @CFI_Official
      @CFI_Official  5 років тому

      Hi Pranesh. Yes, all of our free and premium courses come with certificates.

  • @jonk9044
    @jonk9044 7 років тому

    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?

    • @siambogdan2460
      @siambogdan2460 7 років тому +1

      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"

  • @Marc_Koch
    @Marc_Koch 6 років тому +1

    Called in to speak with a sales person about CFI consulting services and never got a call back.

    • @CFI_Official
      @CFI_Official  6 років тому

      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.

  • @darkking5949
    @darkking5949 5 років тому

    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?