FOUR ELMS BOOKKEEPING Mudford, Yeovil, Somerset  01935 850807

Example of Visual Basic/Excel project

Payroll Calculation Tool

Four Elms Bookkeeping's Payroll was written for use with Microsoft Office and HMRC's Basic PAYE Tools 2012 as a quick and easy way of calculating a payroll without recourse to commercial software. It generates an Excel workbook after each payroll run containing payslips, summary information for each employee and also YTD figures for the company as a whole and uses open source database software for storing and retrieving payroll data. The calculated figures from this tool can be input into HMRC's Basic PAYE Tools for online filing.

Although accessed via a Microsoft Excel workbook no numbers are entered by the user into worksheet cells. Instead data is input via a series of input forms and associated software written in the Visual Basic for Applications (VBA) programming language. This enables numbers input by the user to be checked for common errors before the software processes and produces payroll output. The user has minimal interaction with the Excel worksheets which function only as displays for payroll data. The software incorporates income tax and national insurance calculation methods published by HMRC in its guides for software developers.

Originally intended for payroll runs of small businesses having up to 20 employees, the software is coded to process higher numbers of employees if so instructed.

The payroll tool is used in conjunction with Microsoft Office and one of the following versions of Microsoft Excel: Excel 2007, Excel 2010 or any later version of Excel. The tool also stores payroll data in the open source MySQL database which can be freely downloaded from www.mysql.com. A user name and password to the MySQL database are input into the payroll tool before you run each payroll.

The payroll tool includes the following:

• Payslips for all employees listing period deductions, period Statutory benefits, Employer National Insurance and cumulative YTD tax paid, National Insurance paid, Statutory benefits received and pay received YTD for National Insurance and Income Tax purposes.
• Monthly or weekly payroll runs with facility to input hourly rates and hours worked.
• Calculates income tax for all cumulative and Week1/Month1 tax codes including Week 53 calculations
• Calculation of all statutory benefits (Sick Pay, Maternity Pay, Paternity Pay (OSPP & ASPP) & Adoption Pay) on input of applicable dates. Automatic continuation of maternity, ASPP and adoption pay until entitlement expires.
• Pension scheme deductions may be applied to chosen employees. Automatic calculation of pension scheme deductions set as percentages of salary with facility to assign fixed amount pension deductions or additional voluntary contributions to individual employees.
• Automatic calculation of student loan deductions
• Facility to include bonus payments, expenses, AOEO deductions and miscellaneous deductions
• Prompts user to input personal data for all new employees in anticipation of RTI
• Summary payroll for companies with automatic calculation of amount owing to HMRC.
• Extensive checks on consistency of data input by the user to help prevent errors.
• 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 and retrieval
• Simultaneous recording of payroll data to a database and to an Excel workbook and ability to retrieve past payroll data from Excel rather than the MySQL database.
• Software can run data from more than one company if users need to do the payroll for more than one business. Multiple databases can be created to store payroll data for different groups of employees.