Accounting in Excel - Part I : How to design and structure your business chart of accounts

hi everyone and welcome to accounting and Excel my name's Mel rose today I am making a tutorial series in how to build your own bookkeeping system in Microsoft Excel environment I will go through step by step from building your own chart of accounts to recording your entries and eventually preparing your financial statements so let's get started but before getting any further let's define what is a chart of accounts so chart of accounts is the backbone of our financial reporting we use this as a financial organizational tool to classify financial transaction I have divided my sheet here into two separate areas on the left side of the sheet you will find a list of my chart of accounts it is organized from balance sheet account to the income statement account on the right side is a table where we can add our accounts our goal here is as soon as we added an account on the table our chart gets automatically updated having the summary right next to our table saves us time into analyzing what are the accounts missing and how are we going to code an added account by the way there is no strict rule as to adding code to your account but these codes will help you understand the relationship and here keys of your accounts remember the D foundation of your financial reporting relies on how organized and clear your chart of accounts are structured so let's start designing our chart of accounts from scratch so let's start with our title area start by the company name followed by the title of the report chart of accounts and for our date of reporting let's use the function today these function returns the current system date and it also refreshed whenever the worksheet recalculates so on these area is where our list of accounts it's let's not prepare for our table let's start with our table header FS for our financial statements either it's a balance sheet account or an income statement account followed by FS Group EFS subgroup account code then we have the account name followed by concatenation of our account code and account name let me just do some formatting here so that you could see the whole sheet there you go and let me freeze the view so that we could move further and let's follow that with sub-account code sub-account name and a header to concatenate our sub-account code and sub-account name so let's convert our header into a table it's called to insert and my tables header minimize that's it you could see the whole table let me just get rid of the filter button here and let me wrap the header first it's minimize the size of our column now we have our data entry table ready let's go ahead and do by voting to structure our chart of accounts but before we start by voting let me just go ahead and rename our table without with table cola or table chart of accounts hit enter and let's go ahead and do the pie voting though going to put our pie vote summary in our existing worksheet and let's go ahead and drag AFS APIs group of s subgroup we are not adding the column account code and account name but just the code where the account name the column code with sub-account name I will explain that further later on and our date it's not showing up so let me just edit that I just merged the cell just so you know okay let me just remove the buttons and remove the field headers we don't want the grand total so let me just take that one out turn it off and now we have our table structure let's go ahead and fill the cells so for our FS column let's use a data validation for that and let's go ahead and do list you only have two entries for that balance sheet and income statement and we don't want the user to type it every time or you don't want yourself to type this every time so we want the user to be able to select which accounted a one and which statement should it mean for the FS group let's fill that one later on let me just change that let's say we wanted to have for a subgroup a cash so that is a current asset account for this one I will use 101 for cash as cash is the first item on our account how you structure code your accounts depends on how complex your financial transaction is keep in mind that as your business grows there will be more transaction so make sure to design it in a way that it will ease or facilitate the adding of new accounts so in this column you wanted to concatenate our account code column and our account name column this is because we want this column to show up on our chart of account lives so let's go ahead and try to refresh our sheet here and see how it would look like on our report so it looks like the way it's supposed to be cash is under the current asset square and fill FS Parliament in this column you wanted to create a map or a guide so that it will tell us what code should be used if we are adding a new account so what we wanted is from 100 to 200 is an asset account 100 being the current and 200 being the non current let me just go ahead and quickly fix the formatting here now we are ready to fill our self account column so under cash there are different kinds of sub accounts we have cash on hand cash and savings account cash and current account and so on so when we design a record we wanted at the account cash becomes the parent account and the different sub accounts becomes the child of the cash account since we code our cash at 101 let it be our sub account cash on hand code at 1001 it really doesn't have to be four numbers you can use two three four five that depends on how complex your financial transactions are and on our last column you wanted to concatenate again be sub-account code column and be sub-account name column let's quickly hide the columns for formulas are since we don't want this to be edited let's go ahead and refresh our sheet here and check if the structure are in order let me add another sub account for cash let's put it cash in that savings account and let me just quickly refresh our sheet and there you go cash in dark savings account is added on our chart of account list now let's add a button that will help us refresh the whole sheet with one click in order to activate this button we will use macro so macro is a recording of a repetitive tasks like the one with it with refreshing our sheet so basically in this button it is where or it has free coordinates but before we can record our task make sure that you have your Developer tab activated on your ribbon but if you don't let's go ahead and add that so on your sheet click file tab and click options select customize ribbon and find the main tab select a Developer tab and click Add Developer tab should appear on the next window select Chuck and click OK to Developer tab should appear on your ribbon now that the Developer tab is available on our ribbon let's now add our macro on a very bottom left corner of your sheet you will find a shortcut for recording your macro click the square button and a window should appear name your macro and a shortcut key and add little description and click OK now we are ready to record our macro click again the square button below go to the data tab and click refresh go back to the square button and click stop now let's assign our macro to the button right click the button and select assign macro select the refresh sheet macro that we did earlier and click OK now it should be working so let's try balance sheet and let's go for 100-200 acid we will do non-current asset this time non-current acid 201 and let's do property plant and equipment and since it is our first record so let's go for 201 1 0 0 1 let's put building this time our table did not automatically update let me just drag this small button here and click refresh now our chart of accounts is structured so our chart of account is ready for our next video we will connect our chart of accounts to our journal entry table stay tuned and hit the like button so you get notification as soon as the video is posted

Channel: Accounting in Excel with Melrose

Views: 11,059

Rating: 4.9300699 out of 5

Keywords: excel, accounting, learn

Id: HAtwU4RjCoc

Length: 11min 6sec (666 seconds)

Published: Sun Oct 27 2019