CPA Builds Full Accounting System in Google Sheets

hey everyone I want to quickly run you through this bookkeeping template that I built out in Google Sheets before I run through each tab I just want to kind of highlight some of the key things that you'll be getting out of this template so starting off here with the analytics tab you're going to see your month-over-month performance in terms of Revenue and expenses and of course profit as well if we keep scrolling down you're going to see a little bit more data on your sales performance who your top customers are based on Revenue and then the percentage breakdown between those customers you're going to see a little dashboard on your accounts receivable and how often you're collecting any aging AR that's overdue in the various buckets you're going to see your top performing products in terms of highest profit margin uh lowest profit margin and then also just the top selling products in general in terms of Revenue and then down below you're going to see um a little bit more detail on your expenditures month over month and spend by category this is all automated so as you go through each Tab and enter enter your expenses sales you know bills expenses payable so on and so forth this is all going to populate automatically some other key highlights from this bookkeeping template you're going to be able to manage your inventory um so if we click here you're going to see you just enter your products the opening quantity opening cost that calculates the opening value and then uh quantity in and quantity out is automatically calculated so quantity in would be any purchases you make through the purchases tab inventory purchases Tab and it'll automatically bring in that quantity and then quantity out is any items that you sell it'll automatically deduct that quantity based on cost of goods sold and it'll give you the total quantity left it also autoc calculates the average cost per unit average price per unit and that's how profit margin is is determined and you can see that up top here as well a couple of other things I'll I'll quickly highlight before I go into each tab there's a bank reconciliation available here um and then everything's automated once you input your transaction so for example as you input data from a sales expenses journal entry standpoint you're going to see this trial balance autop populate so it's always good to uh check back on this tab to make sure total debits equal total credits because this trial balance also drives the balance sheet that auto populates here so I created that for you and then also the profit and loss statement as well that I've created and autop populates so you don't have to worry about that so I just want to kind of highlight some of the key features now if we go through how to actually use this if we start off here in the settings tab first thing we're going to do is Select our currency so you can see there's I think there's like over 30 currencies that I've added here but if you switch your currency for example to let's say Euros then this currency will will will apply across all tabs so it's all automated so for example if I click into sales and AR you'll see you know you can even just see up top like the euro currency is applied now so changing the currency here applies it everywhere the next thing is the fiscal year so all you need to do here is really enter the start of your fiscal year and it'll automatically calculate the End by adding an additional 12 months so to highlight this in action right now I'm in a Jan to 31st genan 1st December 31st year end if I click into the analytics tab you'll see that my overall performance month over month is tracked during that uh fiscal period if I go and change this let's say I have instead a March 31st year end so April 1st fiscal year goes from April 1st to March 31st if I go back to the analytics tab you'll see now I'm from April 1st to March 2024 of course I don't have data for Jan 2024 Feb and March but as I populate that you'll see this update automatically and that applies to all other tabs as well so if I go to sales and AR you'll see here I've got data starting from April I just don't have data from Jan Fe March in this dashboard either so all of that is automated I'm going to go ahead and switch that back to Jan first other things here tax rates so any tax rates that apply on the collection of taxes or even when you purchase anything and have to pay tax on it you can just enter all the various rates that might apply and then those will show up in other tabs to Auto autocalculate your uh taxes and then down here I've just added a few other notes for each tab to give a little bit more backround and insight so you can go through and and read through that the next thing we want to do in terms of setup is head over here to the customers tab so this is kind of highlighting all of your customers and their information along with total sales and total items ordered total sales and total items ordered you can see here this is a form formula and the look if you look in the top left hand corner so it'll pull all of the sales data for your customer and then populate it into these pie charts showcasing total sales by customer and then total orders items ordered by customer as well but yeah you can go in here enter your customers detail your customers details and then it'll Auto populate the sales information you're for here for you accordingly next the account section so this is where you're going to manage your chart of accounts so you can see here I'll talk about this formula in a second but down below you'll see all of your assets liability equity revenue and expense accounts I looked online at what are the what the sort of common accounts were so I think I've covered it all so you shouldn't need to add any additional ones you can certainly rename any existing ones if you are going to add addition additional ones just be careful with formatting cuz these accounts then impact the the trial balance the balance sheet and the profit and loss statement so just be mindful of that but I think for the most part this should cover what you need just rename if you if you need to but in terms of what to do here if you have an opening balance for any of these accounts just enter them here in the opening balance column and then as of date some of these line items autop populate so for example the inventory balance Auto populates from the inventory products and inventory tab um or the inventory management tab I should say and the owner's Capital Auto auto populates or autoc calculates based on this formula above so just be mindful of of some of those notes but in this case like opening assets minus opening liabilities and retain earnings equals opening Capital if you don't have opening balances totally fine just skip through this but uh if you do you can go ahead and enter them there and then they will autop populate into your trial balance and balance sheets so you don't have to worry about that okay the next section inventory management so this this is where you're going to go in and enter all of your products services or anything that you offer and sell as part of your business so you can see here I have a a fictitious coffee business so I've set up a bunch of the coffee products that I sell you can select the type of product they are so product service digital other you can add other options if you'd like enter the opening quantity if there is any enter the the cost per unit and then that'll give you the opening value and so this value here should equal if I go back to the account section yeah you can see here 5255 it'll bring that in automatically from the inventory opening balance that you see here if I keep scrolling over here quantity in this is going to autop populate from the inventory purchases tab so anytime you purchase inventory from a vendor or supplier it'll bring in that quantity automatically into this quantity in column and then quantity out it'll Auto populate uh based on the sales and AR tab tab so any sales you make it'll take that quantity and automatically reduce it for you here to bring you at your quantity available and then based on your purchases and your sales it'll calculate the average cost per unit average price per unit and then it'll give you the overall profit margin and that's what drives these dashboards or reports up top so on the left you see your you know top products in terms of highest profit margin uh lowest profit margin products low stock items based on quantity available and then High stock items so this is a great indication of what you need to reorder or maybe maybe products that just aren't selling very well and maybe you don't need to order too many of okay so the next tab I'll go through is the sales and accounts receivable tab so this is where you can track all of your sales for the year for the fiscal year and whether these sales are due on receipt net 15 net 30 45 60 so if you have customers that pay on account or or have special terms you can enter that information here it all calculates the due date for you automatically you can see here in terms of other things to fill out Revenue category So based on the accounts that you've that we've outlined from a revenue standpoint they will appear here so that'll appear on the income statement you can select the customer that this sale is being made to and then the product that you're selling to them you'd go ahead and then enter the quantity price it'll Auto calculate revenue and then you can select the appropriate tax rate it'll calculate autocalculate the tax collected bring you to the total and then you pretty much need to enter the cash received so for those you know sales on terms you just specify how much of that cash you received and then it'll calculate the remaining balance for you for cost of good sold purposes you'd go ahead and enter the cost per unit of the product that you sold so you know in this case I sold I sell sell I made a sale of the original blend for eight bucks but how much of that original blend cost me two bucks and then I'll give me the the total cost of this item and all of this will then autop populate these reports up top so month-over-month Revenue top selling products and then your aging accounts receivable report so for those that haven't paid yet or you know actually break down these buckets so current meaning any current invoices that are not overdue but you're awaiting payment uh invoices that are 1 to 30 days overdue 31 to 60 61 to 90 90 days plus and if you use this filter up top and let's say you clear out this filter and select 90 days plus it'll filter all those customers and orders or sales I should say that are 90 days over do that you can then follow up on because they haven't paid yet or maybe you just haven't updated the cash received so you can go ahead and do that I'm going to select all here another thing I'll highlight on each of these tabs where input is required you'll see there's a bunch of these hidden columns up top and I did this for uh a specific reason so if I open these out one second there we go if I open open these out what's happening is it's automatically tracking the accounting impact of that transaction so it's automatically tracking the revenue impact sales tax payable because you're collecting sales tax and on the sale so that's going to go to that liability account accounts receivable if the sale is on on terms cash received and cost of goods sold so you don't have to worry about the accounting side it's automatically doing this for you based on these formulas I've put a note in here saying you know make sure not to manually adjust this unless you you kind of know what you're doing but even then I wouldn't touch these um uh predetermined formulas if you do need to make an entry I do that on the journal entry tab which I'll get to in in a second but by having this Auto this accounting impact autoc calculated this is how the trial balance I'll just go go ahead and hide this again that's how the trial balance and the um balance sheet and income State automatically get get populated just hide these again so that's sales bills and expenses very similar go ahead and enter your the expense or the bill select the expense category so that's how it'll show up on the income statement the amount tax rate taxes owed which will automatically be calculated based on the tax rate total due amount that you've paid so you might have terms that you know with those who you know you you've purchased off of so you can just put in how much you've paid and then the remaining balance due and similar to as I mentioned on the sales and AR tab you're going to have this like accounting section there we go you're going to have this accounting section that shows the impact but again that's just for your information I wouldn't be modifying those those values and then up top you'll see the the chart of month-over-month expenses and then expenses by category okay the next tab I've broken out inventory purchases so I brought in low stock items and high stock items for your reference and this is where you can track any kind of purchases of inventory you select the vendor or supplier product quantity in and then just go through the motions of filling out the rest of this information and as I mentioned that would flow over to the inventory management tab okay um I'm going to go through the journal entries tab next actually so as I mentioned if you do need to make any manual adjustments I do that here on the journal entries tab so everything's automated so you shouldn't need to go in here but if you do need to make like for example you're amortising an asset or depreciation depreciating an asset or something to that extent or prepaid expenses you can go in here enter the enter the journal entry select the debit account the credit account and then this will autop populate into the trial balance balance sheet and profit and loss St accordingly again I would only populate this if you know what you're doing here from a journal entry standpoint otherwise I would just stick to the automated entry input entries bank reconciliation so let me talk about the input section first so first thing here you can see bank statement and you can see all the transactions this is where you would populate all of your bank transactions let's say from your checking account credit card so on and so forth with the spend and receive columns so you would go in and enter this information and it's going to calculate autocalculate your opening balance what you've spent slre received and your closing balance for cash and this is over the year over your fiscal year you can see here data from Jan 1st to December 31st that's brought in automatically from your fiscal year start and end so it's an annual ongoing reconciliation so that's the information from your bank statement and then over here on the right hand side this is the data brought in from your cash account these um equations up top or formulas are autoc calculated so don't worry about that and same with this data this data is Auto automatically brought in so as you enter sales data expense data purchases of inventory this is automatically brought in it's split up by inventory purchases bills and expenses and Cash In from from sales and AR so in terms of your task you pretty much just need to go through your bank statement here and match it up to the corresponding entry here and say that you know you've reconciled that particular amount so it's for your own reference in terms of the actual cash reconciliation as you're going through and doing the reconciliation you'll see the bank balance which is taking the closing balance of your bank statement and then the closing balance of your cash account from here and then signaling whether you uh are reconciled or or not okay so already went through the here the journal entries tab TX summary So based on all of the sales that you've made you're going to see a section here on sales tax payable because you've essentially collected this tax from a customer so you're going to have to remit that back and then tax expense so any kind of expenses or taxes you've paid on expenditures or purchases that you can track here so you can see how much you've collected versus actually spent for your own purposes and so you have that reference for for tax purposes okay and then as you've you're kind of going through and entering all this information the trial balance is autop populating so you can see it's brought over all of the asset accounts liability Revenue equity revenue expense with all the total debit balances and credit balances I would just continuously like as you're entering transactions less so the automated transactions here but like the journal entries for example if you are using that tab I would continuously revisit the trial balance to make sure Deb total debits equal total credits because that then drives the balance sheet in profit and loss statement so just make sure that that is all balanced at all times and then you've got the balance sheet showing your Financial Health as a as of a certain date so as of the end of this fiscal year it's just sort of a running total of all your all of your assets liabilities equity for the year uh up to up up until a certain point I should say and then the profit and loss date which tracks your income or revenue and expenses over the course of over the year I've left a few of these you can see here I've left some space here and just just in case you bring in other expenses but yeah this will give you your total profit so all of this is totally automated once again all you really have to do is input information into really the sales and AR tab bills and expenses inventory purchases and then everything else should automatically populate for you and then of course from a setup standpoint your customers enter your customers opening balances on your accounts and then just outline or input any products that or products and services that you're actually selling but yeah that's pretty much it this is going to give you sort of a full comprehensive sort of bookkeeping SL Finance template for your business and as I said like it's going to autop populate the analytics tab as well where you're going to have this data refreshed in real time un handy for decision-making purposes so I hope that was helpful and I hope you enjoyed the template

Channel: learnwithpre

Views: 12,030

Keywords: Automated Accounting System Google Sheets, CPA Google Sheets Tutorial, learnwithpre, Google Sheets Bookkeeping Template, Advanced Accounting Google Sheets, Financial Management System Google Sheets, DIY CPA Accounting System, Google Sheets Finance Template, Comprehensive Bookkeeping Google Sheets, Google Sheets CPA Accounting Setup, Easy Google Sheets Accounting, Google Sheets Accounting System Tutorial, Automated Bookkeeping Spreadsheet, Financial Tracking Google Sheets, LwE&#%

Id: ilx2EplSgLQ

Length: 18min 59sec (1139 seconds)

Published: Fri Dec 15 2023