Learn Financial Modeling Essentials in Excel (FREE Crash Course)

Finance For The Future
12 followers
66 Video Views·Apr 16, 2026
In this financial modeling crash course, we'll start from a blank excel file
and progress all the way to building a fully dynamic financial model.
You'll not only learn modeling industry best practices,
but also pick up on practical excel tips along the way. Let's get into it.
Here we are in the excel file and the first step is going to be to set up the years.
Let's say that we start at 2023 and then from here if we want to drag this all the way to
right, what we can do is just select the previous year and add a plus one.
Now just shift right and ctrl R. This way this value right here I can change to say 2025
and all of my other years are going to update accordingly.
That said, three of these here are actual figures or historical ones that have actually happened,
while these over here are forecasted years, so it would be nice to get some distinction
and for that we can do some formatting.
First, just press on ctrl 1. That's going to open up this format cells area
and we want to go all the way to the custom area down below.
Here you just want to put this number sign and in quotations let's put an A,
which is going to stand for actuals. In the sample you can see what that looks like.
We have that part done and for these forecasted years we're just going to put an E for estimate.
So again, I'm just going to do the same action here, which is this one,
followed by an E in quotations. Awesome!
And to make these years stand out a bit more, maybe we can change their background color.
So let's say that it's going to be from this part over here all the way to the end
and we can go over to this bucket area and let's say choose a blue color like this
and then same thing with the actual font color. I can make it white and bolden it with ctrl B.
On the top of that right here, maybe we would have the different titles,
like maybe this is for the income statement and for this we can actually just add a darker blue.
So up over here I'm going to choose one of my recent ones,
but feel free to choose a different one if you like.
For the font color I'm going to make sure white.
Then down below over here is where we need to add all the different line items.
So we would have the revenue, the COGS, which is cost to goods sold,
and that would reach the gross profit. Let me fast forward while I do the rest.
Awesome! So we have these line items and by the way you can download this excel file for free
in the video description to follow along, so you don't have to write this manually.
For some of these though they're not actual line items, it's more like their totals or subtotals,
like the gross profit over here. So it would be nice to make it stand out by pressing ctrl B to
bolden. For adding the border we can either do it manually by clicking on the top border here
or the other way is with the alt key shortcuts. So in this case it would be alt H B T for the
border. Now I just want to copy this style and paste it down below. So I'm gonna press ctrl C
to copy that and then down over here operating income I want the same style. I'm gonna do
ctrl alt V. That's gonna open up this pay special dialog box and down towards the bottom of it I
want to select formats. I'm just using the arrows to go down there and press enter on the formats.
I want to do the same thing down below, so ctrl alt V again. For the pre-tax income I want to
select just the formats. Finally for the net income we're gonna work on this one slightly
differently as it's a bit more important than some of these subtotals. So again ctrl V to bolden and
this time let's add a top and a bottom border. So again we can just use the mouse for top and
bottom border. And finally this is a bit optional but some people also like to add some kind of a
color on it like this yellow over here to make it stand out. Awesome! So we now have the income
statement formatting and we can actually start to add some values in here. So for these actual
figures right here they're going to be manually added. So let me fast forward how I do that.
As you can see right now these are in black but because these values are actually hardcoded
meaning that they're not a formula we just type them in manually. We should change the color.
This is typical in financial modeling. So we want to go to this part right here and it's going to
be a vivid blue color. The exact hex code if we click on more colors under this custom area
in the blue part here you just want to put 255. It's this hex code down below as well if you prefer
to use that. Now press on ok. Awesome! So now it's very obvious that these are hardcoded values
and then if we were to link this let's say I just want to select this value for 2026
then this one should be in black because it's a calculation. As you can see I'm simply linking
on the previous. Finally sometimes you might also see some green values. If that's the case it
basically means that it's linking to another worksheet. So if I had another sheet down below
and I link the value from here then it would probably be in green. That said this isn't as
common as using blue or black. At this point you might start to wonder it's great we have all these
actual values but how do we forecast them going forward? That's where all the different income
statement drivers or assumptions come into play which we're going to add in this bottom part. So
income statement assumptions are going to be down over here. Let me actually just add a bit of
formatting here so alt hh is going to be to add the background color. I'm going to go for this
blue color alt hfc and then I'm just going to choose white color like that and bolden it. So
over here the first line item would be the revenue growth rate. The idea is that we calculate the
historical growth rate that we have for these two years and based on that we can forecast going
forward. I'm going to show you a few different ways to forecast though. So firstly for the revenue
growth rate for 2024 it's the 2024 amount divided by 2023 minus one and we can just control r to
drag that to the right so it's that same formula. That said for 2023 we don't have enough data
because we don't have the 2022 value to relate to. Okay so we have the historical growth rates
that said should we just scale them out into the future? Should we take the average? And that's
part of the dilemma here. Some people might think of just taking the average of these past two years
and then making that equals to all the other years so just scaling it out like that. That said other
people might think well because the growth rate is higher in 2025 than 2024 maybe we should just
take the 2025 figure and then just scale that one out like that instead. While those are two basic
solutions there are some more complex ones. For instance you can look at equity research reports
or get the analyst consensus estimates and for this you can go to a page like yahoo finance for
free and take a look at it. So you can see right here for the revenue estimate for apple we have
all of these analysts that have submitted their reports so 24 analysts 19 etc and based on that
yahoo finance has collected the average revenue for the next quarter the current year as well as
the next year. Another way would be by taking the management guidance. Typically in their company
filings they're going to say what they expect the revenue is going to be in the next quarter or in
the next year so we could use that reference. And these are just some of the methods but really
there's so many more. You can go for a bottom-up approach like if we were tesla we would start to
multiply that by the average price for those cars to try to get our revenue estimate. In our case
let's suppose that we're just happy taking the average like this so I'm gonna delete this lower
part and use this to project the revenue going forward. It's gonna be the previous years multiplied
by in parentheses one plus the growth rate for the 4.7 percent. Hit enter there and now we can
drag this to the right and ctrl r. Awesome and at this point we're ready for our first of six
excel pro tips. The first one is simply freezing panes. As this model gets longer and longer it's
always nice to be able to see the header rows. So for this we can go right below the header
so this cell right here c4 and then head over to view and click on freeze panes. Freeze panes again
and now if we scroll down you notice that we have that top header regardless of how low we go. Same
thing goes with the first column so column b in this case. Even if I move this to the right like
this you can see that that column is always going to be there. That makes sense especially if your
models are on the longer side. Following the revenue forecast we should work on some of the
different expenses in this income statement. So it's gonna be the cost of goods sold as a percentage
of revenue and then same thing with the sgna research and development etc. So let me fast
forward while I type these. Nice so COGS as a percentage of revenue. We do have the 2023 figure
for this so it's just going to be the COGS divided by the total revenue and hit enter and we can just
drag that to the right with ctrl r. Same thing down below with the sgna so that's going to be
this part right here divided by the total revenue and finally r and d as well. It's this 400 divided
by the total revenue. Now shift right ctrl r. So we've calculated the percentages for the historical
years and now we need to project them going forward and in this case let's just take a simple
average of these three years. It's gonna go down so ctrl d like that and now from here all we need
to do is link them shift down arrow ctrl d and shift right arrow ctrl r. Awesome that's nicely
set up and let's actually fill these figures in. So the COGS is going to be this 62.7 percent
and multiply that by the revenue value hit enter there shift right ctrl r. Same thing over with
sgna it's gonna be this figure this percentage figure right here and multiply that by the revenue
and finally for r and d it's the same thing so this figure multiplied by the r and d percentage
again shift right ctrl r. All right now we can finish off the final two line items in this
income statement. So first for the other income slash expense net this has always stayed constant
at 50 so all we need to do there is maybe just link it for this part right here ctrl r and going
forward we're gonna link it to the previous year like this. Awesome now for the tax rate typically
you're going to be given this value maybe by the management guidance so let's say I just go for a
25 percent assumption here because this is an assumption we should change the color here to
a blue color like that. Now going forward there we can simply link it and ctrl r. Awesome so let's
fill in all these other line items this one we're just taking from here and then the taxes is going
to be the pre-tax income multiplied by the tax rate of 25 percent shift right and ctrl r right
now it's saying zero as we haven't added all these totals so let's actually work on that
pretty gross profit is the revenue minus the cost of goods sold shift right and ctrl r down below
operating income is gonna be the gross profit minus the sg&a costs minus the r&d costs and again
shift right ctrl r down below here for pre-tax income that's going to be the operating income
minus any other income or expenses net part shift right and ctrl up again and finally the very end
is the pre-tax income minus any taxes to reach the net income figures ctrl r there to drag that
to the right. Awesome so we built out the income statement and now this brings us to our excel tip
number two which is making sure we have the right colors and for this there is a cool shortcut so
let's select all of these numbers right here i'm gonna press ctrl g and from here press on special
when you do that we can click on this constants part this is basically gonna make sure all of
these values here are manual inputs instead of calculations press on ok and it should only
highlight those that are constants for all of these that are formulas you can see that it's
not highlighting them anymore so this would be a great way to find out if there's any values in
here that accidentally are calculations typically in a financial model you're also going to have a
cover page which is also known as a control panel so down over here you can see that i've set up a
very basic one where on the one hand we have all the general settings so here i can change the
company name monitor when it was last updated and who should get contacted then on the right of it
we have the table of contents and then on the left we have some other data we'll cover later
so first of all we want to make sure this is dynamic so you'll notice right now that if i
change this to 90 for instance this part up here actually updates and to do something like this
let me first delete what we already have it's actually quite simple all i need to do is press
equal sign select the company name from the settings area and then an ampersand and in
make sure we add a space here as we're gonna put financial model close the quotations and we just
hit enter that space is important if not it would just say nike financial model all in one word like
that so right now we have nike financial model split up and we can do the same thing over here
so we can call this the income statements and quotations income statement of space and close
quotations ampersand and we're gonna go under the company name from here so you can see this
is fully dynamic awesome and you've just learned the excel tip number three which is the ampersand
from here for the table of contents instead of just it being listed out it would be cool if
this was dynamic and for this we can use the next excel tip which is control k also known as the
hyperlink so i can press control k in here and place in this document and from here i can choose
the forecast sheet that we've just been working on so whenever someone presses income statement
it goes over to this sheet that's basically how you would make a table of contents a cover page
so keep pressing control k for all of the different worksheets the last part of the cover page i
wanted to mention is this left hand side with the scenario analysis which is actually something that
we'll cover a bit later in this video and then down below we have alerts so if you had a balance
right here you would want to make sure that the assets is equal to the liabilities and equity
so far we've been working on a relatively basic model but if you want to make full valuation
models like a disconnect cash flow or a comparable company's valuation i'll recommend you check out
our complete finance and valuation course here you'll learn the essentials of accounting finance
valuation and financial modeling in excel in the course we'll first cover financial statement
analysis using apple's real annual report as an example then we'll get into financial modeling
through a three statement model on apple after that we'll begin the valuation phase where you
learn to do a discounted cash flow a comparable company's valuation and a precedent transactions
valuation on apple 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
so if you're interested in checking this out head over to the link in the description below
all right back to the video the next steps for us are going to be to build out the scenario analysis
and also make the model dynamic and this brings us to our excel tip number five for areas like
the income statement assumptions down below here it makes sense to group them and for this all we
need to do is select the different rows that we want grouped and then head over to the data and
click on the grouping button which is this one right here all the way to the right so there we
go and now i can press on this one up top or on the two to expand that same thing goes with the
columns so let's say for these forecast years we don't always want to see them we can press
on group to add that and just collapse it like that because getting a forecast right can be
tricky we create different scenarios and that's what we're gonna do right here with the revenue
line item so here we can put revenue scenarios and down below it's typical to add three different
scenarios one is going to be the best case then we have the base case and finally we have the worst
case now let me format this area right here so i'm just going to take from this part up top
and ctrl c ctrl alt v and all i want to paste are the formats now we have this same look
for these figures right here the actual ones they shouldn't really change so we're not going to add
anything there but for the ones going forward these are the ones that we would want to update
so here all i'm going to do is press ctrl x and drag these all the way down here now for the best
case let's say that we just want to go up by two percent so it's going to be plus two percent in
here and then for the worst case let's say we just want to go down by two percent to this figure
minus two percent and hit enter ctrl r there and then control r on this top part as well
based on that right here as the revenue growth rate we want to be able to choose
so we're going to type an equal sign and use the choose formula hit the tab key there the index
number is we need to choose whether we want the first scenario the second or the third
and that's where the cover page part comes in this scenario analysis you might remember
so i need to select this first one make sure i lock this with the f4 key there if i add those
daughter signs so it doesn't move around comma and then we can go back to the forecast over here
and choose this as the value number one the best case number two is the middle case and number three
is the worst case now we can drag these to the right with ctrl r and let's make sure these are
all in percentage alt h p for that but to add another decimal place is alt h zero nice that's
all looking good but in fact the income statement doesn't really seem to be changing even if i
change the scenario to number three here you'll notice that nothing updates that's because right
now we're linking to the wrong part you might recall we're linking to this base part but in
fact we should be looking to this income statement assumptions area so we just need to make that
small switch right here and it's gonna be plus this value right here now shift right and ctrl r
and this should update accordingly in a fully dynamic manner so if i put a two in here these
values are going to update same thing if i put a one awesome so the scenario analysis seems to be
working correctly but right now for a new user that goes into the cover page it's not very clear
the best case or maybe even the worst case so that's why we can use another excel tip
this case number six by right clicking here and going to new note or new comment
alternatively the shortcut there is going to be the shift f2 key so in here we can just type
something like one is equals to best case two is the base case and three is the worst case
so i can hover over this area and you'll see how we get these instructions before we continue with
this model do let me know in the comments if you want a part two of this video awesome so far we've
done some great work on this model but right now it is actually a bit fragile that's because for
instance here if i change this to a five and we look at the forecast you notice that it's giving
us a ton of errors which is very concerning this is happening because our formula can only account
for three different scenarios so we need to make some changes in here to protect this to just have
one two and three as the options and for that we can go over to data and click on data validation
which is this check mark right here let's click on that and we want to allow for a list that's
going to be values one comma two comma three that's all we want if you want to have an error message
here's where you can write something like hey you can only add one two or three or something like
that this case i don't really want one so i can change this to let's say a four now and you'll
notice it just gives me a generic pop-up error data validation is great but it only works for
one particular cell in this scenario right here there's a lot of things that we wouldn't want
changed for example all of these black values we don't really want changed instead we would just
want all of the blue ones which are the manual inputs or the assumptions to be able to get
changed so for this what we can do is select all of the blue ones let me just select this one too
with the ctrl key right click and go to format cells within this area we can go all the way
to the right where it says protection and untick on lock press on ok then and then staying on the
same sheet we can right click and click on protect sheet feel free to add a password if you want i'm
notice what's gonna happen now in this area when the blue ones that i had highlighted i can't change
these values and everything is updating accordingly that said if i go to the black values and try to
make some changes you'll notice that i get this pop-up that's preventing me from doing that
how awesome is that and right now you've just worked on the income statement but if you also
want to work on the balance sheet and cash flow statement you should watch this video over here
alternatively if you want to create a full discounted cash flow model then you should
watch this other video over here hit the like and subscribe and i'll catch you in the next one
