Speaker 1: Hey guys, in today's video I'm going to show you how to create a month-end closed checklist, which is going to act as a guide for your entire accounting team to be able to close the books in any given month and produce financial statements. So we'll go ahead and create this in Google Sheets, and I'm going to show you how to come up with all of the accounting tasks that are needed to be able to close the books in any given month, and then we'll come up with the deadline or the number of working days needed to close each task, and then the deadline itself. So we'll create a calendar feature here to select a date for each of these events, and then we'll add in an owner for each task and a reviewer, and then we'll create a very neat color-coded process by which when you change the status on each of these tasks, then you'll have a different color code so you can visually see immediately what is the status of the month-end closed and be able to address any bottlenecks to close the books. So all of that is coming right up. And if this is your first time watching one of my videos, welcome to the channel. My name is Bill Hanna, and I'm the financial controller. I'm a licensed CPA in the great state of New York, and my role as a financial controller means that I have to close the books each and every month and produce financial statements. And in this video, I'm going to give you the best practices on coming up with a month-end closed checklist. So what I'm going to do is I'm going to give you a finished product as a link in the description down below, so you can go ahead and download that and use it and modify it for your own purposes, but also I'm going to give you the full process on how I come up with what's needed from my own perspective by going through the financial statements. So I begin from the financial statements, the balance sheet and income statement, and I work my way backward into a month-end closed checklist. So without further ado, let's dive into today's video. All right, so I'm going to show you a quick overview of the month-end closed checklist, and then I'm going to show you how I created it, basically by going through the balance sheet. So I have my balance sheet right here. I have my income statement, and then basically this is what I use to work backward and create in the checklist. But basically, I have here the month. So this is for the month of November, 2020, and I have all my categories here, so I can immediately know whether I'm talking about cash, liabilities, operating expenses, and basically I'm going to come up with the tasks here, and I'm going to show you in a minute here how I came up with all of these. And then the working days, so I have here, bank reconciliation usually takes one day, so this is working day one, and the deadline for it is going to be then December 1st. And the owner is Tim, the reviewer is Lisa, and then I have the status here. Each of Tim and Lisa will need to come in here and then choose a status. So here, if this is blank, it's going to be red, and I'm going to show you how to create the color coding. It's basically conditional formatting in Google Sheets. Basically when Tim is working on it, he can change it to in progress. When he's done, he can say done, and then Lisa is going to come in here as well and put done, which is already here. So this is the overall structure of the month in close checklist, and now I'm going to show you how I actually created it. All right, so let's go through step by step how to create this. So basically, I created here a tab called sandbox, and I'm going to recreate the checklist so I can show you how I did it. So let me copy the header so I can use that in my sandbox, and then we will begin drafting the task. So basically, once I have the header, I need to put in my task. And basically, the way that I come up with the task is go back to the financial statements, and this is the easiest way to come up with the tasks, is go back to the financial statements and work backwards. What do you need, for example, to be able to close the books on cash? So basically, I'm going to copy the tasks here, and then I can copy and paste to my checklist. So for cash, I need to do a bank reconciliation. And then for accounts receivable, to be able to close that, I would need to do my billing. So billing is what creates accounts receivable, and once all the billing is done, I need to do an AR aging and reconciliation to the balance sheet. So basically, download an AR aging and reconcile that to the balance sheet. For inventory, what I need to do to be able to be comfortable with the inventory balance is do an inventory recon at month end, which basically means that I'm going to check all the inventory and all the inventory locations and compare the number of inventory to the books to be able to make a reconciliation. For property, plant, and equipment, most likely that's going to mean that I have to do depreciation. So I have to run depreciation. For liabilities, to be able to have liabilities correct on the book, I need to book all vendor invoices. For accrued expenses, and actually for accounts payable after I record vendor invoices, I need to reconcile AP aging recon to balance sheet. For accrued expenses, basically accrue for expenses, basically going through my invoices and making sure that I received all the invoices and recorded them, and if something is missing from invoices from vendors, I will accrue for it. Deferred revenue, I will reconcile deferred revenue, which basically means that I'm going through my deferred revenue schedule and checking if there's any of the services or products that have been booked as deferred revenue in the past, maybe have been delivered to the customer so I can book as revenue, so I can move out of deferred revenue into revenue. Long-term debts, most likely this means I'm going to create, I'm going to reconcile the loan statement. So I'm going to generate a loan statement from the bank and reconcile that to the loan balance on the balance sheet. And then if shareholder equity, sometimes you'll have maybe equity transactions like maybe stock option expense and things like that. So I'll record that here. So let's say stock option activity. And this is basically the task for the balance sheet. So I'm going to go ahead and transfer this over to my sandbox here. This is the task that I need to do to be able to close the books, and I'm going to go ahead

Speaker 2: and delete these empty rows.

Speaker 1: So now that I got my task from the balance sheet, I'm going to go ahead to the income statement and look at my tasks here. What kind of tasks are needed to be able to generate the income statement? So basically for tasks for sales, we said before billing. So we did billing in the balance sheet side, so we don't need to boot that again. We're fine here. For cost of revenue, this means COGS. Obviously this is going to be part of billing as well. Whenever you record billing, you're recording COGS most of the time. But also there is another step, which is a check for expired goods. So basically if you have goods that are subject to expiration or obsolescence, you need to be checking for those on recording any obsolescence or expiration and cost of revenue. For operating expenses, we record payroll. For rent, maybe rent or deferred rent, depending on the situation. If you have a deferred rent situation, you need to be able to amortize the deferred rent. Legal fees. Well, we talked about accrual before and recording vendor invoices, so you might not need to do anything here. And then for R&D, check for any vendor invoices. But we already talked about vendor invoices before. Sales and marketing is also mostly vendor invoices. You may have some travel activities, so maybe ensure all travel expenses are recorded. So this is a task here for sales and marketing, since sales and marketing tend to do a lot of traveling. And then after this, you'll have a couple of non-operating items here, such as interest expense. So maybe record interest expense from loan statement. And then depreciation expense. We talked about that when we did the balance sheet for PP&E taxes. It means that you most likely have to accrue taxes. So that's another step. And then basically, you can then transfer these tasks over to the checklist that we are creating from scratch.

Speaker 3: And I'm going to go ahead and delete the empty rows here again. All right.

Speaker 1: Now that I have all of my tasks, I can basically go ahead and enter the category. So bank reconciliation is cash, billing is going to be AR, revenue, and so on. And I'm going to go ahead and fill in all of the rest. All right. So now that I have all the tasks here in the categories, I can go ahead and fill in the number of working days that are needed to be able to complete. So for bank reconciliation, usually the bank statement is available on day one after the month ends. So I can go ahead and do day one. For billing, usually that takes about five days in my company, so I'm going to put five working days. AR aging then is also day five. And then inventory reconciliation, day six. I'm going to basically go ahead and fill in all the rest of the tasks here. After I've entered all the days here, before I forget, I need to generate financial statements and review. This is usually the last step in closing the books, and the category is general. And then basically now I have all of my tasks and the dates, and I can go ahead and put in the dates. So for the dates for the deadline, the easiest way in Google Sheets is to create an actual date picker by highlighting the whole section here, the whole number of cells, right-clicking, and you go to data validation and criteria, change that to date, and leave it as a valid date, and save. And what that's going to do is it's going to allow you, when you double-click it, is to have a calendar, and you can easily then select a date. So then for day five, I'm going to go ahead and select one, two, three, four, that's going to fall here. I'm going to do that for all of the rest here. After putting in all the dates for my deadline, I can actually go ahead and sort this here, sort these by date, so I can have an actual order of the things that I have to do. So I can highlight these and just go to data, sort range, and I'm going to sort by column E, so choose column E, and sort. So basically I have then beginning from day one all the way down to the end, to day seven. The next step is going to be to assign the owner and reviewer for each of the tasks. So basically for bank reconciliation, the person who's going to perform it is Tim, who is the junior accountant, and the reviewer is going to be Lisa, who is the senior accountant. And then here I can create the sign-off for the owner and reviewer. And basically the sign-off, if you remember from the finished product to the checklist, basically is going to be a list, a drop-down menu, either not applicable, in progress, or done. And basically we can go ahead and create that. And that's going to be by doing a data validation for all these cells, by choosing from a drop-down of options. So to create a drop-down of options first, I have a tab here for input. And in here I have the status, and there are three kinds of status, there's done, not applicable, and in progress. So this way the person can only choose from these options. So the way to create the data validation will be to select the range of cells, right-click it, data validation, list from a range, and basically point it to where the range is. So I'm going to go here, and I'm going to point it to this, here, and then click OK, and save. And now basically all the person has to do is choose from a drop-down menu, either in progress, not applicable, maybe in that particular month, or done. So this is basically how to do it. And then to color code it, so the easiest thing to do is to color code this section here so that when it's blank, it's red, and that's going to help you visually know what's pending. So when you're looking at this, you know immediately here there's a red section, and that means that this needs to be done, or it needs to be reviewed. So to create the color coding, this is going to be also conditional formatting. So I'm going to highlight my section here, and then right-click, and do conditional formatting. Basically I'm going to say if the cell is empty, then I want this to be pink, and click done, right? Then I want to create another rule here that says if it exactly matches the word done, then I want it to be green, so I'm going to leave this as green, click done. So I have two color codes now, cell is empty, is pink, and then green for when the word done is there. And then I can create another conditional formatting and say if it's exactly the words in progress, then I want this to be yellow. So now I have empty, I have done, I have in progress. There's another option which is not applicable, so that should be also green if it's not applicable. So if exactly not applicable, then it's going to be green, and done. And basically that's it. This is what I need for the conditional formatting. At the beginning of each month, when we're closing the books, this is going to be all pink because we're still working on it. And as Tim reviews the bank reconciliation, when he picks it up to reconcile it, he can say in progress, and when he's done with it, he can say done. This way when Lisa comes in and looks and sees that this is green and is done, she can begin her review or just put done when she's done reviewing. And then after that, I can go ahead and assign owner and reviewer for the rest of the tasks here. So this is pretty much the whole thing. It's pretty simple. And now when Tim goes in and finishes one of the steps, he will go in and select done. And then Lisa will know that Tim is done, and she will go ahead and also do done here. So this way, when John, who is a corporate controller, would log in, he will know what's done and what's pending and what needs to happen for the books to be closed. So it's pretty simple. Like I said, I'm going to leave a link in the description below. Go ahead and download it and change it for your own needs. And if you like this video and learn something new from it, go ahead and smash that like button and I'll see you in the next video.