FOUR ELMS BOOKKEEPING

Example of Excel/Visual Basic for Applications Project

Small Business Accounting Tool

Four Elms Bookkeeping's Small Business Accounting Tool is an Excel workbook whose standard functionality has been extended by incorporating thirty different Visual Basic for Applications (VBA) code modules and interactive forms. Each module providers an element of a dual entry bookkeeping system which includes the key functions and interfaces you would expect from a commercial accounts package. It was designed to avoids the need to purchase commercial software licences when preparing accounts for customers whose accounting transactions are relatively straightforward. The suite of VBA modules can connect to free open source database software for storing, backing up or retrieving accounting data as often as required.

Although its basis is a Microsoft Excel workbook no numbers are entered by the user into worksheet cells. Instead a series of buttons embedded in the worksheets activate input forms and run software modules written in the Visual Basic for Applications (VBA) programming language. The VBA code checks data input by the user for common errors before the software processes and posts them to the accounts. The Excel worksheets function mainly as displays for accounting data.

The accounting tool can be used with any of the following versions of Microsoft Excel: Excel 1997/2003, Excel 2007 or Excel 2010 and can be run with the Windows 7 and Windows 10 operating systems. The Visual Basic code behind the Excel worksheets applies a standard double entry bookkeeping system.

The VBA enhanced workbook provides the following features:

• General Ledger which can contain up to 999 accounts
• Sales and purchase ledgers storing customer and supplier contact details
• Ability to create up to 1000 sales ledger and purchase ledger accounts
• Creation of sales invoices
• Automatic calculation of early payment discounts received from suppliers or due to customers
• Balance Sheet and Profit & Loss Accounts recalculated after every new transaction
• Bank reconciliation utility
• Facility to set up recurring payments or receipts with automatic reminders when they fall due
• Audit trail of each transaction posted
• Choice of ten VAT tax codes to use with each transaction
• Calculations of VAT due under VAT Margin Scheme included (for second hand car dealers and other traders in second hand goods)
• VAT100 form with box entries automatically populated. VAT returns can be calculated on an accruals or cash accounting basis.
• Facility to enter details of stock items purchased or sold for quick entry into sales or purchase records.
• Automatic calculation of number of stock items held.
• Aged debtors and creditors reports
• Activity reports on any General Ledger accounts
• Activity reports on any Sales or Purchase ledger account
• Year end (or period end) journals
• Automatic checks on consistency of ledgers, control accounts, balance sheet and profit & loss account after each transaction with instant user alert if any error is found.
• Automatic user prompting where input is inconsistent or missing
• User input boxes programmed for easy entry of figures with automatic conversion to accounting format
• Embedded SQL and ADO code allows quick access to the Open Source MySQL database for data backup, storage, retrieval and management of backup data
• Multiple copies of the software can be made if users need to do accounts for more than one business.