in this video we're going to cover five types of financial models you should know we're gonna rank
them from easiest to hardest let's go coming in our first spot at one star we've got a variance
analysis also known as a budget versus actuals and this makes sense to track how you're doing
relative to your budget both for personal finances or for company finances so let's take a look and
you can download all of these Excel files we'll be working with for free in the video description
below this model typically has three different sections this first one is a dashboard tab where
you can actually see the comparison so you can see on our income side we've got the budgeted amounts
next to the actuals and we have the variances next to them both in absolute terms as well as
percentages and you can see up over here that it's fully Dynamic and that we can change the month say
from April to May and all the figures are going to change as well same thing goes with the expenses
down below followed by our savings which is simply our total income minus our total expenses
and then to the side over here you can see that we have a few simple visuals just to see things
a bit differently so let's take a look at how this actually works if you go control page Zone to the
next tab you can see that we have the second tab which is going to be the budget inputs this is
basically where you input all of your budgets so you'll probably do this say at the beginning
of the year where you would put all your salaries and maybe any other forms of income that you might
have and then just below over here you would fill in all your expenses so really you're only looking
to fill in all of the blue areas and nothing else all of that is going to be dynamic in the form of
formulas as you can see here once you've done the budget side you also have to do the actual side
so this would be updated say every month this is where you'd be able to add all of your expenses or
incomes that you have along the way so you can see here we have all of these categories which
are the same ones that we saw earlier and next to that you can see that you can actually put
a description for it say on January 8th you went to groceries and you can have a small description
that you went Walmart shopping and here's the full amount for it if you want to add a new
line like say you spend something you can just go to control down arrow all the way to the bottom
just copy what you have there Ctrl C and then Ctrl V down below and over here you can change
everything from say the month let's say this was in June that's going to change dynamically let's
say in this case it was something like your bonus you got your bonus and then say mid-year
bonus and the amount can be say 5000 hit enter there and now when you go to the dashboard if
you change the month that would be able to change automatically as well next up are two stars we
have a capital budgeting model and this makes sense for a company trying to decide how they
want to allocate their money for example maybe you work at Nike's Finance team and you're between
opening a store in New York and San Francisco so this type of analysis would make sense there
if we take a look at the simplified model over here you can see that we're between two projects
project one and project two and so we're trying to decide which is better for us and within it
you've got the cash inflows so the money coming in and the cash outflows and you can see in year zero
there's no money coming in but there is an initial investment that makes sense maybe you have to go
ahead and purchase a property or maybe you have to just remodel it so that it fits your brand or
something like that and from there your costs should stay fairly even maybe that's just the
cost of the employees or the rent payment and just above that you have the cash inflows which should
be rising maybe as more people find out that this store is open here right and then just below that
you have the net cash flow which is simply one minus the other and the cumulative cash flow
which is whatever you had in the past plus this year's net cash flow and to assess which project
is better that's when the time value of money comes into play and so you can't just simply sum
these cash flows either as they're in different time periods so what you need to do instead is
use the net present value and the discount rate to try to get to that so as you can see over here
that's how you calculate the net present value and similarly you have the irr and the payback period
down below and using these three you're gonna be able to assess which project is more appealing and
whether it makes sense financially same thing goes down below for project two this is how you would
compare them a bit more equally for more detail on Capital budgeting you can check out some of
these links up over here where I go over it in detail all right now at three stars we've got
the comparable companies model also known as comps and this is an external form of valuing a company
meaning that you take similar companies and see their financials and try to apply them to your
company to get a valuation so looking at the model the first thing we would need to do is try to find
the comparable companies so in this scenario let's say that we're looking to Value enjoy
lemon so we have all of these other comparable companies that are also in the same industry say
in the lemon industry and we also want to try to make sure that they have similar Revenue figures
that they sell in the same geographical region maybe that they have similar customers right from
here we want to try to get all of their financial information online so if they're publicly traded
companies maybe we can go through Yahoo finance Bloomberg or some other paid resource and try to
get some of these financials over here once we have this relevant Market data as well as their
financials we can get to the valuation phase by doing what are known as multiples which are all
of these things over here if we look closely at them you can see that it's simply going to
be the division of one value over the other in this case Enterprise Value over Revenue
then Enterprise Value over ebitda and finally you've got the price to earnings ratio so you've
got all of these multiples for the competitors and from there we can start to get the average the
median and so on and so forth as you can see if you click here the median is all of all of these
competitors over here once you have these values you can get to the actual valuation phase which is
just down below over here you can see first that we want to calculate the implied Enterprise Value
which is simply going to be the median in this case we chose multiplied by the revenue that's
what gets us there and from here you need to do a few more steps like subtractions and divisions to
finally reach the implied value per share which is simply going to be the share price based off
of this multiple and based on these competitors same thing goes with this other multiple and
this last one over here and really what's quite hard about this is finding the companies that are
comparable sometimes if you're trying to value a very large company like apple they might not
have companies that are not similar to them and so that might make things a bit difficult
and speaking of financial modeling if you want to learn more about the topic I'd recommend checking
out Financial Edge which provides certified online finance courses and is also sponsoring this video
they've recently updated their private Equity associate course which teaches you everything
you need for a career in the buy side ranging from Financial Accounting to complex leveraged buyout
modeling alongside the Recently Added private Equity interview section and if you're interested
in other areas of Finance they also offer courses on investor banking trading portfolio management
and more so if you're interested you can get 25 of using Code Kenji 25 the link for that will
be in the description below now let's move on to the four star category and here's the
three statement model and this model consists of linking the income statement the balance sheet and
the cash flow statement and from there create a forecast for the company's future position
this model consists of five main sections as you can see we've got the income statement
then just below that we've got the balance sheet then we've got the cash flow statement
over here followed by the fixed asset schedule and finally some assumptions down below for the income
statement and the balance sheet now some more complex statements might also have a few more
sections like what might be the debt scheduling section as well as for the steps here you
typically start with the income statement as you can see over here we have some income statement
assumptions now depending on your company you might have some historical figures and from there
be able to project forward based on average growth rate or something like that but in this case let's
say this is a new project so we just made some simple assumptions on the number of cups that we
hope to sell let's say this is a beverage brand the average price for that cup and so on and so
forth from here you'd be able to derive on income statement like so simply by multiplying and so on
next up right below you have the balance sheet which is going to attract the assets and the
liabilities here as well as the equity and for this again it's going to be using some of the
assumptions that we looked at below so these um down over here the balance sheet assumptions
which includes some of the accounts receivables payables the beta mounts Etc as well as the fixed
asset schedule over here which we have and this would give us things like um whatever
fixed assets are as well as the total depreciation which would tie into all of this right over here
once we have the income statement and the balance sheet we can go ahead and work on the cash flow
statement down below which consists of the operating activities the investing activities
and finally the financing activities in order to reach our net cash flow so from there we can
go ahead and fill in the cash item here which is simply going to be the previous year's Cash Plus
the net cash flow that we calculated over here now I realized the three statement model does look a
bit daunting and there's a lot of steps involved so if you want to look at that more in detail you
can check out this free video over here where I go step by step to create a three-statement model
now for the final and hardest model in this video we've got the discounted cash flow model
also known as DCF and this is an intrinsic form of evaluation meaning that you look at the company's
internal cash flows to derive evaluation which is very different to the comps valuation we saw
earlier which was extrinsic looking at other companies if we walk through this discounted
cash flow model you can see that this very top part looks quite a lot like the income
statement that we saw earlier but from there it starts to look a bit different down over here
which is where we calculate the unlevered free cash flow basically here you can see that we've
got a few different assumptions including the fixed asset schedule and the networking Capital
down over here for these you can see that we need them one here for Capital expenditures
and this actually ties into if I click inside there all of this fixed assets casual over here
and same thing goes just below that for the change in working capital if I say go onto this one the
under current assets you can see that that's pulling over here where we've calculated the
total current assets and then we've got the total current liabilities and based on that
we're able to find the networking capital and with it we're finally gonna get to the unlevered free
cash flow over here which you can see the formula for up in here once we've calculated the unlevered
free cash flow as you can see we then move on to calculating the weighted average cost of capital
that's because for all of these as you can see that it's for estimated years so in the future
so we actually need to discount them back to the present now to discount them we need a specific
discount rate that's what we calculate using the weighted average cost of capital so let's scroll
down until we find that area which is going to be just after the assumptions so over here and you
can see that this is going to be a combination of the company's equity and the company is that and
taking that weighted average out of those to reach a percentage so as you can see here we've got the
weighted average from the dead side and then the equity side from here if we go over inside the
formula that's where we're taking the weighted average by doing the multiplications of their
proportions so here's the debt divided by the debt plus equity and down below we have the weight for
the equity divided by the plus equity and finally that's going to get us the work which in this case
is 6.2 percent primarily because most most of what we have is in the form of equity and there's very
little in the form of it all right so we know the discount rate and we also have the unlevered free
cash flow so let's go ahead and find the present value to do so it's just gonna be right below
so right over here you can see that we have the present value of the free cash flow so basically
we've got the unlevered free cash flows from above and from here what we're doing is we're getting
the walk as you can see here's the walk that we're taking and we're also getting the unleveraged free
cash flow and discounting it back in this case uh for for one year so to to the power of one
same thing for the next one it's gonna be to the power of two so we're basically putting it back
to year zero which is the present value now that we've calculated the present value of the free
cash flows there is one more step we need to take which is actually to calculate the terminal value
so as you can see over here we have the cash flows up to 2026 and we've actually sumed them all up
up over here you can see the sum or present value of the free cash flows but we're not
really accounting for 2027 and all the years going forward and it's fair to assume that the company
is probably going to still be around so we need to account for them somehow and that's where the
terminal value comes into play which is down over here and in this case we use the growth method and
there are two different types of methods that you can use for this not really going to get into it
here because we do have a separate video on that up over here so that then gets us to the present
value of the terminal value so we've discounted the terminal value from this end Year all the way
back to present and from here we can go ahead and do the calculation from Enterprise Value which is
what we currently have all the way to the equity value divide that by the number of shares that
we've got and that's going to give us the share price for the company which in this case we're
valuing at 358 dollars and again I realize that that's a lot of information but hopefully it's a
good initial overview you can check out this video like I mentioned earlier to see the discounted
cash flow in more depth now there's many more complex Financial models out there like an lbo and
a Christian dilution model or an option pricing model but hopefully this is a good starting point
for more on the free statement model check out this video or check out this other video
for more on a discounted cash flow hit a like and that subscribe and I'll catch you in the next one