Reference no: EM132852344

VBA - Project

Download and open Project 5.xlsm. Save it as yourLastName_P5.xlsm (replacing yourLastName with - your last name!) and write the VBA code to complete the assignment requirements outlined in this document.

The high-level view of this project is that it requires you to develop a Payroll Solution that achieves the following:

1. Delivers a user interface to facilitate adding employee data to the Payroll Worksheet

2. Delivers a solution that processes the data on the Payroll Worksheet and prepares it for delivery to our Payroll Service provider (ADP) who creates the ACH records for our bi-weekly payroll

3. Delivers a Payroll Summary Report that summarizes totals for the Payroll data to be sent to ADP

Deliverable 1: A User Interface for adding employees to the Payroll worksheet (one at a time) Insert a new worksheet and design an input area where a user will enter a new employee's payroll data. Data to be captured includes: Employee ID, Name, City, State, Department, Health Insurance, Pay Basis, Hours Worked and Rate of Pay. The layout could be similar to this screen shot, though I would expect a better user interface that includes headings, better formatting and some styling that makes the input area attractive and the process intuitive.

EmpID's should be assigned by the system, and not entered by the user. The user will input data directly onto the worksheet - and click a button to add the data to the Payroll Worksheet. When the user clicks the button to add the data, validate the inputs as defined here to ensure the integrity of the data in our payroll system.

Data Validation:
Use the "Switch" method - so that bad data is identified in a single validation. Highlight each cell that contains bad data. If the data is bad - the row should not be added to the Payroll worksheet.
• Ensure that the user has provided a value for the Employee Name, City, State and Department cells.
• The Health Insurance value represents a deduction withheld from the employee's pay corresponding to the level of coverage chosen. Ensure that the value in the cell for Health Insurance cell is valid: Family, Single or None. To simplify payroll processing, rewrite the data back to the input area in upper case (UCASE() function) - for example: (Range("B3") = Ucase(range("b3")
• Validate the Pay Basis. Only Hourly or Salary values are valid. To simplify payroll processing, rewrite the data back to the input area also in upper case.
• Validate the Hours Worked for a reasonable value. Anything above 60 hours is invalid.
• Validate the Rate of Pay for Hourly employees - anything above $50 / hour is invalid. There is no upper limit for employees paid on a Salary basis.

If any value is invalid, highlight the cell, display a descriptive message box and end the processing. DO NOT WRITE BAD DATA to the Payroll worksheet.

If the data is valid, process it:
• Find the last row of data on the Payroll Worksheet
• Assign the new Employee and ID. Find the last Employee ID in that last row of the Payroll worksheet, increment it by 5 and write that value back to the Employee ID cell on the Input area.
• Write each value from the input worksheet to the first blank row of the payroll worksheet (last row + 1) - mapping the input data to the Payroll Data columns. For a good challenge, find a way to use a looping mechanism for this step. Think about how the Cells(row,col) object might serve that purpose.
• Format numeric data written to the Payroll worksheet, as needed.
• Display a descriptive message box to inform the user that the employee was added. Provide information about the row# and the assigned EmpID.

Deliverable 2: Process Payroll Worksheet Data to Calculate Payroll
You will process all rows of the Payroll worksheet to calculate the data for the columns pictured below.

Formulas for each column are provided in the following section.

Employees are classified as HOURLY or SALARY and Reg Pay, OT Hrs, OT Rate, and OT Pay (Columns J, K, L, M) are calculated using the Pay Basis (Column G).

When Pay Basis = Hourly: Hourly Employees are paid for each hour they work. Any hours worked above 40 are deemed "Overtime" and are paid at a higher wage. (Regular) Reg Pay is calculated as Hours Worked (up to max of 40) times the employee's Regular Pay Rate. Hourly employees who work more than 40 hours are eligible for overtime pay. OT Hrs is calculated for hourly employees who worked more than 40 hours by subtracting 40 from the Hours Worked.
The (Overtime) OT Rate is calculated by multiplying the Regular Pay Rate times 1.5 and OT Pay is calculated by multiplying OT Hrs times OT Rate.

When Pay Basis = Salary: Salary employees are paid a flat wage. Their (Regular) Reg Pay is the same as their Regular Pay Rate. You do not calculate a salary person's Regular Pay Rate times Hours Worked. They are not eligible to earn overtime pay. A Salary employee's OT Hrs and OT Rate, and OT Pay should always display as 0.

Gross Pay (Column N) = Reg Pay + OT Pay

The next 3 columns (Columns O, P, Q) represent money withheld from the employee's pay by the employer. They are calculated using standard withholding rates. The standard withholding tax rates should be defined as named CONSTANTS in your code (using the CONST declaration) and have the following values (these values do not display on the worksheet - though they are used to calculate Fed W/H, State W/H, and City W/H (as defined below):
Federal Tax Rate = 15% State Tax Rate = 5.50% City Tax Rate = 2.75%

Fed W/H (Column O) = gross pay * federal tax rate Insurance (column R) is aso a deduction and is determined by the value of Health Ins (Column F)
State W/H (Column P) = gross pay * state tax rate If Health Ins is: Family, Insurance = $125.00
City W/H (Column Q) = gross pay * city tax rate Single, Insurance = $50.00
None, Insurance = $0.00
Net Pay (Column S) is the amount the employee actually receives. Net pay = Gross Pay - Fed W/H - State W/H - City W/H - Insurance

Connect the Calculate Payroll command button to your VBA code, so that when clicked, the payroll is calculated for each row in the payroll worksheet.

Deliverable 3: Produce a Summary of the Processed Payroll

As another output of processing the payroll data, produce the following Summary in the area provided (just left of the Payroll data). Accumulate each row as part of the loop that processes the worksheet data (Deliverable 2). Use accumulator statements. (You may NOT write a sum function in the cells AND you may not use the WorksheetFunction.SUM function in VBA).

Coding Requirements:
• Write your code in a code module (not a sheet or workbook code sheet). This assignment requires the creation of two Subroutines:
o One to capture and validate inputs and write data to the Payroll worksheet, and
o One to Process the payroll data and produce the summary report.
• Follow good coding practices - declare variables for ALL inputs used within a validation, calculation or formula. Do NOT calculate directly from OR into a CELL. Meaning - you must declare and use variables for all payroll processing - and only assign the values back to the worksheet after all processing is done. The only exception is for Deliverable 1 - which is spelled out in the assignment introduction.
• Format your CODE - by using indentation and comments to make your code more readable.
• Organize your code into sections to:
o Declare your variables
o Declare and assign your program named constants
o Assign worksheet values to variables
o Calculate required values
o Assign calculated values back to the worksheet
o Format worksheet data as needed

Attachment:- Vba Project.rar

Attachment:- Project 5.rar