hi guys and welcome to the cash flow template so this is going to be a
difficult one tracking your cash flow is super important whenever you're running your business
and this template is supposed to help you doing this now that being said cash flow is of course
a very complex topic and i'm i've tried my best to make this as easy as possible but you need to
wrap your head around it but this is what this video is for so i'm going to walk you through
every single step that you need to take in order to make sure that in the future you can easily
plan your cash flows using this template coming from accounts receivable and accounts payable
so let's first take a look at the number of sheets that we have in total we have five support sheets
always marked in the light green and we got two main analysis sheets accounts receivable and
accounts payable so let's first spend a minute on the mapping the mapping is actually quite big if
you compare it to other templates in case you've downloaded some of them now again we've got the
familiar year and month we're also seeing a conversion factor in case you're working with
very large numbers and you want to show them in thousands then we have products and raw materials
we got customers the payment terms so when do they need to pay us we invoice them and then 30 45 or
60 days later they of course need to pay us and at the same time we also have vendors meaning that
we procure from them and we have to pay them again with a couple of payment terms here now
f until i are quite generic as are d and e to be honest but what you want to make sure is
whenever you bring in your customers first give them the right name and also assign the right
payment terms because this is going to be super important as you will see in just a minute
the same of course holds true for vendor and payment terms now this is exactly the same just
applying to accounts payable so now let's take a look at the next support sheet which is sales
data now sales data is essentially how much we've sold to which customer in which period
in which year there's nothing more to say and of course you want to make sure that the green cells
reflect your actual sales and they're also in line with the data you're seeing right here please do
me a favor do not change the order why because we're using named ranges for the year the period
the product customer and sales and if you fiddle around with that order you'll see that later
on the formulas won't work as nicely now again that being said this is the template that has a
customization level of three meaning that it's quite complex and you need to put in some time
to really make this work for you so once we've done this and we got all the data set
we can actually analyze our sales so let's go over here to the sales sheet
what do you see here essentially you see the product that we've sold
you can of course change i mean these are drop down lists all referring to the map mapping table
and you can select any kind of product that you want to analyze you can also change the customer
and depending on the customer the payment terms will update automatically
you see that for accessories we didn't have any sales in jan but we had some in feb some in march
and so on and so forth and depending on your selections here this will of course change every
single time what does the top row indicate well this one is just giving you the total sales it's
a subtotal let's calculate it what do you do if you need additional space if you have additional
products if you have additional customers and so on and so forth as always i encourage you use
the rows in between tables so you can just easily go ahead input like three additional rows
you can drag down all of this because these are just formulas
and so let's do that quickly and you see your total range over here is adjusting you've
taken into consideration these additional three rows as well and you essentially have three more
categories to choose from okay so now once we've sorted out the mapping the sales data
and the sales as such we can actually look at the cash flow now accounts receivable what are they
indicating us they're saying that depending on the payment terms that we have meaning 30 days 45 days
60 days and so on and so forth now again make sure that this ties in with this because you know you
want to track those this data essentially right so depending on that let's say we have a sale
in jan for 1.3 million i'm just referring to this data tab right here now this means that within 30
days we will still be collecting that now the cash flow analysis as it is constructed here
will show you that it's going to be collected right here so essentially you inflate your
accounts receivable balance in jan but you're also collecting that money so what's going to be your
net cash flow impact for the month it's going to be zero because these are offsetting each other
what about the 60 days so here we have a sale for 24.1 million but we have payment terms of
60 days so the customer essentially has 60 days before he has to pay us now this will be collected
in february you see so when you make changes first of all this is all coming automatically
right you see the formulas are constructed in a way so that they can only pick up whatever
days you've selected here of course making sure that this ties in with the general
mapping data you have defined over here but what you need to make sure then is that these formulas
are still making sense let's say if you don't have any customer that has 30 days of payment terms
you shouldn't be looking at like this right you should make adjustments to these formulas
so change the white ones but do not change the green ones because these are just going to be
the total sum of those which will then translate into your chart down here called net account
movement so what does it mean if our net movement for the month of jan 2019 is 119.4 positive that
actually means that we've on the bottom line we've increased our receivables balance by 119.4 million
now if you're familiar with the cash flow concept you will know that increases in
the accounts receivable balance are actually a bad thing because they are a negative cash flow
you've sold it yes but you haven't collected the money that's the key piece of cash flow
only when you collect the money you actually have a cash inflow so for the month of jan well we've
sold quite a bit we're collecting only 7.1 so that the net movement for the month is 119 positive
so so much for the accounts receivable let's move on to the accounts payable
first of all we have a similar setup as with the sales data but in this case it's invoice data
so we are procuring raw materials from different vendors who are giving us a purchase price and
essentially we update this data as we see fit now when we need to see what we're actually procuring
we can have the exact same view as we had with the sales but now for invoices and again this
will just pick up whatever month and whatever raw material or vendor selection you have selected
now note that the payment terms again will just update according to the vendor selection right
here and then in the end what you want to analyze are your accounts payable and the net movements
of course so we have the exact same view as with the accounts receivable but this time guys the
concept is different so if you are procuring a lot your vendors are going to give you an invoice that
invoice will say look your net your payment terms are 30 days 45 days 60 days and so on and so forth
so are you actually paying for that 17.9 million that you've accrued for
in january yes you are because payment terms are 30 days and now our model
works that way that actually takes into consideration those 30 days and thinks hey
we're paying it in the same month but with the 60 days one you're not going to pay in jan directly
because you're going to pay in feb because it takes two months to complete it essentially right
now of course a flaw of this model is i'm gonna and i'm completely with you is now you could say
yeah but it depends on when we actually sold it right if we sold it or if we procured it on the
first of january then yes we would essentially have to pay for it on the 31st and at the same
time if we only procured it on the 29th then this wouldn't work as nicely i'm with you guys
right now of course this is not to be taken as the source of truth for cash flow of course you have
your bookings and you should always refer to the actual invoices that are paid but this is a great
tool to help you plan your cash flow as we project it into the 24 month future so that being said
with cash flow and accounts payable the concept is different because as i said before
yes we have sourced materials that are worth in total 32.8 million but we're only paying 20.8
so the net movement is actually 12 million we have a cash inflow so to speak because we got a service
but we haven't paid for it yet of 32.8 million but we've only paid for 20.8 so that means our
net inflow or net movement on the accounts payable is 12 million for the month and that is it for the
cash flow template again this is a very complex one you need to invest some time to make sure
that this really suits your needs but i guarantee you it's going to be a super helpful tool when it
comes to analyzing and most importantly planning your cash flows because remember cash is king