Case Study Idea

Part/Purpose/Task

Procedure

Items to print and discuss

Import the files from the classroom into IDEA software (review the video Importing an Excel spreadsheet)

· Make sure your files are downloaded and saved from the classroom.

· Drop an drag the files in one by one

· Be sure you check the box to select the “first row is field names”

N/A

Part 1a/to see if employees are being paid properly

· Close all of the other files and only leave open the file called Payroll EMPNO, sort (double click on the file name) the file by Gross Pay in descending order

· Using the same file, sort the file by Medicare Taxes in descending order

· Using the same file, sort the file by Net Pay in descending order

· Print screen the sorted report for gross pay and discuss the results

· Print screen the sorted report for Medicare and discuss the results

· Print screen the sorted report for net pay and discuss the results

Part 1b/to see if any suspicious transactions exist (review the video for summarization)

· You are going to run an analysis to look for any suspicious transaction. Close all files used in Part 1a. Open the Payroll EMPNO file, click on Analysis/Summarization by EMPNO include all fields on the right (check all of the boxes), select Sum stats and create a new database called Payroll Sum by EMPNO

· Sort by No_ of_ Recs showing the highest number first to see if anything looks unusual (by clicking on the blue numbers you can see additional detail)

· Print and discuss the results found

· Discuss any possible issues you may have found and if you feel further investigation is needed

Part 2/to see if employees are being paid properly (review the video of direct extraction)

· Make sure the Payroll EMPNO file is open (close any other open files), you are going to investigate any tax exemptions by reviewing gross and net pay using Criteria: Gross_Pay =NET_Pay

· Sort by EMPNO

· Print and discuss the results found

· Discuss any possible issues you may have found and if you feel further investigation is needed

Part 3/to see if employees being paid really exist (review the video for joins)

· Close any open files

· The July Pay Records and Payroll Master Record files should be loaded into IDEA already on the left side

· Make sure the July Pay Records data is open on the screen. Click Join, the Primary database is set for you, click Select to add in the Payroll Master file.

· Click Match and select EMPNO for Primary and EMPNO for Secondary and click ok

· Click Records with no secondary match and click ok

· Print and discuss the results found

· Discuss any possible issues you may have found and if you feel further investigation is needed

Part 4/ to see if employees being paid appear to be actual employees

· Close out all databases

· Click on Payroll EMPNO to open

· Clear out any old criteria (right click on criteria and select clear)

· Analysis/Summarization by Pay_date, summing Gross_Pay and name the results Payroll Sum by Pay Date

· Click on Pay_date to identify any off-cycle payments

· Click the blue numbers for more detail

· Print and discuss the results found

· Discuss any possible issues you may have found and if you feel further investigation is needed

Part 5/ to see if terminated employees are being paid

· Close out all databases

· Open the Payroll Master file

· Perform extraction using the Direct icon (on terminated employees) with criteria: TERM_DATE <> " " (you do need the “ “) name the extracted file Term Employees

· With the Term Employees file open on the screen, join the July Pay Records file (secondary) with Term Employees (primary), list Matches only, and match key field as EMPNO. Call the file July Term Employees

· Print and discuss the results found

· Discuss any possible issues you may have found and if you feel further investigation is needed

Part 6/ to see if excessive OT is being paid (review the video for appending files and the handout on modifying fields)

· Close out all databases

· Open the July Pay Records file

· Data/Append a field (using field manipulation) called PCT_OT that calculates the percentage of overtime dollars to gross pay dollars (parameter: OVERTIME/GROSS*100). This will be a virtual numeric field with 2 decimal places

· Double click on PCT_OT to sort the field highest to lowest

· Print and discuss the results found

· Discuss any possible issues you may have found and if you feel further investigation is needed

History File

· Print history for each of your files (left side above criteria) as documentation for your report. On the Payroll EMPNO file only expand the boxes by clicking on the + signs before you print

· Print History file as exhibit