Build a 3 Statement Financial Model (FULL Tutorial Free Template)

in this video we'll build a three statement financial model where we'll have a fully integrated income statement balance sheet and cash flow statement we'll also have supporting schedules and assumptions to forecast these three statements and finally we'll make a revolver to make sure we don't run out of cash so let's get into it and here's the Excel file that we'll be working with which you can download for free in the video description the first step for us is going to be to forecast the income statement and for it let's first remove the grid lines which

can sometimes be a bit distracting by going to alt W VG that's the same as going to the view Tab and unticking on grid lines here now if we scroll down over from the side you can see that we have these X's so we can just hit control down arrow to skip to the next section control down arrow again all the wait till we get to the assumptions and the first assumption here is that for the revenue well it's going to be the price price times the quantity Where here we have the units and the

price per unit so we can use those in the revenue tab over here by going to equals and then we'll go ahead and select the quantity multiplied by the price per unit and hit enter next up in the income statement we have cogs which is short for cost of goods sold and these are the direct costs associated with selling for example if you sell lemonade then it's going to be your lemon your water and so on so we're going to go to equals and let's make sure this is negative here and then if we scroll

down all the way to our assumptions again we have it as a percentage of Revenue so it's going to be that 43% here and we'll multiply that by our Revenue up over here we're going to multiply it by the revenue and hit enter so for the gross profit it's simply going to be the sum of these two so we'll just use the equals Su formula and select the two and hit enter next up we have sgna which stands for selling General and administrative sometimes you'll see this as a percentage of Revenue as is the case

for us here or it can also be a fixed amount for example a line item like rent which is included in sgna doesn't necessarily scale alongside Revenue depending on the business model for example a software company probably doesn't need rent to scale with revenue for now let's go ahead and fill in this assumption so it's going to be equals to the revenue multiplied by let's make this negative as it's going to be a cost and it's going to be multiplied by that 35% and we'll hit enter then we've got depreciation which we'll leave out for

now but we'll get back to later in the video so for now let's just hit the shift right arrow to select that area and let's highlight it in Orange by hitting alt HH and I'm just going to put the light orange over here just to make sure that we don't forget we can already set up the operating profit which is simply going to be the sum of the gross profit plus sgna because it's negative plus appreciation and we'll hit enter same thing with interest for the time being we'll leave that out we'll get back to

it after the balance sheet so we'll go to alt HH again and let's also put it in that light orange color and hit enter for earnings before tax it's going to be the sum of these two over here so operating profit plus interest assuming it's negative and then for the taxes here it does get a big trickier that's because if we don't have a profit to be tax so an earnings before tax that means we shouldn't really have a tax amount so we're going to create a condition for this we'll go to equals if we'll

hit the Tab Key there and The Logical test is that this figure here has to be greater than zero if not there's actually nothing to be taxed on so if that is the case we'll multiply that earnings before tax multiplied by the tax rate which we should have down below as that 21% so we'll select that comma and if it's not the case meaning that this figure here is less than zero then in that scenario we're just going to put a zero there as there's no taxes close a parenthesis and hit enter let's go back

inside of the formula by hitting the F2 key and at the very beginning we're just going to put a negative sign and net income is simply going to be the sum of these two so we can go to equal sum and let's sum these two over here here so now that we have this for one year and we've made it Dynamic so all we need to do is select the whole area so control shift down then we'll go shift right arrow and just use the shortcut crlr to drag that along all the way to the

right if you like what you're seeing and you want to learn more about financial modeling check out our complete finance and valuation course where you can learn all about accounting Finance valuation and financial model in on Excel if you're interested check out the link in the video description in the course first we cover financial statement analysis using Apple's real annual report as an example then we get into Financial modeling through a three statement model on Apple using different scenarios to make it Dynamic unlike in this video we'll use Apple's real financial statements taken from their

annual report and we'll also have different cases and scenarios to see how the forecast would affect our model after that we begin the valuation phase where you learn to do a discount a cash flow a comparable company's valuation and a precedent transactions valuation on adobi looking at their real financial statements to eventually derive a valuation range lastly we'll show you how to present an investment thesis using a stock pitch format we also have several other courses including Excel powerbi financial accounting and more so if you're interested in checking it out head over to the link

in the description below all right back to the video the next step for us is to forecast the fixed assets which is going to help in finding this line item over here for depreciation which we're still missing from the income statement so let's head down with control down arrow using the excess to the side and if we go down over to supporting schedules we'll be able to find the depreciation ation area over here so up here we have capex which is short for capital expenditure and these are the funds that we use to buy or

upgrade any kind of fixed assets like machinery and equipment now these fixed assets do get worn out over time hence why we have the existing assets useful life these are the ones that are pre the forecasted period and then the new assets use for life so these are the ones that we have bought uh in the future you could say so first up here let's link up the PPE which is short for property plant and equipment for 2023 end of period so we'll go hit the equals key we can actually find this figure in the

balance sheet which we'll get into fully later so let's head over to the balance sheet and right here you'll be able to find it and let's hit on enter there so now that we have the Copic and we also have the useful life we can calculate the depreciation of each asset if you're not too clear on depreciation it's when we allocate the cost of a tangible asset over its useful life so a tangible asset basically means one that's physical meaning that you can actually touch like a machine or equipment in that scenario we would take

the full cost let's say cost 10,000 divide that by the useful life to eventually reach the cost per year which would be the depreciation with this information we can calculate the existing asset depreciation which is simply well the existing um fixed assets that we have which is this figure here and we're going to divide that by 10 which is their useful life let's make sure we lock this whole area so we're simply going to select everything in here and hit the F4 key there to get those dollar signs and hit enter now we'll go shift

right and controlr this way we have the existing asset depreciation and now we need to work on the new asset depreciation and that's why we have this small table over here all we've done here is we've put this sideways with the transpose function so I can go equals transpose there hit the top key and I can simply select this area here and you can see that it distributes it kind of um vertically as opposed to horizontally and from here well we've calculated the amounts for hey this is the useful life and this is the copex

in one year so therefore we have this much in annual depreciation cost here it's going to be this much plus anything new that we bought which is this amount and so on and so forth so for the new asset depreciation it's actually going to be the sum of all of these here this way we account for any new ones we purchase and we'll hit enter there so we can go shift right and controlr then the total is simply going to be the sum of the two so we can go to equals sum and we'll select

these two now shift rate and crl R again now in this section we have made some small simplification just in the interest of time but this is enough for us to plug it back into the income statement so we'll go up over here go to equals let's make it negative and we'll take the calculated depreciation so this figure right here and hit enter now we can go shift right and crlr awesome we filled in the income statement the depreciation but we're still missing the interest so let's go ahead and try to calculate that for this

let's head over down to our assumptions so using the control down arrow all the way over here where we can see the long-term debt basically if you're not too familiar with interest it's the fee that you have to pay for borrowing money so in our case we have borrowed 40,000 and we have an interest expense of 7% meaning that the actual interest amount that we pay is simply the long-term debt multiplied by the interest percentage and hit enter now we can go to shift right and control R now we can go back up to the

income statement and fill in this final line item by going to equals and let's go ahead and select it from down over here it's going to be that 2,800 there and hit enter let's make sure to make it negative put the minus sign in front here and then shift right and contrl R all right now that we've wored on the income statement let's move on to the balance sheet and it's just going to be right below the income statement and as you can see for it we have the 2023 actual figures and the forecast of

figures for 5 Years first for 2023 let's make sure that it's all looking okay by getting all of the totals so equals sum here for all of the total current assets the total assets is the current plus the non-current which is simply PPE for us and we'll hit enter for total current liabilities it's the sum of these two so we can use the alt equals is the shortcut there and hit enter for total liabilities total current plus the non current and hit enter and shareholders Equity alt equals again and hit enter finally total liabilities plus

Equity is this figure plus this one right here and hit enter now for a balance sheet we can always make sure it's correct by seeing if it balances so does the total assets so equals total assets minus total liabilities and Equity equals z if that's the case it means that it's working well so that's all looking good now we can work on all of the forecast TI and the first line item is cash and cash equivalents which we don't quite know yet because we need to use the cash flow statement to find it so for

the time being we'll select it and ALT HH again let's highlight it in Orange just so we don't forget now we can get started with accounts receivable for this we'll head over towards the bottom where we have some assumptions and we'll make them over here as you can see under balance sheet assumptions we have accounts receivable as a percentage of Revenue so for 2023 meaning our historical year we'll take the accounts receivables that we already had in the past so this figure right here and divide that by the revenue that we had for that period

which is this figure over here and hit enter there so it's a 6.2 what we want to do from here is basically project it out ideally we would have like 3 years of previous historical periods and we would take the average but in this case we only have one year of data so we'll simply link that then hit shift right and crlr same thing goes with inventory down below we can just select the area so shift right and then we can just go to shift down and controll D that's going to drag this formula down

to make sure it's all correct we can always double click inside of it inventory as a percentage of cogs we can see that we have cogs over here well selected and the same thing should be the case here with inventory so it's all looking accurate while we're here let's also add the revenue and the cogs over in these two lines that we actually calculated using the income statement earlier so we'll go to equals and select the revenue up over here and the Cog is right below it so we can just go shift down shift right

contrl R to drag to the right and contrl D to drag down let's make sure to change the signs here to a negative minus here just so it's consistent so shift right controlr again now that's all looking good and we can calculate the accounts receivables and the inventory just by going back up over here and accounts receivable is going to be equals to the actual percentage that we calculated multiply by the revenue and hit enter there now same thing with inventory so we can drag both of these shift down contrl D and then shift right

crlr then we can take the sum so it's going to be we can actually take it from here so we just go shift right and controlr it's the same formula to make sure it's working you can always press the F2 key and you can see it's summing the right area for property plant and equipment we actually need to use the fixed asset schedule that we had down below let's go ahead and scroll down so it's going to be this schedule over here we had worked on it for depreciation we just need to go a bit

further with property plant and equipment so we have the ending PPE which is this figure over here that we found earlier which is actually the same under the balance sheet as this figure over here hit enter there now the beginning PPE for the next year for 2024 is actually the same as the ending PPE for the previous year then for copics it's going to be this figure right over here that's an increase for us it's positive in that we're getting more Assets in while depreciation is negative for us as that's removing from our fixed assets

so the sum of these by hitting alt equals is going to be our ending PPE now we can go to shift right and crol R again to drag this along let's plug this back into our balance sheet so up over here we're going to go to equals and let's plug it so let's go ahead and select it from down here the 57,000 it should be now we can go shift right and contrl R all right so we now have the total assets amounts so let's go shift right and controlr that should be working well so

we can move on to the liabilities for accounts payable down below it's going to be similar to the receivables if we scroll down you can find it over here accounts payable as a percentage of cogs for the historical period so let's go ahead and take the accounts payables which is going to be this figure here and divide that by the cogs that we actually placed down here and hit enter so again we'll use the same method as before and just link it shift right and controll R awesome now we can go back up to the

accounts payables here and select equals we'll take the the cogs and multiply by by that percentage so cogs multiply by that 19.6 and hit enter now for the revolver we'll get back to this later in case you're not too familiar a revolver is basically like a credit card for a company so whenever there's not enough cash you can always take money from it so for now we'll just put alt HH and we'll leave it at Orange the idea here is that if we don't have enough cash we would take money from the revolver we'll get

to it later though now let's select this area shift right and controlr but same thing up above here shift right and contrl R awesome we can move on to long-term debt which we actually had down under our our assumptions as well so let's go ahead and find that that's simply this figure right here for long-term debt as you can see it stays constant shift right and contrl R total liabilities is going to be the sum of the current so equals total current plus whatever else is left which is only this figure here so shift right

and control R finally we have the shareholders Equity section with common stock first and for this one we'll just assume that we don't issue any more stock or repurchase stock either so let's leave that at a constant 25,000 then for retained earnings down below this requires a bit more of a formula hence why we have it in a supporting scale schedule so if we look down over here you'll notice that we have the retained earning schedule and it's got the beginning of period retained earnings which is the same as the ending so let's go ahead

and Link that from our historical balance sheet figure which is going to be this one right here and hit enter then that's the same as the beginning kind of the same pattern as the pp above hit enter there the net income we just calculated using the income statement so it's going to going to be equals to this figure up over here and we'll hit enter then we've got dividends which is basically when you pay out money from the company to the investors so in this case you can see that we have some assumptions on how

much has been paid out now it's negative here because it's kind of taking away from the retained earnings or from the net income of the company we'll go to alt equals and hit enter there now we can take these areas so shift down shift right and controlr same thing over here shift right and crlr just make sure you don't select this area and go to shift control right sorry just because you'll make the dividends amount the same throughout which is not quite right you can go back by pressing contrl Z so once we have these

figures let's go back up over here and plug retained earnings which is going to be equals to this figure down over here the ending period retained earnings and now we can select them shift right and controll R the total shareholders Equity simply we've calculated here as the sum of the two so we can go shift right and crlr now total liability in equity is equals to the total liabilities here plus any shareholders equity and we'll hit enter now we can go shift right and controlr now to make sure this is correct we can also do

the balance check so we can go to shift right and controlr but you'll notice that the figures are not correct here that's because we're missing the cash line item up top so we'll fill that in in The Next Step using the cash flow statement so if we scroll down by hitting control down arrow over to the cash flow statement the ending of year cash balance that we find will plug back into the balance sheet so let's get started with this as you can see for the cash flow statement we're using the indirect method if you're

not too familiar with the differences between the direct and indirect method I'd recommend you check out our financial accounting course so first we're going to plug the net income from the income statement so we'll go to equals and select the net income right here hit enter there then we'll add back the appreciation so make sure that this figure is positive we can go to equals let's put a negative there so we have a negative on a negative making a positive and we'll select appreciation from right here under the income statement and hit enter we're adding

back depreciation here as a whole point of the operating activity section is to go from net income all the way to cash from operations so depreciation is a non-cash expense it simply takes the total cost of the asset and allocates it over its useful life for accounting purposes there's no actual cash outflow going on every year hence why we add back the cash to net income this way we man managed to remove the effect now moving on to change in accounts receivables for this we're going to go to equals and we got to make sure

we get the signs correctly so we have accounts receivables up here now if accounts receivables has gone up from 2023 to 2024 is that positive or negative for cash that's actually negative for cash and the reason is that we haven't managed to convert these receivables Into Cash instead they've increased as accounts receivable so what we'll do here is take this years minus this years over here and so that change will be negative for us inventory will go to equals and the concept is exactly the same so if inventory is going up that means that we

didn't convert this inventory like what could be lemonade for a lemonade stand Into Cash which is negative for our cash balance so this one minus this one here and hit enter Then for accounts payable it's the opposite now if accounts payable has gone up that's a positive thing because we didn't have to pay cash to these suppliers or vendors that we maybe owe money to so we'll select this year minus the previous and hit enter with that we can calculate the cash flow from operating by going to alt equals and hit enter next up we

have Capital expenditures under cash flow from investing activities for this we can go to equals and all the way down we should find it the copics under our fixed asset schedule and hit enter now as for the signs here if we spend cash on buying a building or equipment that's obviously a cash outflow so we'll put it as a negative sign and hit enter there that's our total cash flow from investing activities and then we've got the change in long-term debt now we'll go to equals and we had the New Years long-term debt minus the

old years now there's no difference here as we then read any debt let's see for the change in revolver that's going to be equals to we'll set it up for now even though there's no figures this one minus this one and hit enter there change in common stock is pretty much the same thing so the this figure over here minus this other one and we'll hit enter finally for dividends this is obviously a cash outflow as we're taking cash from the company and giving it to investors so under the retained earning schedule we have it

as a negative so we'll leave it as such once we have this we can calculate the cashlow from financing by going to all equals and hitting enter awesome we're almost done with the cash flow statement just have the beginning of year cash balance which is the same as the ending of the previous years so we'll go to equals and we're going to select this figure right here hit enter there the change in cash is the sum of of the operating plus the investing plus the financing activities and hit enter and the ending of your cash

balance is going to be the sum of these two so we can go to alt equals and hit enter once we have this and if we linked it all correctly we can just go to shift right and control R awesome with this information we can link the ending year of cash to the balance sheet so we'll go up over here go to equals and let's select it from down over here and hit enter now we'll go to shift right and crlr and you can see that our balance sheet is balancing so that's all looking very

good so far there's still one final step we need to cover which is the revolver also known as a revolving credit facility this makes sense in the scenario where the company doesn't have a positive cash balance maybe the forecast you made is very negative and it's expected to not have cash in the future then the revolver is needed for example in many Financial models you might have instead of just one set of assumptions you would have multiple assumptions like a best case a base case and a worst case which is how we teach it at

career principles that scenario under the worst case it's likely that you'll need a revolver so if we scroll over to the bottom of the financial model you'll notice that we have this area called the revolver the first part here with the available cash simply has the goal of finding out how much cash the company has so pretty much the same as the cash flow statement except that it doesn't include the revolver itself so let's go ahead and fill in this section by going to equals the beginning cash balance we had under the cash flow statement

as well so it's going to be this figure here hit enter Then the cash flow from operations we had also calculated as this figure right up over here cash flow from invest thing same thing I know this is a bit tedious so feel free to go faster if you want to and as you can see for the cash from financing we actually have it split because we don't want to include the revolver so the change in long-term debt we calculated up over here hit enter change in common stock same thing it's going to be up

over here it's going to be this one hit enter and the dividends is going to be equals to this figure right here the- 10,000 hit enter now the cash available for the revolver is the alt equals so it's going to be the sum of all of these and hit enter now let's drag this along for all of the years so shift right and controlr let's scroll down lower and over here we have the revolver beginning balance which is simply going to be equals to if we scroll up over here as you can see we didn't

take out any revolvers so we'll hit there and it's going to be zero then we go back up to the beginning balance which is the same as the ending and let's go ahead and set up the total as well which is going to be the sum of these two here now let's drag this along so shift right and controlr now we can work on the change in revolver so we need to set up some kind of a formula that only triggers the revolver if the cash available here is less than zero because that means that

we're going to need to borrow some money now if it's greater than zero we don't want to borrow anything also if there's any revolver outstanding we do want to pay it off with the cash available that we have so the formula that we'll use here is going to be equals a minus Min formula hit the top key this is a lot easier than using IF statements in this scenario and we should take whatever is smallest between the cash available comma and the beginning balance we'll close the parenthesis and hit enter now let's drag this along

with shift right right and contrl R and we'll test this out later but first let's go ahead and Link it to our balance sheet so up over here we're going to go to equals let's go ahead and Link the revolver ending balance not the change but this figure here instead then we'll go to shift right and contrl R all right now let's test this revolver so you can understand what I've been speaking about so over here let's suppose that we want to trigger it by putting a 50,000 just to see what exactly happens so let's

say I actually go for 75,000 and hit enter you can see what's going on here where because we had a negative cash available of 75,000 that means that the revolver needs to take on 755,000 cuz that's the amount we need to borrow to have a cash balance of zero and then in the following years because we have excess cash it's going to use that cash available to pay down the revolver amount as you can see over here so that seems to be working well let's go back by pressing contrl Z Now the revolver is also

going to have an interest so let's go ahead and calculate that down below it's going to be equals to the revolver interest rate multiplied by the ending balance and we'll hit enter there so the total interest expense is going to be the revolver's interest rate multiplied by the interest expense for the revolver plus the other interest that we have which is the one with the interest expense for the long-term that here so we'll also add that and hit enter now we can go ahead and select these shift right and controlr let's go ahead and Link

these updated amounts into our income statement so if we scroll back up here under the interest it's not going to be just this figure anymore but instead it's going to be equals negative and all the way to the bottom we'll find it right here so it's that same 28,000 for now but you'll notice that we get this errors sign that basically says that we have some circularity error this happens when a formula is trying to calculate itself so for now let's just hit on Escape let's go ahead and make sure we link everything for now

with controlr and you'll notice that we have this blue arrow which is basically showing that we do have the circularity error an example of when circularity might happen is if we do something like equals and we try to sum the cell that we're currently trying to find the total of so like J2 plus j3 plus j4 in this case we would have a circularity error as we're trying to find the total in J2 so it's calculating itself now if we need to use circularity as is this current example there are a few workarounds and one

of the easier ones probably is using the iterative calculation so you can see right now that we're not actually getting the values even though we're linking to the right area so the 28,000 is showing as a zero so we can head over to the file towards the bottom under options here in the popup we'll go over to formulas and just enable iterative calculations make this value very big and this one very small and you can just hit on okay there now you'll see that we're getting the values back in we don't have the arrow anymore

our balance sheet is balancing so let's go ahead and test it out down below using the revolver and let's activate it by say putting another negative negative figure here like let's say -75 ,000 again hitting enter now you can see that it's all working in that we have to take out the revolver so that makes sense we also have to pay interest on that revolver so that's making sense there let's see if everything is still balancing though we go over to the balance sheet it seems to be balancing so that's all looking good now this

model does have some simplifications so if you want to learn more complex ones using real financial statements such as apples check out our complete Finance evaluation course over here or check out this video over here on a DCF which is the next step after a three statement model hit the like and that subscribe and I'll catch you in the next one