MGA 311 Accounting Information Systems Professor Metzger

Spring 2016 Project #3

Point Value: 50 points

Assignment Type: Individual

IDEA

You will be utilizing IDEA to complete a review of the attached data files. You will be importing the data files into a new project in IDEA, merging the data sets, and answering questions on the data set.

Attached are the following data files:

  1. Payroll Master Records
  2. July Pay Records

You will need to select the attached files that corresponds to the last number in your UB student ID number. You should be importing both the ‘Payroll Master Records’ file and the ‘July Pay Records’ file with the last number of your UB student ID number.

Steps:

·  Open IDEA and create a new IDEA Project, named: Project3_Last Name_First Name

·  Import the data files provided above into the project you just created

·  Verify that you have successfully imported the above data files by verifying the following:

o  Payroll Master Records – 280 records, Total Salary Amount $33,152,257.00

o  July Pay Records – 263 records, Total Gross Amount $1,604,236.87

·  Summarize the July Pay Records to obtain totals for each employee who received pay in the time period

·  Then create a comprehensive database by using a ‘Join’ of the Summarized July Pay Records and the Payroll Master Records file. Be sure to not duplicate field names on the comprehensive database.

Required:

·  Based upon the comprehensive database you prepared above, you will need to answer the following questions about the data through use of IDEA functions and features:

1.  What is the total of the ‘Salary’ field for employees within the ‘Operations’ department?

2.  Which department has the highest average salary?

3.  Are there any duplicate employee numbers (EMPNO)? If so, please include all employees that match in the exported file.

4.  Extract all records that meet the following criteria: Sum of Taxes is $0 and Sum of Gross is greater than $0.

5.  What employees received pay in July that were not included in the Payroll Master file?

6.  What employees received ‘gross’ pay in excess of $5,000?

7.  Recalculate ‘Net pay’ by taking ‘Gross’ minus ‘Taxes’, ‘Insurance’, ‘Credit Union’, and ‘Overtime’. Then calculate if any differences occurred based upon your recalculated ‘Net pay’ vs. the one included in the original data file. Extract all employees with an inaccurate net pay per your recalculation.

8.  From the employees extracted in Q7, perform another extraction of employees that had a net pay difference of greater than $1.00.

9.  For each grade level, what is the maximum ‘Salary’ amount?

10.  What employees have the same value in the ‘Name’ field?

For all of the questions listed above, you will prepare an ‘export’ (Home, Export, Microsoft Excel 2007-2010) of the answer you obtained. You will need to support all of the questions above with an exported data file. Once you have completed all 10 questions above and exported the databases into Excel, you will then merge all of the Excel files into one workbook for submission to me via UBlearns. Please label each worksheet with the corresponding question number.

By default, IDEA will save exports to the C drive of the computer you are working on:

Libraries\Documents\MY IDEA Documents\IDEA Projects\Assign 2_Last Name_First Name\Exports

This is what my path looks like: C:\Users\metzgerl\Documents\My IDEA Documents\IDEA Projects\MGA311_Project3\Exports.ILB

You will need to find the path described above and copy the exports you created onto your flash drive and/or email the files to yourself. Please do so before logging off of the computer, especially if you are working on a computer in one of the SOM labs. The SOM lab computers (in the ARC, CoLab (Jacobs 345), and Jacobs 214) will not allow things to be saved to the C drive.

NOTE: In IDEA there may be more than one way to obtain the same answer: this assignment is meant for you to become comfortable with the various ways to manipulate, summarize, and extract the data.

Things to note:

·  Please use the IDEA lab lessons as a reference. The lessons provide you with step by step instructions on how to do such things as: import data, how to create a new project, how to summarize the data, how to create an extraction, etc.

·  To save your IDEA project if working in a computer lab:

o  By default, the project will save itself to the C drive under the following path

Libraries\Documents\MY IDEA Documents\IDEA Projects – whatever you named the project

This is what my path looked like: C:\Users\metzgerl\Documents\My IDEA Documents\IDEA Projects

o  Find your project and copy it to your Flash drive or email it to yourself (I am not sure about the size of the file so please DO NOT log off of the computer until you receive it in email if that is the option you select)

o  When you want to work on the project again, you will go to Home/Select/External Projects and then browse to find your project.

Required Deliverables to be submitted:

  1. UBlearns Submission:
  2. Excel worksheet (Excel file) of your exported IDEA files, merged into one workbook. Please make sure that you have labeled your worksheets with the corresponding question number as defined above. You will upload this file to UBlearns by the start of your assigned class time. Be sure to complete the correct version of the project based upon the last digit of your UBIT number.

This project is worth 50 points and is to be performed individually. As there are 10 questions included in the project, each question will be worth 5 points. Since you are submitting Excel files, please review your work prior to submission to ensure that the exported file is sufficient to answer the question as listed above. Additionally, it includes enough field names so that I know who or what you are referring to.

The deliverables should be high-quality and professional in nature, similar to what would be presented to your Management team.

This is an individual assignment, therefore it is expected that you will submit your own, original work product. Students who submit identical or partially identical work products will receive a grade of zero.

Please note: assignments MUST be submitted by the start of your assigned class time on the due date. Late assignments will not be accepted.