- Even in large blue chip companies, like the DAX 30 companies, in Germany, for instance, Volkswagen
and BMW, Zeeman's and so on. Most employees of these companies
lack basic Excel skills. This is at least based on my experience. Having worked in consulting
for many, many years. In this video, I'm going to share a basic key technique in Excel review on how to build great,
simple financial models. Hi guys, my name Heinrich and welcome to another coffee break with me here on my channel Firm Learning. On this channel I try to
help you to become successful in the first years of your career. When I've already talked
a lot on this channel about how to create consulting style, PowerPoint presentations, and maybe you also already know that I have a whole online course on the topic of slide writing
on the topic of PowerPoint. You can check out all the
details, in the video description. If you're taking any value
at all of this video, please hit the like button
for the YouTube algorithm, and also subscribe to my channel
for more content like this. In this video, we're going to
build a simple financial model that is built around
the formula of SUMIFS. So how we structure this is videos that first I'm going to give
you a small introduction to the SUMIFS formula in case you're not yet familiar with it, then I'm going to show
you the simple project that I put together, mainly
based on this formula. And then third, we jointly go
over the key building blocks on how to build this financial model on how to build this project. So without further ado,
let's start to jump into it. So let's start by talking about
what the SUMIFS function is because as I told you, this
whole model will be strongly built around the SUMIFS function. And here we are on the official
Microsoft support page. So this page tells us
that the SUMIFS function. One of the math and trig functions
adds all of its arguments that meet multiple criteria. So as the names says SUMIFS
you sum certain things, if certain criterias are fulfilled. So how does the syntax of
this function look like? And don't worry later we will explore how this also looks on
a practical example. And the syntax demands to
first give the sum_range to give a range of the numbers that are supposed to be summed, then to give a criteria_range. So where the certain
things are to be found that need to meet certain criteria, then the criteria that
is supposed to be met. And then again, you
can add other criteria. So two, three, four, five, pretty much how much criteria you want. You can add them to the SUMIFS functions. So lets now look into
the practical example. So let's imagine we have a
basic list of company data here. So here is your column with the different customers
that you're serving. Here we see a column of months, and number of the sales. And let's just pretend this
is a transaction table. So whenever a customer
makes a transaction, then the month of the
transaction is recorded and then the sales then belongs to the
individual transaction. And now you can use SUMIFS formula to add up based on certain criteria to receive then the numbers by month. And then also more
complicated version of that. But let's start by now understanding what the sales per month are that you find here in this table. And of course manually,
this would be pretty messy. You wouldn't want to
manually add all the numbers in January, then all the
numbers and February and so on. But this is now where
SUMIFS formula can add. So here you start by typing
in this formula SUMIFS, and then he wants the arguments. So these are the arguments
that we saw before. So first the sum_range and
the range of the criteria, and then the specific criteria. So let's start by the sum_range. So what is supposed to be summed up? Well this is the column of sales and here it's good practice
to then also fix this. So by these dollar signs and the windows hot key is F4 to directly get these fixed references. Now you're adding the criteria_range. So where is, Excel supposed to look at to understand the criteria? And here we only want to
sum up now the sales numbers that belong to the individual months as we will have them here in, and which is why now we mark the we highlight the month column
as the criteria column. And again, we make the fixed reference. And now what is the specific
criteria that we wanna add? And this is now the month so we know click here on January to fill the numbers for the first column. And now we see that apparently
in the month of January of you know what it sum up or the numbers we have here in January this would sum up to the 33,000. And because now we had all
the references correctly, if we now fil it in, we see all the numbers
for the specific months. So you see that now here
the columns they are fixed, but of course this moves. So here March, April,
May, June, July and so on. And now if we sum all of this up. This gives us a total of 255,000. And just as a little check. So here just the basic, sums
formula that sums up numbers. We sum this up this way this is exactly the sum
of all these figures, which of course makes sense because these are the only
months that are included. So what do we need to do now if we actually wanna have a full matrix of not only the months,
but the customers as well? So let's first understand what all the distinct customers are because as you can see, many of them appear several times. And here, just the basic
little way to do that is to use the remove duplicates function. So if you highlight
certain values like that, and then you press remove duplicates, it's all selected correctly okay. And then it takes out
all the duplicate values and it only leaves with
the values that are unique. So while there were 34 rows here apparently only six distinct
customers were in this table. Now by taking the list,
we can transpose it because now we won't
have it in the row here. And by this, you mark this, you copy it with control + C and then you insert with paste special and now here with transpose to make sure that you get all these things in this one line. And now what we will use now is a SUMIFS formula with two dimensions. So with two different criterias. So again, we use the SUMIFS. We start exactly the same way. So the sum_range is the same column. And then we have the months
here first as the column, and then as an additional value. But now, because won't actually
copy the formula all over, make sure that you fix the column, right? You do that by having the dollar sign only in front of here,
the J the column symbol. And if you press F4 several times, you can target between
these different modes. So now we have it set up
correctly for the first dimension, but now we wanna do it for
the second dimension as well, because now we do not only
want to have the criteria that had only sums out the
values that have the right month, but we also want to introduce
a second criteria on Where the resemble supposed
to only sum up the values, where it meets here the right customers. So lets do that let's highlight
the customer's column again, let's fix it. And now we need to
highlight this one here, because you only want to calculate it if it has exactly the same values. So the same name from this column in here. And here again, let's also make sure the
reference is correct, but now we need to keep
the line, the row constant, not the column, but the row, which is why in all the dollar signs is in front of the row number
instead of the column, letter. And if we now close this. This apparently shows us
that the customer Paul in the month of January
didn't do any revenues. But let's now look how it
may looks out with the rest. So if we now highlight this and copy it over exactly in this way, we see now that it really
fed off the exact values based on the name and the month that they're most present
here in the table on the left. And if again, now we make a sum which pretty much sums up these values. We would see that this is
of course the very same, right, if we just add
together all these values. So this is the basic idea,
how SUMIFS function works, how this function works. Again, you have first the sum_range for the value that you wanna sum up, and then you can introduced pretty much as many criteria as you want. So now I could even add
here a third criteria and afterwards a forth and so on to really take all of these
dimensions into account. So this, just to explain how SUMIFS works. I hope this is clear because now we're going to jump into little financial models. So what will we do now
with this SUMIFS function? And my idea now was to build
a liquidity model with you. So it's just a simple little use case out of financial modeling. And the basic idea here is that we want to forecast our liquidity. So we want to forecast what
the cash of the company is, and we wanna do that on a daily basis. And so I just set up a very
simple little model here. What you can see is that
here you have the dates. So this is now the year 2021. So you can see it ranges
from 1st of January until the very end it is going to be the
31st, the 31st of December. So the last day of the year, and then we have all the years in between, since you have to count them here, remember was as you have the 365 days that we have here in the columns. And actually there is
a real story about this because I once worked for a company, it was one of our clients. We had a liquidity model like this, and of course it was a
bit more complicated. This is obviously now
very simplified version so with lots of more lines, but pretty much a model like this. Also every column, every day and what they were doing is they weren't really
building this with formulas as you can see currently
we are using right here and you can already see that many of these things are
based on SUMIFS formulas, but instead of that they
have a poor accountant really filling in each
individual field by hand. So there was one guy who
really put every single number in every single field in this
huge spreadsheet of 365 days and then times all the
rows that he had here. And of course this was insane
in terms of the workload, but then also in terms
of changing something. So imagine how you do
some financial planning and how your plan that
your personnel cost, for instance later on down
the year increase or decrease then you obviously look up
each individual sale by, it was just insane. But this is how people still work today in many of these companies and, you know, by taking this little, by looking at this, little video, I hope that you really understand how you could do these
little things much easier. And now I also put together here just a simple little chart, but you can pretty much see
how the liquidity evolves over time based on this
simple little model and what you see here. Now, this is, of course
also you can use it from a business sense. That by having a forecast like that, you suddenly you realize
that you were okay in March, I seem to be underwater, right? My liquidity seems to be negative. So probably need to be,
to revise my planning, and to do something about how I wanna handle my business operations, to make sure that somehow even this out, to make sure that
actually I'm able to meet all my financial obligations at this point in time, because of course, it looks like that in the end of the day
in the end of the year, actually there is enough liquidity there just the temporary lack of liquidity that someone needs to be,
of course taken care of. But that's now look into this model. How does it work? So the idea is that you always have a cash position at
the start of the day. Then you have some revenues,
you have personnel costs, you have direct spends or the direct material that
goes into your products. And then you have indirect spend. So pretty much, enough
for you to pay a rents, the utilities, offers materials, anything else that you need to pay. And then at the end you have the cash that remains at the end of the day. So if you take the starting position then you add the revenues of the day, you then subtract personnel,
you subtract direct spend subtract indirect spend, and
then you end up with the cash. But how or where are these
numbers now coming from? Because you see that
these are formulas, right? So none of this is
actually really hard coded hard written to the table. And here now I have a
couple of basic helper tabs, so let's briefly go over them. So here we have a revenue tab. So these for instance could be taken out of the revenue plan
for the company, right? So companies usually do a revenue plan, but they have some kind
of planning that says, okay, during the year, this will be the monthly values
that they make per month. And you see it fluctuates a bit by month. And then what I did here is I wrote here how many
days the month have. And then if I divide that to the revenue by the days
I get the daily revenue that I expect the company to make. Then the personnel cost so
this is now the payroll. So what amount of money do you need to pay before your payroll? And when does it need to be paid out. So here you see now the dates where I expect all of this to be paid out. And here we see the amount and apparently here in the month of July so the seventh month of the year, I plan to hire a couple of new people because my personnel expenses go up. Then we have direct spend so the stuff that directly costs that are caused by your suppliers. And here now the simple way I did it is I just pretend to be a four
supplier, so A, B, C, and D. I pretended that some of these suppliers, for instance, with supplier
A we made a payment plan. So we decided, okay, I need
this amount of material. And this is when we are
going to pay you, right? So here I now put the
individual payment dates for that supplier and the
cost, the money that I, now plan to pay with that guy. And then with supplier D for instance there's only one payment
in the beginning of August. And then last but not
least indirectly spend. So this is all the rest, right? So the office material,
utilities and so on. And here of course, now we just pretend we know how much that is
roughly going to be per month. Maybe also based on historic values. Again, we divided by days and
then we have the cost by day. So these steps revenues, personnel, direct spend, indirect spend, we just assume you know that we got this as an input from your financial team. And of course now if you work, you know, with a company or with a client, of course, these inputs are
all much more complicated, or maybe even use
yourself to build a whole need to build a whole
independent model, right? Just to come up with these values, just to understand in the end, what the value is supposed to
be, that you pay each month. But basically the idea is the same, right? So let's just assume that we have this and now we want to build our
liquidity model based on that. So how do we put this together into this kind of big chart that really gives me the cash position that I will have every single day? And again, I just wanna stress that this is now of course,
on purpose, highly simplified, but the logic would be exactly the same even if you have hundreds suppositions that then are inputs into
this liquidity model. The logic is exactly the same, just for the purpose of
showing it in a pragmatic way. I would do it like this. So lets just now jointly bid it, let's jointly bid, this
little liquidity model, now to really show you step
by step, how this works. And to do that I took out here all kind of the main inputs so just deleted all of this. And now the only thing that's
left is this basic formula. So what is now or here
let's start with this one, this formula, this gives you
the cash position of the day. So we start with the starting position then so basically the money that you have in your bank on the 1st January here we just assume that
it's going to be zero. So we just take here of the
value that is inserted here. So we just assume that we start the year with zero cash in the bank. Then we add revenues and then
we subtract all the costs. This is pretty much how it works. And then on the next day, so we either look whether a
new bank account was inserted. So if now you're going
to update this model, you can just insert then on the next day, the new daily bank account of this is not the case
as it's a student here. So if this is empty, so this is just a basic formula, then it picks the value, the
ending value of the prior date. So, pretty much, if you just
go here in the middle of it, pretty much just picks the end position that the last day ended with. And this is then the new value that the new day will start with, right? So this is just how this all
is set up in a basic way. And let's just now start by
now inserting the revenue line and what you see here above I just inserted a little helper line here, and this is just the month. So there's this month formula. So if you use this month formula this way, and you input in the condition
here in the input, a date, then it returns the month
value from one to 12. So you see these dates are all in January. So it gives me a one, then February it gives
me a two and so forth. And then the very end in
December, it gives me a 12. So this is just a simple
way to have the month value, here, present as well. So I just hide this, this
row for simplification. So let's start with the revenue. Now we insert SUMIFS. So what are supposed to be summed? And now what we want to insert here is this daily revenue value. Because you know, the model is
put up based on daily value. So we need to get a daily value to insert. So we now highlight, this row, this column
as the revenue column. And now we use it in a trick because we do not really want to add up these numbers, right? Because in January, it's always going to
be exactly this value, but I'm still using SUMIFS
just for simplification. Now I know that there would be other ways to do that as well. You could use different
kinds of lookups to do that, but I just believe it's always easier with these kinds of models and to always just use the same formula in this case SUMIFS
just to simplify it all. Right? So let's start with that. And now we use as a condition the month. Here is now the month that we use. So we only wanna take the
value of the respective month. And now we insert here the value that he supposed to look at. And here, now this is E1, right? So this was now the hidden field where we had here in this case,
the number one of the month. And you see directly that it's inserted. I just wanna make it clear
so let me unhide this again. What I inserted here was this field. This one here that we had here on top that had just hit with
when you're off the month. So now you see it gives me 111 and this was exactly the value that I want here to appeal
on every day in January. And if I now copy that until the very end, now we have all the revenues inserted and you see that here now i just made a sum of all these values so I'll know what the total is. And this total is
supposed to be 31 million. So let's check if I add all of these up here you see the sum 31 million. This is exactly the sum
that now has been inserted. Now of course the liquidity
looks really great because we just have the revenue and this is now how the chart looks like. Okay, let's continue yes. Let's continue by adding
the personnel costs. So again, we go into the personal tab, we highlight the sum_range, so these are now the personnel
values that we wanna add. Now we'll do it not a bit differently because now we do not want the month value because we do not want this number to appear in every single day, because this is not the daily value, but this is now the value
for the whole month. So instead we highlight the date column because we want this number only to appear at this very specific date. So in January this is not the number
that is supposed to come up at every single date in January but only on the 27th of January, where we assume that the salaries, the payroll is going to be paid. So now back in here as another condition, we highlight here the date field, and again, just go over the logic. So we add up a number in the personnel that was the individual monthly value. Then we look into the date
column here in personnel, and then we only sum it up to a value if then the date that we put here exactly equals the date
that we had here above. So here now on the first, this is zero because we
already know that the salaries are always going to be
paid out on the 27th. So if I answer this now here, you see it's always zero until the 27th, here's now where the date appears. And then later again, here the 27th of February, and then later that year,
it should raise to the 900 because we knew that then at that point, it decreases to the 900. So now direct spend I'll
do it in a similar logic. So here now we use SUMIFS
again, on the, this value, but you know what now that here now, indeed, we want to sum something up because here, now we could have situation where we have maybe several payments on the same date, right? So this is right now, not the case, but let's imagine that the supplier B also wants to get paid
on the 15th of January. So this is exactly the same value. Then on the 15th of January, we really want to have the sum of these two numbers in there, right? So not only look up an
individual one but the sum, so this is now where we
also really just need SUMIFS where we cannot just use
a simple lookup function. So let's start, we use this. Then we use the dates in the
same way as we had before. And then also we highlight here the date just, as we did earlier. Now I oops now I mark it and
I copy it until the very end. And now here we have the 9 million in and hopefully this now exactly
equals what we have here. Yes, the sum is 9 million. And what you can also see here now is that on the 15th of January here now exactly we have the 3 million that we wanted to have. And this is just a sum of all the numbers, throughout the 15th, right? To here 1 million plus 2 million
equates to the 3 million. And here now already, we can see that now this
liquidity looks a bit similar, or more similar to what
we had in the beginning. So lets now end with the indirect spend and this now form follows the same logic as we had with the revenues. We just sum up here, the daily costs. Then again, we use now the month, because this is not really the value that is supposed to be every single day. Now, here we insert the
monthly values here above. So you don't, we don't take the dates, but as we did in the
beginning, we took the month because now this is what matches the criteria column that we had before, you know, here we see 10.8 million and this, of course equates exactly to 10.8 million that we had here. So yes, this is now what
the chart looks like. So you remember that earlier, it was in March the down number, but now it is already a bit earlier and this is because we
moved this a bit forward now I don't know where this was I think it was a couple
of months later, right? But this is now the reason. So that's it right? Now, you built this
simple financial model. And now of course, I hope
that it is just clear that the big advantage of that is if you know, we wanna
make any kind of changes, you can just directly alter it So if you want to maybe
hire even more people, and then in the very
beginning, or in the very end, here you have 1.2 million,
actually, you just had it here. And now it directly goes into the model and you see, now that here, then the liquidity goes down in the end. And this of course is much easier than needing now to pull
up all these numbers. I mean, just imagine how difficult it is to navigate here
around these daily values. Which would be insane. So this just a very simple, basic financial modeling technique. Thanks to all of you for watching. I hope this was valuable. If it was please hit the like button for the YouTube algorithm. And of course, subscribe to my channel. If you wanna see even
more content from me, I also have an Instagram. My handled is @FIRMLEARNING. So check it out to see more. And I also have a mailing list where I send out regular content updates on how to be successful in
the first year of your career. If you're interested in that, there's a signup link below in the video description as well. Of course, now I'd be
very interested to hear, to get a bit of a feeling from you, whether all of this was
completely new to you, or whether you were already familiar with these kinds of topics. Also for me to further tailor the future content on Excel
further to your needs. So let me know below in the comments, whether this was new, whether you learned something, whether you already knew all of that to give me something to work with. And of course, if you've
any other question you'd like to ask me, feel free just to leave a comment and then I'm happy to reply. Thanks a lot to all of you guys. I really enjoyed doing this for
you and have a good weekend.
