Payroll Spreadsheet User Guide 04242019a

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 10

DownloadPayroll Spreadsheet User Guide 04242019a
Open PDF In BrowserView PDF
Payroll System User Guide
PRELIMINARY
7 March 2019
Revised 24 April 2019
Program Version 1.33

By Thomas Butler
517 Oak St.
Rockford, Illinois 61104
mail.thomasbutler@yahoo.com
815-668-6146

Payroll System User’s Guide

2

Contents
Introduction ......................................................................................................................... 3
Architecture/Design of Program ......................................................................................... 4
Moving Around the System ................................................................................................ 4
Quick Start Setup ................................................................................................................ 4
Setting Up for The Year ...................................................................................................... 5
Running a Pay Period.......................................................................................................... 5
Employee Record Setup ...................................................................................................... 6
Employee Name .............................................................................................................. 6
Manually Change Employee Name ............................................................................ 6
Change Employee Name (automated)  .................................. 6
Employee ID ................................................................................................................... 6
Special Vacation Time Rate............................................................................................ 6
Worksheets in the System ................................................................................................... 7
Home ............................................................................................................................... 7
Employee Template ........................................................................................................ 7
Employee Worksheets .................................................................................................... 7
Payroll Summary ............................................................................................................ 7
Check Summary .............................................................................................................. 8
Pay Stub .............................................................................................................................. 8
End of Year Process ............................................................................................................ 9
Create W2s ...................................................................................................................... 9
Create W3 ....................................................................................................................... 9
Create an Empty Database for Next Year ....................................................................... 9
Manually Create Database .......................................................................................... 9
Automated Create Database .................................................. 9
Payroll Software Legal Requirements .............................................................................. 10
Appendicies....................................................................................................................... 10
Appendix A, Links to Latest IRS Publications ............................................................. 10
Appendix B, References ............................................................................................... 10
Appendix C, Payroll Program Known Issues ............................................................... 10

Payroll System User’s Guide

3

Introduction
This payroll system is modeled off of a spreadsheet, written in SuperCalc5, which was created in
1995 by George Fell for the Natural Land Institute, Rockford, Illinois. As Mr. Fell was not a
professional software developer, he was unaware that he was violating a prime tenet of software
development; never mix program code with data. Unfortunately, a spreadsheet does just that. It
uses formulas embedded in the spreadsheet cells to manipulate the data. Where this is a problem
is the spreadsheet, i.e., code/formulas, cannot be updated by simply copying in a new
spreadsheet. Instead all of the data must be re-loaded into the new spreadsheet. In addition, the
data is not secure and can be inadvertently exposed to program developers or others who
inevitably will have to perform some maintenance task. In addition, maintenance of the
spreadsheet with formulas is convoluted because of the inherent obfuscation created by the
spreadsheet “cell” paradigm, since there are formulas. i.e., program code, embedded throughout
the spreadsheet. The resolution of this fatal software issue of mixed code and data is simple. To
have the data stored in a database, a series of files containing only the data and NO program
code. This is a “best practice” of software development. Having said that; let’s pay…
The payroll spreadsheet is a hybrid of Microsoft Excel formulas and Microsoft Visual Basic for
Applications (VBA) macros programming. To the user, it looks like any other spreadsheet.
However, under the cover, the more complex operations are performed by Visual Basic for
Applications (VBA) macros.
The payroll spreadsheet performs all of the required functions necessary for a small business
payroll system. Some of the main features are:
Payroll summary
Check summary report
Employee year to date payroll information
Employee pay stub
Employee leave report
End of year W2 creation (numbers printed in same position as on W2)
End of year W3 (totals of W2s) creation (numbers in same position as on W3)
In addition,
Employee settings can be changed during the year without affecting the previous
pay periods.
The pay stub is formatted to print name and address of the payee to use window
envelopes to ensure confidentiality. Note distribution to employees of the pay stub
created is at the discretion of the employer. Its main purpose is in operation of the
program and ending a completed pay period; marking it locked.
The software creates an employee ID number. This number is used to reference
the employee within the system, and it consists of last two characters of last name
and last four of SSN.
Employees can be set up for salary or hourly pay.

Payroll System User’s Guide

4

Architecture/Design of Program
The payroll spreadsheet workbook has several fixed spreadsheets with VBA macros and a
variable number of employee record spreadsheets (one for each employee). The fixed sheets are:
Home
first sheet, has links to each employee sheet
GeneralData
setup/configuration for the year’s payroll process
PayrollSummary
real-time summary of payroll
CheckSummary
list of checks for a pay period
EmployeeTemplate blank employee sheet (do not modify)
DO NOT MODIFY OR CHANGE ANY FORMULAS ON THE FIXED SHEETS
And DO NOT CHANGE THE ORDER OF ANY SHEETS IN THE WORKBOOK.
(Normally this will not be possible, since the protection is turned on.)
Return to the ”Home” sheet from any other sheet by clicking cell A1 of any sheet.
The employee sheet names are prefixed with “EMP_” and appended with the employee’s name;
i.e., “EMP_Thomas_Butler”.
The GeneralData sheet is the main “control” sheet for the program. This sheet has the
setup/configuration information. In addition, there is a click-able list of the employee sheets that
is also on the Home sheet.
An employee sheet is created from the employee template. The employee sheet has three predefined areas:
a table of payroll data,
a facsimile of a pay stub,
and a facsimile of a W2 form.
The facsimiles are used to create a printable version of each.

Moving Around the System
Cell A1 on every sheet (or page), which has the business title, has a link to the Home sheet. Click
on cell A1 at any time to return to the Home sheet. Use the employee list of links on the Home
page to go to an employee page (note it is possible at any time to use the sheet names at the
bottom of Excel to move from sheet to sheet.)

Quick Start Setup
Make a copy of the program/spreadsheet and use the copy. This way one can always go back and
make a fresh copy. Name the copy “NLI2019payroll.XLS” or something similar.
ALWAYS enable macros when starting the program. Parts of the program are written using
macros, and the program will NOT function without macro functionality enabled.
Delete the sample employee records. After all of the employee records are deleted, go to the
Home page and click on “Refresh Employee List”. The list should be blank.
On the GeneralData page, verify that the configuration parameters are correct for the year. Click
on cell A1 to return to the Home page.

Payroll System User’s Guide

5

On the Home page, click on “Add Employee” and add an employee sheet. The employee sheet
can be filled in now or at a later time.
Go back to the Home page and add another employee record. At any time, one can click on the
“Refresh Employee List” on the Home page to update the list of employees.
Go to the PayrollSummary sheet and click on “Update Summary”. All of the data should be zero.
As data is entered on an employee sheet, this sheet is updated in real-time. Any time an
employee is added or deleted, click on the “UpdateSummary”. For confidence, one can click on
“UpdateSummary” at any time.
On the employee sheets, add the relevant employee data:
Name, employment start-date, salary or hourly,
marital status, federal and state exemptions.
Enter voluntary deduction amounts and any garnish amount if required.
It is not required to enter the address, phone, or employee’s Social Security Number.
However, at least the last four digits are needed to create an employee number for the
system.

Setting Up for The Year
The GeneralData sheet holds the configuration of the payroll system for the year.
To configure for the year enter,
Select the year,
Enter the pay dates for each month,
Select the number of holidays for each month,
From the IRS-P15A document, enter the amount for an allowance/exemption
Enter the tax rate table information for the selected year
Make any adjustments to the state rates and allowance amounts
Make any adjustments to the leave time factors
Add employee records and data.

Running a Pay Period
1.Go to the Home page and click on ‘Refresh Employee List” and verify all employees have
been added to the system.
2.Ensure the employee’s pay rate, exemptions, and deductions are correct.
Note that after going to an employee’s page the link on the Home page will no longer be
blue. This can be used as a reference as you are entering the month’s pay data.
3.Go to the employee sheet and find the pertinent pay period to enter the number of hours. If the
person’s pay rate or salary has changed enter that in too. It doesn’t matter which is
entered first. In addition, enter any new values for contributions or other amounts that
might have changed to include marital status.

Payroll System User’s Guide

6

4.Select the pay period and click on Update Pay Stub. Scroll down to see a facsimile of the pay
stub. The pay stub is a handy tool to help verify all data was entered correctly.
5.After all of the pay information is correctly entered, the pay stub can be printed by clicking
“Print Pay Stub”. This process locks in and closes out the pay period, so GET IT RIGHT
before printing a pay stub. The closed pay period is indicated by the values being all
grayed out.
It might be preferable to enter all of the employee data, double check it in the Payroll
Summary and THEN print all of the pay stubs to close all of the employee records for the
pay period.
Once the pay period has been closed, the user CANNOT go back and change the
data; sort of. It is possible to unprotect the sheet and edit the values. Think of the
unprotecting of the sheet as an escape if the user makes a data entry mistake.
Note the pay stub has a “validated” box for staff to initial that the data have been verified.
6.After all of the employee data has been entered, go to the CheckSummary sheet and click on
“Run Report”. This will generate a list of checks for the pay period. Click on “print” to
send the created list to the printer.
7.Thank Tom, the developer, for creating this work-saving program with a check for $50!

Employee Record Setup
Employee Name
The employee name is as part of the employee record/worksheet, so a special process must be
executed if the name must be changed after the employee record has been created; i.e., mid-year.
Manually Change Employee Name
1.Unprotect the worksheet
2.Change the employee name field
3.Rename the employee sheet on the sheet tab
4.re-protect the worksheet
Change Employee Name (automated) 

Employee ID
Made by program from last two characters of last name plus last four of SSN
An employee ID is used for tracking the employee in the system and it is created from the
last two characters of the last name and the last four numbers of the social security number. At a
minimum, enter the last four into the employee record.
Special Vacation Time Rate
On the ”Leave Time” area of the spreadsheet is the field “OR”. This entry will override
the normal leave time factors. Enter zero to use normal vacation time rates (default), or enter a
multiplier for override; i.e., 0.106

Payroll System User’s Guide

7

Worksheets in the System
The worksheets are ordered Home, GeneralData, PayrollSummary, CheckSummary, and
EmployeeTemplate followed by the employee sheets name EMP_ to EMP_ .
each employee has his/her own worksheet.
DO NOT DELETE OR CHANGE THE ORDER OF THE WORKSHEETS
Home
The Home worksheet is the root page for the system. It lists all employees in the system, and it
has a link to each employee worksheet.
All worksheets can reach Home by clicking on the business title on each worksheet.
Employee Template
The EmployeeTemplate is used to create a blank employee record using the “Add Employee”
button. DO NOT MODIFY THE EmployeeTemplate
Employee Worksheets
The employee worksheets are named with the prefix characters of “EMP_” plus the name of the
employee; i.e., “EMP_Thomas_Butler”.
The employee worksheet has several discrete sections:
(Click the business title (in cell A1) to return to the Home page)
1.The left of the worksheet contains payroll values.
2.The right of the worksheet contains vacation values.
3.The lower part of the worksheet contains a facsimile of the pay stub and any other
additional data that is NOT printed on the “Pay Info”.
4.The lower section also contains a facsimile of a W2 for the employee.
The pay stub section of the worksheet contains data for the selected pay period after clicking the
“Update Pay Stub” button. Clicking on “Print Pay Stub” will close out the pay period (actual
printing can be canceled).
The “Print Pay Stub” button should be done and the pay stub printed before proceeding to the
next pay period. However, any pay period can be selected to fill in the pay stub, which allows for
the printing of a previous pay period pay stub (however note that this will include the current
totals for al pay periods too).
Payroll Summary
The Payroll Summary keeps a real-time summary of the payroll data. When data is entered on an
employee record, that data can be seen added to the totals on the Payroll Summary immediately.
The “Print” function prints the payroll summary within the bounds of the range of data on the
PayrollSummary worksheet.
Any time an employee is added or deleted the “Update Summary” must be executed to refresh
the formulas.

Payroll System User’s Guide

8

Check Summary
The CheckSummary will list all of the paychecks for a selected pay period.
The “Run Report” function will create the paycheck summary on the worksheet.
The “Print” function prints all of the data within the bounds of the range of data on the
CheckSummary worksheet.

Pay Stub
The pay stub section on each employee worksheet contains data for the selected pay period after
clicking the “Update Pay Stub” button. Clicking on “Print Pay Stub” will close out the pay
period (actual printing can be canceled).
While it is not required to actually print the pay stub on paper, as part of the data processing
procedure, the system DOES require the user to click on “Print Pay Stub” to close out the pay
period. NOTE one could print the pay stub to a PDF if desired by selecting the PDF Printer for
output devices.
Closing out the pay periods is what allows the software to do mid-year changes to deductions,
marital status, etc.
Note distribution to employees of the pay stub created is at the discretion of the employer. Its
main purpose is in operation of the program and ending a completed pay-period; marking it
locked.
Changing any of the monthly deduction amounts, allowances, pay rates will affect only the
open pay periods (those NOT grayed out).
The “Print Pay Stub” button should be done, and the pay stub printed, before proceeding to the
next pay period. However, any pay period can be selected to fill in the pay stub, which allows for
the printing of a previous pay period pay stub (note this will have current YTD data though).
After all of the pay information is correctly entered, the pay stub can be printed by clicking
“Print Pay Stub”. This process locks in and closes out the pay period, so GET IT RIGHT before
printing a pay stub.
Once the pay period has been closed, the user CANNOT go back and change the data; sort
of. It is possible to unprotect the sheet and edit the values. Think of the “unprotecting” of the
sheet as an escape if the user makes a data entry mistake.
The closed pay period is indicated by the values being all grayed out.
Note the pay stub is formatted to print name and address of the payee to use window envelopes
to ensure confidentiality.

Payroll System User’s Guide

9

End of Year Process
For the end-of-year process, print the employee pay info, leave info, update the pay stub, and
print the final pay stub,
Print the W2s and the W3 and then create a new empty spreadsheet with the employees from the
current year.
Create W2s
A facsimile of the W2 is on the employee sheet. The values are propagated from the employee
record in real-time.
This function will print the employee’s W2 data in the same position as it is entered on the IRS
W2 form; facilitates easy filling in of the form.

Create W3
A facsimile of the W2 is on the payroll summary sheet. The values are propagated from the
employee record in real-time.
This function will print the W3 data in the same position as it is entered on the IRS W3 form;
facilitates easy filling in of the form.

Create an Empty Database for Next Year
from the previous year’s Employees
Manually Create Database

1.Copy the entire spreadsheet and name it “NLI2020payroll.XLS” or something similar
for the new-year.
2.Delete the previous year’s employee record.
3.Create a new employee record for each employee.
4.Enter the employee detail, deduction amounts, salary, and tax parameters, etc.
5.For the first pay period, enter in a test reported work hours and salary to see that
numbers change and get updated on the payroll summary.
6.delete the test value.
7.Repeat for each employee…lots of work!
8.Be sure the settings are correct for the year on the GeneralData sheet.
9.Print the GeneralData for your files.
10.Print the PayrollSummary for your files; should be all zeros
11. Go to the Home page and click on “Refresh Employee List” to verify everyone is in
the system.
11.yada yada yada
Automated Create Database

This function will reset all of the employee data for the new-year and carry over any
remaining leave time.

Payroll System User’s Guide

10

Payroll Software Legal Requirements
1.Federal labor Standards Act (FLSA) requires employers to maintain payroll records for a
period of three years [2].
2.Rounding of numeric values for accounting best practices [1].

Appendicies
Appendix A, Links to Latest IRS Publications
IRS Publication (Supplemental) P15-A Employer's Tax Guide
IRS Withholding Form W4
State of Illinois IL-700-T.pdf Withholding Tax Tables
Illinois Withholding Form IL-W4
IRS Publication (Circular E) Employer's Tax Guide P-15
IRS online forms ordering (free)
IRS form W2
IRS W-2 Instructions
IRS Form W3

Appendix B, References
1.Federal Labor Standards Act (FLSA) software rounding of time record values retrieved from
https://www.wagehourinsights.com/2014/05/keeping-payroll-well-rounded-and-flsa-compliantwage-hour-faq/

2. Federal Labor Standards Act (FLSA) record keeping requirements retrieved from
https://www.govinfo.gov/app/details/CFR-2013-title29-vol3/CFR-2013-title29-vol3-part516

Appendix C, Payroll Program Known Issues
1.At this time the system does not support overtime hours. State of Illinois law requires payment
of 1.5 time regular rate for more than 40 hours worked in a week.
Ref. 820 ILCS 105/4a (1)) (56 Ill. Adm. Code 210.400)
829 ILCS 105
2.That’s all.

Today is April 24rd in the year of our Lord 2019.

The End.



Source Exif Data:
File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.5
Linearized                      : No
Page Count                      : 10
Language                        : en-US
Tagged PDF                      : Yes
Title                           : Payroll Spreadsheet User Guide
Author                          : admin
Creator                         : Microsoft® Word 2016
Create Date                     : 2019:04:26 10:30:12-05:00
Modify Date                     : 2019:04:26 10:30:12-05:00
Producer                        : Microsoft® Word 2016
EXIF Metadata provided by EXIF.tools

Navigation menu