59
EXL
6
your TASK
Your task is to create
a payroll register to
calculate the weekly
payroll at The Hub.
The spreadsheet will
contain payroll data
for all employees for
one pay period. It will
include formulas to
calculate regular pay,
overtime pay (based
on each employee's
hourly rate of pay),
federal and state
income taxes, Social
Security (FICA) tax,
Medicare tax, and
each employee's net
pay. Formulas will
also be entered to
calculate the totals
of the earnings and
deductions columns.
the SKILLS
· Format decimal
places
· Scaledatatofitto
one page
· Multiplycells
· Subtract cells
· Format numbers to
accounting
A
payrollregisterisaspreadsheetthatcalculatesthegrosspay,payroll
deductions,andnetpayforagroupofemployees.Businessesuse
this data each pay period to generate paychecks and to maintain proper
accountingrecordsfortaxpurposes.
1. UtilizetheAutoFillfeatureofExcelwhencopyingformulastoadjacentcells.
2. FormatnumbersinfinancialdataintheAccountingformatsothat$signsare
left-alignedandnumbersaredecimal-aligned.
3. Format all numbers with the same number of decimal places.
4. Readthroughallinstructionspriortocompletingtheproject.
1. UsingMicrosoftExcel,createanewblankworkbook.
2. SavetheworkbookasProjectEXL-6PayrollRegisterinyour"Excel"folder
under "The Hub" folder.
3. KeythedataasitappearsinFigureEXL-6.1intothespreadsheet.Unless
otherwisenoted,thefontshouldbesettoArial10point.
4. UseAutoFittoadjustthewidthofthecolumnssothatdatafitsappropriately.
5. Calculatethepayrollforthefirstemployeeasfollows:
· EnteraformulaincellF6thatwillcomputetheOvertimePayRate.Employees
earn1.5timestheirRegularPayRatefortheirOvertimePayRate.
Hint:
=D6*1.5.
· EnteraformulaincellG6thatwillcomputetheRegularPay.
Hint:
=C6*D6.
· EnteraformulaincellH6thatwillcomputetheOvertimePay.
Hint:
=E6*F6.
· EnteraformulaincellI6thatwillcomputetheGrossPay.
Hint:
=G6+H6.
· EnteraformulaincellJ6thatwillcomputetheFederalIncomeTax.
Hint:
=I6*25%.
· EnteraformulaincellK6thatwillcomputetheStateIncomeTax.
Hint:
=I6*5%.
· EnteraformulaincellL6thatwillcomputetheFICA.
Hint:
=I6*6.2%.
· EnteraformulaincellM6thatwillcomputetheMedicareTax.
Hint:
=I6*1.45%.
Payroll Register
For Evaluation Purposes Only