4113 Data Analytics Project

Two years ago, Panther Suppliers upgraded its general ledger system. The Controller of Panther has some concerns with respect to restrictions on various accounts and has hired you as a consultant to evaluate the effectiveness of the system. You have been provided with a very large data dump to a flat text file (testData.txt), which includes the combination of various queries run by the Controller. The data has four columns: (1) Account, (2) Ledger ID, (3) Dollar Value, and (4) Currency.

The controller has identified 5 accounts (four liability and one expense) that she wishes you to evaluate. Each of these accounts and associated restrictions are detailed below. She also has one final request related to foreign currency, which is listed last.

Account 202100 (liability)

Description: This is a general payables account for transactions less than $1,000, which only require supervisor approval. The controllers query should have only pulled invoices to the account (i.e., credits), so there should be no positive (debit) values appearing in 202100.

Tasks:

Determine whether the account has any debit (positive) entries. If so, continue with task 2 and 3.

Determine the number of debit entries. In addition to the raw number, express this number as a percentage of the total number of entries in the account.

Determine the total dollar amount of debit entries in this account.

Determine the number (if any) of invoices posted to this account that exceed $1,000 (credits to the account that are less than -1,000, such as -1050, -1100, etc.).

Account 202200 (liability)

Description: This is a maintenance-specific payables account that should only include purchases made with the maintenance purchasing system. Entries booked by this system have ledger IDs that begin with 7, 8, or 9. Note that any ID beginning with 7 is an intercompany purchase that should also appear in account 502200 (discussed below).

Tasks:

Determine whether the account has any manual entries (ledger IDs beginning with 1). If so, continue with task 2 and 3

Determine the number of manual entries to this account. In addition to the raw number, express this number as a percentage of the total number of entries in the account.

Determine the total dollar amount of manual entries to this account.

Account 203100 (liability)

Description: This account holds other accrued liabilities, which represent estimates that are updated each month. To facilitate evaluation of these estimates, the company requires the ledger ID of entries of this type to begin with either a 101 or 102.

Tasks:

Determine whether the account has any entries with ledger IDs that begin with something other than 101 or 102. If so, continue with task 2 and 3.

Determine the number of invalid entries to this account. In addition to the raw number, express this number as a percentage of the total number of entries in the account.

Determine the total dollar amount of invalid entries to this account. Since this account houses both debit and credit adjustments, express this dollar amount in absolute value, and then express the absolute value as a percent of the total absolute value of all entries in the account.

Account 204000 (liability)

Description: When setting up the accounting system, the firm's implementation team erroneously linked the invoicing system to this unearned revenue account. Instead of correcting the problem, the company accountants have instead been manually closing out unearned revenue periodically. The controller worries this manual fix has resulted in errors. The invoicing system ledger IDs begin with "100" and the manual adjustments begin with "105". Note that all invoicing should be done in USD.

Tasks:

Compare the total dollar value of transactions with ledger IDs starting with 100 to the total dollar value of transactions beginning with 105.

Verify that the account only has transactions denominated in USD.

Account 502200 (expense)

Description: As referenced in the description for account 202200, this account houses intercompany purchases and should include the debit side of all entries in 202200 with ledger IDs beginning with a 7.

Tasks:

Determine whether any transactions from 202200 are missing in 502200. If so, continue to task 2 and 3.

Provide the number of and the total dollar value associated with the missing entries.

Provide a list of these entries in csv form (separate from your other analyses). Include the transaction ID, the dollar amount, and the currency.

In addition to the account-specific tasks, the Controller told you that the implementers of the system did not activate automatic foreign currency translation: while entries correctly identify the currency, the system fails to treat Euros differently from US Dollars. The Controller has been adjusting each account manually. Therefore, for any account where you identify transactions denominated in Euros, provide the total dollar value of transactions in Euros as well as the needed foreign currency translation (assume the exchange rate is $1.15 per Euro).

Requirements:

To accomplish the above tasks, your group of 3 to 4 students should prepare a Jupyter Notebook in Python 3.x that processes the data file (testData.txt), and displays the results of your analysis. You will create your Jupyter Notebook on GSUs DICE cluster by accessing the following link and logging in with your student ID: https://dice.gsu.edu:8000 You should then click Start My Server and select a job profile: 1 core for 12 hours should be sufficient per session, but you can access additional resources for shorter time periods from the dropdown.

The data can be found in ACCT 4113 - Assignments - Data. This directory also contains a file called smallData.txt for use in testing your script.

Before starting your project, you should complete the tutorial here: Python Tutorial for Data Analysis Project.ipynb (or by navigating to ACCT 4113 - Assignments and clicking on the file). To run a code block in the tutorial, place your cursor inside the code block and click the Run button near the top of the page.

To create a new Jupyter Notebook for your project, navigate to ACCT 4113 - AssignmentSubmissions, click New in the upper right-hand corner, and click Python 3. To create a new code block in your Notebook, click the plus (+) symbol in the upper left to insert a cell and set the dropdown box in the center of the menu bar to Code. To create a new text block, set the value in the dropdown box to Markdown. Please title your Notebook and include a text block containing the names of all group members that contribute to the project.

Each group must turn in the following three items through one of the group members AssignmentSubmissions folder on DICE:

A Jupyter Notebook detailing responses to each account-specific task and the code used to perform your analysis. The Notebook should look professional and clearly communicate all findings, but keep the responses detailed and brief.

A spreadsheet or csv file for the final task on Account 502200.

A separate document reporting the specific contributions and billable hours (time spent) by each group member.

Please let me know by e-mail in which directory I can find the files for your group.