Create a Bookkeeping Spreadsheet using Microsoft Excel - Part 1
HTML-код
- Опубликовано: 25 июн 2024
- Part 1 of 6 - Learn how to create a bookkeeping spreadsheet using Microsoft excel.
In this video I will demonstrate how to create a bookkeeping template using Microsoft excel.
Need help with Sage or other accounting software? Drop us an email at info@bpfs-online.com
Take our Online Sage training course
www.freebookkeepingaccounting...
Bookkeeping Tutorial/Training
Learn more at www.freebookkeepingaccounting.com 
Хобби
No problem. Please 'like', subscribe, and view more videos. Thanks for the comment.
Thanks a lot. Please 'like' my videos, subscribe, and view more. Much appreciated.
Thank you for the videos! I've been searching for a blog or tutorial that would walk through building a a basic, but functional (and easy to customize) bookkeeping spreadsheet. For someone like me, with barely any accounting experience, this is easy to understand and will help get organized quickly. Thank you again!
No problem. Thanks for watching.
You're welcome. Please 'like', subscribe, and watch more videos.
Thank you so Much is a very good start , I appreciate that you teach those who cannot afford enough money to go to schools or colleges to learn these things. i am one among them. thank you, God Bless You
Thanks Bookkeeping Master for making things so simple. Now to put your videos into practise. Pity you couldn't supply a starter template to make things even simpler but thanks for the starter.
Thanks a lot. Please 'like', subscribe, and view more. Much appreciated.
Thank you. Simple and effective. Life would be so easy.
Thanks for watching
thank you for sharing your knowledge and experience
thank You so much...that was really helpful!
Really nice simple instructions. Really appreciate you posting this!
My favorite RUclipsr ever! :) Thanks for sharing your knowledge .
Thanks a lot! Your kind words are very much appreciated.
Thank you so much, this helps a lot! I am currently a new business owner who needed to create a bookkeeping system. Also I really appreciate the fact that your videos are short and to the point. Subscribed!
No problem. I am glad the video was of benefit. Thanks for watching.
That was so clear! I don't feel so overwhelmed anymore. Thank you so much!
I'm learning a lot from your videos. Thank you so much for your great tutorials.
Glad you like them! Thanks for watching
Thanks for a great tutorial. Being a graphic designer I thought it would be hideous, but I appreciate how useful Excel is for keeping good records. Cheers :-)
No problem. Thanks for watching.
Thank you so much I am science student don't what is basic book keeping but u taught me.
You can save yourself quite a lot of hastle and formula editing by widening columns from the outset rather than merging two columns together.
Thanks for this; He doesn't even tell me HOW to merge them anyway. Couldn't get thru the preceding step of step 1 without it!
CalBarrier, LLC 2:58 shows Merge Cells
Outset?
Hi, could you possibly do a spreadsheet for property development? i find these videos extremely useful but find it hard to work out what area certain spending would go. You explain and show everything so clearly and make it look simple. Thanx
Just so you know we are using this set up for the Brighton Community Workshop Project accounts. I like the videos mainly for the way you talk. I know enough about Excel to make our spreadsheets work best for us but this set up got us heading down the right path so thank you. Maybe you should update these videos, nearly 10 years old now.
It's on my list. Hopefully early 2022. Thanks for the suggestion and for watching.
Thank you :) This is very useful.
just what I have been looking for ! Thanks :-))
oh thank you thank you..you are the answer to my requirements..i will thank you for my whole life :)
This helped me so much, thank you!
Thanks
I can't do bookkeeping any more than I can fly. But owning a very small business, I have little choice. But following these short videos, I can work it out in Excel. Thanks.
No problem. I have more free business and bookkeeping videos on my website.
Thanks for your help . . . Really useful.
I am whoever you want me to be...
Well done with the vids series of accounting...certainly makes more sense than the first year of accountancy I did...we didn't have PC's let alone youtube instruction then, way back in '72....(the only PC's we had were police constables walking, or driving around..lol)...and now lately the only thing I used Excel for was to organize info for music classification of all my song sheets.
Thanks for watching.
thank you very much!!!
Thank you very much for sharing this.
No problem. Thanks for watching.
Hi. Just subscribed to your channel. Thanks so much for the video. You made it look easy and I actually understood Microsoft Excel for the first time ever. Keep up the good work.
Thanks a lot. Please check out our site and watch more.
CHEERS BUDDY! Great Vid!!! clear coherent speech you set president for other tutorial vids.
Thank you
Great video and easy to follow, thanks very much
Glad you liked it! Thanks for watching and for taking the time to comment
Really good job, thank you.
Thanks Mark. Please check out our website - there may be other videos that will be of benefit.
thanks very much uploading the video. It is very helpful.
Great set of videos i just completed the set and set up as I watched very easy to follow by pausing when needed.. and then entered my data for first 6 months :( I am in deficit LOL To be fair my bus consultant is helping me start up ! so she will to analyse it with me ;)
+Nadene Allan Thanks for watching
Thank you very much
Very well done. Thank You.
No problem. Thanks for watching.
Thank you for sharing this knowledge.
snoo333 No problem. Please check out my website. Thanks for watching.
BookkeepingMaster is there a template i can buy pre made?
Yes, make a donation of £10.00 via my website www.freebookkeepingaccounting.com and drop me an email info@bpfs-online.com. You will need to update the dates and enter your own accounts (expenses, etc.) but the template is for your use.
thank you so much
i can learn new things
thank you
No problem. Please check out my website for more free videos.
This really is a super tutorial - thank you very much!! I have been using the spreadsheet since 2015 because I found myself acting as Company Secretary for our Management Company for our block of four apartments/flats. I wanted to be able to keep the accounts and be able to send the accountant our spreadsheet along with any receipts etc to keep our costs down with her as we are non-profit making and such a small company. This spreadsheet REALLY helps to keep everything clear and easy to fill and use. The only thing I can't ever get my head around is the final report that she produces for us... the figures look nothing like the figures that I give to her...but that's not your problem it's because I just don't understand accounting. Anyway, thank you again.
Thanks Gill. If you are unsure of any of the figures, please ask the accountant to explain - use her as much as possible within your budget. Thanks for watching.
thank you!
+aravinda madushanka No problem. Please check out my website for more free videos.
Very useful viedos and easy to understand, thank you so much! May I have a question tho? What is capital account in this case? If I have machinery or equipments bought for business use should I put them in capital accounts category? Thank you in advance!
Thanks a lot
No problem
Hello BookkeepingMaster thank for good videos. I have some question to ask. If I have many expend data and I want to open it like you did; in the first of your video before you start making a new one. How can I do that. Thank you. I will waiting for your answer.
thanks 4 sharing
pacificshabz No problem. Please check out my website for more free videos.
i will mate
how did you extend the 2 cells you merged -
I was able to merge the 2, but not to extend it to the rest of the columns
Easy tecnique=Highlight cells from A to AH. Right Click. Select: column width. (I set column width to 4).
+Laura Honey
right
@@laurahoney9988 i can't see column width after i right clicked
Wonderful stuff.keep doing well...
Thank you so much for your kind words
Jahangir Ghouri Are you from india???
Hi there, thanks for doing these videos they are really informative. I had few questions though I hoped you might answer.
1) Is this using the double entry method? I did watch your double entry video tutorial but I may have missed if it was being used in this tutorial series.
2) Where you have created the month overview tab, would that be copied and edited for each month. So would the tabs look like april, may, june... and then the profit and loss tab? Or is this set up to work for each month individually as I would prefer to have a spreadsheet where each month functions as a tab and the balances carry on to each month.
Thanks
Sam Davison 1) Yes, this uses double entry bookkeeping 2) Yes, I copy each month onto a new tab. With a bit of work you can also add a running annual p & l also. Thanks for watching.
Hi. Great video. Very useful for a fledgling small business. Was there any particular reason why you merge cells to make them wider across the whole sheet, as a starting point? An alternative would be to double the column width to 17 and drag this dimension across the whole sheet. I understand why you merge cells for headings etc. but merging cells that will be used to store data (a) gives you the formula errors you identify (and then fix) in the video and (b) make cut paste within the spreadsheet difficult. You may have a very good reason but merged cells for data is a pain.
Hi Roger. Thanks for the comment. The merged cells are totally personal preference. You are right that it creates slight difficulties later on. You are not the first to comment on this so hopefully viewers read the comments and decide to widen the cells.
If time allows I will try and edit the videos to reflect feedback I have received from viewers.
Thanks again.
if we r selling for credit then where i sud enter the amount instead of entering to bank ac or petty cash ac or etc
I would recommend following the categories set by HRMC and use a Table Tool rather than merge cells: DATE PAID invoice no: Details COMPANY/PAYMENT TO DETAILS DUE PAID Rents, Rates, Power, & Insurance Petrol Capital Subsistence Stationery & office costs Membership Advertising & Marketing Total
I use these categories and pull through to another sheet the totals for my accounts.
Very good suggestion Sally. If I were to re-make these videos, your suggestions would be some of the changes I would make. Thanks for watching.
thank you
Thanks for watching and taking the time to comment
Why merge the cells? it made everything a mess instead of simply making the cells larger.
Good point. I wondered that as well. The books all say never merge cells that you might populate with data.
@Seth Tucker It's much better practice to Center Across Selection than to merge. Merged cells can be a pain in the butt to deal with any time you need to select a range of cells that includes the merged cells, like copy/paste, sorting, etc.
good info
Thank you
BookkeepingMaster thank you so much for making these videos. I studied accounting but my work experience is at the stock market as a trader.I want to learn how to use my knowledge in English as it is my second language and I want to move to Australia. Do you have any recommendation except watching these 6 videos?
Get qualified. Have a look to see what accounting qualifications are accepted in Australia. Thanks for watching.
thank you
Your video is very helpful, however it will be better if you think of some people don't know how to get to the places like you do, to mention something , you should say right click or left click and guide people to go along with you.
Hi Thanks that a big help understanding excel, do you have any information about how to incorporate vat into this as well - Jason
You can simply add a VAT column in the assets and liabilities side of the spreadsheet. If the business is VAT registered though, you may wish to consider accounting software. I hope this helps.
any differences for a non profit? I am an intern working on a project, for a not for profit home that cares for people in the last 3 months of life (kinda like hospice). Thanks
Thank you for making these videos they have been a big help, I am planing on doing this monthly, how would I bring it altogether for self assessment purposes.
All profit and loss statements need adding together. These are the totals HMRC will need. If you want to make things very straightforward you can label your expense accounts with the same headings HMRC use. Have a look at your self assessment for the details. Hope this helps.
BookkeepingMaster Would this be the totals on the balance sheet?
***** Sorry Bill, I am unsure what totals you are referring to. Drop me an email (address on our website - www.bpfs-online.com) and we can chat further.
Can you please tell the whole processors of accounts transaction till end to TRIAL BALANCE on SAGE?
I'm very new to accounting. How and where do the Chart of Accounts go? Is that was I label under expenditures? These videos are fantastic!
There is no chart of accounts on a basic excel spreadsheet like this. If you need something more complex, you may want to consider accounting software; Sage, QuickBooks, etc.
Great help Bookkeeping Master. Where would a dividend be put on the spreadsheet?
bulwul1 Yes, entered and recorded in the same fashion as drawings
Is it absolutely necessary to make all the entry cells, double cells (2 cells merged into one)? Or will I be fine just making them each 1 cell?
Cheers from Canada!
No it's not. Thanks for watching.
Thank you for making this set of great videos. I wanted to ask about how to move forward to the next month. How do I copy and paste the layout for the next month below the previous month? Thanks
You can either copy the document and paste for a new month - so you would have a document for each month - or copy the spread sheet and paste on a new excel tab - so you would have one document with 12 tabs, one for each month.
You can do the latter by clicking on the top left box between the letter and number column and row headings. This will highlight the whole spread sheet. Right click copy. Open a new tab and right click paste.
BookkeepingMaster
Thanks for such a quick reply. Appreciate your help.
Hi There, am I correct In assuming that a VAT Control a/c could be added in the same way the bank a/c works? and the balance of the sheet at month/quarter end is the value due to/from HMRC? Thanks!
Yes Liam. Thanks for watching.
Hello BookkeepingMaster. Thank you very much for this awesome video. May i ask your advice? Is Microsoft Excel enough for me to use it for a business like Video/Film Production SMEs company? Please i really need your advice thanks in advance!!
Excel can do everything you need it to, if you know how. The advantage of accounting software is that it saves a lot of time. If you just need to keep your 'books' then excel will be fine as demonstrated in the video. If you have a number of credit suppliers and credit customers and need regular credit control, customer statements, various management reports etc. then accounting software would be recommended.
Thanks for posting this - What dose the a/c stand for?
Account. Thanks for watching.
Thank you for the tutorial, what is a capital account?
The owners investment in the business. I hope this helps. Thanks for watching.
Can’t you import from the bookkeeping software
Hi. I've just discovered your tutorial It is fantastic! Thank you. Will I be able to use this for my start up hairdressing business? Any help would be much appreciated.
Yes you can. The spread sheet is ideal for new and small businesses. Thanks for watching.
Thank you
At 0:50, I understand how you merged cells A1 and B1, but how did you repeat the cell merge across the row and down the columns so easily? I am new to Excel, so please forgive my ignorance. (Also, I'm actually using Open Office Calc. I'm not sure if that makes a difference.)
When you highlight a cell, a little black-box will appear at the bottom-right of the cell. Click and drag this. Thanks for watching.
It does not work for me. Maybe it's a difference with Open Office Calc. Thanks anyway
Hi, you have helped me so much with these videos, I was just wondering, would I add a drawings account also? its not an expenditure..?
Donna Gullan It should be included in the videos. It is entered with the expenses but recorded on the balance sheet. Finish the video series, if you are still unsure, email me
the above link for bpfs-online course is not working...
Here you go... www.freebookkeepingaccounting.com/free-bookkeeping-course. Thanks for letting me know
Sorry I may be stupid but I got lost at step one here? When going to column AH and down to 77 do I need to select anything to keep that setting?
+Phoebe McBarron I can just email the template to you? Please make a small donation via my website and then email me. Thanks for watching. www.freebookkeepingaccounting.com info@bpfs-online.com
Do you have a video series that goes in depth about how to make a bookkeeping spreadsheet for a merchandising business? (Inventory, COGS... does the sheet calculate)?
No, this is currently the only bookkeeping spreadsheet tutorial. I hope you find what you are looking for.
Hey, did u find the video on making bookkeeping spreadsheets
Good
Thank you
Hi I am trying to get a basic understanding of bookkeeping and started off with creating a spreadsheet following your video. I find this method very easy to understand and can follow the process however I have also tried to watch your free bookkeeping course and am now totally confused. This spreadsheet looks nothing like what you've shown in the bookkeeping course?
The principles are the same but just like most things, practice can seem much different to theory. I suggest you continue to learn. All will become clear with experience and time. Thanks for watching.
All great but my Excel sheet does not add decimals for some reason. If it's not a round number it would not add it to Total..What did I do wrong/did I not do ? Please help, thank you!
right click and go to format cells. go to currency (or numbers) and select 'to two decimal places'.
Are you big Keith from The Office?
A question where do we add purchase control a/c and sales to this?
It can be done - too complex to give clear details over the comment section though. The sales can be added before the asset and liability columns. You will need to record the sales twice (double-entry); in the new sales column and in the appropriate asset column. PLCA can be added just like a bank account. This should be pretty straight-forward. Thanks for watching.
good
Just wonder it this a double-entry bookkeeping spreadsheet?
Salina Cheong Yes it is
Not sure how you merge the cells. I clicked on the first two and dragged them over but only the first two were double
+Sandy MacKay If you follow the cursor on the video, you should see the button that I press.
Hi
Great site, Great help for an old PopPops like me
I seem to have a problem on profit and loss page 2 when i go to transfer (=) sum of bank account which is E20 on Asset Liability page 1 to my balance sheet, it refers to E20 on second page which is blank. what am I doing wrong.
Try F20 or D20. If none of these work let me know.
How do I set up for a service related biz I do consulting and get paid at time of service, some transactions have a commision taken and some pay me monthly
Sherri B Hi Sherri. If you need a spread sheet creating please email me (details on website).
Do you think this is a valid course for someone who's studying towards a BA in Accounting at university?
Yes.
I have a question the Microsoft excel is already in the computer or I need to download it??? if is so how I do that???
it usually is in your computer.. if you're using windows.. if not.. you can purchse it or download it
X
What about the cash flow statement?
Shaun Keegan Hi Shaun. If you check my channel or website you can find cash flow videos. Thanks for watching.
Is there a reason each column "NEEDS" to be two columns wide?
No, just my personal preference. Feel free to use a single column. Thanks for watching.
It would be great if you could update :)
Yes, it's on my list. Thanks for watching.
can i get a full template for your bookkeeping course
Yes, please make a small donation on my website and send me an email. Thanks for watching.
what does the a/c mean? The account number?
a/c means account. Thanks for watching.
may I please have a copy of this template? thank you
+K Vang Yes, please make a donation on my website and then drop me an email. Thanks for watching.
what is product of resale means?
A product that is purchased for resale. Such as a clothes shop that purchases clothes to sell in the shop. I hope this helps. Thanks for watching.
I want xl n+1datas=1 xl data
Thank u soo much can you please give me more videos with yearly basis transactions??
Can I get copy of this file for study purpose, please?
+7absinth Yes, please make a small donation via my website and send me an email. Thanks for watching.
+BookkeepingMaster I have sent you mail.
+BookkeepingMaster ;Hello Master. Please accept my small token of appreciation for the good effort. Keep it coming. I too need the templates; Will you please let me know what email I must send the donation confirmation?