How To Create Month End Close Checklist. Start With The Financial Statements Line Items!

hey guys in today's video i'm going to show you how to create a month and close 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 months 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 the date for each of these um events and then we'll add in an owner for each task and our viewer 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 muslin close and be able to address any bottlenecks to close the books so all of that is coming right up [Music] and if this is your first time watching one of my videos welcome to the channel my name is bill hannah 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 and coming up with a month in 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 thought process on how i come up with what's needed for 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 and close checklist so without further ado let's dive into uh today's video all right so i'm going to show you a quick overview of the month in 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 backwards and creating 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 uh 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 the status so here this is blank is 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 the overall structure of the month and 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 um 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 tasks 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 cache so basically i'm going to couple the tasks here and then i can copy and paste to my checklist so for cash i need to do a bank reconciliation right and then for accounts receivable to be able to close that i will need to do my billing right so billing is what creates accounts receivable and once all the billing is done i need to do an ar paging and reconciliation to the balance sheet so basically download any or aging and reconciliate that reconcile that to the balance sheets for inventory what i need to do to be able to comfortable with the uh inventory balance is do an inventory recon up monthly end which basically means that i'm going to check all the inventory in 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 right for accrued expenses um and actually for accounts accounts payable after i record vendor invoices i need to reconcile an uh ap aging recon uh to balance sheets uh for accrued expenses basically uh 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 uh deferred revenue i will reconcile reconcile deferred revenue uh which basically means that i'm going through my deferred revenue schedule and checking if there's any of the services or products that are 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 uh long-term debt uh most likely this means i'm gonna create i'm gonna reconcile the loan statement so i'm gonna generate a loan statement from the bank and reconcile that to the loan balance on the balance sheet and then if you know 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 and delete these empty rows so now that i got my tasks 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 uh this means cogs um you know 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 and recording any obsolescence or expiration and cost of revenue for operating expenses we record payroll for rent you know 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 uh legal fees um you know well we talked about accrual before and recording vendor invoices so you might not need to do anything here and then for r d um check for any vendor invoices but we already talked about vendor invoices before uh sales of 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 of marketing since sales marketing team tend to do a lot of traveling and then after this you'll have a couple of non-operating items 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 ppne uh taxes uh 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 and i'm gonna go ahead and delete the empty rows here again all right 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 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 you can go ahead and do day one for billing uh usually that takes about five days in my company so i'm gonna put five working days um ar aging um then is also day five um and then inventory reconciliation day six i'm gonna 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 um and then basically now i have all of my tasks and the dates um 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 the date so then for day five i'm gonna go ahead and select one two three four that's gonna fall here i'm gonna do that for all of the rest here after putting in all the dates from my deadline i can actually go ahead and sort this here sort these by uh dates 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 a 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 to sign off if you remember from the finished product to the checklist uh basically it's 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 that is 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 i'm going to point it to this here and then click ok and save and now basically all all the person has to do is choose from a drop down menu either in progress uh not applicable maybe in that particular months or done uh so this is basically how to do it and then to color code it so the easiest thing to do is to color code um this section here so that when it's blank it's red and that's gonna help you uh you know visually know what's bending so when you're looking at this you know immediately here there's a red section and that means that this 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 two color codes now uh still 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 um 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 down 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 finish 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 uh do uh done here so this way when john who's a corporate controller would log in he will know you know what's done and what's pending and what needs to happen for the cl 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 [Music] you

Channel: The Financial Controller

Views: 30,636

Rating: 4.9692569 out of 5

Keywords: how to close the books, how close the financial months, how to create financial statements

Id: m_SfIf8PKpg

Length: 15min 54sec (954 seconds)

Published: Sat Dec 26 2020