Data analysis project

Data analysis project 150 150 Affordable Capstone Projects Written from Scratch

Project Scenario

Ian DuPerre is responsible for conducting annual internal audit procedures over payroll for Bibitor, LLC. While tying a sample of paychecks back to employee digital time cards (DTC), Ian discovered two where the DTC looked mysteriously similar. Further testing revealed these two time cards had be modified and ultimately lead to the discovery that one of those paychecks went to someone who no longer worked for the company. Ian brought this to his superior, Taylor Gale, who decided to expand the audit to determine if there were any other paychecks to individuals who were not actually employed by the company.

Unfortunately, Ian does not know how to use business intelligence technology to efficiently and effectively analyze entire populations of data and therefore is unable to see the project through. As a result, Taylor asked Bibitor’s external audit firm to provide a risk advisory services associate to assist with the data analytics on this engagement. Your firm sent you.

Taylor asked you to plan a project that will examine the payroll check population for the fiscal year ending June 30, 2016. Through other procedures Ian performed, Taylor has deemed the following risks to be low and areas you do not need to focus on: salary, hourly rates, and overtime hours. The main concern is whether there are any employees that are on the payroll that shouldn’t be. And if so, how much money has the company paid that shouldn’t have been paid so they can recoup their losses and turn over any applicable documentation to law enforcement.

In order to get you started, Taylor has provided the company’s payroll manual and flowcharts (see PDF attachments). He has also directed you to the company website where you found the following information regarding Bibitor, LLC:

“Bibitor LLC is a retail wine and spirit company, with 79 locations within the fictional state of Lincoln. They sell spirits and wine products. Bibitor, LLC has been serving the area for over 50 years. Their wine and spirits selection is hand-picked and focused on value. Their employees are trained as personal beverage concierges and provide unmatched service to all customers. There are over 10,000 brands throughout the organization.

Depending on the year end, sales can range from 420-450 million dollars and cost of goods sold can range from 300-350 million dollars. The total number of records in the dataset is 15 million. This includes sales transactions (12.5 to 13 million records) purchase transactions (2.3 to 2.5 million records) and inventory. Bibitor employs 1,000-1,100 individuals between their corporate headquarters and 79 locations.”

He also noted that if needed he can provide financial statements, demographic information for the state of Lincoln, and industry data for Dunn & Bradstreet.

Mission requirements: 

Taylor wants to make sure you understand Bibitor as a company in the wine and liquor industry has requested that you provide the following planning related documentation before giving you access to the data you want:

  1.    A brief summary (no more than 2 paragraphs) of your understanding of Bibitor, LLC, the industry, and the company’s payroll process (1 points)
  2.   A bullet point list of your understanding of the project’s purpose and desired deliverables (2 points)
  3.   A formal data request memo dictating what data is needed to complete this project (5 points)
  4.   An action plan detailing the types of data analytic tests you plan on running in order to complete this project (7 points)

Taylor will review, revise, and approve these items before providing you with the data.