Checks and Balances, Inc.

Payroll Company

Summary

Challenge: Payroll company Checks and Balances, Inc. sends out custom-made spreadsheet templates to its customers to collected payroll information - name, address, department, tax status, etc. The information collected must conform with the company's back end system's requirements or else the company must manually inspect and correct any inconsistent entries.
Solution: The newly tuned spreadsheet leverages Excel's built-in functionalities to impose logical restriction on where information can be entered and what kind of information can be entered, significantly boosting the consistency of the information collected, and, ultimately, saving the payroll company hours of manually checking for errors.
Features Utilized:

Examples of Requirements and Solutions

Requirement

User cannot enter alphabet characters in cells that require numeric input such as zip codes and phone numbers.

Solution

Strict data validations are imposed on such fields. When the user enters alphabet characters in fields where numeric input is required, an error message is shown and the characters are rejected.

Features Implemented

Screenshot

Requirement

User cannot leave the last name of an employee blank.

Solution

If the user fails to fill out any required cells such as the last name of an employee, the cells will automatically be highlighted yellow through Excel's conditional formatting feature to catch the user's attention.

Features Implemented

Screenshot

Requirement

User must selected one of the 50 states abbreviations for the address.

Solution

An in-cell drop down list of states abbreviations is created through Excel's data validation. User can only select or enter a state's abbreviation that is in the drop down list.

Features Implemented

Screenshot

Requirement

User cannot alter the layout of the record tables. User can only enter information into designated input cells.

Solution

All cells outside the designated input area are locked. The layout and format of the record tables are also locked. User can only enter data inside the designated record tables.

Features Implemented

Screenshot