Let's get started on this training a super simple one this week in fact just a little bit of vba code it's going to be great for those beginners and also we're going to go over how to create this cash flow statement and why it is important please don't forget of course to subscribe make sure you click on the notification icon bell and smash that like button that helps keep these trainings up front and always available to you each and every day so what is a cash flow statement well let's go over a little bit of a summary i have here a cash flow statement is really important for business it's also known as the statement of cash flows it actually shows how much cash.
Is generated and used during a given time period of time if you're running the business even if you're making a profit you've got to know what amount of cash is coming in it's one of the main financial statements analysts use to build in a three statement model right so we're using operating activities investing activities and financing activities those are all very very important the operating activities they're like the principal revenue generated activities of an organization and these are other than like not investing or financing it's basically any cash.
Flows from your operations whether you're buying and selling goods or you're buying and selling services or you're offering services that's the main activities investing activities the second one now those are any cash flows from acquisition and disposal of long-term assets if you're selling like like you're selling machinery that you're not using anymore or you're or you're investing in type of machinery that's where it would go this is not part of your normal operating activities.
And then of course you have financing activities those are any cash flows that result in change of size of the composition of a contributed equity what does that mean if you're borrowing money or you're financing other people right it's the cash that goes in and out when you're doing that for businesses so those are really important and it comes in different details so we look at our cash flow statement we've got a cash from receipts right so these are our operations so we have our cash coming into the operations and we have it based on the year so the customer payments a hundred and four thousand customer payments we have other income from our operations and then we have also in our operating operations those are the main things what inventory did we purchase you know in order for us to sell or maybe administrative expenses that you needed to.
Pay your labor in your office age or your wages maybe you paid interest on that or your income taxes paid those are all part of the operations very important and so what that will do is you'll determine the net cash flow from those operations and then we have it on a per year basis of course or we can hide the previous period and then also we have investing activities investing very important property or equipment sales if you've sold any property or equipment you'd want to put.
That into that category loan calculations right loan collection excuse me if you've collected on a loan like you've loaned out money to another business or another person and you've collected on that that's income that's cash coming in to you so we need to record that and then security sales if you're selling the securities or something and that cash came in for that so here's where the cash came in and of course cash going out right if you're also purchasing any property or purchasing any equipment that would be cash out loan payments right if you're making payments on a loan that would be cash out also securities and purchases if you're making purchases of different securities.
That would also be cash out and lastly the third category is the financing category also very important so if you issue a stock and you get money from that that would be an income or if you're borrowing money from another entity that would be cash coming in as well also if you repurchase stock if you pay for that and you're purchasing stock that'd be cash out because you're actually making that purchase if you're repaying loans you have a loan out and you have to pay it back that would also be cash out or if you're paying dividends to your stockholders that also would be cash out so that all combines for our financing activities right we get the net increase in cash 2500 for the year so basically this is simply all of our operations along with.
Our net cash flow along with our net cash notice these are negative in this case and that still we still have a net increase in cash of 2510 and that was for the year of 2021 and for year 2020 if we want to show that if we want to show a monthly detail to break those items down into every month for the given year now where does all this data come from it comes all from transaction data so if we scroll over here we can see a lot of transactions right so it's all based on transactions transaction id a date the type of it the name the account and whether it's deposit or.
Withdrawal so our idea is to take all this data in and bring it into a cash flow statement so this is very similar to what you might find in some sort of a transaction database so it's very similar to that and you'll generally have an account and you'll have a type and something like that so keep that in mind you could also have an account and then a related type depending upon how you set it up but it's generally something like this okay so what our job is to do is our job is to take this data and bring it into something called the cash flow statement now what we have here is what we have a staging area and oftentimes what i really like to do is create this staging area so.
I don't always necessarily here's my main report i don't necessarily really want to have my data in here because when we have our main report data we can have different reports so if i delete all that and just double click on here to refresh it it's all going to come back and that's really nice why do we want to do that well several reasons one we don't want formulas at risk in front of the user that's certainly reason so there's no formulas at risk here two we may want to use this area for different reports if you've taken any of my dashboard courses or seen dashboards you can have a menu of reports here so you click on cash flow profit and loss and things like that.
RELATED TOPICS:
And those particular reports can appear right here so we have our main report area we can show
Several different reports in the same area if we use this area here that way we have somewhere else where we're creating and simply bringing over that information into this area so that's another reason and another reason you may want to have is you may want to show or hide certain issues maybe you want to use vba to go through the rows and only show certain rows or only show a certain it allows us to use a filter so maybe we only want we don't want to show investing activities we could do that as well without having to hide or show rows so there's lots of options of why.You want to do that so we don't have any formulas here these are just values and as you see we can just delete that so what do we have we do have our formulas in here so this is called our staging area and if you were let's bring this let's minimize that you see here we have our data here now if you were going to create additional reports all you would need to do is create additional staging areas over here so you could create a profit and loss here you could create additional reports here maybe a balance sheet here or net worth or anything like that graphs and then just bring them over into the main area so it's a there's a great advantage to having this staging.
Area for your reports and so that's just what we did here so we've got a few named ranges not very much so let's go over some of the named ranges for our data we have this one here it's called cf year i'm calling it cash flow year cf year and that's the year we have a number of years so basically we have the cash flow years this is part of the drop down list and i'm just calling that years inside our main data here in our transaction data we have a few ones and let's just go over some of the named ranges that i've created here so if we take a look inside our name manager here we've.
Got transaction date here this is a we're using offset formula so that means that no matter how long the data is it's all going to be encapsulated inside that so it's going to go all the way down to the bottom of our data in this case row 362 those are the dates and then transactions as we add transactions that named range will grow and the reason we want to create a named range for our transaction date is because our summaries our totals will be based on months or based on years so we certainly need to track that date we also have the transaction deposits and this is also.
A are using offset a dynamic name range for all the deposits and we have another one called transaction type here those are all based on the transaction type that's very important because we're totaling it based on specific types and lastly we have transaction withdrawal so all the withdrawals there so those are really the four main name range based on our transaction database and then what we have here is we have our transaction here so we've got our header row here and all we need to do for our header rows use a formula because we know we've got the cf year based in the named range right directly based from whatever is located in e3 we can use.
That inside formulas and so to do that basically all we're going to do is just type in equals and then for the year ended december 31st every year is going to end on the same date and then put a space and then the year and so that's going to always show so no matter what year is there our statement of cash flow subtitle will take care of that year so it's going to be very simple and also what we have is we have the year the current year which is the cf year and then we have the previous year which is the cf year minus one then of course we just have simple january march april.
May we just have all the months so all this data and then i've just got a total here to keep make sure that we're your our totals are accurate right we have the totals that are current year just to make sure there's a different way we're basically summing up all of our totals and we'll go into these formulas right now so we can understand exactly how we're arranging them so that's how we get our header title so now we have cache on receipts this is just a fixed name and then basically i just put everything as a fix because it's always going to be the same so we don't need to make these variable as we do sometimes in vba although you could but so what we have is customer payments so what i want to do in this particular formula is basically.
I want to know all the customer payments all of the deposits minus any withdrawals for customer payments for the given year which is twenty twenty two zero two one so how do we do that we're gonna use the sumifs and what are we summing we're summing again all of the transaction deposits and we're gonna base it on a specific date we know that it has to be here's the there's the criteria range is that transaction date why was so important we're going to use greater than equal and i basically need to know the first of the year greater than equal the first of the year so we can use our date function here and we're going to be based on that year.
Here's that year again that year and then january is the month and then january 1st as the first of the day so that's all we have we need to make sure that the transaction date is greater than the first of january of that year we also need to make sure that it is less than or equal to the last day of the year so again we're going to put in another range and criteria range and then the criteria for that range is going to be less than or equal again we're using the date function.
One more time and we're going to use the year the same year but this time we're going to use the month of december 12 and the last day so we know it must be less than or equal that day
Also we need one more we need to make sure that the account type is the same as customer payments so we're going to do the account type is equal to whatever is in k6 and notice the absolute is in front of the k meaning we want it fixed on that specific column but not fixed to any specific row so that when we drag this to the right as in the months or we drag this down for additional our types our transaction types the function will allow us to drag it down and maintain the formula.Integrity okay so we want to do all of that i want to basically add up all the deposits now in case there's any subtractions there could be some returns or something there could be some withdrawals so we want to also subtract out any withdrawals and to do that we're going to do the same exact thing except this time all we're going to do is sum the withdrawals right everything else is the same the transaction date is greater than equal the first of the year transaction date must also be less than or equal the last day of the year and the transaction type has to be k6 that is going to get us our total so when i drag that down or copy the formulas down to this this it's.
Automatically going to add up negative values are going to be showing in the red parenthesis and why is that well that's based on our currency so when we go into the number formats i've chosen this one right here this currency right here i want it in red and i want it in parentheses right if i were to choose a different one it would show so that's based on the number format that i have selected here because that's how i want it to show and so we're going to do that for each one of these now we also want to know the net cash flow so this is simply all we need to do the net cash flow from operations is simply the sum of all that relatively easy and we've done the same thing here we're basically using the same exact formula and the same formula here now for the year previous.
All we need is just make a few slight uh additions right or differentiations on that this time it's going to be basically again we're looking at the transaction we're looking at the dates but this time the date is greater than or equal to year minus one so each instance of the year we're gonna subtract one here so here minus one and then we're also going to subtract all the withdrawals also again the date is greater than here the year minus one here and the year minus one so basically all we're doing is using the same exact formula except using the year before and that's going to get us our values now what we want to do is i want to know the month now this is the month of the.
Given year 21 not the previous year this all these totals are based on the current year 2 2 0 2 1. so what i want to do is i want to sum up in this case only those dates for this customer payment that landed in the month of january now how do i get that month of january i know that january is the first month right but what i don't want to do is i don't want to choose you know we have our dates i don't want to choose month one month two month three right i want a single formula that i can just copy all over the place so how do i do that well if i if the only differentiation here is.
Months and i know january is one february's two and so on and so forth when i use this date function what am i going to do i'm going to base it on this if i know this is one let's take a look what column is this we'll call them miam if this is 16 and this is column 17 and i need to extract one from here and i need to extract two from here the best way to do that is simply to subtract 15 and so that's just what i've done inside the formula what that will do is make that dynamic month based on the column that way this is automatically two this is automatically three.
When i saw when i subtract 15 from the current column and that's just we did here so we're making pretty much the same one but this time our date so we still have we're still totaling our deposits our date has to be greater than it's going to be based on the current year and then the month what is that month number the month is basically whatever the current column is minus 15. so in january's case it's going to be 16 minus 15. so that's going to be the month month so all we have to do there then what i want to do is i need to know the last day of the month so we're going to use less than or equal the last day of the month and to get the last day of the month.
We can use the eo month function eo month function what that's going to do is get us the last day of the month and we want the current month right that month so again we're going to use the exactly the same date the column minus 16 minus 15 give us one but this time we're going to say zero months ahead or zero months back so we want that current month that is going to get us this formula here is going to give us the last day of the month so i need to make sure that the transaction is less than or equal to last day of the month then the transaction type's also going to be k6 we're going to do exact same thing we're subtracting out any withdrawals so doing that right here and all we need to do again same thing here first day of the month here using january here in this first case.
Minus again using eo month also again there so as we move forward so every time we subtract and june is going to be right 6 7 for july so that way our formula is very very easy all i need to do is just copy this formula here and then just forward it or drag it over either way it's going to work just fine just like that and then all you do is just bring it down or i can copy it here and bring it down it's the same formula just to copy down so if i want to do it very very simple i can do it with through all those because i'm using those months so that's how we have to do it very very simple.
To do that all right so let's go into detail so we understand how we're getting the sum and then all i've done is just simply summed up all these and just drag that comma drag it over here simply the sum of whatever's here so in other words slightly different for the months i'm going to sum it in the single column however for the entire year we're going to put the sum in the adjacent column just to give it a different look all right this is simply the total of all the months i want to make sure that the total of all the months here just to double check is equal to the sum here i want to make sure that those values are simp are exactly the same and they are in this case just to make sure to double check our work so they are that's always a good way to check so we've done exactly the same form authorized so relatively simple it's a kind of a big formula.
But it's a relatively simple we're simply summing all of the deposits minus the withdrawals and you know if it's more withdraws than deposit it's going to be a negative number like it is in these cases okay great so once i have all the data in here and i've got everything just the way i want it what i want to do is i want to then bring all of that data all the values and the and the formats i want to bring them over the formats are already there into our summary page so we can do that with a single line of code and when do we want that to happen well we want that to happen.
Anytime the user makes a change to this right so if they select a different year i want to make that change it's very fast so you notice it's very fast so how do we do that well it's just a single line of code and this is going to be based on the change event when the user makes it change to e3 we want something to happen and that's going to come inside vba it's going to do that for us so to get into vba you can use the shortcut keys alt f11 or you can use your developers tab here and click on the visual basic and if you don't have this developers tab you can just get it i'm using excel 2010 an older version but it works just fine and so if you want to get that developers tab visible.
All we need to do is just go into the customize ribbon in the options and make sure that you have the developers option selected here so once we do get into vba it's going to be based on the change event so we're focused on this cash flow cheat right here this is the sheet i'm focusing on right cash flow is the one we're making and we're going to focus on the worksheet here and we're going to focus on the change event when a user makes a change now if the user makes a change to more than one cell i want to exit the sub and we can do that with target count large if it's greater than what x sub exit right on change exit on change of more than one cell at a time.
So we don't if the user makes a change to e3 only e3 if not intersect right if the user may change target means the cell that they've changed and e3 is the same means they've made a change to e3 and is nothing meaning nothing and not cancel each other out that means they have made a change to e3 and another condition we also want to make sure that e3 is not empty can't be empty it has to contain a year if both of those conditions are true then what do we want to do we want to run a magnet called cash flow refresh if you want to get to that macro all you need to do is.
Right click and of course click the definition another way simply to go in our only module here and just find it up here and of course if we scroll up here you'll see it is a single line of code that's all we need right all i need to do is basically take whatever is in our staging area here and then bring it over into our main area so since our fields are all we don't necessarily need to clear it out because we only have one part so i'm basically i'm going to take all the way from j4 through aaa and down all the way down to a37 and bring it directly inside here actually i'm gonna probably start with here e i want to bring over e six all the way down through.
V so we're actually going to bring we're gonna start it over actually our subheader row here actually in j3 so we want to bring that over to you because that includes the date so that's all we need to do with one line of code so cash flow the sheet here range e6 through v40 is simply equal to the transaction database that's the sheet j3 through aa37 bring over the values and the formats the formats are already set right the formats all i did once just to copy those formats all you need to do is just copy them one time because we have a single report and so that'll be just fine so all i need to do is just paste them in here and then just paste everything.
In here and then we don't worry about that so circular reference warning because those are just formulas and then that's all we have to do just re-run the code rerun the code here and there's no values for 2022 2021 bringing those values and notice it is only those values that are brought over not the formulas and that's what we want only the values so a single line of code is going to do that for us great that's really cool we've got some other macros that we want to show you right this is for the monthly detail but what if i run the code and i want to show the previous period i only want to show the single year if i change the year i want to make sure that we're only bringing.
Over notice it only brings over what we need actually everything's being brought over but we're actually hiding the columns right we're only showing the columns that are affected and so how do we do that well what we do is we have a macro that's tied to this this is called previous period and this is going to be basically a billion and it's going to be based on b3 here b3 so if i right click here format the control i look at the cell link and i see that that's b3 that is the cell linked to that so i know if b3 is true we should show the previous period that previous period is.
Based on columns i and j however if we unselect it we should hide columns i and j so we just need to tie the macro to this if we assign the macro we see that there has been a macro to sign it called previous period if we click on the edit it's going to take us into that same module again here this is the first one we have previous period toggle so this is the macro scope this is the macro that we're going to be focusing on so with the cash flow right what we want to do is i want to know is b3 true or not if b3 is true then we are going to show those two columns in that case.
Let's call this show previous period show previous period and otherwise we are going to hide previous period and it's very simple all we need to do hide previous period great so if we're showing the previous period all again all i need to do is unhide those columns in other words hidden equals false based on i and j entire column hidden equals false so basically we're unhiding it with that however if b3 is not true meaning b3 is false we're going to do this we are going to hide the previous period in that case i and j entire column hidden equals true so this is the macro that's.
Going to run every single time we click on on this right here and that's simple macro is just going to let us hide or unhide that very very simple now but i also want to show the monthly detail if i've decided to show uh this specific year you're in the 2021 but i want to show all the detail for that month i only want to show that so what do i want to do then in that case i want to hide both the years 20 and 21 i want to hide those four columns and i want to show the columns all.
The way from k through v k through v so those are the columns that we're going to show so how do we do that well that's very very simple so we can do that with a macro called cash flow monthly detail it is that same macro that is also tied to this option here and it is this option is based on b4 right so if we right click here and go into the format control we can see that the cell link is b4 right so as we change it it goes from true to false and true to false okay so again we can run that macro and then determine based on what is in b4 and that's just what we're going to do inside the macro so again focused on the cash flow this time we're going to say if range b4 equals.
True then we want to show the monthly detail in that case we're going to hide the annual columns those four columns are located in g through j the entire column hidden equals true we're going to hide those four columns we also need to show the other columns show those 12 monthly columns and that's in k through v so k through v entire column hidden equals false we want to show those monthly calls great but what i also want to do is i want to hide that previous period right i don't.
Want the user to be able to click on the previous period because there's no previous period right i want to make sure to hide that option so how do we do that well the first thing we do is we give this a name we're going to call it previous period and then the second thing we do inside vba is we want to hide that shape so we can do that with just this single line of code right here shapes previous period dot visible equals mso false that is going to hide the previous period option right we don't want the user to select that they only should select that when our annual option is shown however here's basically when it's we're showing the monthly detail when it's true but what if it's false if it's false we're going to hide the monthly detail.
And that's just basically the opposite of what we did before this case when the entire column g3j those annual columns hidden equals false meaning we do want to show those annual columns and the monthly columns are actually going to be hiding those so k through v those 12 columns hidden equals true we're going to hide those monthly columns also what i want to do is i want to determine based on whatever is located in b3 have we shown the previous period or not for example what i'm saying is once i unclick this i need to know are we going to show the previous period or not now let's unselect it so in this case we're not showing the previous period.
I've selected monthly detail here now what i want to do is i want to select unselected the question is are we going to show that second year or not the previous year or not and we're going to know it based on whatever is in b3 so if b3 is false we are not going to show that previous year however if b3 is true when we unselect this we are going to show that previous year so we're going to base that on b3 so that's just what we do inside here we're going to base it on that idea if b3 equals false than range the entire column hidden equals true we're going to hide the previous.
Period if it's unselected so only if b3 is false then we're going to hide that previous period otherwise it's already displayed right it's already displayed here we've displayed it here that those the second column but now i need to know whether we're going to hide it we're going to hide it only if b3 is false we're hiding that previous period if it's unselected and then also the last thing that i want to do is shapes previous period visible equals true meaning i want to show that option again right we want to make sure that that option becomes visible again after they unselect it i want to make sure that this option group option is visible to do.
That we just make sure that we do that inside the vba code making it msoc true making that visible again that's it that's all we have to do to switch between the monthly detail very powerful i also want to print this right now i want to print it out and i want to be able to print it out print that to whatever the default printer is when i click here i want to print that my default printer is currently snagit editor so it's going to print to my snagit application now if we take a look inside here you see my snagit application it's right here and it's going to show us that particular report here but also what i want to do notice that it is a portrait right it's a portrait.
Right meaning vertical however what if i decide let's just show that i'll bring this down a little bit here so what if i decide that i want to make it landscape right the monthly version there we go now we can see it the monthly version is landscape right so if i'm on the monthly detail notice this is more of a landscape a horizontal form so if i print that i need to make sure that this one gets printed in landscape so if we take a look inside the printer notice that this one's landscape it.