Skip to main content
Loading Explore guides…
Three Hooks Financial Model Method · Acedra
Home
Explore
Business
Three Hooks Financial Model Method
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
41 segments
Copy
Hide video
0:00
if you ever built a financial model and got stuck trying to balance the balance sheet at the very end you're not alone that's where most people panic and go into fix it mode usually right before the deadline but what if I told you there is a way to structure your model from the start so the balance sheet balances automatically step by step every single time in this video I'm going to show you the method I teach and use in every model I built it's what I call the three hooks
0:37
all right so here's the idea the three financial statements are always talking to each other balance sheet cash flow income statement but if you don't set up those connections properly from the beginning it becomes a nightmare to troubleshoot later so I like to create what I call the three hooks right at the start of the model it's a smart way to link the statements through structure and logic so that as you build
1:04
each schedule revenue cost capex whatever the balance sheet stays balanced and if something goes wrong you know exactly where to look let me show you how it works in Excel we have a fully functional model here with the last few years actual so we can project the next five years i have already created the schedules you can see right here on the bottom you see the operating schedule you see the investing schedules and you see the financing schedules so you can
1:41
open each one of them and explore as you wish but right now I'm not creating schedules i have already done this before so I can concentrate in helping you understanding how to link the results of those schedules in the three statements by the way this model the way it is fully functional you can play with and you can even change the numbers and make your own it's right here down in the description the link for you to download it so download it follow along play with it and
2:18
understand how the three statements connect so let's get started by um creating the three hooks that's the first thing I do usually as I am beginning a model even before I create the schedules I just go back and create the three hooks as I create each one of these schedules I'll have the three hooks in place and I'll know that my balance sheet balances in this case I already create the schedules we're just going to link them in the three statements but it's
2:52
going to work the same way so let's start by the income statement i need the totals in place first so the first thing I do is I'm going to pull the formulas and if there's no formula on it you have to create them so I'm going to pull the formula for all the totals from the income statement and I'll do the same for the cash flow by the way if you noticed everything that is blue is hardcoded numbers so I'm not pulling
3:33
those because I know they're hardcoded i'm just pulling the black cells because they're formulas whenever you are creating a financial model make sure you use the blue cells to show what is hardcoded and what can be changed in your model without breaking the formulas so now I'm going to pull the totals also for the balance sheet and notice I am pulling only the totals i'm not pulling any of the blue cells
4:09
now grab this check right here on the bottom as well so now I'm ready to create the three hooks the first hook is going to be the net income going straight to my cash flow statement so I'm going to link this right here just a simple link
4:31
nothing fancy the second hook is my ending cash which being already calculated because I have the totals right here my engine cash is going to go straight to my cash line in the balance sheet and my third hook is going to be on my retained earnings the retained earnings the formula for the retained earnings is the previous year of the previous period plus the net income the accumulated net income plus or minus the dividends so I'm going to do the formula
5:14
right here i'm going to get the previous year plus my net income and it should be minus but because I'm linking directly to my cash flow it's going to be plus as well because in the cash flow my dividends is negative so I'm going to link right here now you can see I have a difference in my balance sheet and that's because the balances are not calculated yet even if nothing happens in my model for the next
5:56
few years all the balance remain the same so that's what I'm going going to do now i'm going to create temporary formulas to keep my balances the same as my last period
6:14
so I'm just going to copy this formula and paste it on every line except the ones that I already created and to paste I only want to paste the formula i don't want to lose the format i'm going to hit Alt S and it's going to open the paste special i can choose formulas and click okay so now my three hooks are in place just to review them the first one is my net income coming straight to my operating activities in the cash flow the second one is my ending cash
6:52
in the cash flow coming straight to my assets in the balance sheet and the third one is my retained earnings making the connection with both sides bringing the net income and the dividends so now that I have everything in place let's start linking i'm going to start with the revenue and as I told you I have already built all the schedules right here so I'm going to open the operating schedule and I'm going to link this to my total revenue
7:30
now that I have the the revenue in place you can see that it flows down through the income statement it ends up on my cash because I received that revenue right away i didn't um set up any type of accounts receivable yet so it's like I received everything in cash and it flows straight to my balance sheet once I have that straight on my balance sheet at the same time I am changing my retained earnings because of my net income so it's it's actually changing my balance sheet
8:08
in both sides of the equation the assets and the equity so I'm going to do the same with the cost and it's going to be the same effect so I'm not even go through but you see that it's going to happen the same here for my SGNA i am going to grab let me just open my assumptions right here because I need the inflation to link i'm going to grab the last period and I'm going to apply one plus inflation right here
8:55
now I have linked everything from revenue and cost let's take care of the working capital the working capital is all the current lab all the current assets minus the current liabilities in the balance sheet however I don't use the revolving credit as a current liability for the working capital even though it is a current liability i'm going to use this separately so for now I am going to project only the accounts receivable inventory and accounts payable of
9:33
course if you have a more complex structure of working capital there might be other accounts there so consider for working capital all the current assets minus the current liabilities i already calculated them right here on my operating schedule so I already used um days of accounts receivable days of inventory days of accounts payable GSO GIO as you wish to call um and I use them i used the average of the last few years to project them in the
10:12
future five years so I did plainly this you can do whatever you want as an assumption i just use the average page so I'm going to link them straight here on the accounts receivable on the inventory and now I can copy this to the side and you see I already have a difference right here but we're going to take care of this right away and accounts payable once I have that I have a difference right here on my um balance sheet and I am going
11:12
to take care of this by adjusting my cash once I say that I have accounts receivable it means that I am delaying the cash I am receiving so remember when I added the revenue right away and it flow through straight to my cash what I am saying with the accounts receivable by increasing my the amount in my accounts receivable i am saying that I am not receiving all of that revenue right away in the same period i am leaving some of that
11:41
revenue for later the same with my costs i am not paying everything on the same period i am leaving to pay some of that later so whatever remains in my working capital whatever remains in my current assets or current liabilities I need to adjust in the cash so that's why I am going to link this right here to the changes in working capital that I calculated right here
12:18
and with that I am balanced again so now I have done all the operating schedule i am ready to go on the investing schedule so the investing schedule is the PP&E property plans and equipment this is the line of the fixed assets this is where I calculate the fixed assets so you'll see here on my schedule that I first calculated the the balance of that account in the balance sheet and I also calculated the depreciation and to calculate the balance of
12:57
the balance sheet I use two items as well i use the capex I increase the balance with the capex and I decrease it with the depreciation so those are the items that we are going to link in the income statement balance sheet and cash flow so let's start with the line the main line the netpn once I link that I have a difference again in my balance sheet so now I'm going to go to the capex which is in the cash flow and it's going to be a dispersement it's going to be a
13:46
cash dispersement a cash outflow i'm going to pay for the equipment that I'm buying so money is going out of my bank account so I'm going to link this negative down to my capex
14:07
and of course I still have a difference because the last piece that is missing is my depreciation so the depreciation is considered as an expense and it goes straight to my income statement but in this case my income statement is taking it as positive it's already considering on the calculation here it's already subtracting it from the so to get to the so I am going to link this as a positive number right here so make sure you don't make that mistake and I have it right here
14:47
now when I do that I am reducing my net income and the net income is used in my cash flow however depreciation is not a cash transaction so it shouldn't it shouldn't affect cash in this way what we are going to do we are going to put it back so that's why we add the depreciation back right here in the cash flow so when you see how depreciation impacts the three statements it impacts the three statements through the PP&E line on the assets because it it is reducing my
15:32
PP&E every year and at the same time it's being expensed so it's reducing my retained earnings the net income is reduced and it's coming here reduced but it should it shouldn't have an impact on the cash flow so now I have that with the investing schedules as well i have balanced my balance sheet so now the last one is the financial schedules going to close the investing i don't need it anymore for now and I'm going to open the financial schedules i have here two
16:10
schedules i have the equity schedule and I have the depth schedule let's just talk with the equity schedule i should have calculated the balance in my common share line it doesn't change because I didn't issue shares i didn't made any purchase of shares so my common share line remain the same so I'm just going to link here just for the sake of having it correct and dynamic because if I
16:41
ever change this I'd like to have it linked on the right place so I'm going to link this right here and the other side is the dividends so when I pay dividends it's going to affects the cash flow and also affects the retained earnings so those are the two sides of the equation that the dividends affect but it goes in right here and I will link it but I want it negative so because I am paying
17:17
the dividends it should be negative on my cash flow so I'm going to link it right here common share dividends and once I do that my balance sheet is balanced as well i don't have many other pieces of equity to put it on i just have the common shares but I always do a retained earnings schedule just to make it more clear what is going on inside so I have it here it shouldn't change anything as you can see the number is the same but I like to link it back to my schedule so
18:00
just for sanity I am going to keep it link it to my schedule and for the shareholders equity if there was anything going on usually this is something that is pretty stable it starts with a initial capital and then it keeps like that forever until the um partners or the owners have any type of distributions then if so if you want to show that you will do the same as you did in the con shares and make sure you also do the same treatment for the distributions as you did with
18:40
the dividends but right now I don't have anything so I'll just keep the same balance now the debt that's the most fun one so let's get into it the first one that I'm going to see is the revolving debt the revolving depth is one that covers for any gap you have in your cash and this is already set up the way it is bringing all the items from the uh cash flow so I can calculate my free cash flow for the year and you can see right here that I have free cash flow for the first three
19:17
years and then I begin to have a gap in my cash so that's when my formula is borrowing down some money to cover for that gap and that's when I have to pay interest so I am going to link this you are welcome to explore this this formulas and I might be shooting some videos about this in the future but for now I'm just going to link this this is pretty complex but it's how the model becomes dynamic and this model is fully dynamic so I will start by linking the ending balance
19:56
of my revolving credit to the balance sheet so it's right here i will link it right here ending balance and you see that I already shows a difference right here and that's because I'm missing the cash flow part so whatever I borrow or whatever I issued of debt or whatever I repaid of debt needs to go in the cash flow so I'm going to link this right here and it already is with the right signal so I don't have to worry about that right here
20:51
so you see that when I borrow money money gets in my account and when I repay it's going to get out so in this case it's just getting in and my balance sheet is balanced i still have one other piece for the revolving which is the interest but I'm going to add them together at the end and link just one time because I can sum them together and it goes to only one line in the income statement so let's do the last piece of debt that I have
21:21
here which is the fixed term it's a long-term debt so I have calculated here the same way I calculated the balance in the balance sheet by just getting the the previous year's balance and adding or subtracting any issuance or repayment and then I end up with my final balance with my ending balance and based on that I calculate the interest so I'm going to link this right here to my long-term debt and it doesn't matter how many pieces of depth you
22:01
have you have to make an schedule for each one of them because they have different characteristics
22:14
and now I am going to put on the whatever on the on the cash flow i'm going to put whatever I paid or issued of this debt which is this right here
22:36
now you can see my balance sheet is balanced but there's one piece missing it's the interest that I need to link there on my income statement and the reason it doesn't unbalance my balance sheet is because everything in the income statement is affecting cash and is affecting equity at the same time
23:07
okay so we have everything linked you're welcome to play with this and make changes and break it and fix it as much as you want so you actually learn how to link those schedules in the three statements this is a very important thing to do as you are building a financial model from scratch
23:29
now that you've seen it in action you can start every model with the three hooks in place you never need to fix an out of balance model from scratch again because your structure will catch it for you i know it might feel a lot to cover in one video but if you're ready to go deeper you can check out my full course where I build a financial model from the ground up step by step always keeping the hooks in place and always showing the explanation for the financial
23:59
concepts you'll find the link for the course in the description below also if you have questions put on in the comments i'm here to help hit like and subscribe it means the world to me to know that you guys are taking a lot of advantage of those videos i'll see you in the next one
Here to help you learn
Sign in to keep a private conversation with your course tutor.
Ask me anything about the material…
Sign in to chat