How to Link the 3 Financial Statements in Excel

[@johnpapazisis1997]

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]

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]

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

[@diraviams9717]

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

[@fredgorman1575]

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

[@sunwoonyuen7769]

@@diraviams9717 has has mi

[@CFI_Official]

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]

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

[@woodypham6474]

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]

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

[@yourstruly5DA]

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

[@Cassoma54]

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

[@asimhabbani2426]

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

[@abdumail]

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]

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

[@shrikantbadge3978]

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.

[@amogh1685]

great stuff, goldmine of information available for free.

[@rizalukman7982]

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

[@aaboualy03]

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

[@CFI_Official]

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

[@Tracks777]

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

[@Cameruption]

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]

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

[@chrisgarrido8299]

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]

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

[@ajk548]

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]

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

[@TSquared2001]

Love this form of financial statement integration here.

[@CFI_Official]

Thanks!

[@khakifit]

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!

[@lizharaldmarketing]

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

[@judyl.7811]

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.

[@farhadahmad2373]

Thank you for building this great model.

[@KaranJanii]

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

[@TSquared2001]

This is a good go-to for a refresher.

[@darioguarracino8961]

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

[@storybegins8483]

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

[@trisviel7777]

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

[@Foxygrandpa2131]

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!

[@ricardolivos]

Excellent webinar Tim, thanks!!!!

[@rhodahmatrobah7437]

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

[@loposantos]

Very enlightening class. Well done

[@MPOON100]

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

[@Denise.Conway]

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

[@siddavid9355]

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]

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

[@androidtempaccand2315]

This is absolutely fantastic, thanks for sharing.

[@jacobstromburg5803]

Soooo much more clear than mergers and inquisiitions

[@CFI_Official]

Glad you've found it helpful!

[@mjcamposyt]

Congrats for the tutorial!!!

[@darioguarracino8961]

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.

[@shalafang7099]

Many thanks for sharing

[@DonMaloy]

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

[@joshuaofomaja506]

This is really nice!!!! Thank you

[@ThikushoMichael]

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

[@sam_official1710]

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]

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

[@daniilostroumov2906]

Thank you so much for doing this!

[@thefutureinvestor]

This is awesome! Thanks for the excellent video

[@geludan1]

great stuff, very straightforward, thank you

[@CFI_Official]

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

[@pedromendez3300]

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

[@georgeaharris14]

Excellent video. Cheers

[@JinaCollins-l1w]

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

[@sreenadhb]

It is excellent, thanks for sharing

[@isaacadelugba7444]

This form of integration is simple and logical.

[@lijujose424]

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

[@RA-rh5lb]

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

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

[@gigigogua9326]

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

[@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?

[@rikiboy212]

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

[@6darthwader]

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?

[@paulocampbell7638]

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

[@alecosavvas3361]

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

[@soundaryasuresh1845]

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

[@abideenadeniyi3971]

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

[@pl4195]

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

[@Adeymolahubs]

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

[@alexrosen8762]

Very useful. Thanks for sharing :-)

[@CFI_Official]

thank you!

[@HariHaran-fn8lv]

very useful one for the beginners...

[@richardgordon]

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

[@Mad1080]

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

[@MrGlobalxD]

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

[@tinashemasango1114]

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

[@CFI_Official]

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!

[@youssefmagd7501]

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

[@Jasssi2505]

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

[@internalaudit5649]

Fantastic Job

[@sahilrege7203]

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

[@jayelliott1]

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]

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

[@comedypawn]

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

[@David-yn3kq]

thank you very helpful

[@jamesfields7036]

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

[@monafakhoury15]

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

[@jigneshsolanki9737]

Thank you Sir

[@michaelarndt2713]

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.

[@joshuakumah5678]

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.

[@wilsonmoscoso4620]

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

[@_gamtam]

Thanks a lot!

[@slee4966]

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

[@bcca2284]

I thought it was weird too.

[@talebbagazi1435]

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]

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!

[@marcosvinicius6733]

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?

[@MarinaM-w6t]

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

[@chrisgarrido8299]

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.

[@leenalata2076]

how is the growth rate forecasted?

[@delmon8256]

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

[@CFI_Official]

Hi, You can email us at [e-mail] with any questions you might have and we would be happy to assist you!

[@delmon8256]

How could I give reference to this particular video

[@CFI_Official]

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

[@simfinso858]

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

[@ferriskf]

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

[@MsMelodyca]

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

[@erickasalvador3792]

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

[@CFI_Official]

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.

[@praneshjawake8246]

Please provide certification after course

[@CFI_Official]

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

[@mahmoud-ragab2020]

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

[@peternguyen572]

Very good! I love this video ^_~

[@CFI_Official]

thank you, glad you enjoyed it!

[@White9Zombie]

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?

[@omkartawade]

too good !!

[@Marc_Koch]

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

[@CFI_Official]

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.

[@jonk9044]

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]

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"