Introduction

Excel Accounts Overview During this lesson we shall look at the following; Definitions and explanations, Navigate through a template with monthly information, and a one for quarterly Bookkeeping as follows; Opening BalancesIncome and Receipts for 4 quarters Purchases and Payments for 4 quartersVat Return Information Assets Summary Liabilities Summary Trial Balance Income and Receipts Summary Purchases and Payments Summary Profit and Loss Account. Balance Sheet Excel is a commercial spreadsheet application written and distributed by MicrosoftOn this course we examine how Excel can be used for Bookkeeping and Accounts Preparation. Bookkeeping is the recording of information from source documents, checking what is recorded for errors, and summarising into periodical reports.Accounting involves adjusting some of the recorded information, recording what is not yet recorded, and preparing summary reports for the whole year, in form of Financial Statements. Excel is not an accounts software, but we can format or programme it to have some processes done by the spreadsheet. However, this is not an excel course, we expect you to have had some previous exposure to Excel.Microsoft Excel has the basic features of all spreadsheets,[1] using a grid of cells arranged in numbered rows and letter-named columns, to organize data and carry out manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and charts, and with limited three-dimensional graphical displayBelow is a list of some functions which our properly formatted spreadsheet can do, It can intergrate opening balances with the transactions in the financial year.It exposes errors of double entry on individual sheets It generates periodical sums for individual columns on analysis sheetsIt computes the figures for Vat Reports It generates periodical and cummulative balances for Assets, Liabilities, and owners accounts It assembles all the figures for periodical Trial Balances It generates figures for periodical and annual Profit and Loss accounts It compares figures for the current year, with those of the previous year, by computing the change amount and the percentage change It computes the profitability ratios in form of the Gross Profit Margin, Net Profit Margin, and the Return on Capital EmployedIt generates figures for the periodical and annual Balance Sheets It computes the liquidity ratios in form of the Current Ratio and the Quick Assets Ratio.It can summarise and analyse information in several other ways, depending on the formatting.Differences and Advantages of Excel Bookkeeping and Accounting; Its simpler and takes a shorter period to leanDouble entry is made on the same sheet, instead of a comprehensive General Ledger.Instead of ledger accounts we record in columns.Once a spreadsheet is formatted, all we do is recording the source documents. The rest is done by the spreadsheet. The formatting for a Sole Trader business is done only once, and the template can be used for any other Sole Trader. Minimal modifications are made to suit the layout for a partnership and limited company. All we have to ensure is leave the original template blank. The same template is used for many years to come. Its not a software but many processes can be automated like the summing, balancing, and transfer of information Using a formatted spreadsheet saves the time we would spend manually preparing the summary reports.Using a formatted spreadsheet saves money. If we already have a spreadsheet on a computer, we don’t need to buy software. Some software is paid for, plus the annual subscriptions. If a formatted spreadsheet can generate the required reports, then we don’t have to spend money buying Accounts software.The approach here is entirely practical, its fully implementable in a working environment It’s the most commonly used software for preparing Accounts Reports. For any report that is not prepared by a particular software, we resort to excel.Being able to format a spreadsheet gives us a deeper understanding of the Practical Bookkeeping and Accounts Preparation.Its some kind of introduction to programming, getting some understanding of how some software work, although its not based on a database.The benefits mentioned above are just a few. Even if it takes abit of time formating a spreadsheet, the benefits are worth the effort.This particular model of Excel Accounts is designed for those who have studied Bookkeeping and Accounts before. Its aimed at equiping you with an affordable tool which speeds up the summarising and reports preparation. The version for those who have not studied these before, is different and much longer. So in this version we don’t explain most of the terminologies used.Please note that you should also know the basics in Excel.Reports Which Determine the Content Vat Return Assets Activity Summary Liabilities Activity Summary Trial Balance Profit and Loss Account. Balance Sheet Let us examine the layout and content of the spreadsheets. Please remember that all these spreadsheets are created or located in one single workbook.Required Spreadsheets We require about 34 sheets in the same workbook, and they are utilised as illustrated below. The need for each sheet and why its laid out that way, is explained in the chapters ahead.Sheet 1 is where we record the general guiding information. Sheet 2 is for recording Opening Balances.Sheet 3 is for recording Income & Receipts for the first quarter Sheet 4 is for recording Purchases & Payments for the first quarter Sheet 5 is for recording Income & Receipts for the second quarter Sheet 6 is for recording Purchases & Payments for the second quarter Sheet 7 is for recording Income & Receipts for the third quarter Sheet 8 is for recording Purchases & Payments for the third quarter Sheet 9 is for recording Income & Receipts for the fourth quarter Sheet 10 is for recording Purchases & Payments for the fourth quarter Sheet 11 Summarises all the Income and Receipts analysesSheet 12 Summarises all the Purchases and Payments analysesSheet 13 is for generating figures for Vat Returns Sheet 14 Summarises the activity for Assets Sheet 15 Summarises the activity for Liabilities Sheet 16 is for the Trial Balance Sheet 17 is for the Profit and Loss Account Sheet 18 is for the Balance Sheet Sheet 19 is for recording Individual Creditor Accounts Sheet 20 is for recording Individual Debtor Accounts