Basic Excel Accounting System

I show how to create a very basic accounting or bookkeeping system for a small start-up company or non-profit using Excel. The system works well for tracking financial transactions for very small companies or side-businesses. I have used this system for my own small business ventures that don't have a lot of transactions like real estate investment projects or rentals. I have also set this up for friends and non-profits that I have worked with to help track cash and expenses. Updated version of this video available • Simple Excel Accountin...
Excel worksheet available at: drive.google.com/file/d/1A__q...
Note: you cannot directly edit the google file but you should be able to save it and then edit it.

[@TheActorMichaelParagon]

Just WOW!!! I wish I'd found this 3 years ago when I began my company vs. now as I'm organizing a 2nd! GREAT information! Well presented and concise. Thank you!!!

[@beebclark9663]

Wow, thank you so much. This is totally doable. You've given me inspiration!

[@AccountingOutside]

I as so glad it is helpful, let me know if there are other accounting topic that would interest you.

[@Agentxile]

I've looked at a lot of bookkeeping and accounting videos and your presentation was the best one period. I worked in various accounting departments in my career, but I never contributed to the entire process start to finish. I completely understand now. Thanks!

[@AccountingOutside]

Thanks, glad it was helpful

[@jonathanaleman2338]

Incredible information! Please keep posting guides,tips,and maybe some insight on accounting for small family businesses

[@brynnmatthews1730]

Love this!! I'm just starting up a bookkeeping business (for small businesses) and this is great information. I'm still working on putting my systems in place so thank you-great content!

[@AccountingOutside]

Thanks, glad it helped - best of luck with your bookkeeping business!

[@magmaticly]

Very helpful! Exactly what I was looking for. Thanks a lot!

[@AccountingOutside]

Glad it was helpful

[@linnypareto4559]

Thank you for the help and sharing of your knowledge

[@AccountingOutside]

My pleasure.

[@Jona1han]

Amazing video truly helpful !

[@AccountingOutside]

So glad!

[@marksmith4512]

This was so life changing Thank you soo much!

[@AccountingOutside]

Glad it helped!

[@hendali5253]

Thank you so much that is very helpful

[@AccountingOutside]

Glad it was helpful!

[@nzabeen]

very helpful. i will do this. i dont have to buy the software bow, for small business.

[@alfanhilmi1574]

you save my life

[@AccountingOutside]

Glad it was helpful!

[@awongjunior1717]

How did you get the closing sales amount of 140?

[@immaculatenthigah2233]

wao!!this is so helpful

[@AccountingOutside]

Thanks!

[@TerryGrier]

One more Q: Cash could be your checking account (assuming I am using a Cash method) and if I use a Business Credit Card (in the LLC names) - can I just add "CC" next to the CASH line and make entries there too >?

[@AccountingOutside]

You can add columns for any "accounts" or categories that you want to track separately. If you want to track charges on the credit card I would suggest you add a column in the liabilities section. Good luck with your business!

[@satyabanukil779]

How to automate the calculation. Is it not possible for the system to put negative sign automatically based on the transaction.

[@AccountingOutside]

Good question and one I had not considered before. Unfortunately, it is not really possible to automate the negative signs because there will end up being positives and negatives for each accounting depending on the type of transaction.

[@JaneKungu-zb3uv]

i want to know

[@float3609]

Great video, thanks. Question: If Jones pays $50 then I'm assuming it would go in the cash column with a corresponding debit in the accounts receivable column, but we have also recorded a debit in the sales column attributable to Jones to offset the accounts receivable on 4/20/20. How do we handle the debit of $50 from the sales column?

[@AccountingOutside]

Generally you are correct with your entries, the $50 in the sales column stays in sales until we close out all the "Revenue & Expense" (temporary) accounts at the end of the period (about 10:50 in the video). The sales column tracks the total sales for the period so any amounts put into it will accumulate over the period, which is different than the Asset/Liability/Equity accounts that move up and down during the period. Because sales and expense accounts only accumulate during the period, we have to close those accounts out at the end of the period so that they can begin to accumulate the sales etc. for the next period. Hope that helps. FYI-negative amounts in the spreadsheet would equate to 'credits' in accounting terminology, and positive amounts would equate to 'debits'. Best of luck!

[@kabouch]

Would you please share the final excel sheet?

[@AccountingOutside]

My pleasure, I have put it on a google drive: drive.google.com/file/d/1A__qOa3fT7xyA7Vir47baBjSGVHPaGFD/view?usp=sharing

[@audiebez3009]

I know this might seem a silly question, why after any abbreviations is abb. Do you use a full stop.

[@AccountingOutside]

Not an accounting thing, but American english typically places a full stop (period) after abbreviations. While not necessary in this type of context, I have gotten in the habit because the period makes it clear to my American students that it is an abbreviation and not a new term they need to learn.

[@julibethap9806]

Helpful. Do you have any excel ready to download for a small bakery shop just to track the sales, expenses and a trial balance

[@AccountingOutside]

There is a link in the description to a google file that you can then copy to your own folder.

[@drealexatos3459]

Followed the accounting but got confused with Excel formulas, learning both for the first time. Late, unfortunately.

[@AccountingOutside]

Glad you were able to follow the accounting. I try not to use too complicated of excel formulas but I can see how they could be confusing your first time. Without providing a full tutorial here are some helpful hints to get started: (1) start formulas by entering the equal sign (=), this tells excel that everything after is an equation; (2) I use the SUM() command a lot to add amounts in cells together, by typing =SUM( and then selecting the cells that I want to add together (note: that cells can be selected across worksheets in excel). Good luck!

[@victorfletcher4188]

What if clients pay for the sale same day? Do I still need to make 2 lines for the actual sale and the cash/ receivable? When I put it in the same line the check sum still does not equal 0 and I put - for sales too.

[@AccountingOutside]

You can treat it as a sale for cash. It should balance out to put a positive amount in cash and a negative amount is sales.

[@victorfletcher4188]

@@AccountingOutside yeah I got it ! Sorry I was putting the amount in cash and receivables and I should leave receivables blank! Lol sorry man this is my first time your video is extremely helpful! One other question if I use cash app and business accounts charge 3% transaction fee for all purchases. How should I categorize that fee? In other expenses??

[@AccountingOutside]

@@victorfletcher4188 Glad you figured it out! Yes, put the transaction fee in other expenses - or if you want to track it separately and are comfortable with the worksheet you can create a separate column for it. Best of luck

[@jayesh640]

Sir can you take test for journal entry.....

[@AccountingOutside]

No but good luck.

[@haodev]

How do I record when I take money out of the company?

[@AccountingOutside]

Reduce cash (negative) and also reduce the capital/equity account (positive).

[@dailyjobsads939]

Sir we need practical work of accountant how work a accountant

[@AccountingOutside]

Thanks

[@vig6321]

at the 6 minute mark, how come it's -50 for sales? I don't get it. why is it negative? I get that it has to balance out to zero. I just don't understand why sales has to be a negative number.

[@AccountingOutside]

There are a couple ways to look at it: 1. Structurally, it has to balance to zero (to your point) and using negative numbers is one way to accomplish that. You could use positive numbers and use formulas in excel to ensure that it balances, but I have found that simply using negative numbers is generally clearer. 2. Conceptually, underlying the worksheet is the notion that assets are positive numbers and claims on those assets are negative numbers (debt & equity). For instance, if the owner invests $100 in the company the company gets cash and the owner has a claim on that cash; so we record it as positive $100 in cash and negative $100 in owners equity. Following this, when a sale is made, cash is received and the owner has a claim to that cash - so we record the positive cash and the negative owner claim on the cash. Hope that helps.

[@vig6321]

@@AccountingOutside Thank you! This makes sense to me now.

[@elainemoreno1412]

Where do you get the 41 in april

[@AccountingOutside]

Not sure about the 41, but the $51 is Sales of 150 minus Expenses of 99. That is your net income for April that gets added to the owners equity at the end of the month.

[@anggiehien]

I wish u can do details for Gst

[@AccountingOutside]

Thanks for the suggestion, unfortunately I am not familiar with the various "goods and services tax" policies and requirements that exist around the world. While I believe that this system is sufficient to provide adequate books and records for many regulatory purposes (I have made it through a regulatory audit with this system) I cannot guarantee it will meet specific requirements by different agencies or taxing jurisdictions.

[@anggiehien]

@@AccountingOutside thank you for ur response. I couldn’t find anywhere to handling gst and hst (sale tax) which we have to report monthly. None mentioned How it works with all other reports, unless double works or double entry. I found my way , but hope someone else better can show to the learner

[@marcomedina8833]

Thank you sir 🫡

[@felixkimilu8544]

thanks G

[@AccountingOutside]

Welcome

[@monjier]

And now we add excel magic to use dynamic arrays and ranges with automated dynamic lists across all the financial statements by month, and one transaction table to handle every historic transaction 😂😂 god i love excel.

[@AccountingOutside]

for sure

[@TerryGrier]

I do not understand the Closing Entry of -41 .. and Sales being 140 .. where did the 41 come from?

[@AccountingOutside]

I think you are referring to the equity side of the closing entry, which is net income for the period (revenue minus expenses). The closing entry for revenue is the amount of revenue for the period (as you indicate) and the entry for expenses are the expenses for the period. The difference between revenue and expenses is net income and it the amount of the closing entry to equity for the period.

[@TerryGrier]

@@AccountingOutside PS.. Thank you .. I am starting a consulting business and I am setting up your system in Google Sheets. I think for the first time person ... That using your system helps me understand what is going on ...QB while easy ... and pretty reports - did not help me understand the process.

[@tracymedia74]

While I found the video helpful, I am still confused. Is there an email where I can send my questions?

[@AccountingOutside]

I'm not really equipped for that - brief questions can be posted as comments but my capacity to address questions is somewhat limited... sorry

[@petertenay1785]

I somewhat get why revenue is an equity account, but y is expenses an equity account?

[@AccountingOutside]

Revenue and expenses (all all other income statement accounts for that matter) are simply tracking changes in equity for the year. A simple example, if I run my own company and at the end of the year my accountant tells me that my equity went up by $10, my next question is going to be why ... to which the accountant responds that I had $25 of revenue and $15 of expenses, generating $10 of net income that was retained in the company (as equity). Hope that helps.

[@superiorbeing95]

When I used spreadsheets, I used separate sheets for expenses, sales etc. this made me think twice about that

[@AccountingOutside]

I typically try to get as much on one sheet as possible, to simplify balancing the accounts and reporting.

[@AccountingOutside]

I have an updated version of this video available at kzbin.info/www/bejne/eoGpYoONnbqfZsk

[@bytohara9699]

Can't really see

[@AccountingOutside]

Sorry, there is a link to download the spreadsheet that might help.

[@ronsullivan132]

LOL, you need to cut back on the caffeine. You get three steps ahead of me on each item.

[@hungry-cd9yc]

It’s not raining outside

[@AhmedHassan-xp1bc]

Bro there is something wrong depreciation is devided by 48 the No. Of months and devided by 2 because i bought the equipment in 15/4( 2500%48)*15/30

[@AccountingOutside]

That works for the first month of depreciation but you will need to adjust the all the following months to just the 2500/48. Alternatively, some people simply take a full month of depreciation the first month of service because it usually does not cause a meaningful difference and saves some computations.

[@user-nf4es6gw5x]

Nice but you’re too fast😭

[@AccountingOutside]

Sorry about that

[@almaskhanjadoon]

The sound quality is shit😔

[@AxadaahmedAxadaahmed]

Aaaa

[@etornahm393]

Very very poor I was expecting to see some formulas but no You were only working for yourself

[@AccountingOutside]

Focus of the video is the process. Formulas were visible in the video, and the full spreadsheet is available to download from the link in the description.

[@AxadaahmedAxadaahmed]

Aaaa